Excel VBA Userform and Access

[DHT Tech]

I have built a farily extemsive multipage userform for user input. The userform will populate an excel spreadsheet to begin with, and eventually (hopefully) populate an access database. I have used an example from another forum to populate my main combobox with a list in an access database stored on my C:\ drive. For maintenance alone, I believe this is definitely the better way to go however, this spreadsheet has been used by my company for the past 10 years, and they are still wanting to hold on to it for now.

What I have in place is code to establish a connection with the database, find the field it is told to look for, and list it in the combobox on the userform. What I need it to do is populate residual text boxes on the userform based on the selection made in the combobox that is populated by the access database. My code looks like this:

Private Sub UserForm_Initialize()
On Error GoTo UserForm_Initialize_Err
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Databases\StaffDatabase.mdb"
rst.Open "SELECT DISTINCT [Department] FROM tblStaff ORDER BY [Department];", _
cnn, adOpenStatic
rst.MoveFirst
With Me.ComboBox1
.Clear
Do
.AddItem rst![Department]
rst.MoveNext
Loop Until rst.EOF
End With
UserForm_Initialize_Exit:
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
UserForm_Initialize_Err:
MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
Resume UserForm_Initialize_Exit
End Sub

again, this is a sample that I found using google. What I need to determine is how to ask the program to connect with more than one field (I think), and to automatically return the value of the subsequent fields in the text boxes on the userform. Additionally, I'm not sure if a 'change_event' might be the necessary trigger to populate to text boxes? I hope I am clear enough as I am unsure if 'I' understand my needs properly?

If anyone can provide either insight, examples, or links, it would be greatly appreciated. I have scoured several different forums for the past few weeks and have found examples of nearly everthing that I don't need, and nothing specific to what I do need.

Also, I would like to know if this is simply some rendition of a VLookup or a direct database query?

Again, any insight would be greatly appreciated.

Thanks and Regards,

 

[Norie]

You can do this with a SQL query but exactly how is hard to tell without more information.

In general you can use a list of the fields you want to return from each record in the table just as you have used [Department]

eg [Department], [Field1], [Field2]

That would return those fields for each record in the table.

You can also use * if you want to return all fields.

If there are other tables involved then it gets more complicated.

Also if you want to use criteria you need to incorporate that in the query.

For example if you wanted to return all the records from a table where the department is X

SELECT *
FROM [Departments]
WHERE [Department] = 'X'

As for which event should trigger whatever you are doing, that kind of depends on what you want to do and when you want to do it.

 

[DHT Tech]

Hi Norie, thank you so much for your promt reply. A few things I'd like to touch on; I have seen the asterisk a million times but misunderstood what it meant. I thought it was simply a place holder in the examples I was looking at, and I was to put my field name in where the asterisk was - apparently this is not the case. I tried several variations of how you described to run the query - with square brackets, without brackets, field names with two words got square brackets, etc... and I'm not sure if the query worked as I didn't know how to tell it to send the subsequent information to a particular text box. Any insight on how I may be able to accomplish this? in excel, I have done this quite successfully with "INDEX/MATCH" functions, but that was only a few small tables. This will end up being quite a comprehensive, and more importantly a completely dynamic database as there will be several admin assistants entering new data manually after I am long gone.

Thank you again for any help you'd care to provide.

You can do this with a SQL query but exactly how is hard to tell without more information.

In general you can use a list of the fields you want to return from each record in the table just as you have used [Department]

eg [Department], [Field1], [Field2]

That would return those fields for each record in the table.

You can also use * if you want to return all fields.

If there are other tables involved then it gets more complicated.

Also if you want to use criteria you need to incorporate that in the query.

For example if you wanted to return all the records from a table where the department is X

SELECT *
FROM [Departments]
WHERE [Department] = 'X'

As for which event should trigger whatever you are doing, that kind of depends on what you want to do and when you want to do it.

 

[Norie]

Sorry I don't quite follow.

If the data is in Access you shouldn't need to use any Excel formulas

The code you posted is using a query to get the unique department names in a recordset.

' use query to return unique department names from staff table
 
rst.Open "SELECT DISTINCT [Department] FROM tblStaff ORDER BY [Department];", _
cnn, adOpenStatic

Then you loop through that recordset and add the value from the Deparment field to the combobox/listbox.

Do
.AddItem rst![Department]  ' add value from Department field
rst.MoveNext                    ' move to next record
Loop Until rst.EOF              ' continue until end of recordset

So you could use another query with criteria, to get the record(s) you want.

Perhaps if you posted more information about the tables/fields in Access and what you've got on the userform.

 

[DHT Tech]

Norie, I think I understand how the loop works, and it does populate my combobox (with the small glitch of duplicate values), but my problem is trying to populate two other text boxes on the same for based on the decision made in the combobox. How do I direct traffic one the query has extracted it from the database? How do I ask it to put the relevant values from the db into the text box? (the reference to the Index/Match formula was an example of how I was thinking it would work in my mind is all). Thank you for your patience.

 

[Norie]

Is the code you originally posted working fine to populate the combobox?

If it is just leave that part, it's done if you like.

What you need now is another query to get the data you want using the selection in the combobox as criteria.

I still don't know what fields and/or controls you have on the form so let's say you've got Field1 & 2 and TextBox1 & TextBox2.

You can try this code, but please not it's probably got a few errors in it.

Private Sub ComboBox1_Change()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String
 
' check to see if anything has been selected in combobox
   If Combobox1.ListIndex = -1 Then Exit Sub
 
   strSQL =   "SELECT [Field1],[Field2] "
   strSQL = strSQL & " FROM tblStaff "
   strSQL = strSQL & " WHERE [Department]= '" & Combobox1.Value & "'"
 
   cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=C:\Databases\StaffDatabase.mdb"

   rst.Open strSQL , cnn, adOpenStatic
 
   rst.MoveFirst
 
   If Not rst.EOF Then
      TextBox1.Value = rst.Fields("Field1").Value
      TextBox2.Value = rst.Fields("Field2").Value
   Else
      MsgBox "No records found"
   End If
 
   rst.Close
 
   cnn.Close
 
   Set rst = Nothing
   Set cnn = Nothing
End Sub

 

[DHT Tech]

You've nailed it right on the head however; the one piece that is giving me a hiccup is this:
rst.Open strSQL , cnn, adOpenStatic

What I am reading is this:
1. Open a new recordset
2. Refer to these instructions (strSQL = "SELECT [Field1],[Field2] "
strSQL = strSQL & " FROM tblStaff "
strSQL = strSQL & " WHERE [Department]= '" & Combobox1.Value & "'")
3. make a connection to the database
4. Determine the cursor type.

Is this correct?

If so, why would it be stopping at that line in the code unless there is some ambiguity in the strSQL statement?

 

[DHT Tech]

Norie, I figured it out - mostly. It was the single apostrophe's on either side of the combobox1.value that was making it not work as that is strictly a 2 decimal number that will result. (My apologies for any ambiguity as it is certainly not intended.) This is very fantastic however, I now notice that when I finish the 'change event' of the combobox, it does not always update the text boxes. Any insight on why that may be? Again, I cannot thank you enough for the help you have provided... this was simply an impossibly task without any guidance.

 

[Norie]

I assumed you were populating with department names, which would of course have been text so would have needed the single quotes.

Don't know why it doesn't work sometimes.

I can think of two possibilities of the top of my head, both of which unlikely though.

Here they are:

1 You are entering a value that's not on the list.

2 There is no record with the department name/id/number you enter.

Like I said unlikely, for 2 you should have been informed there was no record.

For 1 nothing would have happened at all - the first line of code checks to see if what's entered on the list.

You might want to stick a breakpoint or two (F9) in the code and step through to see what's happening.

If there are particular values that it's not working you can test them.