Select record from combobox list and have record populate in subfrom (1 Viewer)

[wehoscottward]

Hello.
I would like to select a record from my combobox dropdown list and have that record populate in my subform. Currently, I’m only able to select the 1st record at the top of the dropdown list to appear in my subform. But I would like to select any record from the dropdown list and have it populate my subform. I’ve not had my luck in my Google search, so any help is appreciated here.
Thanks, Scott

 

[pr2-eugin]

Try using the AfterUpdate Method of the Main Form's ComboBox.. Requery the SubForm's Recordset. Something like.

Private Sub comboBoxName_AfterUpdate()
    If Me.comboBoxName.ListIndex <> -1 Then
        Me!subFormName.Form.RecordSource = "SELECT theFields FROM theTable WHERE someID = " & Me.comboBoxName
        Me!subFormName.Form.Requery
    End If
End Sub

 

[wehoscottward]

Hi, Paul.
Thanks so much for this!
I have 2 questions, however:
My Combobox = [Combobox2]
My Subform = [My WRCA Data Entry Form]
The Field from my dropdown list = [Member_ID]
What is TheTable ?
What is SomeID ?
Thanks, Scott

 

[pr2-eugin]

TheTable would be the name of the table from which you wish to show the information for the Selected Member ID, which again is the SomeID..

 

[wehoscottward]

Hi, Paul.

Here’s the code I entered however, I’m getting an error message.
Private Sub Combobox2_AfterUpdate()
If Me.Combobox2.ListIndex <> -1 Then
Me![WRCA Data Entry Form].Form.RecordSource = "SELECT [Member_ID] FROM [WRCA Data Table 2013] WHERE [Member_ID]= " & Me.[Combobox2]
Me![WRCA Data Entry Form].Form.Requery
End If

End Sub
Here’s the error message:
The Run-time error ‘3075’:
Syntax error (missing operator) in query expression ‘[Member_Name]=Scott Ward’.
Scott Ward is the Member Name I used as the query parameter attached to the combobox. The combox box give me all records with the Member Name Scott Ward.
Thanks, Scott

 

[pr2-eugin]

I guessed that MemberID would be a Number not Text.

But if it is a Text you need to make sure you enclose them inside Quote marks.. Something like..

Me![WRCA Data Entry Form].Form.RecordSource = "SELECT [Member_ID] FROM [WRCA Data Table 2013] WHERE [Member_ID]= " [COLOR=Red][B]& Chr(34) &[/B][/COLOR] Me.[Combobox2] [COLOR=Red][B]& Chr(34)[/B][/COLOR]

PS: Please use Code Tags when posting VBA Code

 

[wehoscottward]

Hi, Paul.

Thanks--that seemed to solve that problem, however, there's a new problem. I get a new Run-time error '2424':

The expression you entered has a field, control, or property name that Microsoft Access can't find.

Also in the subform, the member's name is populated correctly, however, all the other relevant fields in the form have #Name? in them.

 

[pr2-eugin]

Make sure that the Field Names are spelt correctly and actually exists..

Regarding the #Name Error, please include all the field names that the SubForm has. For example if the Subform has the field MemberID, FirstName, AddressLine1, County, Postcode. If you use

SELECT MemberID FROM theTableName;

Then all the other fields will have the #Name error.. To overcome this you need to change your SELECT query..

SELECT MemberID[B], FirstName, AddressLine1, County, Postcode [/B]FROM theTableName;

 

[wehoscottward]

Hi, Paul.
Thanks so much for your reply here. You’ve helped me a lot in the last week. What ended up working for me here was to link my subform to my combobox using the record ID (rather than the member’s name, which is how I had it linked). As we know, the record ID is unique for each record in the datatable, whereas, there are multiple records with the same member’s name in the datatable. Using the member's name as the reference point was confusing the subform. Once I linked the combobox to the subform using the record ID, I was able to click on any record in my combobox dropdown list and that specific record would instantly appear in my subform, which is what I wanted. My database is all set for production now! Thanks again.
Sincerely, Scott