Search (1 Viewer)

[Thedon123]

i have a text where the user enetrs a code. what i need to do is to check that the code he has entered is correct by checking it with the codes that are held in a query. ho would go about doing that.

 

[Mile-O]

Say the code is entered into a textbox, txtCode

If IsNull(DLookup("[Code]","qryCodes","[Code] = '" & txtCode & "'") Then
    MsgBox "That is not a valid code."
    Exit Sub
End If

 

[Thedon123]

soory mate i understand the code. but do i have to declare txtcode and code before this code.

 

[Howlsta]

 is equivalent to whatever the field is called in your query, substitute txtCode for whatever your textbox is called or alternatively rename your textbox to txtCode.

So, no you don't have to declare these.

Rich

 

[Thedon123]

thanks a lot the help is really appreciated

 

[Pat Hartman]

The suggested edit isn't going to do anything to prevent bad data from being stored.

You need to put the code in the BeforeUpdate event of the text box:

If IsNull(DLookup("

","qryCodes","[Code] = '" & txtCode & "'") Then
    MsgBox "That is not a valid code."
    Cancel = True
End If


"Cancel = True" cancels the update event so the bad data cannot be saved.

The easiest and most user friendly answer is to use a combobox with its limit to list property set to yes.

 

[Thedon123]

Thanks for the help pat.

THe problem is i have atleast 69000 records. the combo box does not display all of these. what i want to do is to let the user enter the value in a text and then check it against a query to see that it exists. the code above does not seem to do that. is there another way. i can check against a table also if the query is causing the error.

 

[Pat Hartman]

What code are you using?

What event is the code in? If the code is not in a BeforeUpdate event where you can cancel the update, you can't ever prevent bad data.

What happens when you run it?