Search based on Text Box Entry

[russdwright]

I have a form based on a table called 'fAppInfo'. In it are the following fields:

AssetID (PK)
AppName
CIID
Contact1
Contact2
Contact3
Contact1Email
Contact2Email
Contact3Email
Contact1Phone
Contact2Phone
Contact3Phone

I would like to have these fields remain blank on the form until I input the Asset ID and either click on a Search button or press Enter. Right now, all it does is scream at me since it thinks I'm trying to change the value.

I was able to setup a Combo Box but I'd rather not have someone looking through a list of (right now) 360 entries.

Any advice/input is appreciated!

[aytee111]

Do you want the user to select a record based on the Asset ID? Will they know what it is?

Make an unbound combobox for the id, then in the AfterUpdate event:
Me.Filter="AssetID=" & Me!cmbAssetID
Me.FilterOn=True
Me.Requery

In the OnOpen event for the form:
DoCmd.GoToRecord,,acNewRec

[russdwright]

OK, that seems to work somewhat. Now, I need the other fields to load based on what I enter as the AssetID.

And, to answer your question: yes, they will know what the AssetID is.

[aytee111]

The fields need to be bound to the record source, then when you filter to that record the data will show.

[russdwright]

The fields need to be bound to the record source, then when you filter to that record the data will show.

By Record Source, you mean Control Source?

[aytee111]

"Bound" fields have a value in their control source which matches a field in the form's record source.

[russdwright]

I have those already setup, but when I select/enter a known AssetID nothing loads

[aytee111]

Not possible! If you open the table and filter on an asset id then the record shows. You are doing the same thing here, filtering on a value. Post your code. Are you using the right field name for the filter?

[russdwright]

OK, I had the wrong labels entered.

Do I enter "DoCmd.GoToRecord,,acNewRec" directly into the event, or do I use one of the builders to enter this code?

[aytee111]

Type it in directly - from Properties > OnOpen > [Event Procedure] > click the 3 dots to take you into VBA. Typing it will ensure that the correct syntax is followed (for instance the correct number of commas).

[russdwright]

OK, that got it! Thanks! Still not a fan of using the ComboBox (wanted a text box with a Search button), but it works smoothly enough.

[aytee111]

Keep on using them, you will find that the three lines of code required for a combobox is far simpler than buttons and boxes and all the code that those require. The only time a text box is better is when a user enters a partial entry, such as Smith. However, the filter code will go in the AfterUpdate event, no button needed.