Access - Update a Combobox on Form Load if only one Item in list

Hello all,

I am trying to tweek a Form load for a Clock out event.   When the user selects ClockOut command it opens a Clock Out Form.  That Form looks to a query of all "open" timecards for the logged in employee.  

If there is only 1 timecard open for the Employee in the query I want it to actually select that timecard in the form's ClockIn time Combobox.

However, it updates the list for the combobox, but I'm doing something wrong in the selecting of that item.

Here is my code:

    Me.cboClockInSelector.RowSource = "SELECT TimeCardID, ClockIn_Emp_ID, ClockTime_Shift_Begin, ClockEvent_Shift_In" & _
    " FROM qry_TimeCards_ToClockOut" & _
    " WHERE ClockIn_Emp_ID = cboSelectEmpID.Value;"
    
    If Me.cboClockInSelector.ListCount = 1 Then
        Me.cboClockInSelector = Me.cboClockInSelector.ItemData(0)
        
    End If

Open in new window

wlwebbAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
And yes, column headers in a combo box do indeed add to the list count.  So to check for one record in a combo that has column headers, you would actually need to check for two rows.  The ItemData property is similarly affected, so with column headers, the first row of data would be ItemData(1) as opposed to ItemData(0) for a combo without column headers.

Try this for your combo with column headers:

    Me.cboClockInSelector.RowSource = "SELECT TimeCardID, ClockIn_Emp_ID, ClockTime_Shift_Begin, ClockEvent_Shift_In" & _
    " FROM qry_TimeCards_ToClockOut" & _
    " WHERE ClockIn_Emp_ID =  " & Me.cboSelectEmpID.Value
    
    If Me.cboClockInSelector.ListCount = 2 Then
        Me.cboClockInSelector = Me.cboClockInSelector.ItemData(1)
        
    End If

Open in new window

0
 
mbizupCommented:
Give this a try - you need to seperate the reference to the combo from the rest of the string.

Assuming the ID is numeric:

    Me.cboClockInSelector.RowSource = "SELECT TimeCardID, ClockIn_Emp_ID, ClockTime_Shift_Begin, ClockEvent_Shift_In" & _
    " FROM qry_TimeCards_ToClockOut" & _
    " WHERE ClockIn_Emp_ID =  " & Me.cboSelectEmpID.Value
    
    If Me.cboClockInSelector.ListCount = 1 Then
        Me.cboClockInSelector = Me.cboClockInSelector.ItemData(0)
        
    End If

Open in new window



For a text emp Id:
    Me.cboClockInSelector.RowSource = "SELECT TimeCardID, ClockIn_Emp_ID, ClockTime_Shift_Begin, ClockEvent_Shift_In" & _
    " FROM qry_TimeCards_ToClockOut" & _
    " WHERE ClockIn_Emp_ID =  '" & Me.cboSelectEmpID.Value & "'"
    
    If Me.cboClockInSelector.ListCount = 1 Then
        Me.cboClockInSelector = Me.cboClockInSelector.ItemData(0)
        
    End If

Open in new window

0
 
wlwebbAuthor Commented:
Mbiz...

Couldn't get the result I expected....

In the query I have for this emp_id ONLY 1 "Open" timecard.... have others but tagged as Closed in a Yes/No field.

So, I added a Msgbox to display the DCount.   It gives me a result of 2.  However, when I open that query it shows only 1 record plus 1 line for adding a new record.

Tried a lot of different things, then On the Combobox I selected Column Heads NO and it gave me the correct record count.  

Is that what it is supposed to do???????
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
mbizupCommented:
What was the exact syntax for your DCount?

This will give you the count of what is in your combo's rowsource:

DCount("*","qry_TimeCards_ToClockOut", "ClockIn_Emp_ID =  " & Me.cboSelectEmpID.Value)

Open in new window

0
 
hnasrCommented:
You may add the follwing to the DCount.

... + cbo.Properties("ColumnHeads")

If there is a column Head the result is True or -1, otherwise it is False or 0.
0
 
wlwebbAuthor Commented:
Thanks mbiz...
0
 
mbizupCommented:
You're welcome :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.