Problem with combo box on a form

[mcomp72]

Hi everyone. I’ve run into a problem with one of my forms, and I cannot for the life of me figure out why it’s happening. It’s a bit hard to explain, so to help you understand it, I’ve created a video showing what is happening. You can find it here:
https://youtu.be/w91wdSln7G8

Basically, I have a Form called Timecards. The Recordsource for it is a Query called Q_Timecard_forForm. On the form, I have setup a couple of combo boxes. (Ignore the ones in the header for now… those don’t work correctly, and I will probably make a second post about them in the coming days if I can’t figure it out.) The two combo boxes in question are directly under the labels “Job” (I’ll call this one cbJob from here on out) and “Employee & Position” (I’ll call this one cbE&P). cbE&P is cascading from cbJob – it will only show a list of employees (& their respective position) that have previously been assigned to the job that was selected in cbJob.

All that works fine. The problem happens when I switch back and forth between two records where the selection for cbJob is different. As you can see in the video, at the beginning, the current record has a Job by the name “Star Wars Promo” and an employee is already listed in cbE&P. At around :06, I switch to a different record, which is for a different Job (and thus a different selection in cbJob). Even though I had previously selected something for cbE&P for that record, it is not showing up. (I know there is an employee assigned to that record, because you can see “18” in the EmployeesOnJobs text box on the right side of the screen. I added that text box there so I could be sure that data was actually a part of the record.)



A note about the video: the video capture of the screen didn’t work quite right in the next part. You can’t see exactly what is happening, so I will explain it. The next thing I do (at about :09) is click on cbJob, and it drops down the list of choices. You can’t see the list appear in the video, for some strange reason. What I do next is select the SAME job name that was already selected (Super Bowl Commercial). Once I do that, the name in cbE&P suddenly appears.

Then if I switch back to the record I was on at the beginning of the video, you’ll see the same problem has occurred: cbE&P is blank. I repeat the same sequence as above: I select the SAME job in cbJob as was already there, and suddenly the correct name shows up in cbE&P.

I can’t figure out why this is happening. I’ve looked at my code related to both combo boxes, but don’t see anything that would be causing this. Any ideas?

[mcomp72]

In case it is helpful, I am pasting ALL of the code inside the Timecard form. I wouldn't think the ones associated with buttons would have anything to do with it, but for the sake of being thoughout, I'm pasting it all.

Option Compare Database
Option Explicit

Private Sub CalculateButton_Click()

If Me.Dirty Then Me.Dirty = False

Call Module3.CalculateTimecard

If Me.Dirty Then Me.Dirty = False

End Sub

Private Sub CreatePDFButton_Click()

If Me.Dirty Then Me.Dirty = False

Call Module1.CreateSingleTimecard

DoCmd.Beep
MsgBox "PDF creation complete."

End Sub

Private Sub DeleteButton_Click()

'If Me.Dirty Then Me.Dirty = False

If MsgBox(Prompt:="Are you sure you wish to delete this record?", Buttons:=vbYesNo, Title:="Confirm Deletion") = vbYes Then
    On Error Resume Next
    DoCmd.RunCommand acCmdDeleteRecord
    If Err.Number = 0 Then
        MsgBox Prompt:="Record Deleted.", Buttons:=vbOKOnly, Title:="Deletion Successful"
    Else
        MsgBox Prompt:="No deletion occurred.", Buttons:=vbOKOnly, Title:="Error"
    End If
Else
    MsgBox Prompt:="The record was not deleted.", Buttons:=vbOKOnly, Title:="Canceled"
End If

End Sub

Private Sub EmployeeNameComboBox_AfterUpdate()

BoxRentalGreyedOut

End Sub

Private Sub Form_Current()

BoxRentalGreyedOut

End Sub

Private Sub Form_Load()

Dim rstQ_EmployeesOnJobs As Recordset
Dim JobID As Integer

Set rstQ_EmployeesOnJobs = CurrentDb.OpenRecordset(Name:="Q_Employees On Jobs", Type:=RecordsetTypeEnum.dbOpenSnapshot)

If Not IsNull(Me.OpenArgs) Then

    Me.EmployeeNameComboBox = Me.OpenArgs
    
    'this will get the JobID off the Q_EmployeesOnJob for that specific record and put that number in the JobNameComboBox
    JobID = rstQ_EmployeesOnJobs.Fields("JobID")
    Me.JobNameComboBox = JobID
    
End If

rstQ_EmployeesOnJobs.Close
Set rstQ_EmployeesOnJobs = Nothing

End Sub

Private Sub JobNameComboBox_AfterUpdate()

Dim rstT_Jobs As Recordset
Dim rstQ_EmployeesOnJobs As Recordset
Dim FoundEmployeeOnJob As Boolean

Set rstT_Jobs = CurrentDb.OpenRecordset(Name:="T_Jobs", Type:=RecordsetTypeEnum.dbOpenSnapshot)
Set rstQ_EmployeesOnJobs = CurrentDb.OpenRecordset(Name:="Q_Employees on Jobs", Type:=RecordsetTypeEnum.dbOpenSnapshot)

FoundEmployeeOnJob = False

'On Error Resume Next

rstQ_EmployeesOnJobs.MoveLast
rstQ_EmployeesOnJobs.MoveFirst

Do While Not rstQ_EmployeesOnJobs.EOF And FoundEmployeeOnJob = False

    If Me.JobNameComboBox = rstQ_EmployeesOnJobs.Fields("JobID") Then FoundEmployeeOnJob = True
    rstQ_EmployeesOnJobs.MoveNext

Loop

If FoundEmployeeOnJob = True Then

    Me.EmployeeNameComboBox.RowSource = "SELECT EmployeesOnJobsID, NameAndTitle " & _
                                        "FROM [Q_EmployeesAndPositions] " & _
                                        "WHERE JobID = " & Me.JobNameComboBox & " " & _
                                        "ORDER BY NameAndTitle;"
                                        
    Me.EmployeeNameComboBox.Requery
    
    With rstT_Jobs
        .FindFirst "JobID = " & Me.JobNameComboBox
        If .NoMatch Then
            MsgBox "Could not find a record for Job #" & Me.JobNameComboBox & "in T_Jobs. Something is wrong."
            rstT_Jobs.Close
            rstQ_EmployeesOnJobs.Close
            Set rstT_Jobs = Nothing
            Set rstQ_EmployeesOnJobs = Nothing
            Exit Sub
        End If
    End With
    
    If rstT_Jobs.Fields("WBSNumber") <> 0 Or Not IsNull(rstT_Jobs.Fields("WBSNumber")) Then Me.CommentLine1 = "WBS #: " & rstT_Jobs.Fields("WBSNumber")
    
Else

    MsgBox "No Employees are assigned to that Job, so you cannot select it.  If you wish to fill out a Time Card for someone on that job, you must first assign an Employee to it via the Employees On Jobs Form."
    Me.JobNameComboBox = Null
   
End If

rstT_Jobs.Close
rstQ_EmployeesOnJobs.Close
Set rstT_Jobs = Nothing
Set rstQ_EmployeesOnJobs = Nothing

End Sub

Private Sub SaveButton_Click()

If Me.Dirty Then Me.Dirty = False

End Sub

Private Sub WeekEnding_AfterUpdate()

Dim rstT_Timecards As Recordset
Dim JobID_Table As Integer
Dim EmployeeOnJobsID_Table As Integer
Dim WeekEndingDate_Table As Date
Dim JobID_CurrentRecord As Integer
Dim EmployeeOnJobsID_CurrentRecord As Integer

Set rstT_Timecards = CurrentDb.OpenRecordset(Name:="T_Timecards", Type:=RecordsetTypeEnum.dbOpenSnapshot)

JobID_CurrentRecord = Me.Controls("JobNameComboBox")
EmployeeOnJobsID_CurrentRecord = Me.Controls("EmployeesOnJobsID")

With rstT_Timecards

.MoveLast
.MoveFirst

    Do While Not .EOF
    
        If rstT_Timecards("TimecardID") <> Me.Controls("TimecardID") Then
    
            JobID_Table = .Fields("JobID_notFK")
            EmployeeOnJobsID_Table = .Fields("EmployeesOnJobsID")
            
            If IsNull(.Fields("WeekEnding")) Then
                WeekEndingDate_Table = 0
            Else
                WeekEndingDate_Table = .Fields("WeekEnding")
            End If
            
            If JobID_Table = JobID_CurrentRecord And EmployeeOnJobsID_Table = EmployeeOnJobsID_CurrentRecord And Me.Controls("WeekEnding") = WeekEndingDate_Table Then
                MsgBox "A time card for this Job/Person/Position already exists with this Week Ending date.  You cannot have more than one.  Please select another Week Ending Date."
                Me.WeekEnding = Null
                Me.D1Date = Null
                Me.D2Date = Null
                Me.D3Date = Null
                Me.D4Date = Null
                Me.D5Date = Null
                Me.D6Date = Null
                Me.D7Date = Null
                Me.WeekEnding.SetFocus
                rstT_Timecards.Close
                Set rstT_Timecards = Nothing
                Exit Sub

            End If
            
        End If
        
        .MoveNext
        
    Loop
    
End With

Me.D1Date = DateAdd("d", -6, Me.WeekEnding)
Me.D2Date = DateAdd("d", -5, Me.WeekEnding)
Me.D3Date = DateAdd("d", -4, Me.WeekEnding)
Me.D4Date = DateAdd("d", -3, Me.WeekEnding)
Me.D5Date = DateAdd("d", -2, Me.WeekEnding)
Me.D6Date = DateAdd("d", -1, Me.WeekEnding)
Me.D7Date = Me.WeekEnding

rstT_Timecards.Close
Set rstT_Timecards = Nothing

End Sub

Sub BoxRentalGreyedOut()

Dim rstQ_EmployeesOnJobs As Recordset
Dim EmployeesOnJobsID As Integer

Set rstQ_EmployeesOnJobs = CurrentDb.OpenRecordset(Name:="Q_Employees On Jobs", Type:=RecordsetTypeEnum.dbOpenSnapshot)

EmployeesOnJobsID = Me.EmployeesOnJobsID

rstQ_EmployeesOnJobs.FindFirst "EmployeesOnJobsID = " & EmployeesOnJobsID

If rstQ_EmployeesOnJobs.NoMatch Then
    MsgBox ("ERROR: Could not find a record in the 'Q_Employees On Jobs' query where the EmployeesOnJobsID field is equal to " & EmployeesOnJobsID)
    Exit Sub
End If

If Not IsNull(Me.EmployeeNameComboBox) And rstQ_EmployeesOnJobs.Fields("BoxRentalYesNo") = "Yes" Then

    Me.BoxRentalAcctCode.Enabled = True
    Me.BoxRentalTotalAmt.Enabled = True

Else

    Me.BoxRentalAcctCode.Enabled = False
    Me.BoxRentalTotalAmt.Enabled = False

End If

rstQ_EmployeesOnJobs.Close
Set rstQ_EmployeesOnJobs = Nothing

End Sub

[June7]

Cascading (dependent) comboboxes with lookup alias don't work nice in Continuous or Datasheet form. This is a well-known and often discussed issue. Solutions involve requerying the combobox, dynamically setting the combobox RowSource between conditional and non-conditional, and including the lookup table in the form RecordSource to bind a locked textbox to the alias field and set it on top of the combobox. Suggest you do a quick web and forum search on the topic keywords. Might be some help in https://www.access-programmers.co.uk...d.php?t=275155

[mcomp72]

Ah... glad to hear I'm not the only one experiencing this weird behavior. I took your advice and decided to try requerying the combo box in the Sub OnCurrent. That did the trick. Now the cbE&P is filled in properly in all records!

[June7]

It may seem like weird behavior but it is really quite consistent and to be expected with the nature of lookups with alias. Glad you resolved to your satisfaction.