How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Pete Your Own Question
Pete, Computer Engineer
Category: Programming
Satisfied Customers: 17273
Experience:  Over 16 years of experience in computer programming.
Type Your Programming Question Here...
Pete is online now
A new question is answered every 9 seconds

I'm looking forward to your help once again. Do you work

Customer Question

Hi Doctor!
I'm looking forward to your help once again.
Do you work with MS Access and vba?I have an Access form that can be filtered based on a text search for a field in the table that is the record source for the form.However, I need to also be able to filter the form for records using a text search on two tables:tblCities
City_ID (PK) City Name State Name
1185 North Springfield PA
1581 Springfield IL
1582 Springfield MACity_ID is the Primary Key, and also the foreign key in the second table.tblCouncils
Cncl_ID (PK) City_ID (FK)(###) ###-####(###) ###-####
On the form, if I enter "Springfield" in a txt box, it should search tblCities for any full or partial text match on City Name, and return the corresponding City_IDs.
It should then search for and return all Council_IDs for records that match City_IDs.
Finally, it should filter the form for the resulting records, e.g., 414, and 415.I already went through one expert on here, but he got bogged down by the following incorrect assumptions:the underlying query should only search for an exact text match
that it should only pull the first match (instead of multiple matches)
that each and every City_ID appears in tblCouncils
that there is a one-to-one relationship between City_ID and Cncl_ID.The text search also did not work with any city that had a space in the name.I've attached a sample database that includes his attempt at an answer, and my failed attempt to fix it.If you can do this, let me know what you think is a fair price.
Submitted: 11 months ago.
Category: Programming
Expert:  Pete replied 11 months ago.

Hi Rob,

I see you have been waiting some time for a response...

The Doctor is currently offline - can I help with this, or would you prefer to wait for The Doctor?

Customer: replied 11 months ago.
Hi Pete,
Actually, I was able to figure it out by changing the record source for the form in question from a table to a query, where I can include any text field I want.Thanks anyway!