Help with a search feature or combo box

[jmanis]

Hi. I am somewhat new to Access but have a pretty basic knowledge of the program and how to do the basics. I am trying to make a database for someone at work to keep track of their volunteers.

On the form that I am having trouble with I am trying to have some kind of option for the user to select or search to view a volunteer record. I have managed to get a combo box to show the names but when I select one, it doesn't update the records in the form below. Then I tried a search box using some code I found on a thread on here but that is not working either. Here is the code and the error I am getting.

The code reads as follows:

Dim strCriteria as string
strCriteria="EmployeeName='" & Me.Text21 & "'"
DoCmd.ApplyFilter , strCriteria

Error reads:
Run-time error '3075':
Syntax error (missing operator) in query expression 'Last Name='___"



The ___ is whatever last name I try and search for.

If anyone can provide me with some guidance or code that would be greatly appreciated. Thanks.

[NTC]

you don't need coding experience for this functionality. Assuming your main form works right - and you are able to click thru the records to see the data....then you should be able to put that form into design view, add a combobox to the header of this form - - and during the wizard that will launch be sure to select the option to find a record based on the select (as this option may not be the default)

you can delete that combobox that is not working correctly...

[jmanis]

The wizard only has two options. One to use a table/query which is the one I used to get the names to show up before and the other option is for me to specify what values I want. Am I missing something here because the second option doesn't seem like it would be the one you are talking about.

[NTC]

The Access2007 Combo Box Wizard offers 3 choices:
*look up values in table/query
*type values
*Find a record

it is the 3rd choice that you want.

if you are certain that you are in a form, and using a combobox...then perhaps you should google about to see if there is a hotfix or something... or perhaps you don't have the current version - - it should be SP2

but the combobox wizard always has offered 3 choices.... I suppose theoretically if a db has only 1 table then the look up option and the find a record option are the same thing.....are you a single table db?

[pbaldy]

The wizard will not always show the third option. Never if the form is unbound, and in my experience not if the form is bound to SQL rather than directly to a table. The code it will create looks like:

    Dim rs As Object

    Set rs = Me.Recordset.Clone

    rs.FindFirst "[ID] = " & str(Me![Combo16])
    Me.Bookmark = rs.Bookmark

For your names (ie a text value), you'd probably need to add the single quotes around the value, similar to how you did above.

[jmanis]

pbaldy, thanks for the advice and the sanity check. I gave your code a try and this is what I wound up putting in:

Private Sub cboSelect_Click()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "Contact Name=" & Str(Me!cboSelect)
Me.Bookmark = rs.Bookmark
End Sub

The field I am trying to select the records is called Contact Name and the combo box is called cboSelect.

After I put in this code I received:
Run-time error: '13':
Type mismatch

Any ideas?

[pbaldy]

Did you notice this:

...you'd probably need to add the single quotes around the value, similar to how you did above.

Also, because of the inadvisable space, you'll need to bracket your field name.

[jmanis]

I did see that and I did try it with the quotes. I just tried the brackets and I am still getting the same error. Here is the code in case you see something I messed up.

Private Sub cboSelect_Click()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Contact Name]='" & Str(Me![cboSelect]) & "'"
Me.Bookmark = rs.Bookmark
End Sub

[pbaldy]

What is the row source and bound column of the combo? Make sure you're not comparing a name to an ID or something like that.

[jmanis]

Row source and bound column are correct. No more error message but when I select the names nothing happens, it stays on the same record.

[pbaldy]

Can you post the db?

[jmanis]

Here is the database. This is the first one I have attempted to make on my own and I already know I made a bunch of minor errors so Im open to any suggestions.

The combo box is located on the header of the Volunteer Information File Form.

Thanks for looking into this for me.

[pbaldy]

The bound column is not correct. It is set to 2; the row source only returns 1 column, the name. Also, the code is not associated with the event property. I would use the after update event. If you're unsure of how to associate the code:

http://www.baldyweb.com/FirstVBA.htm

My PC has been acting up so I can't test right now, but make those 2 changes and see how it goes.

[jmanis]

I changed those two things and I am getting the same runtime 13 mismatch error as before.

[jmanis]

Went with a filter option instead. Thank you everyone for your assistance.