Setting combo box row source & control source from 2 diff. queries

[PRMiller2]

Hi all,

I have a form that displays three combo boxes, two of which are dependent on values from the first:

cboSourceLevel1 should reflect pk_SourceID, which indicates where the prospect came from (direct mail, referral, etc). If certain events are met, the other two become enabled. For example, if the prospect was a referral, cboSourceLevel2 becomes enabled and displays the name of the person who referred the client.

Those two values are forein keys stored in the client table. However, users may edit cboSourceLevel1 to change the source of business from, say a referral to direct mail, etc. THOSE values are stored in a seperate soureofbusiness table.

My problem happens on form load. The combo boxes should display the existing values stored in the client table, but the user should be able to select a new option from the combo boxes. On load, the combo box row dropdown lists load properly, but they do not display the values currently stored in the client table. They display randomly. Here's the code on form load:

Private Sub Form_Load()

    Dim frm As Access.Form
    Dim strPerson As String
    Dim strProperty As String
    Dim strPropertyLead As String
    Dim strSource As String
    Dim strSQL As String
    Dim strSQL2 As String
    
    Select Case g_intMenuID
        Case 3 'Client
            Me.lblClientName.Caption = "Client Name:"
        Case 4 'Prospect
            Me.lblClientName.Caption = "Prospect Name:"
    End Select
    
    Select Case g_intMenuOption
        Case 1 ' Add new
'            Set frm = Forms!frm_ClientMaintenance
'            Call UnbindForm(frm, True)
            Me.Caption = "Add new prospect"
            Me.lblClientName.Caption = "Prospect Name:"
            Me.cboContact.Enabled = False
            Me.cmdInsert.Enabled = False
            Me.txtClientName.SetFocus
            Me.txtStartDate = Format(Now(), "mm/dd/yyyy")
            Me.txtEndDate = Format(Date + 90, "mm/dd/yyyy")
            Me.txtClientName.SetFocus
        Case 2 ' Edit existing
            Me.Caption = "Maintenance"

            strPerson = "SELECT pk_ContactID, [contactLastName] & ', ' & [contactFirstName] AS Name" & _
                        " FROM person" & _
                        " WHERE active='Yes'" & _
                        " ORDER BY contactLastName, contactFirstName;"

            strProperty = "SELECT pk_PropertyID, IIf(IsNull([unitNumber]),([streetNumber] & ' ' & [direction1] & ' ' & [streetName] & ' ' & [streetSuffix] & ' ' & [direction2]),([streetNumber] & ' ' & [direction1] & ' ' & [streetName] & ' ' & [streetSuffix]) & ' ' & [direction2] & ' Unit ' & [unitNumber]) AS Address, city.cityName" & _
                          " FROM property LEFT JOIN city ON property.fk_CityID = city.pk_CityID;"
                          
            strPropertyLead = "SELECT pk_PropertyLeadID, PropertyLeaDescr ORDER BY PropertyLeaDescr;"

            strSource = "SELECT pk_SourceID, SourceDescr FROM sourceofbusiness ORDER BY SourceDescr;"
            
            strSQL = "SELECT client.pk_ClientID, client.clientName, client.start, client.end," & _
                     " client.relationship, client.active, client.notes, leadSource.fk_SourceID," & _
                     " leadProperties.fk_PropertyID, leadProperties.fk_PropertyLeadID, leadReferrals.fk_ReferralContactID" & _
                     " FROM (((leads LEFT JOIN leadSource ON leads.pk_LeadID = leadSource.fk_LeadID)" & _
                     " LEFT JOIN leadReferrals ON leadSource.pk_LeadSourceID = leadReferrals.fk_LeadSourceID)" & _
                     " RIGHT JOIN client ON leads.fk_ClientID = client.pk_ClientID)" & _
                     " LEFT JOIN leadProperties ON leadSource.pk_LeadSourceID = leadProperties.fk_LeadSourceID" & _
                     " WHERE (((client.pk_ClientID)=" & g_intRecordNumber & "));"

            strSQL2 = "SELECT person.pk_ContactID, [person].[contactLastName] & ', ' & [person].[contactFirstName] AS Name" & _
                      " FROM person" & _
                      " LEFT JOIN personClient ON person.pk_ContactID = personClient.fk_ContactID" & _
                      " WHERE (((personClient.pk_PersonClientID) <>" & g_intRecordNumber & ") AND ((person.Active)='Yes'))" & _
                      " OR (((personClient.pk_PersonClientID) Is Null) AND ((person.Active)='Yes'))" & _
                      " ORDER BY 2;"
            
            Me.RecordSource = strSQL
            Me.cboContact.Enabled = True
            Me.cboContact.RowSource = strSQL2
            Me.cboRelationship.ControlSource = "relationship"
            Me.cboSourceLevel1.BoundColumn = 0
            Me.cboSourceLevel1.ControlSource = "leadSource.fk_SourceID"
            Me.cboSourceLevel1.RowSource = strSource
            Me.chkActive.ControlSource = "active"
            Me.cmdInsert.Enabled = True
            Me.txtClientID.ControlSource = "pk_ClientID"
           ' Me.txtClientName.ControlSource = "clientName" THis may need to be populated separately, another form?
            Me.txtContact.ControlSource = "Name"
            Me.txtEndDate.ControlSource = "end"
            Me.txtNotes.ControlSource = "notes"
            Me.txtStartDate.ControlSource = "start"
            
            Select Case Me.cboSourceLevel1.Value
                Case 7 'Listing
                    Me.lblSourceLevel2.Visible = True
                    Me.cboSourceLevel2.Visible = True
                    Me.cboSourceLevel2.BoundColumn = 0
                    Me.cboSourceLevel2.ControlSource = "leadProperties.fk_PropertyID"
                    Me.cboSourceLevel2.RowSource = strProperty
                    Me.cboSourceLevel2.ColumnCount = 3
                    Me.cboSourceLevel2.ColumnWidths = "0;1.8;.7"
                    Me.lblSourceLevel2.Caption = "Listing:"
                    
                    Me.lblSourceLevel3.Visible = True
                    Me.cboSourceLevel3.Visible = True
                    Me.cboSourceLevel3.ControlSource = "leadProperties.fk_PropertyLeadID"
                    Me.cboSourceLevel3.RowSource = strPropertyLead
                    Me.lblSourceLevel3.Caption = "Source:"
                Case 8 'Referral
                    Me.cboSourceLevel2.BoundColumn = 0
                    Me.cboSourceLevel2.ColumnCount = 2
                    Me.cboSourceLevel2.ColumnWidths = "0;2.5"
                    Me.cboSourceLevel2.ControlSource = "leadReferrals.fk_ReferralContactID"
                    Me.cboSourceLevel2.RowSource = strPerson
                    Me.cboSourceLevel2.Visible = True
                    Me.lblSourceLevel2.Caption = "Referred by:"
                    Me.lblSourceLevel2.Visible = True
            End Select
            
            Me.txtStartDate.SetFocus
    End Select

End Sub

I'm not quite sure why I can't get the combo boxes to populate with the appropriately referenced foreign key. It may very well be something obvious at this point, but I've been looking at it too long to tell!
Any help would be greatly appreciated!

 

[PRMiller2]

Well aren't I an idiot. .BoundColumn should have equaled a 1, not a 0. For some reason I thought it should be a 0, as it is in the properties when working in Design View.

Wow. Well, hopefully someone learns from my simple mistake!