Search/Match a Field

[ChronicFear]

Hello All,

I have searched these forums for multiple days now, and attempted to reverse engineer just about every post on searching from forms so that it works for my database, but, unfortunately, I'm simply not good enough at VBA to get it to work. I would love it if someone could please help me, or at least point out which parts of existing posts I can combine to get what I need.

What I have is a form where people enter various information about a transaction. One of the fields is a customer ID number. After they type in the number, I would like the db to search the table with all my customer information (tblBorrowers) looking for the ID number that was just typed in. At this point, one of two things should happen:

1) Match: If the customer is already in the system, I would like a command button to open the customer information form (frmBorrowers) populated with that customer's information and allow the user to update the information if needed.

2) No Match: The same command button mentioned above opens the customer information form in add mode so that the user can input all the new information.

I can get everything to work fine except the search function. I've also tried permutations of dlookup, but no success there, either.

Also, I'm fine with the search requiring an exact match instead of returning results that are similar, so no need to worry about that.

I sincerely appreciate the time you've spent helping me.

Thanks,
CF

 

[rainman89]

what you could do is use a combo box and the not in list feature of the combo. the button would be tied to the combo to open the form and if the button didn't have a value, then add a new one.. have you looked into the "not in list" function?

 

[ChronicFear]

I didn't even know that was an option. It works great now!

Thank you so much.

As a side note, do you know if it's possible to format the combo box so that it doesn't actually have a drop down section? My thinking is that since we have hundreds of customers, I don't even want users to have the option of clicking and wasting time scrolling through all the options. I'd rather they just type it in. Thanks!

 

[rainman89]

glad to help