Cascading Combo Box to select record to edit

[Pyro33]

Hi,

I've been an anonymous lurker on this forum for a while and have been very grateful for the wealth of knowledge and help I have been able to find here.
I've recently run into an issue with an Access database I am working on that i wasn't able to solve. I am fairly new to Access only recently having the need to learn the program due to work.

My current issue is that I am attempting to create an attendance database that needs to be able to be updated if someone calls in sick.

I have created a cascading combo box where you can select a date up to 15 days into the past with the following code in the rowsource for cboDate

SELECT DISTINCT tblAttendance.dataDate FROM tblAttendance WHERE (((tblAttendance.dataDate)>Date()-15)) ORDER BY tblAttendance.dataDate DESC;

an after update event has been coded to show only employees who have been scheduled for the day

Private Sub cboDate_AfterUpdate()
    On Error Resume Next
    Me.cboAgentName.RowSource = "Select tblAttendance.agentName, tblAttendance.attendance, tblAttendance.timeOffReason, tblAttendance.planned , tblAttendance.reportedLate, tblAttendance.minsLate " & _
        "FROM tblAttendance " & _
        "WHERE tblAttendance.dataDate = " & Format$(Me.cboDate.Value, "\#mm\/dd\/yyyy\#") & " AND tblAttendance.agentName IS NOT NULL " & _
        "ORDER BY tblAttendance.agentName;"
End Sub

And then an after update event is also coded into cboAgentName after an employee has been selected to populate the rest of the attendance fields

Private Sub cboAgentName_AfterUpdate()
    Me.cboAttendance = Me.cboAgentName.Column(1)
    Me.cboReason = Me.cboAgentName.Column(2)
    Me.cboPlan = Me.cboAgentName.Column(3)
    Me.chkLate = Me.cboAgentName.Column(4)
    Me.tboLate = Me.cboAgentName.Column(5)
End Sub

So basically what i need to do is be able to use the cascading combo boxes to select the date and employee to go to that specific record so i can update the other fields. Currently when i attempt to do this with my current form, it automatically goes to a new record when i select a date and name and doesn't update the existing record.

Please let me know what i'm missing or where i went wrong. Much appreciated.

 

[Pyro33]

Hi,

I have solved this on my own, for anyone who may be interested or having similar issues what i did was have my Date combo box row source as

SELECT DISTINCT tblAttendance.dataDate FROM tblAttendance WHERE (((tblAttendance.dataDate)>Date()-15)) ORDER BY tblAttendance.dataDate DESC;

and an after update event

Private Sub cboDate_AfterUpdate()
    Me.cboAgentName.Requery
End Sub

then my employee combo box had a row source that took the value of the date from the date combo box, hence the requery after update event

SELECT tblAttendance.ID, tblAttendance.dataDate, tblAttendance.agentName FROM tblAttendance WHERE (((tblAttendance.dataDate)=[Forms]![frmAttendance]![cboDate])) ORDER BY tblAttendance.agentName;

and an after update "SearchForRecord" macro where ="[ID] = " & Str(Nz([Screen].[ActiveControl],0))

also had to make sure both combo boxes were unbound

Cheers!