[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2012-09-18
7
Medium Priority
?
454 Views
Last Modified: 2012-10-09
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

0
Comment
Question by:wlwebb
  • 4
  • 2
7 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38411763
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
 

Author Comment

by:wlwebb
ID: 38412078
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38412218
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 38412238
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
 
LVL 31

Expert Comment

by:hnasr
ID: 38412633
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
 

Author Closing Comment

by:wlwebb
ID: 38476324
Thanks mbiz...
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38476804
You're welcome :)
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question