populating a subform according to selection in main form combobox

[kiri19]

I am new to ms access, i need some help populating a subform (visitForm) according to a selection in a combobox(searchCombo) on the mainform(clientInfoForm)
so far it pulls up the info but opens up a new datasheet instead of populating the subform.
In the searchCombo box on change event i have :

Private Sub searchcombo_Change()
DoCmd.OpenQuery "visitResultsQuery"
Me.visitForm.Form.RecordSource = "visitResultsQuery"
Me.visitForm.requery
End Sub

The visitForm subform is based on the visitResultsQuery query.

Thanks in advance for your help!

 

[dhookom]

There is no reason to open the query. If the saved record source of visitForm is visitResultsQuery then all you need to do is requery the subform control.

Duane
Hook'D on Access
MS Access MVP

 

[kiri19]

yes the recordsource for the visitform is visitResultsQuery
you mean the syntax should be Me.visitform.requery?


let me explain better.

I have 2 tables "ClientInformation" and "Visits"
The 2 tables are related by the clientID.
ClientInformation
--------------------------
ClientID --> Primary key

Visits
---------------------------
ClientID


visitResultsQuery query looks like this:
SELECT Visits.Visit, Visits.Need, Visits.[New Client], Visits.[Client ID]
FROM Visits WHERE (((Visits.[Client ID])=[Forms]![ClientInfoForm]![searchcombo]));

Private Sub searchcombo_Change()
Me.visitForm.requery
End Sub

I have tried with Me.visitform.requery and Me.visitform.Form.requery, but the subform is still blank, not sure what is wrong

 

[dhookom]

Tell us about searchcombo? What do you get if you open the debug window (press Ctrl+G) and enter::

? [Forms]![ClientInfoForm]![searchcombo]

Do you see the value you expected?

What do you see if you open the query?

Is Client ID a lookup field? Is the bound column of SearchCombo the ID or a name or what?

Do you understand that if the combo bound column is the ID, you use Link Master/Child of the subform control without any code?

Duane
Hook'D on Access
MS Access MVP

 

[kiri19]

in the debug window ? [Forms]![ClientInfoForm]![searchcombo] returns NULL.
The query is empty when run.
The searchcombo property sheet is as follows:

control source is empty
row source: select * from ClientInformation;
row source type: table/query
bound column 1.

 

[dhookom]

The Row Source should select specific fields like:

SELECT [Client ID], [ClientName]
FROM ClientInformation
ORDER BY ClientName;

If the debug window returns Null, then it seems like no item has been selected in the combo box.


Duane
Hook'D on Access
MS Access MVP

 

[kiri19]

Ok so i have corrected the query to include all the fields required , i removed the "*"
?[Forms]![ClientInformation]![searchcombo] in the debug window returns the client ID selected in the combobox in this case 2.
So how do i get the subform to be populated with values returned by the query.
The subform is based on the query, so do i do Me.visitForm.requery?

 

[dhookom]

If this was my app, I would just set the link master/child properties to the combo box and the Client ID field. No code, no criteria in the query.

Duane
Hook'D on Access
MS Access MVP