Form Seach Box-all attempts have failed.

[leamas]

I've googled how to input a search box into my form and all methods have failed or turned up an error of some kind.

I simply want to have a search box on my form that draws from one field, a unit #, and fills the form with the other fields corresponding to that unit #.

I've tried the command button wizard and it just gives me one button that pops up the 'find' box. This has failed, along with my VBA strings.

If anyone can suggest a solution, I will very gladly and appreciatively give it a try.

Thank you.

[pbaldy]

If the form is bound to the table, have you tried the combo box wizard, choosing the third option, "Find a record..."?

[leamas]

I have tried this- in my table, i have test unit #'s that i am putting into the 'find' box and each time i receive an error.

What I would like is to have the text box next to the search button, rather than one lone command button.

Thanks for your reply!

[Missinglinq]

You can do this will one of the built in functions that the Combobox Wizard generates. Here's a step-by-step, although you may have done some of this already, especially # 1 & 2:

1. Create a form using your table/query as the Record Source.
2. Go to the Field List and drag the fields you want retrieved onto the form
3. Place a combobox on the form.

When the Combobox Wizard comes up:

• Select "Find a record on my form based on the value I've selected..."
• Next
• Select the appropriate identifying field to appear in the Combobox Next
• Size the Combobox column
• Next
• Name the Combobox
• Finish

Now the user can use the dropdown arrow of the combo box to retrieve the data or simply start typing the identifying data in and the combo box will start to autocomplete the entry. When the correct entry is found hit <Enter> and the appropriate data should be retrieved.

If you insist on using a Textbox, you could use the code/macro created in the Combobox AfterUpdate event in the AfterUpdate event of the Textbox, but why reinvent the wheel, and have a Textbox and a Command Button instead of simply using a Combobox?

Linq ;0)>

[pbaldy]

What I would like is to have the text box next to the search button, rather than one lone command button.

I didn't say command button, I said combo box.

[JeroenMioch]

Well most of my databases have such a field. Its not easy material but i think you can copy it to suit your needs.

I punt my code behind the afterupdate of the textfield :

Private Sub YourUnitNumberField_AfterUpdate()
    FilterForm
End Sub

Then put the filterform code in the VBA of your form like this :

Private Sub FilterForm()
    Dim strFilter As String
    Dim blnFilter As Boolean
    
    blnFilter = False
    
    strFilter = " 1=1 "
    If Me.YourUnitNumberField <> "" Then
        strFilter = strFilter + " and UnitNumber like '*" & YourUnitNumberField & "*'"
        blnFilter = True
    End If    If blnFilter Then
        Me.Filter = strFilter
    Else
        Me.Filter = "1=2"
    End If
   
    Me.FilterOn = True
End Sub

I presumed UnitNumber is the field in your table, and YourUnitNumberField is the name of the textbox your searching with.
Edit these to fit your own table field and form field.

Let me know if it works for you

Greetings, Jeroen

[leamas]

You can do this will one of the built in functions that the Combobox Wizard generates. Here's a step-by-step, although you may have done some of this already, especially # 1 & 2:

1. Create a form using your table/query as the Record Source.
2. Go to the Field List and drag the fields you want retrieved onto the form
3. Place a combobox on the form.

When the Combobox Wizard comes up:

• Select "Find a record on my form based on the value I've selected..."
• Next
• Select the appropriate identifying field to appear in the Combobox Next
• Size the Combobox column
• Next
• Name the Combobox
• Finish

Now the user can use the dropdown arrow of the combo box to retrieve the data or simply start typing the identifying data in and the combo box will start to autocomplete the entry. When the correct entry is found hit <Enter> and the appropriate data should be retrieved.

If you insist on using a Textbox, you could use the code/macro created in the Combobox AfterUpdate event in the AfterUpdate event of the Textbox, but why reinvent the wheel, and have a Textbox and a Command Button instead of simply using a Combobox?

Linq ;0)>

Any thoughts on why it bounces back with "invalid outside procedure"?

Thanks though this works great.

[leamas]

Thanks Linq, reinventing the wheel is very time consuming! lol appreciate all your replies!

[Missinglinq]

Any thoughts on why it bounces back with "invalid outside procedure"?

Should have nothing to do with the step-by-step I gave you. This error means that there is something, maybe code, maybe a Command, maybe a variable declaration, in the code module but not within an event Sub. Usually means that some stray bit has been has been left where it shouldn't be, sometimes by accidentally deleting only part of a Sub.

...If Me.YourUnitNumberField <> "" Then

Jeroen, you should be aware that your line of code above does not guarantee that YourUnitNumberField has data in it, which I'm guessing is your purpose. Empty Controls in Access Forms seldom contain Zero-Length String (ZLS), which is all that your code is checking for. Instead, they usually are simply Null.

To check for both ZLSs and Nulls, you need to use something like

If Nz(Me.YourUnitNumberField, "") <> "" Then

Linq ;0)>

[leamas]

Linq- I have incorporated the combo-box into my form. I seem to have hit another kink yet to be ironed. The combo-box contains Unit #'s. When I search for a unit number and update the details within the form, it changes the unit number in my table. Perhaps by some miracle, do you know how to correct this?

Thank you!!!

[pbaldy]

Make sure the control source of the combo is blank.

[leamas]

Make sure the control source of the combo is blank.

pbaldy,

Thank you for this. I had originally had the combo blank, and absentmindedly assigned it to a field. I appreciate your help.

Cheers

[pbaldy]

No problem.