query equals text box

[emshim]

How can i show command button if text box equals one or more query values?

Hi,

I have had a look at quite a few threads, but cannot find what i need. I am looking to show a command button if the value typed into the text box equals one of the values in the query. I understand how to show/hide a command button due to another post, but it is getting this to happen IF the text box matches one or more of the values in the query.

I have txtCustNo as the text box to type a number into
I have qryCustNo as the query name
i have cmdFindCust as command button

Should i have the code under the "after update" part of the text box also?

Thanks for your time,

Emily

 

[antoka05]

Try with this code... I hope it's what you need:

   Dim rs As Recordset
   Dim query As String
   
   Set rs = New ADODB.Recordset
   query = "select count(*) from [qryCustNo] where qryCustNo.<myfield> = """ & Me.txtCustNo & """"
   recordsMatchingText = 0
   rs.Open query, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
   If Not rs.EOF Then
      recordsMatchingText = rs(0)
   End If
   rs.Close
   MsgBox ("Found " & recordsMatchingText & " records")

In your VBA window please choose menu Tools, then Reference and then select 'Microsoft ActiveX Data Objects 2.x Library'

Regards,
Antonio

 

[emshim]

Thanks for the reply. Although i can see what this is ment to do, for some reason, i cannot get it to work. The message box appears with 0 records every time. I do need it to see if what is in the text box matches any value in the query, but if it does, i need the command button to show, and if not, i need it to go to a message box. I will check again to make sure i have copied your coding correctly.

Thanks again for your time,

Emily

 

[emshim]

I think i have got it! i just had to change when the code was being executed and hey presto! it works. Thanks very much Antonio, you have been a great help. I will probably be back on with another problem soon anyway!

Emily

 

[antoka05]

Glad to know it helped you.

Regards,
Antonio