Combo Box & Child Records

[Talgo]

I have a unbound combo box on a Main form using a Select statement to populate the combo with employee numbers from the Employee table. The Main form does not have a Recordsource.

I would like to have all the child records for the employee selected in the unbound combo appear in the Subform. I would like to do this using VBA. My Subform's recordsource is set to the table containing the child records(Time Cards).

I don't want any employee numbers to show in the combo box until one is selected. That's why I don't have a recordsource for the Main form.

I can not get the child records to appear in the Subform. Any suggestions?

 

[JunglesMcBeef]

G'day,

Have you got anything so far that I can have a look at, or are you starting from scratch?

 

[Talgo]

Yes, I have various code snipets doing what I have described above. The Subform is a single form to show one record at a time. The combo box has the following lost focus event:

If Len(Forms!TimeCards!cboEmployee & "&quot = 0 Then
MsgBox "You must enter a valid EmployeeID before proceeding!", vbinformationn + vbOKOnly

Dim frm As Form, cbo As ComboBox

Set frm = Forms![TimeCards]
Set cbo = frm!cboEmployee
cbo.Enabled = True

frm!WorkDate.SetFocus
cbo.SetFocus
Set frm = Nothing
Set cbo = Nothing

End If

It also has an after update event as follows:

Dim rs As Object

Forms![TimeCards].RecordSource = "Employees"

Set rs = Me.Recordset.Clone
rs.FindFirst "[EmployeeID] = '" & Me![cboEmployee] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

The Subform has a form open event as follows:

Me.Filter = "[WorkDate] = Forms!TimeCards![WorkDate]"

The Main form & Subfrom are not linked because I receive an error message saying "You can't build a link betwenn unbound forms" The two tables I'm working with are Employees & TimeCards

I'm not sure I have given you what you need, but let me know if you need more info.

Thanks for helping me.

 

[JunglesMcBeef]

Ok, try creating a query with the fields you want displayed in the subform, making sure to include the field that matches the combo box field. Make the criteria for that field the combo box eg. [Forms]![TimeCards]![cboEmployee]. Then create the subform based on that query, and in your LostFocus and AfterUpdate events remember to Requery your subform to update the record being displayed. Does this help?

 

[Talgo]

I did try your suggestion but didn't have any success. I then went back to a saved prior version of my DB which did function properly & updated it for changes to bring it current. I then tried your approach & it now works fine.

I have discovered a virus on my system & wonder if that had something to do with my problem. I removed the virus before going back to the prior version. I guess I'll never know for sure if that contributed to my probelm, but I'm glad everything is now back to normal.

I appreciate your help, thanks again.