How to load all Product ID's by default

[Rangerguy2000]

"If you want this combobox to work as a navigation aid, then it should be UNBOUND. Bind a textbox to the Driver_ID field to display this value but don't allow edits in that control."



This worked, all I had to do was to blank-out the Control Source for the Driver_ID textbox and now the drop-down menu is updating the fields and it is letting me input new metrics.

But, when I pick the next driver, the form is updating the next drivers name in the tbl2_Drivers and recording the entries for the original driver in the tbl3_MetricDetails.
e.g.
I use BCGL-001 to update entries for 2018-11-01, then pick the next driver BOGD-001 from the Driver_ID box;
The form is updating the tbl2_Drivers BCGL-001 with the BOGD-001 drivers name etc.
Also, the tbl3_MetricDetails is recording entries for BCGL-001 and not BOGD-001.

I am still working through it, will update if resolved, but if you have a solution, please let me know.
Will work on the frmSub after I solve this.

[June7]

That doesn't sound like what I suggested.

I suggested a BOUND and Locked textbox and an UNBOUND combobox.

Need code behind UNBOUND combobox to navigate to existing driver record. This can either filter the form or go to record.

Delete the cboDriver_ID_Change() event code.

Then input a date to either load existing records or create new records in tbl3_MetricDetails.

Option Compare Database
Option Explicit

Private Sub cboDriver_ID_AfterUpdate()
With Me.RecordsetClone
    .FindFirst "Driver_ID='" & Me.cboDriver_ID & "'"
    If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
Me.Record_Date = Null
Me.Record_Date.SetFocus
End Sub

Private Sub cmdClose_Click()
    DoCmd.Close
End Sub

Private Sub Record_Date_AfterUpdate()
If DCount("*", "tbl3_MetricDetails", "Driver_ID='" & Me.Driver_ID & "' AND Record_Date=#" & Me.Record_Date & "#") = 0 Then
    CurrentDb.Execute "INSERT INTO tbl3_MetricDetails(Record_Date, Driver_ID, Metric_ID) SELECT #" & Me.Record_Date & "# AS RD, '" & Me.Driver_ID & "' AS DID, Metric_ID FROM tbl4_MetricIDs"
    Me.frmSub_MetricDetails.Requery
End If
End Sub

[Rangerguy2000]

Hi thanks .. this seems to be on the cusp of working completely successfully.

I just had blank-out the Control Source for the Driver_ID textbox and now the drop-down menu is updating the fields and it is letting me input new metrics.

But the frmSub is not pulling all the default Metric_ID's from 1-11. I have a feeling there needs to be some code change in the below:


Private Sub Record_Date_AfterUpdate()
If DCount("*", "tbl3_MetricDetails", "Driver_ID='" & Me.Driver_ID & "' AND Record_Date=#" & Me.Record_Date & "#") = 0 Then
CurrentDb.Execute "INSERT INTO tbl3_MetricDetails(Record_Date, Driver_ID, Metric_ID) SELECT #" & Me.Record_Date & "# AS RD, '" & Me.Driver_ID & "' AS DID, Metric_ID FROM tbl4_MetricIDs"
Me.frmSub_MetricDetails.Requery
End If
End Sub

[June7]

The code creates 11 records. Subform displays those records that match the date in Record_Date textbox. What else should it do?

You keep saying you blank out ControlSource for textbox - why??? Should be combobox with blank ControlSource.

[Rangerguy2000]

No the Combobox cboDriver_ID .. when I delete the Control Source for this, the function works fine.

Now that you say is creates 11 records is not displaying the records, so my assumption is, it is not creating the records.

[Rangerguy2000]

Here's the latest code with some extra lines I added ...

Option Compare Database
Option Explicit


Private Sub cboDriver_ID_AfterUpdate()
With Me.RecordsetClone
.FindFirst "Driver_ID='" & Me.cboDriver_ID & "'"
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
Me.Record_Date = Null
Me.Record_Date.SetFocus
End Sub






Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec


End Sub


Private Sub Record_Date_AfterUpdate()


If DCount("*", "tbl3_MetricDetails", "Driver_ID='" & Me.Driver_ID & "' AND Record_Date=#" & Me.Record_Date & "#") = 0 Then

CurrentDb.Execute "INSERT INTO tbl3_MetricDetails(Record_Date, Driver_ID, Metric_ID) SELECT #" & Me.Record_Date & "# AS RD, '" & Me.Driver_ID & "' AS DID, Metric_ID FROM tbl4_MetricIDs"

Me.frmSub_MetricDetails.Requery

End If


End Sub




Private Sub cmdClose_Click()
DoCmd.Close
End Sub

[June7]

Did you fix the subform RecordSource query as suggested in post 15?

Look in the table are the new records there?

[Rangerguy2000]

Yes the tbl3_MetricDetails has new records once the frmMain is run.
Current issue is the non-display of Metric_ID's 1-11 be default.
Following is the SQL code for the qry1Sub_DriverMetricDetails that runs the frmSub in the frmMain.
I have changed the INNER JOIN to RIGHT JOIN manually in the SQL code:


SELECT tbl3_MetricDetails.Record_Date, tbl3_MetricDetails.Driver_ID, tbl3_MetricDetails.Metric_ID, tbl4_MetricIDs.Metric_Name, tbl3_MetricDetails.Flag_ID, tbl5_MetricFlags.Performance, tbl5_MetricFlags.Controllable, tbl3_MetricDetails.PointsEarned
FROM tbl5_MetricFlags RIGHT JOIN (tbl4_MetricIDs RIGHT JOIN tbl3_MetricDetails ON tbl4_MetricIDs.Metric_ID = tbl3_MetricDetails.Metric_ID) ON tbl5_MetricFlags.Flag_ID = tbl3_MetricDetails.Flag_ID
ORDER BY tbl3_MetricDetails.Metric_ID;

[June7]

Display of records is dependent on value in Record_Date textbox. If there is no value then no records display. Can set that textbox DefaultValue property to maybe Date() so if any records exist they will auto display. Then have more code in the form Current event to run the same code in the textbox AfterUpdate event.

Private Sub Form_Current()
Record_Date_AfterUpdate
End Sub

[Rangerguy2000]

Yes the part you're saying Records will display if they exist in the Table, is working.
What's not working is, I want the frmSub to pull the default Metric_ID's from the tbl4_MetricID's by itself, and their relevant Flags from the MetricsDetails (if they exist).
And the user should only be updating the Flag_ID's.

I will give a shot to what you just suggested in the morning. If you have any more suggestions please let me know.

You have been a great help!
Thanks!