Checking value in another column....

[steyn]

Hi experts

I am trying to make a web-based front-end for one of our programs. I'm running into a problem. Here's a sample of one of our tables:

Code:
CaseNo ChargeCount ArrestCharge FiledCharge
123 1 13 13
123 2 14 14
199 1 03 03
The ChageCount column cannot have nulls and must be unique. is there a way when I'm adding a new charge, i can check to see if a chargecount exists? For example, if inserting a new record, it will first check if there is a chargecount, if there is not, it will make it 1. if there is a charge count, it will +1 to the existing chargecount. does that make sense?

thanks a lot in avance -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[packer]

Run select to see if a record returns

Here's the Code:
select caseNo, max(ChargeAccount) as chargeAccount from tbl_TableName where caseNo = "whatever value"
if rs.EOF and rs.BOF then
--insert the record with a chargeaccount of 1
else
--insert the record, adding 1 to rs("chargeAccount")
end if

bye for now -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[algo]

I think you can use autonumber in access for it? Not sure but think it would work. -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[salisbury]

Originally Posted by algo

I think you can use autonumber in access for it? Not sure but think it would work.

What algo is saying is chargeaccount has to be unique but i think he means based on the case NO because in his example you can see chargeaccount 1 2x. -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[danielle]

something like this:

Code:
strSQL = "insert into tbl_TableName (caseNo, chargeaccount) values ('" & request.Form("caseNo") & "', " & rs("chargeAccount") + 1 & ")"
'this will take the chargeAccount value returned from the recordset and add 1 to it to get a new number. -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --