Hello All,
I am trying to auto populate my userforms listboxes with the associated access database. You will see that my connection string is good because the Manufacturer listbox is populating from the access database already.
The goal of the userfrom is to have the user click on the Manufacturer in (Listbox1). Once that happens the Generic Categories associated with the chosen manufacturer populate in the Generic Category (ListBox2) from the access database. Then the user would click on the Generic Category in (ListBox2) and the associated items with the chosen Generic Category would populate in ListBox3 from the access database.
I think my problem lies in the SQL query statement, but I am not sure.
I have attached my excel workbook along with the access database that goes with it.
Thank you in advance.
Userform Listbox Populating From Access Database
-
-
-
Re: Userform Listbox Populating From Access Database
Is there anyone out there that can help with this?:confused:
-
Re: Userform Listbox Populating From Access Database
yeah the problem is in your query Syntax, you haven't used Execute correctly
you'll need to add a ADODB.Command as new command along with Command type and text if your using execute...
here's your statements
CodestSQL = "SELECT * FROM ItemsTable WHERE Manufacturer = '" & ItemSelection.ListBox1.Value & "' AND GenericCategory = '" & Me.ListBox2.Value & "' ;" stSQL = "SELECT GenericCategory FROM ItemsTable WHERE Manufacturer = '" & Me.ListBox1.Value & "' ;"
HTH
z
-
Re: Userform Listbox Populating From Access Database
You Are the Man...Thanks.
That has been driving me nuts.:confused:
One more thing when I click on ListBox2 the data popultes in ListBox3 and displays only in column one. What am I doing wrong here? My code for ListBox2 is below.
Code
Display MorePrivate Sub ListBox2_Click() Dim cnt As ADODB.Connection Dim rst As ADODB.Recordset Dim stDB As String, stConn As String, stSQL As String Dim vaData As Variant Dim k As Long 'Instantiate the Connection object. Set cnt = New ADODB.Connection 'Path to and the name of the database. 'Assumes the DB and worksheet are in same directory. 'If not, you need to specify DB path stDB = ThisWorkbook.Path & "\" & "Items.mdb" 'Create the connection string. stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & stDB & ";" 'Create the SQL-statement. stSQL = "SELECT * FROM ItemsTable WHERE Manufacturer = '" & ItemSelection.ListBox1.Value & "' AND GenericCategory = '" & Me.ListBox2.Value & "' ;" With cnt .CursorLocation = adUseClient 'Necesary for creating disconnected recordset. .Open stConn 'Open connection. 'Instantiate the Recordsetobject and execute the SQL-statement. Set rst = .Execute(stSQL) End With With rst Set .ActiveConnection = Nothing 'Disconnect the recordset. k = .Fields.Count 'Populate the array with the whole recordset. vaData = .GetRows End With 'Close the connection. cnt.Close 'Manipulate the Combobox's properties and show the data. With Me With .ListBox3 .Clear .BoundColumn = k .ColumnCount = 6 .List = Application.Transpose(vaData) .ListIndex = -1 End With End With 'Release objects from memory. Set rst = Nothing Set cnt = Nothing End Sub
-
Re: Userform Listbox Populating From Access Database
When you debug the code does it error anywhere?
-
-
Re: Userform Listbox Populating From Access Database
No...The code Executes.
It's just that the data ends up in column 1 of ListBox3.
Example:
Column1 Column2 Column3 Column4 Column5 Column6
1
Honeywell
Intrusion
Contacts
Contact
10I want it to look like the:
Column1 Column2 Column3 Column4 Column5 Column6
1 Honeywell Intrusion Contacts Contact 10HTH
X
-
-
Re: Userform Listbox Populating From Access Database
Same result...
-
Re: Userform Listbox Populating From Access Database
may be try changing your column count on the list to how ever many fields you want to select?
-
Re: Userform Listbox Populating From Access Database
Tried that already...Any other thoughts?
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!