Compare a text field to a table

[sroot]

Hello. I have a form that users put in information and then it updates to a table. It works fine but i would like to add something. Right now there is a text box called item that the user puts in the item number. The problem is that sometimes they put in a wrong number. What i would like to do is have it when they hit the update button to compare that item number to a table that lists all of them. so if the text box doesnt equal one of the numbers in the table dbo_item and the field itm_num, i want it to give them a message box with an error. Is this possible? Thanks!

 

[plog]

Possible, but why not use a drop down? That way they can only select valid values to begin with. No validation required.

To do it your way, you add some code to that button's OnClick event that does a DCount (http://www.techonthenet.com/access/functions/domain/dcount.php). If it returns a number greater than 0, then your data exists, if not it doesn't.

 

[sroot]

I wanted to do a drop down but my boss thinks some people might just select any random one so that wouldn't work. I would have to have it so that whatever number they put in is a valid number in that table

 

[sroot]

so how would i get the DCount to work with the text box?

 

[plog]

You would use the text box value in the criteria of the DCount. Give it a shot and post back here what you come up with and any issues.

 

[sroot]

This is what i have so far just to test it but its not really working. I have a feeling i am close. Any ideas?

Private Sub Command31_Click()

If DCount("itm_num,", "dbo_item", Me.Item) > 0 Then
MsgBox ("good")
Else
MsgBox ("bad")
End If

End Sub

 

[plog]

The last part of the DCount is criteria. You can't just throw a value in there, you have to build an expression for it to evaluate. Look at the first Example on this page to see what I mean:

http://www.techonthenet.com/access/functions/domain/dcount.php