How do I automatically populate some fields of a New Record

[geebsterlove]

Hi guys,


I'm new here and looking for some help with an MS Access form. Right now I have a database that keeps track of employees, their projects, and the time frame during which they work on certain projects. I currently have a combo box for EmployeeID and SprintID (the time frame) which work together to filter a subform that lists projects.

I'd like to have a button on my form to Add New Records. I'd like the new records to automatically populate the SprintID and EmployeeID fields of the subform with the values I select in the combo boxes of the main form. I think I need a button with an On Click event? I am unsure how to code such an event.

Any help is appreciated! Thanks!

Amy

[RuralGuy]

If the EmployeeID and SprintID were the LinkMaster/ChildFields of the SubForm control then they would be populated by Access for you when you start a new record in the SubForm.

[geebsterlove]

Oh okay, great. Setting the LinkMaster/ChildFields worked to populate the subform fields with values, but they are not the correct values. It also messed with how my combo box filter works. I want all records to show in the subform until I select values from my SprintID and EmployeeID combo boxes. With LinkMaster/ChildFields defined, I only see one new record upon opening my form.

[RuralGuy]

Then populate them in the AfterUpdate event of the ComboBox.

[geebsterlove]

Sorry, RG, not quite sure I understand. Right now my AfterUpdate looks like this. (Note: Sorry, "EmployeeID" as stated above is actually "PersonnelID"):

Private Sub cboSprintID_AfterUpdate()
' Set the Personnel combo box to be limited by the selected SprintID
Me.cboPersonnelID.RowSource = "SELECT DISTINCT tblTimeEntry.PersonnelID, tblTimeEntry.SprintID FROM tblTimeEntry " & _
    " WHERE SprintID = " & Nz(Me.cboSprintID) & _
    " ORDER BY PersonnelID"
Me.cboPersonnelID = Null


'enable cboPersonnelID
EnableControls
ApplyFilter

End Sub


Private Sub cboPersonnelID_AfterUpdate()

    ApplyFilter
    
End Sub

And my subform is being filtered by the following:

Private Sub ApplyFilter()
Dim strFilter As String
strFilter = ""
' see if there is data in cboSprintID, if so add it to the filter
If Me!cboSprintID & vbNullStr <> vbNullStr Then   
strFilter = strFilter & " AND [tblTimeEntry.SprintID] = " & Me.cboSprintID
End If
If Me!cboPersonnelID & vbNullStr <> vbNullStr Then   
strFilter = strFilter & " AND [tblTimeEntry.PersonnelID] = " & Me.cboPersonnelID & " "
End If

If strFilter <> "" Then   
' trim off leading "AND"   
frmProjectPercent.Form.Filter = Mid(strFilter, 5)   
frmProjectPercent.Form.FilterOn = True
Else   
frmProjectPercent.Form.Filter = ""   
frmProjectPercent.Form.FilterOn = False

End If
End Sub

How do I get all records to display in my subform until I make a selection in my SprintID combobox while SprintID and PersonnelID are my LinkMaster/Child Fields?

[RuralGuy]

Using the LinkChild and LinkMaster properties of the SubForm control, you do not need the filters. Are you willing to try that?

[geebsterlove]

Okay, so I got rid of the filter and set the LinkChild and LinkMaster properties, but I'm still not getting the result I want. The subform will only show records for the first SprintID. How do I get it to show all records in the table?

[RuralGuy]

Initially the LinkChildField and LinkMasterField properties should be blank. Does that give you all of the records?

[geebsterlove]

Yes, when LinkChild/MasterFields are blank, the subform shows all records.

[RuralGuy]

There are lots of ways to skin this cat. I'm thinking 2 invisible TextBoxes on the Main form filled in by the AfterUpdate event of the ComboBox.
Will the SubForm always be limited to PersonnelID and SprintID values? What is the name of your SubFormControl? The control with the LinkChild/MasteFields properiies on it.

[RuralGuy]

I'm pretty sure you can have the LinkChildFields property populated and as long as the LinkMasterFields property is blank or points to blank fields then you will see all of the SubForm recordset.

[RuralGuy]

It has been pretty quiet on this thread. I hope that means you were successful with your task.