SQL question #1

[eelsar]

How do I do the following;

I have a form where a user selects from a combo box an Item name. However the item name is not from the table that's connected to the form. Instead it's ID# ( as a Foreign key) is connected to the form. But I want the program to read in the Item Name into the combo box and not the Id#.

How do I do this?
eelsar

 

[johnwm]

Try this:

Private Sub Form_Activate()
Dim rst As Recordset
Set rst = New Recordset
Me.cboSuppliers.Clear
Me.cboSuppliers.SetFocus
With rst
    Set .ActiveConnection = cnn1
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open "SELECT * FROM tblsuppliers"
    
    Do While Not .EOF
        Me.cboSuppliers.AddItem !suppliername
        Me.cboSuppliers.ItemData(Me.cboSuppliers.NewIndex) = !supplierid       
        .MoveNext
    Loop    
    rst.Close
    Set rst = Nothing
End With

End Sub

This gets supplierID into itemdata associated with each item. The on selecting supplier from combo:

Private Sub cboSuppliers_Click()
Dim mySQL As String
Dim rst As Recordset
If cboSuppliers.ListIndex = -1 Then cboSuppliers.ListIndex = 0
Me.txtSuppID.Text = Me.cboSuppliers.ItemData(cboSuppliers.ListIndex) 'hidden textbox
mySQL = "SELECT * FROM tblBatches"
mySQL = mySQL & " WHERE supplierid = "
mySQL = mySQL & Me.txtSuppID.Text
Set rst = New Recordset
With rst
    Set .ActiveConnection = cnn1
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open mySQL
        Do While Not .EOF
        ' Use data
        .MoveNext
    Loop
    rst.Close
    Set rst = Nothing
End With

End Sub

Let me know if this helps
________________________________________________________________
If you are worried about how to post, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'