Results 1 to 2 of 2
  1. #1
    gazzieh is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    26

    Coding for Empty Combo Box

    I have a series of combo boxes where the rowsource is defined by the selection of the previous combo box.



    The issue is that one of the combo boxes is a date that can either be one of the selectable items or a new date.

    If it is a new date then I need the next combo box to detect that the drop down contains no data and so then use a different calculation for the rowsource.

    The problem is that if I use ListCount then this will report Null if it is empty but I cannot seem to get an IF statement to respond to this event.

    Code:
    Private Sub txtDate_Change()
    
        txtLeader.RowSource = "SELECT [tblStaffDetails]![txtLName] & "", "" & [tblStaffDetails]![txtFName] & "" - "" & [tblStaffDetails]![txtFaculty] AS Staff FROM tblStaffDetails INNER JOIN tblActivityStaffDate ON tblStaffDetails.Staff_ID=tblActivityStaffDate.[Staff Member] WHERE (((tblActivityStaffDate.Activity)=Forms.frmNewRegister.txtActivity) And ((tblActivityStaffDate.dteDate)=Forms.frmNewRegister.txtDate)) ORDER BY tblStaffDetails.txtLName, tblStaffDetails.txtFName, tblStaffDetails.txtFaculty; "
        If txtLeader.ListCount = Null Then
            txtLeader.RowSource = "SELECT [tblStaffDetails]![txtLName] & "", "" & [tblStaffDetails]![txtFName] & "" - "" & [tblStaffDetails]![txtFaculty] AS Staff FROM tblStaffDetails INNER JOIN tblActivityStaffDate ON tblStaffDetails.Staff_ID=tblActivityStaffDate.[Staff Member] ORDER BY tblStaffDetails.txtLName, tblStaffDetails.txtFName, tblStaffDetails.txtFaculty; "
        End If
        txtLeader.Enabled = True
        If txtLeader.ListCount = 1 Then
            txtLeader.value = txtLeader.ItemData(0)
            txtLeader.Enabled = False
        End If
    
    End Sub
    Where am I going wrong?

  2. #2
    c_smithwick is offline Underpaid Programmer
    Windows 7 Access 2003
    Join Date
    Jan 2010
    Location
    Lakeside, CA
    Posts
    49
    Use the Not in List event to set your row source to something different. This event fires AFTER all the change events, so even though you are setting your row source in the change event, you will reset it to the new row source if the user enters a value that is not in the combo box list. You must set the combos LimitToList property to true however, to get the NotInlist event to fire:
    Code:
    Private Sub txtDate_NotInList(NewData As String, Response As Integer)
        txtLeader.RowSource = "SELECT [tblStaffDetails]![txtLName] & "", "" & [tblStaffDetails]![txtFName] & "" - "" & [tblStaffDetails]![txtFaculty] AS Staff FROM tblStaffDetails INNER JOIN tblActivityStaffDate ON tblStaffDetails.Staff_ID=tblActivityStaffDate.[Staff Member] ORDER BY tblStaffDetails.txtLName, tblStaffDetails.txtFName, tblStaffDetails.txtFaculty; "
        txtLeader.Enabled = True
        If txtLeader.ListCount = 1 Then
            txtLeader.Value = txtLeader.ItemData(0)
            txtLeader.Enabled = False
        End If
    
    End Sub
    Private Sub txtDate_Change()
        txtLeader.RowSource = "SELECT [tblStaffDetails]![txtLName] & "", "" & [tblStaffDetails]![txtFName] & "" - "" & [tblStaffDetails]![txtFaculty] AS Staff FROM tblStaffDetails INNER JOIN tblActivityStaffDate ON tblStaffDetails.Staff_ID=tblActivityStaffDate.[Staff Member] WHERE (((tblActivityStaffDate.Activity)=Forms.frmNewRegister.txtActivity) And ((tblActivityStaffDate.dteDate)= Forms.frmNewRegister.txtDate)) ORDER BY tblStaffDetails.txtLName, tblStaffDetails.txtFName, tblStaffDetails.txtFaculty; "
        txtLeader.Enabled = True
        If txtLeader.ListCount = 1 Then
            txtLeader.Value = txtLeader.ItemData(0)
            txtLeader.Enabled = False
        End If
    
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Button Coding
    By yamie in forum Programming
    Replies: 3
    Last Post: 02-22-2010, 10:45 AM
  2. Adding VB code on a embedded coding for a button
    By cwwaicw311 in forum Programming
    Replies: 1
    Last Post: 02-20-2010, 12:25 PM
  3. simple math coding for form field??
    By RCBNewbee in forum Programming
    Replies: 7
    Last Post: 07-13-2009, 08:30 AM
  4. Bar-Coding
    By texasprincess7 in forum Access
    Replies: 1
    Last Post: 02-12-2009, 10:29 AM
  5. parsing data in access (coding?)
    By banker247 in forum Programming
    Replies: 0
    Last Post: 01-13-2009, 12:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums