please help newbie understand combo box

[jono261970]

Hi,

I have an access table with location,make,model and qty fields. I want to display contents of location into combo and display the other fields in text boxes depending on which location has been selected in combo.

I got hold of the following code but can`t make it work?

===================================================


Combo0.RowSource = "Select Table1.Location from Table1;"



Private Sub Combo0_AfterUpdate()

Sql= "SELECT Table1.Location, Table1.Make, Tablel1.Model, Table1.Qty " & _ "FROM Table1 " & _
"WHERE (((Table1.Location)='" & Combo0.Text & "'));"

Set myDB = CurrentDb
Set myRS = myDB.OpenRecordset(Sql, dbOpenDynaset)
If Not (myRS.EOF And myRS.BOF) Then

Text1 = myRS.Fields("Make&quot
Text2 = myRS.Fields("Model&quot
Text3 = myRS.Fields("Qty&quot

End If
End Sub
=====================================================

I have tried changing currentdb to c:\printDB.mdb but to no avail.
I`m really struggling here, I would really appreciate it if somebody could spare 5 mins to guide me through this.

kind regards,

jono

 

[Sheriff]

jono,
You could try this code. This needs to be in the form that will display the results, in the click event of the combo box call the ShowRecord. You could use a list box for the same results. You (may or may not)need the myRS.AbsolutePosition in the click event of combo box.

Good Luck
Sheriff


Sub FillComboBox()
' Comments : This is for ComboBox
' Parameters:
' Created :
' Modified :
'
' --------------------------------------------------
On Error GoTo PROC_ERR

MyCombo.Clear
Do While Not myRS.EOF
MyCombo.AddItem "" & myRS![Location]
myRS.MoveNext
Loop
myRSrs.MoveFirst

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox err.Description
Resume PROC_EXIT

End Sub


Sub FillListBox()
' Comments : This is for list box
' Parameters:
' Created :
' Modified :
'
' --------------------------------------------------
On Error GoTo PROC_ERR

MyListBox.Clear
Do While Not myRS.EOF
dAbcList.AddItem "" & myRS![Location]
myRS.MoveNext
Loop
MyRS.MoveFirst

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox err.Description
Resume PROC_EXIT

End Sub

Private Sub MyListBox_Click()
' Comments :
' Paramete :
' Modified :
'
' --------------------------------------------------

On Error GoTo PROC_ERR

Dim intX As Integer


For intX = 0 To MyListBox.ListCount - 1
' If the item is selected...
If MyListBox.Selected(intX) = True Then
If myRS.EOF Then
myRS.MoveLast
GoTo 10
ElseIf myRS.BOF Then
myRS.MoveFimyRSt
GoTo 10
End If
myRS.AbsolutePosition = intX
10
ShowRecord 'Display Record
Exit For
End If
Next intX


PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox err.Description
Resume PROC_EXIT

End Sub


Public Sub ShowRecord()
' Comments :
' Parameters: -
' Created : 06/15/02
' Modified : 06/30/02
' --------------------------------------------------

On Error GoTo PROC_ERR

If myRS.RecordCount = 0 Then
Exit Sub
End If
Text1 = myRS.Fields("Make&quot
Text2 = myRS.Fields("Model&quot
Text3 = myRS.Fields("Qty&quot

]

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox err.Description
Resume PROC_EXIT

End Sub

 

[tedsmith]

If the name of your MDB is printDB and it is in the C root folder, I would use:
Set MyDb = OpenDatabase(DataPath & "C:\printDB.mdb&quot
Also I never use the semi colon ;
I use:
Sql= "SELECT Table1.Location, Table1.Make, Tablel1.Model, Table1.Qty FROM Table1 WHERE Table1.Location='" & Combo0.BOundText & "'"
Make sure you use a DATACOMBO and set its bound column to Location.
I generally use ADODCs as follows:

Private Sub CustomerCombo_Change()
On Error GoTo InvalidEntry
Criteria = "CustomerID ='" & CustomerCombo.BoundText & "'"
AdodcCustomers.Recordset.MoveFirst
AdodcCustomers.Recordset.Find Criteria
Etc