asked on

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

mbizup

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

wlwebb

ASKER

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???????

mbizup

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

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Hamed Nasr

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.

wlwebb

ASKER

Thanks mbiz...

mbizup

You're welcome :)