Form/Subform refresh on combobox change

[slatetm]

I have searched the web for a solution to this but nothing has worked for me. I have a form with a list of names in a combo box. I have a subform that is supposed to display all of the jobs that the person has not applied for. The subform is supposed to change when the combo box changes. It does change the first time. It displays the incorrect data, but never changes again. I'm certain the SQL statement is correct because I've run it on its own. Any help is appreciated. Here is the code that is in the After Update event for the combo box.


CmbIndividual.SetFocus
Dim iIndivid As Integer
If IsNull(CmbIndividual.Value) = False Then
iIndivid = CInt(CmbIndividual.Value)
Me.frmJobReqsSubmit.Form.RecordSource = "SELECT * FROM tblJobReqs where ID not in (Select JobReqID from tblIndividualtoJob where IndividualID=" & iIndivid & ")"
Me.frmJobReqsSubmit.Form.Requery
End If

 

[CharlieT302]

Hi Slatetm,

If you are tying the subform to the individual on the main form:

You do not need to get complicated with the code. Just use the wizard to create a combo box that locates the individual on the main form.

The subform will change automatically as long as the linked fields are correct.

Open the main form in design view. Right-click upper left corner of the subform (a single click) so that the form container displays sizing handles. If you see a black dot in the upper left corner gray box, you clicked one too many times. From the shortcut menu, choose properties. On the Data tab check the Link Child Fields and Link Master fields properties. They both need to be set to the same common field(s). If so, the subform should change without problem.


If you are tying the subform to the combo box and not the main form, then the record source of the subform should be a query or an SQL statement(Record Source). The ID field should look to the value displayed in the combo box of the main form. Do this in the criteria row of the query.