Combo Box on User Form

[Ann]

Hi,

I found an exercise online on how to create a 2002 Word user form that has
text boxes that I can type into. The data typed into the text boxes ends up
in an Access database table on the click of a button. Now I would like to
add a combo box. I created the box and can get the items to show in my list
but I'm just not sure how to get the item I choose into the database table.

I'm not a programmer but can use some of what I find on the internet and
tweak it for my needs. If I could just get this to be able to use combo
boxes and check boxes it's a tool I can used repeatedly in my job. Can
anyone help me? I really do appreciate any help I can get...thanks in
advance.

Here is the code I have so far:


Private Sub UserForm_Initialize()

cmbChoose.AddItem "Choice One"
cmbChoose.AddItem "Choice Two"

End Sub
Private Sub cmdAlreadyEntered_Click()

'declare variables for new connection and recordset and declare variables
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim vClientFName As String
Dim vClientLName As String
Dim vCompany As String
Dim vAddress As String
Dim vCity As String
Dim vState As String
Dim vZip As String
Dim vPhone As String
Dim vNotes As String

'provide connection string for data using Jet Provider for Access database
vConnection.ConnectionString = "data
source=\\corpfs03\Shared\FACILADM\SHARED\OIPC\Users\Ann\Goals\WordClientInfo.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
'open connection
vConnection.Open

'test connection state...this can later be commented out...but it helps
'let you know if the connection was successful when testing the code
vConnectionState = vConnection.State
If vConnectionState = 1 Then
MsgBox "The connection to this database is working!", vbInformation
Else
MsgBox "You were unable to connect to the assigned database!",
vbInformation
End If

'set variable equal to whatever is entered into First & LastName form
field
'so you can query the DB with this name to see if a record exists
vClientFName = txtFirstName.Text
vClientLName = txtLastName.Text

'open a RecordSet with SQL query results...to see if first/last name
matches a record
vRecordSet.Open "SELECT * FROM tblClientInfo WHERE
tblClientInfo!txtFirstName = " & _
Chr(34) & vClientFName & Chr(34) & "AND tblClientInfo!txtLastName = "
& _
Chr(34) & vClientLName & Chr(34), vConnection, adOpenKeyset,
adLockOptimistic

'if a match is found, display it to the user in a message box
'you'll get a match if you are NOT .EOF (end of file)
With vRecordSet
If Not .EOF Then
vCorrectRecord = MsgBox("Is this the correct record?" & Chr(13) &
Chr(13) & _
vRecordSet("txtFirstName") & " " & _
vRecordSet("txtLastName") & ", " & _
vRecordSet("txtAddress") & ", " & _
vRecordSet("txtCity"), _
vbYesNo + vbInformation, "User Record")
Else
'if you ARE *else* (are .EOF), that means no record was matched,
tell the user
MsgBox "No possible match was found."
End If
End With

'If the answer equals 6, it means they clicked Yes on the MsgBox
accepting this match
If vCorrectRecord = 6 Then
'if yes...set variables from DB fields (the name variables are
already set above)
vCompany = vRecordSet("txtCompany")
vAddress = vRecordSet("txtAddress")
vCity = vRecordSet("txtCity")
vState = vRecordSet("txtState")
vZip = vRecordSet("txtZip")
vPhone = vRecordSet("txtPhone")
vNotes = vRecordSet("memNotes")

'set the form field's bookmarks to the results from the database
field variables
txtCompany.Text = vCompany
txtAddress.Text = vAddress
txtCity.Text = vCity
txtState.Text = vState
txtZip.Text = vZip
txtPhone.Text = vPhone
txtNotes.Text = vNotes
Else
'if not 6, then not Yes, so must be NO...remind user to update
database!
MsgBox "Since this is not the correct entry..." & Chr(13) & _
"be sure to fill out remaining form fields and click *Update* " &
Chr(13) & _
"so this person will be added to the database."
End If

'close objects
vRecordSet.Close
vConnection.Close

'clear object to free up memory
Set vRecordSet = Nothing
Set vConnection = Nothing

End Sub
Private Sub cmdUpdateNew_Click()
'declare new connection, recordset and variables
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim vClientFName As String
Dim vClientLName As String
Dim vCompany As String
Dim vAddress As String
Dim vCity As String
Dim vState As String
Dim vZip As String
Dim vPhone As String
Dim vNotes As String

'provide same as previous connection string for data using Jet Provider
for Access database
vConnection.ConnectionString = "data
source=\\corpfs03\Shared\FACILADM\SHARED\OIPC\Users\Ann\Goals\WordClientInfo.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
'open connection
vConnection.Open

'set variables from text enter into all the AutoForm field bookmark
results
vClientFName = txtFirstName.Text
vClientLName = txtLastName.Text
vCompany = txtCompany.Text
vAddress = txtAddress.Text
vCity = txtCity.Text
vState = txtState.Text
vZip = txtZip.Text
vPhone = txtPhone.Text
vNotes = txtNotes.Text

'Open a new version of the temporary RecordSet accessing the ClientInfo
table in Database
vRecordSet.Open "tblClientInfo", vConnection, adOpenKeyset,
adLockOptimistic
vRecordSet.AddNew
'we can't enter "nothing", so only set variables with some data entered
info the field
If vClientFName <> "" Then vRecordSet!txtFirstName = vClientFName
If vClientLName <> "" Then vRecordSet!txtLastName = vClientLName
If vCompany <> "" Then vRecordSet!txtCompany = vCompany
If vAddress <> "" Then vRecordSet!txtAddress = vAddress
If vCity <> "" Then vRecordSet!txtCity = vCity
If vState <> "" Then vRecordSet!txtState = vState
If vZip <> "" Then vRecordSet!txtZip = vZip
If vPhone <> "" Then vRecordSet!txtPhone = vPhone
If vNotes <> "" Then vRecordSet!memNotes = vNotes

'update the RecordSet to the database, this adds your new client to the DB
vRecordSet.Update
'advise the user the client has been added
MsgBox vClientFName & " " & vClientLName & " has been added to your
database."

'close objects
vRecordSet.Close
vConnection.Close

'clear object to free up memory
Set vRecordSet = Nothing
Set vConnection = Nothing

End Sub

 

[Gordon Bentley-Mix on news.microsoft.com]

Ann,

A bit tricky to understand your code as it's displayed in the NG, but I
think I've got it sussed. ;-D

The short answer to your question is to declare a String variable and set it
to the .Value property of your ComboBox - something like:

Dim vChoice As String
....
vChoice = cmbChoose.Value
....
vChoice = vRecordSet("txtChoice")
....
If vChoice <> "" Then vRecordSet!txtChoice = vChoice
....
txtChoice.Text = vChoice

Something like that anyway, assuming that I understand your code correctly.

I'm not sure if setting the value of the ComboBox from the database is
exactly right - not something I'm too familiar with - but I think it should
work.

And since you say you're not a programmer (not that I really am either
but...), if I might offer a couple of tips for optimising your code -

I'd strongly recommend selecting the 'Require Variable Declaration' option
(in the VBE click Tools | Options... and look in the 'Code Settings' group on
the 'Editor' tab). This will automatically insert an Option Explicit
statement at the start of each new code module and can be very helpful when
debugging because undeclared variables (like the kind that come from
accidentally typing the variable name incorrectly) get flagged - but then I'm
sure you know this and just omitted the Option Explicit statement from your
post, right? ;-) (Actually, I know you've not set this option because of
something discussed below - but that's OK.)

Rather than declaring your variables in each procedure (sub), you can
declare them at the module level and use them across all procedures within
the module. You do this by putting just a single set of Dim statements before
the UserForm_Initialize code and after the Option Explicit statement.

If you declare vCorrectRecord as a VbMsgBoxResult type, then you don't have
to try to guess at the integer equivalents of the return value of a message
box; Intellisense will give you a selection of 'meaningful' values to choose
from - like vbYes instead of 6. (This is how I know you've not set the option
discussed above: vCorrectRecord appears in your code without a corresponding
Dim statement. vConnectionState is similarly undeclared.)

Rather than using <> "" to see if a value is blank, it's *slightly* more
efficient to check the length of a value using the Len function. For example,
the line

If vChoice <> "" Then vRecordSet!txtChoice = vChoice

could be rewritten as

If Len(vChoice) <> 0 Then vRecordSet!txtChoice = vChoice

You could use a module-level String variable for the value used for the
ConnectionString in the statement 'vConnection.ConnectionString = ..." and
set this value in the UserForm_Initialize code. Might save you a bit of
typing if the value changes. I think this could also be declared as CONST,
but again it's not something I do regularly so I'm not completely sure how to
go about it.

Finally, you might consider using the Trim function to remove any leading or
trailing spaces from the values entered into a TextBox. For example, the line

vClientFName = txtFirstName.Text

could be rewritten as

vClientFName = Trim(txtFirstName.Text)

This will ensure that your document looks OK when you write the results into
the bookmarks and keep your database cleaner as well.

Otherwise, I quite like your code, and I've learned a lot from it. I'm about
to embark on a project where I need to read data from a SQL data source, and
your post has given me a fair amount of help. Thanks!
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no
membership required!

 

[Gordon Bentley-Mix on news.microsoft.com]

BTW, I just noticed that you mentioned CheckBoxes in your original post.
Working with CheckBoxes and OptionButtons is similar to working with
ComboBoxes in that you record the .Value property of the control. These
controls return (Boolean) 'True' if selected and 'False' if not. I'm not sure
how these values would be recorded in Access though. Does Access support a
Boolean data type field? If so then you shoudl be fine as the .Value property
of these controls is already Boolean. In addition, VBA should be able to
handle the reverse and use the Boolean value from the database to set the
..Value property of the control.

OTOH, if Access *doesn't* support Boolean data types, then you may need to
record these values as Strings, in which case you will need to convert them
to Strings using the CStr function. Then when you set the .Value property of
the control based on the value from the database, you'll need the CBool
function to convert them back to Boolean. (Although in truth the VBA compiler
should do both operations for you automatically - but I'm a bit pedantic
about these things.)

For ListBoxes, you use the .ListIndex and .List properties. (.List also
works with ComboBoxes.) The .ListIndex property indicates which list item is
selected in the ListBox and the .List property holds the value/s of the list
item. For example, assuming you have a ListBox control called 'ListBox1' with
two columns that contain data like:

Item 1 | Description of Item 1
Item 2 | Description of Item 2
....
Item n | Description of Item n

If you select Item 2 (which has a .ListIndex of 1 because list items are
numbered from 0) and want to display the description of Item 2 (which is in
column 1 because the columns are numbered from 0 as well) in a message box in
response to some event (like double-clicking on the list item), you would use
code similar to:

MsgBox ListBox1.List(ListBox1.ListIndex, 1)

Note that if you have a ListBox that supports multiple selections, things
get a bit more complicated in that you have to interate through the list
items (which involves using a loop and the .ListCount property of the
ListBox) and check the .Selected property of each list item to determine
which ones are selected. But that's fairly advanced stuff and probably best
suited to a discussion outside of the NG.
--
Cheers!

Gordon Bentley-Mix
Word MVP

Please post all follow-ups to the newsgroup.

Read the original version of this post in the Office Discussion Groups - no
membership required!

 

[Ann]

Thank you Gordon for the help. I'm going to give this a try and I'll let you
know how it turns out.

I also want to thank you for going above and beyond and giving me all the
extra information. I will make the changes you recommended to the variables
and add the option explicit...have a great day!

 

[Ann]

Hi Gordon,

I did everything you recommended for the combo box, thanks again. I did get
an error though on the txtChoice.Text = vChoice.

The first was that I didn't declare it, so I did. Then I received a
different compile error, Invalid Qualifier.

I've listed the new code below. I'm still going to try the list boxes,
checkboxes, etc. when I get a chance so thanks for that too. Also, I'm glad
you can use some of this. I was finally able to help someone else.

Option Explicit

'declare variables for new connection and recordset and declare variables

Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim vClientFName As String
Dim vClientLName As String
Dim vCompany As String
Dim vAddress As String
Dim vCity As String
Dim vState As String
Dim vZip As String
Dim vPhone As String
Dim vNotes As String
Dim vChoice As String
Dim txtChoice As String

Const vPath = "data
source=\\corpfs03\Shared\FACILADM\SHARED\OIPC\Users\Ann\Goals\WordClientInfo.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"

Private Sub cmbChoice_Change()

End Sub

Private Sub UserForm_Initialize()

cmbChoice.AddItem "Choice One"
cmbChoice.AddItem "Choice Two"

End Sub
Private Sub cmdAlreadyEntered_Click()

Dim vConnectionState As String
Dim vCorrectRecord As VbMsgBoxResult

vChoice = cmbChoice.Value

'provide connection string for data using Jet Provider for Access database
vConnection.ConnectionString = vPath
'open connection
vConnection.Open

'test connection state...this can later be commented out...but it helps
'let you know if the connection was successful when testing the code
vConnectionState = vConnection.State
If vConnectionState = 1 Then
MsgBox "The connection to this database is working!", vbInformation
Else
MsgBox "You were unable to connect to the assigned database!",
vbInformation
End If

'set variable equal to whatever is entered into First & LastName form
field
'so you can query the DB with this name to see if a record exists
vClientFName = Trim(txtFirstName.Text)
vClientLName = Trim(txtLastName.Text)

'open a RecordSet with SQL query results...to see if first/last name
matches a record
vRecordSet.Open "SELECT * FROM tblClientInfo WHERE
tblClientInfo!txtFirstName = " & _
Chr(34) & vClientFName & Chr(34) & "AND tblClientInfo!txtLastName = "
& _
Chr(34) & vClientLName & Chr(34), vConnection, adOpenKeyset,
adLockOptimistic

'if a match is found, display it to the user in a message box
'you'll get a match if you are NOT .EOF (end of file)
With vRecordSet
If Not .EOF Then
vCorrectRecord = MsgBox("Is this the correct record?" & Chr(13) &
Chr(13) & _
vRecordSet("txtFirstName") & " " & _
vRecordSet("txtLastName") & ", " & _
vRecordSet("txtAddress") & ", " & _
vRecordSet("txtCity"), _
vbYesNo + vbInformation, "User Record")
Else
'if you ARE *else* (are .EOF), that means no record was matched,
tell the user
MsgBox "No possible match was found."
End If
End With

'If the answer equals vbYes, it means they clicked Yes on the MsgBox
accepting this match
If vCorrectRecord = vbYes Then
'if yes...set variables from DB fields (the name variables are
already set above)
vCompany = vRecordSet("txtCompany")
vAddress = vRecordSet("txtAddress")
vCity = vRecordSet("txtCity")
vState = vRecordSet("txtState")
vZip = vRecordSet("txtZip")
vPhone = vRecordSet("txtPhone")
vNotes = vRecordSet("memNotes")
vChoice = vRecordSet("txtChoice")

'set the form field's bookmarks to the results from the database
field variables
txtCompany.Text = vCompany
txtAddress.Text = vAddress
txtCity.Text = vCity
txtState.Text = vState
txtZip.Text = vZip
txtPhone.Text = vPhone
txtNotes.Text = vNotes
txtChoice.Text = vChoice
Else
'if not 6, then not Yes, so must be NO...remind user to update
database!
MsgBox "Since this is not the correct entry..." & Chr(13) & _
"be sure to fill out remaining form fields and click *Update* " &
Chr(13) & _
"so this person will be added to the database."
End If

'close objects
vRecordSet.Close
vConnection.Close

'clear object to free up memory
Set vRecordSet = Nothing
Set vConnection = Nothing

End Sub
Private Sub cmdUpdateNew_Click()

'provide same as previous connection string for data using Jet Provider
for Access database
vConnection.ConnectionString = vPath
'open connection
vConnection.Open

'set variables from text enter into all the AutoForm field bookmark
results
vClientFName = txtFirstName.Text
vClientLName = txtLastName.Text
vCompany = txtCompany.Text
vAddress = txtAddress.Text
vCity = txtCity.Text
vState = txtState.Text
vZip = txtZip.Text
vPhone = txtPhone.Text
vNotes = txtNotes.Text
vChoice = cmbChoice.Value

'Open a new version of the temporary RecordSet accessing the ClientInfo
table in Database
vRecordSet.Open "tblClientInfo", vConnection, adOpenKeyset,
adLockOptimistic
vRecordSet.AddNew
'we can't enter "nothing", so only set variables with some data entered
info the field
If Len(vClientFName) <> 0 Then vRecordSet!txtFirstName = vClientFName
If Len(vClientLName) <> 0 Then vRecordSet!txtLastName = vClientLName
If Len(vCompany) <> 0 Then vRecordSet!txtCompany = vCompany
If Len(vAddress) <> 0 Then vRecordSet!txtAddress = vAddress
If Len(vCity) <> 0 Then vRecordSet!txtCity = vCity
If Len(vState) <> 0 Then vRecordSet!txtState = vState
If Len(vZip) <> 0 Then vRecordSet!txtZip = vZip
If Len(vPhone) <> 0 Then vRecordSet!txtPhone = vPhone
If Len(vNotes) <> 0 Then vRecordSet!memNotes = vNotes
If Len(vChoice) <> 0 Then vRecordSet!txtChoice = vChoice

'update the RecordSet to the database, this adds your new client to the DB
vRecordSet.Update
'advise the user the client has been added
MsgBox vClientFName & " " & vClientLName & " has been added to your
database."

'close objects
vRecordSet.Close
vConnection.Close

'clear object to free up memory
Set vRecordSet = Nothing
Set vConnection = Nothing

End Sub

 

[Gordon Bentley-Mix]

Ann,

Would you mind emailing me your template to look at? It might be easier if I
can view your project as a whole. I know it won't run on my machine since I
don't have access to your data source, but I don't really need to run it; I
just want to see how the whole thing hangs together. The email address in my
profile is valid.