searching forms, subforms and combo boxes

[pbouk]

Hi there,

Question 1


I am referring to this great resource for building search boxes: http://www.access-programmers.co.uk/...d.php?t=188663
and so far the demo search box for my own database is working well, however, I don't know how to get the record I've selected to populate in my form.
If i double click on the record I want (as John Big Booty suggests) it doesn't bring it up.

Question 2
THis relates to combo boxes AND subforms.

My contacts database has many combo boxes, and a subform. How do I make it easy for my users to search them all? I would like it to be possible to search every field. In particular, I have a subform on my main form that holds my addresses. Is it possible to search this in the above search?

If not, can I create another search box on my main form to search the subform? I've done some research on the internet about this, but so far I haven't had much luck finding an answer (that I completely understand!)

I feel like I'm missing something with subforms/combo boxes...why is it so hard to search them??

Note: Filtering is a little tricky for my users as they are using Access for the first time, so I'd prefer it if it were simple like Control-F or a simple search box. (besides, you can't filter combo boxes, can you?)


Apologies if there are a lot of questions here. If anyone has any ideas, I'd be grateful,

Thank you.

[June7]

Maybe these will help

http://datapigtechnologies.com/flash...tomfilter.html

http://datapigtechnologies.com/flash...earchform.html

Yes, combobox RowSource can be a filtered query.

http://datapigtechnologies.com/flash...combobox2.html

For the double-click to work, need code in the form DblClick event.

[pbouk]

Thank you, these videos are really helpful.

I'm still wondering if it's possible to create a keyword search? (I don't think that was covered in these videos) I'm guessing it requires a lot of coding.

For example, in my company search box, not having a keyword option proves to be a problem with names such as Centre Pompidou, or The Museum of Medical History, where the user must remember the first word to find the right institution/company name. I'd prefer to avoid this sort of trial and error.

Thanks.

[June7]

Use wildcard at begin and end of parameter:

LIKE "*" & forms!formname!controlname & "*"

[pbouk]

Thanks, I just realized this!
I created a demo of my search form, and it works well, except it doesn't generate ALL my records. Some contacts don't appear...but I can't work out why.
It seems only about 2/3 of my contact list comes up when I run the query.

I've attached the query here.

[pbouk]

If this helps:

It doesn't seem to display many contacts who are non USA -only two or three come up. About 500-600 contacts from all over the rest of the world should come up, but don't, for some reason.

(by the way, I capitalised Country in my query field box, and it didn't make any difference)

[June7]

Access is not case sensitive so 'country' is equivalent to 'Country'.

Don't know why the non-USA issue. Would have to analyze data. If you want to provide, follow instructions at bottom of my post.

Your search results will not work right if fields are allowed to be null. Need to handle that by creating fields with expression and apply the filter parameters to constructed field. For instance, if Street3 can have null:

Street3Adj: Nz([Street3],"")

[pbouk]

I seem to have resolved the problem, I took another query that was working correctly and amended it, and it's fine now.

Do I enter the info: Street3Adj: Nz([Street3],"") to the query in the criteria section? For each field?
So far my results seem good without this. Even if I have empty fields, they're coming up in the search.

One more thing, I wanted to have a combo box on my form to *just* search contact name, however, some of my Last Name entries actually have two names: the surname of the main contact and the name of the spouse ie.

First Name: Matthew
Last Name: Andrews and Ms. Holly Oaks

I have added LIKE "*" & forms!formname!controlname & "*" into my Last Name field of my combo box, but it doesn't seem to be searching the whole last name, just the first part. So if I search for Oaks, it doesn't come up.

If it doesn't work it doesn't really matter, I can make do!
Here is the query. ComboBox 78 is the combo box name for the search box, the form name is the name of the main form where the combo box resides.

[June7]

Street3Adj: Nz([Street3],"") is not criteria, it is a constructed field. Apply criteria to that constructed field. But if is working without, disregard.

A string is a string regardless of how many names are in it. I don't understand why LastName search for *Oaks* doesn't work.

[pbouk]

Hi there,
I am thinking about your recommendation to use Street3Adj: Nz([Street3],"")
I might need to use if for another query...can you explain what it does? (And what does the Adj mean exactly)?

[pbouk]

To re-phrase my problem: I have two subforms on my form and it looks like my query is not bringing back results if any of my contacts have either or both of the subforms blank (one subform is for addresses, and the other is for categories). I'm not sure how to fix this.
My query is fairly complex and searches a number of different tables. Perhaps this is the problem?

[June7]

Street3Adj is just a name I gave the constructed field. Adj is abbreviation of adjusted. Use whatever name suits you.

Nz is an intrinsic function, review http://office.microsoft.com/en-us/ac...001228890.aspx

[pbouk]

Thanks for this. I've added it to my query for most of the fields.
Did you notice my other post? about having problems retrieving records where the address or category fields are blank?
I've added the constructed field you mentioned to make sure it brings back these records even if these fields are null.... but it's not working.

[June7]

Isn't that why I suggeted the Nz function and constructed field?

Did you apply the filter criteria under the constructed field?

If you want to provide db for analysis, follow instructions at bottom of my post.

[pbouk]

Hi there,
I would prefer not to attach this database to a public forum, but I could send to you personally.
I have put this Nz function in the database, you'll see it included the query below (in two halves). It doesn't seem to be working though. As I said, there are some records that are not being retrieved because they either don't have an address or a category or both. I added the Nz thing to the address type description (as this is always filled if there is an address), but should I add it to all the address fields?