Is there a better way to find records other than a combo box?

[lycialive]

I was wondering if there was a better way to find records other than a combo box-or the search at the bottom. I'm wondering if I'm making too much work for myself, or if combo boxes are the way to go.

I have several forms with lots of records. I use control buttons to go to each form. and on each form is a combo box to search with. Any tips would be great!

Thanks

 

[Kryst51]

Hi, Your question is kind of vague, are you wanting to open a form based on what is selected in the previous form? Is there anything that relates the forms together? You can, through your button, open a form to a record based on the form the button is on.

You could also, if you don't want mutlitple forms, make use of subforms. I have even seen a thread on this site that deals with loading a particular subform based on selections in the main form. You can also have one form that provides filter selections that would then display a form with your filters. It all depends on what you are trying to achieve and how everything is related.

Perhaps you can give us some more information concerning your relationships, purpose, table structure, etc....

 

[statsman]

Create a "search" form with a text box for the field you wish to search for.

Create a query to open the form you wish to display the searched for record.

In the criteria of the query, enter the following in the field you are searching on:
[Forms]![NameOfSearchForm]![TextBox]
Example: [Forms]![CustomerSearchForm]![Text1]

The form should open with the required record displayed.

You may search using as many fields as you wish, just enter the critieria for each field in the query.

Note: If more than one record matches your criteria, they will be displayed in the form one at a time.

 

[boblarson]

The reason a combo box works so well, frankly, is that it selects a list of existing values. So, that way a user has no need to try and figure out what is there, but is presented with the values that exist. With the AutoExpand property set to YES, then typing in the combo box moves to the first value that matches what is being typed and so an additional character being typed gets the next value, etc.

A list box can also present values but typing in a list box generally brings up the item that matches the first letter, and so it is not as dynamic as a combo box with the AutoExpand property. But, with a listbox you can generally see more at a time (including multiple columns) than you can with a combo box.

Setting up a search with text box is not a bad thing, but it is a little bit more "mysterious" as it were because you can type something and it not be in the values available. Not saying that is bad, but just that it takes less to develop using a combo that selects all valid values.

That's just my 2¢ worth.

 

[missinglinq]

My question is always why anyone would want to go to all the trouble of re-inventing the wheel. I've seen this question, more or less, posted dozens if not hundreds of times over the years (I'm retired and work four or five Access forums 10-12 hours a day) and in all that time I've only seen two or three reasons that I thought were valid for not using the combobox. It should take less than two minutes for even a novice to set up a combobox to retrieve a record, with Access doing all the heavy lifting.

Allen Browne has a hack for letting a textbox imitate the combobox AutoExpand feature, but it's not simple to implement, certainly not as easy as the third option in the cbo wizard:

http://allenbrowne.com/AppFindAsUType.html

 

[boblarson]

My question is always why anyone would want to go to all the trouble of re-inventing the wheel.

Yep, I agree. I had one recently who insisted that the combo box was ugly and so they wanted to use, for input, a text box which required their users to remember what the Vendor code was AND all product codes. <shaking head>

 

[missinglinq]

Don't you love the ones who spend days and days and days worrying about how to make their databases look like web pages, with bells and whistles, and pretty buttons, then come back and ask how to make the current date appear on the form or how go to a new record, since they removed those "ugly looking" navigation buttons from the form? All flash and no bang!

 

[lycialive]

Okay, well thanks to statsman, I got the query and text box idea to work out better until I can find a way to make the combo boxes actually work in access 2007. I have several forms, but only on one form does the box work. I actually prefer the combo boxes, and used them on many previous forms in earlier versions of access, but in 2007, they're not functioning too well until I get them to work, this technique with the text box works perfectly! Thanks again!

 

[missinglinq]

"Not functioning too well?" Exactly what does that mean? If you explain the problem(s) we can probably help you. While I personally do not run 2007, a number of people here do.

 

[Kryst51]

"Not functioning too well?" Exactly what does that mean? If you explain the problem(s) we can probably help you. While I personally do not run 2007, a number of people here do.

Which brings us back to my post.

 

[lycialive]

Well, let's see if I can explain it better.

I've built one form for advertising. I use the combo box to find advertising contacts. This combo box works fine.

So, I built a second form for printing costs. I have several records that I need to access (pun intended), which is "print projects". so I again built the combo box to find "print projects". This combo box returns with an error dialogue box with the options to "end" or "debug".

I have 3 more forms which I am using a combo box to retrieve records-which all are currently not working. All five forms have their own tables. I have a huge database that I built using an earlier version and that one works just fine with mulitple combo boxes.

The short version is, only one of my forms works with a combo box. The other 4 do not. I don't want to stop using access 2007 because I really love a lot of the features.

I've been thru this already on this forum, and people offered great advice, it just didn't work is all, so I figured I'd try to stumble my way through. That's why I was asking about another search alternative.

I can't explain it any better than that.

 

[Kryst51]

Hi, I just read your original post, it seems like you just let the problem drop when you were asked to post your db.

I don't have access 2007, so I won't be able to help, but I am sure if you post your db here, or on the original thread, someone might be able to look at it and help you figure out what is going wrong.

Edit:

Nevermind on this part, as that was part of someone else's post, I reread it again.

And looking at your original thread, I wonder, if the code has trouble knowing which [ID] your looking for as a table isn't specified, nor does it say Me.ID, or something like that. but I don't know enough about programming to say for sure, if that is an issue or not.

 

[boblarson]

Okay that is good info. And, we can work through the combo box issues but we have to start by taking them one at a time (combos work just fine in 2007 as long as you don't ask them to do something that they aren't prepared to do, or if something is referring to the wrong thing, etc.).

So, you say

This combo box returns with an error dialogue box with the options to "end" or "debug".

What gets highlighted when you click DEBUG?

 

[lycialive]

the highlighted text is this: rs.FindFirst "[ID] = " & Str(Me![Combo71])

 

[boblarson]

If ID is not a string value then you would use:

rs.FindFirst "[ID] = " & Me![Combo71]

 

[lycialive]

Yeah, that's one of the variations that I tried. It doesn't work either. It's a real tricky one, that's for sure. I'm sure it's some little hairline difference between the 2 program versions, I just don't know what.

 

[boblarson]

Yeah, that's one of the variations that I tried. It doesn't work either. It's a real tricky one, that's for sure. I'm sure it's some little hairline difference between the 2 program versions, I just don't know what.

Okay, so do you really have a field named ID

And, put a message box beforehand to see what the combo is actually returning - if it is returning a value you think it should:

MsgBox Me!Combo71
rs.FindFirst "[ID] = " & Str(Me![Combo71])

Normally I use a period too and not the BANG (!).

MsgBox Me.Combo71
rs.FindFirst "[ID] = " & Me.[Combo71]

 

[wiklendt]

i would check to see if "Combo71" is still "Combo71" - i have previously made controls do something then changed their names from the default so i can reference them better in code.

more directly targetted to your first post, i prefer to use LISTboxes instead of COMBOboxes to "find" records in a form. i like these b/c you get to see much more and you can even scroll down with the arrow key in the list box and the records will change real-time...

my full code on the after update event of the combo looks like this (and i also had issues with the original "find first record" code when i started with access 2007, this one seemed to fix the problem.)

Private Sub lstItems_AfterUpdate()
    
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ItemID] = " & Str(Nz(Me![lstItems], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

note that i use "Str(Nz())" even though what i am looking for is an autonumber ID...

 

[lycialive]

Thanks for all the help, but again, the combo box does not work. I'm using MS Office Enterprise, maybe there's a bug, or a necessary service pak. In any event, I think I've wasted enough time on this form as it just not going to work. I'm thinking of starting over. If I find a cure, I'll post it for sure!

 

[boblarson]

Thanks for all the help, but again, the combo box does not work. I'm using MS Office Enterprise, maybe there's a bug, or a necessary service pak. In any event, I think I've wasted enough time on this form as it just not going to work. I'm thinking of starting over. If I find a cure, I'll post it for sure!

I'll bet you (100% guaranteed) that if you were to post the database, I could get the combo boxes to ALL work just fine.