Verifying if records exist??

[LisaP]

I have created a database for logging computer fault calls. I have 1 table for holding call details and 1 that lists serial numbers that are on contract.

I have created a form based on the call details table [Calls] and I have a text box on this form [SerialNo] for the user to enter serial number.

Now to get to the point :^) After the user enters a serial number, I would like access to check to see if the entered serial number is listed in the Serial No table (thus being on contract) if so then display a message box stating the serial number is on contract, if not then display msgbox advising and asking if want to add serial number to contract.

I think....that i need to use the DLookup function but have been playing around with this all day (without success, hence the post!).

Can anyone provide a solution or a better way of verifying serial numbers??? I did think of a quick and dirty way (change text box to a combo and use the serial no table as the recordsource) unfortunately there are 1400 machines on contract soooooo it would be a pretty big combo!!

Any ideas please help (i don't think i'm going to have any hair left at this rate!).

P.S. Ian, if you read this, thanks for all your help with this - I have managed to import the data after all - Ta.

 

[axa]

Lisa

Another 'quick and dirty' way of doing this would be to use DLookup()'s cousin, DCount(). you can use this to check how many entrys of a serial number are in the table, in other words if it returns a count of 0 for a serial number, it is not in the table. So you could use something like this:

If DCount("SerialNoField","SerialNo","[SerialNoField]='" & txtSerialNo2Check & "'") > 0 then
&nbsp&nbsp&nbsp&nbspMsgBox "Serial No found: On Contract"
&nbsp&nbsp&nbsp&nbsp'--code to do whatever when on contract
Else
&nbsp&nbsp&nbsp&nbspIf MsgBox("Serial No NOT On Contract. Do you wish to add it to the contract?",vbYesNo + vbQuestion) = vbYes Then
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp'-- code here to add serial num to contract
&nbsp&nbsp&nbsp&nbspElse
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp'-- whatever code here to handle non addition of non contract serial num
&nbsp&nbsp&nbsp&nbspEnd If
End If

Hope that helps

axa

[This message has been edited by axa (edited 08-10-2001).]

 

[shacket]

What about this:

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("YourTableName",dbOpenDynaset)
rs.FindFirst "[SerialNoField] = " & Me.SerialNo
If rs.NoMatch Then
<<Insert what to do if it does not exist>>
Set db = Nothing
Set rs = Nothing
Exit Sub
End If
<<Insert what to do if it does exist>>
Set db = Nothing
Set rs = Nothing
End Sub

 

[LisaP]

Guys,

Thanks for the advice, but I am having problems with both options, I think the problem is that there is only 1 option when serial number is listed (i.e. message box stating its OK) but if it isn't on contract I need to display a msgbox asking if want to add Yes/No. If No return to textbox, If yes then open a form to allow serial number to be added...............

I'm sorry my head hurts so much with this that I can't seem to think anymore.