List all records with the same customer name

[wehoscottward]

Hello.

My Google searches have not yielded much help on this question: Is there a way (via the form) to search for all the records that have the same customer name and have that list show up so the user can select the exact record s/he needs?

Thansk, Scott

 

[pr2-eugin]

How about creating a Query?

SELECT someFields FROM theTable WHERE nameField = 'wehoscottward';

 

[Simon_MT]

There is a wizard for creating Duplicate Query. This will go through all the records.

Simon

 

[wehoscottward]

Hi, Paul.

Thanks so much for your quick reply! Creating a query makes sense.

Bear with me here, I'm a beginner in Access and have 2 follow up questions:
1. Once I create this query, is there a way to embed the query into the form so the user can search from the open form?
2. Once the query brings up the list of records with the same customer name, is there are way for the user to select a record whereby the record gets populated into the open form?

 

[pr2-eugin]

1. Once I create this query, is there a way to embed the query into the form so the user can search from the open form?

Yes, this is what you are supposed to do if you want to give the user the choice of selecting one result to be opened by.

2. Once the query brings up the list of records with the same customer name, is there are way for the user to select a record whereby the record gets populated into the open form?

Yes, What you now need is to follow the steps below.
1. Create a Form that will have a Search field (a text box) a button, and a SubForm.
2. Create a Query, as shown above but instead of using the hardcoded name use..

SELECT someFeilds FROM theTableName
WHERE nameField = Forms!theMainFormName!theTextBoxName;

3. Save the Query and then set this as the RecordSource for the subform.
4. On Click of the button, just requery the subform.

 

[wehoscottward]

Hi, Paul.
Thanks so much for the road map here! Since I’m really green at all of this, I need to work through this one step at a time. So, first I’m working on the query. I have used the code below, however, I’m getting a syntax error.
Here’s the code in using per your example:
SELECT [Date], [Member_Name], [Member_ID], [Account] FROM [WRCA Data Table 2013] WHERE [Member_Name] = Forms![WRCA Data Entry Form]! [Search_for_Member_by_Name];
Are you able to see what’s wrong here?
Scott

 

[pr2-eugin]

When it says syntax error it will either show from where the Error starts/highlight the place of error when you click OK, did that happen? As I cannot see anything wrong with the code.

 

[Brianwarnock]

I cannot see what's wrong but

Don't use Date as a field name , it is an Access reserved word and using it will at some point likely cause problems, also it is best to only use alphanumeric and _ (underscore) in object names , multiple spaces in error are a pain to spot and anything but the characters I suggested prevent the help of intellisense when using VBA.

Brian

 

[wehoscottward]

Here's the warning message I get:

Invalid use of '.', '!', or '()'. in query expression '[Member_Name} = Forms![WRCA Data Entry Form]! [Search_for Member_by-Name'.

Scott

 

[Brianwarnock]

There are 2 errors there
A } instead of ] after member_name and' instead of ] at the end, but that did not apply to the original. Did you copy and paste the code or retype?

Brian

 

[Brianwarnock]

Oh and there is a space between these after the !

[WRCA Data Entry Form]! [Search_for Member_by-Name'.


This does occur in your original

Brian

 

[wehoscottward]

Here's my code via paste: SELECT [Referral_Date], [Member_Name], [Member_ID], [Account]
FROM [WRCA Data Table 2013]
WHERE [Member_Name] = Forms![WRCA Data Entry Form]! [Search_for_Member_by_Name];

Here's my warning message: See attachment.

Thanks so much for your help.

BTW I changed Date to Referral_Date.

 

[Brianwarnock]

It must be that space after the second ! As mentioned as I don't understand why the closing ] is missing from the error message

Remove the space and retry

Brian

 

[wehoscottward]

That worked! Thanks so much, Brian. Now I'll move on to the next step.

 

[Brianwarnock]

Glad it's sorted, errant spaces can be difficult to spot which is a good reason not to use them in object names. Pleased to see the meaningful date name.

Best of luck

Brian

 

[wehoscottward]

I’m on steps 3 and 4 of your instructions above.

3. Save the Query and then set this as the RecordSource for the subform. Done.
4. . On Click of the button, just requery the subform. Im not having any luck with this part. I can see how to add code to On Click of the button, but I’m not sure how to requery the subform?

 

[Brianwarnock]

Long time since so old grey cells struggling but think

Me.subformname.form.requery

If it is not correct try google if nobody still working doesn't respond.

Brian