Seach Function (2000)

**iksotof** · 2003-10-13 13:19

Re post 31111 and the reply from Hans. I have done the coding as suggested and put this in a module, how do I now call the function on the after update event of my combo box.

Kind regards Darren.

**HansV** · 2003-10-13 13:28

You probably mean <post#=301111>post 301111</post#>. For the future: unless a thread becomes very long, it is usually better to ask a follow-up question in the same thread, that makes it easier for other Loungers to keep track of the questions and answers. Thanks.

There are two ways you can use the function you created. Both start the same way:
- Open the form in design view.
- Select a combo box.
- Activate the Event tab of the Properties window.
- Click in the After Update event.

Method 1:
- Type =DoSearch() in the After Update box.

Method 2:
- Select Event Procedure from the dropdown list.
- Click the Builder button (the three dots to the right of the dropdown arrow.)
- Complete the code to look like this:

Private Sub cmbField1_AfterUpdate()
DoSearch
End Sub

- Switch back to Access.

Repeat for the other combo boxes.

**iksotof** · 2003-10-13 13:33

Thanks Hans, sorry for giving you the run around on finding the previous post.

Regards Darren.

**iksotof** · 2003-10-13 20:23

Thaks Hans, i am still having problems with the db I am building at work however, could you please assist with code on my home dummy again? Assuming field 'date' and 'name' are those on which the searches will be based?

Many thanks Darren

**HansV** · 2003-10-13 20:26

I'm sorry, I don't see any combo boxes in the forms, nor the code I posted. Am I missing something?

**iksotof** · 2003-10-13 20:45

Sorry, I attached older version, this is the one.

Thanks.

**HansV** · 2003-10-13 21:07

Darren,

The code I posted in the other thread is headed

Private Function DoSearch()

Private means that the function is only available in the module in which it is defined. Moreover, the code in the function refers to the form as "Me" and it also refers to controls on the form. All this implies that you can't put the function in a general module, it must be in the module behind the form.

Additional note: the function "as is" will activate the first record if you enter a value or combination of values in the combo boxes that cannot be found. If you want to avoid this, replace

If Not rs.EOF Then

by

If Not rs.NoMatch Then

And, if you like, you can give the user feedback if the value or combination of values wasn't found:

If rs.NoMatch Then
Beep
Else
Me.Bookmark = rs.Bookmark
End If

**iksotof** · 2003-10-13 21:21

Marvellous, I really am hard work aren't I, you have enlightened me on a few key notions there. Thank you yet again for your help and patience. Is there a book you can recommend?

Regards Darren.

**HansV** · 2003-10-13 21:42

Many developers like Access 2000 Developer's Handbook: Desktop Edition by Paul Litwin and others (Sybex), and fellow moderator <!profile=WendellB>WendellB<!/profile> recently recommended Beginning Access 2000 VBA by Robert Smith, David Sussman (Wrox Press). Both can be found on Amazon UK.

Others may have more recommendations.

**iksotof** · 2003-10-14 16:22

Post deleted by iksotof

**HansV** · 2003-10-14 18:52

Darren,

In the first place, do yourself some favours:
<UL><LI>Turn on "Require Variable Declaration" in Tools | Options... (in the Visual Basic Editor). See the thread starting with <post#=301389>post 301389</post#> for a discussion of why it is necessary - the thread is in Excel, but the arguments are valid for all flavours of VBA.
<LI>Give your controls meaningful names. You may know now what Combo212 and Option74 are meant for, but if you have to debug your database in a year's time, you'll have a hard time.
<LI>Turn off the Name Autocorrect options in the General tab of Tools | Options... in all your databases.[/list]More specific remarks:
<UL><LI>The Row Source of the Feedback Type combo box on tblFeedback subform refers to a non-existent table tbl_feed_type.
<LI>The combo boxes pols, dates and IFAs on the main form have no Row Source. Why not? A combo box without row source is equivalent to a text box. Also, I have no idea what would be meaningful entries for these controls.
<LI>The filter you create in code refers to fields pol, ifa and date. There are no fields named pol or ifa in the record source tbl_Main. There is a field date, but that is not a very good name for a field, since it is also the name of a VBA function. This can cause confusion. Since Access can't find field names pol or ifa, you get an error message if you enter something in the corresponding combo boxes.[/list]Please clear up these points.

**iksotof** · 2003-10-14 19:25

Thanks Hans, appears I have crossed over since your most recent response. I realised where I was going wrong and have mostly rectified. The only oustanding problem I have is that my third search is based on a field for names where these names are stored in the underlying table as ID

**HansV** · 2003-10-14 19:32

Set the column width for the first column to 0. The value of the combo box will still be the ID, but the user won't see it.

And deleting a post with an attached database is not very user-friendly either. My reply has now become meaningless to other Loungers.

**iksotof** · 2003-10-14 19:40

Cheers Hans, the attachment is back on. I deleted before I realised you had replied, sorry.

Your help has been much appreciated. Thank you.

Thread: Seach Function (2000)

Thread Tools