Not outputting to ALL (3) combo boxes ?

[liamfitz]

I have 3 combo boxes, which are all output to, from a For ... Next loop. Here's the code -

For i = 0 To 2
        Set rst = db.OpenRecordset("SELECT " & tbl(i) & fld(i) & " FROM " & tbl(i) & " INNER JOIN tblPersonalInfo ON " & tbl(i) & fld(i) & "_ID" & "= tblPersonalInfo" & fld(i) & "_ID" & " WHERE ((tblPersonalInfo.Client_ID)=" & Me.Recordset.Fields("Client_ID") & ");")
            X = 0
            rst.MoveLast
            rst.MoveFirst
            X = rst.RecordCount
            If X > 0 Then
                Select Case i
                    Case 0
                        Y = 1
                        Me.cboEthnicity.SetFocus
                        Me.cboEthnicity.Text = rst!Ethnicity
                    Case 1
                        Y = 1
                        Me.cboReligion.SetFocus
                        Me.cboReligion.Text = rst!Religion
                    Case 2
                        Y = 1
                        Me.cboSexuality.SetFocus
                        Me.cboSexuality.Text = rst!Sexuality
                End Select
            End If
            rst.Close
    Next i

When I set a breakpoint at this loop, it loads all the relevant data properly, in the 3 named boxes. However, when let to run normally it only loads the first combo ( as if the code runs 'too quickly' to complete the task ?? Any suggestions.

 

[pr2-eugin]

Not sure how you have your data organized.. but based on your coding, the following are the three SELECT statements..

"SELECT tbl0fld0 FROM tbl0 INNER JOIN tblPersonalInfo ON tbl0fld0_ID = tblPersonalInfofld0_ID WHERE ((tblPersonalInfo.Client_ID)=" & Me.Recordset.Fields("Client_ID") & ");"

"SELECT tbl1fld1 FROM tbl1 INNER JOIN tblPersonalInfo ON tbl1fld1_ID = tblPersonalInfofld1_ID WHERE ((tblPersonalInfo.Client_ID)=" & Me.Recordset.Fields("Client_ID") & ");"

"SELECT tbl2fld2 FROM tbl2 INNER JOIN tblPersonalInfo ON tbl2fld2_ID = tblPersonalInfofld2_ID WHERE ((tblPersonalInfo.Client_ID)=" & Me.Recordset.Fields("Client_ID") & ");"

It looks very wrong to me.. but is this what you are trying to do? Your DB structure is wrong. and need to be normalized properly.. Also if you are trying to add items to your list, you have to use the AddItem method, the Text will only replace whatever value you had previously with the value you are setting now..

 

[liamfitz]

It IS Normalized, but I can't select these 3 pieces of related data, with the rest of the main form's recordset, as they are in seperate tables ( and selecting data from more than one table, makes the recordset READ_ONLY i.e not updateable ) This is EXACTLY which I have had to go to these lengths, to treat this related data seperately.