Combo Box Function

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I have a form that contains only a combo box. Once a selection (Last Name)
is selected from the combo box, it then brings up a detailed customer
record.

The code for the combo box is:

*******************************************
Private Sub cboCompany_AfterUpdate()

' Record Selection
If Not IsNull(Me.cboCompany) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[Last] = """ & Me.cboCompany & """"

If rs.NoMatch Then
MsgBox "Is this a New Record?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


' Open sfrmCustomers
On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "sfrmContacts"

stLinkCriteria = "[Last]=" & "'" & Me![cboCompany] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click

End Sub
*******************************************

I now want allow to see the first name in addition to the lastname. I
increased the column count to 2 (on the Format tab) of the form.

So far so good... however, if I have multipe records where the last name
e.g. begins with "Smith" then I have will pull up the 1st record of all of
the "Smith" records (even though I selected the e.g. 3rd Smith record.

My question is... how do I edit the function above so that it not only
"looks" at the first column (Last Name) but also the second column (First
Name).

Any advice is appreciated.
 
search the recordset by the table's primary key field, instead of "Last
Name".
add the primary key field to the combo box, and make sure it is the bound
column. if you don't want to see the primary key field in the droplist, you
can set its' column width to 0".
change your code to FindFirst on the primary key field.

hth
 
Tina:

Thanks for the info... I tried to now look for the record id (instead of
last name) but it doesn't seem to work since the primary key is an
autonumber. I now get a VBA error that indicates "data mismatch".

Is there a way around this?

--
Thanks,
Tom


tina said:
search the recordset by the table's primary key field, instead of "Last
Name".
add the primary key field to the combo box, and make sure it is the bound
column. if you don't want to see the primary key field in the droplist, you
can set its' column width to 0".
change your code to FindFirst on the primary key field.

hth


Tom said:
I have a form that contains only a combo box. Once a selection (Last Name)
is selected from the combo box, it then brings up a detailed customer
record.

The code for the combo box is:

*******************************************
Private Sub cboCompany_AfterUpdate()

' Record Selection
If Not IsNull(Me.cboCompany) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[Last] = """ & Me.cboCompany & """"

If rs.NoMatch Then
MsgBox "Is this a New Record?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


' Open sfrmCustomers
On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "sfrmContacts"

stLinkCriteria = "[Last]=" & "'" & Me![cboCompany] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click

End Sub
*******************************************

I now want allow to see the first name in addition to the lastname. I
increased the column count to 2 (on the Format tab) of the form.

So far so good... however, if I have multipe records where the last name
e.g. begins with "Smith" then I have will pull up the 1st record of all of
the "Smith" records (even though I selected the e.g. 3rd Smith record.

My question is... how do I edit the function above so that it not only
"looks" at the first column (Last Name) but also the second column (First
Name).

Any advice is appreciated.
Click to expand...
Click to expand...
 
If you're looking for a numeric value, change

rs.FindFirst "[Last] = """ & Me.cboCompany & """"

to

rs.FindFirst "[Last] = " & Me.cboCompany

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Tom said:
Tina:

Thanks for the info... I tried to now look for the record id (instead of
last name) but it doesn't seem to work since the primary key is an
autonumber. I now get a VBA error that indicates "data mismatch".

Is there a way around this?

--
Thanks,
Tom


tina said:
search the recordset by the table's primary key field, instead of "Last
Name".
add the primary key field to the combo box, and make sure it is the bound
column. if you don't want to see the primary key field in the droplist, you
can set its' column width to 0".
change your code to FindFirst on the primary key field.

hth


Tom said:
I have a form that contains only a combo box. Once a selection (Last Name)
is selected from the combo box, it then brings up a detailed customer
record.

The code for the combo box is:

*******************************************
Private Sub cboCompany_AfterUpdate()

' Record Selection
If Not IsNull(Me.cboCompany) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[Last] = """ & Me.cboCompany & """"

If rs.NoMatch Then
MsgBox "Is this a New Record?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


' Open sfrmCustomers
On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "sfrmContacts"

stLinkCriteria = "[Last]=" & "'" & Me![cboCompany] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click

End Sub
*******************************************

I now want allow to see the first name in addition to the lastname. I
increased the column count to 2 (on the Format tab) of the form.

So far so good... however, if I have multipe records where the last name
e.g. begins with "Smith" then I have will pull up the 1st record of
Click to expand...
Click to expand...
all
of
Click to expand...
Click to expand...
 
Douglas:

Thanks for sharing more info on this...

I have changed the value as you suggested... I now get the error that the
"OpenForm action was canceled."

I changed the following:

rs.FindFirst "[Last] = """ & Me.cboCompany & """"
to
rs.FindFirst "[ContactID] = " & Me.cboCompany

&

stLinkCriteria = "[Last]=" & "'" & Me![cboCompany] & "'"
to
stLinkCriteria = "[ContactID]=" & "'" & Me![cboCompany] & "'"


while "Last" = text data type
and "ContactID" = autonumber




--
Thanks,
Tom


Douglas J. Steele said:
If you're looking for a numeric value, change

rs.FindFirst "[Last] = """ & Me.cboCompany & """"

to

rs.FindFirst "[Last] = " & Me.cboCompany

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Tom said:
Tina:

Thanks for the info... I tried to now look for the record id (instead of
last name) but it doesn't seem to work since the primary key is an
autonumber. I now get a VBA error that indicates "data mismatch".

Is there a way around this?

--
Thanks,
Tom


tina said:
search the recordset by the table's primary key field, instead of "Last
Name".
add the primary key field to the combo box, and make sure it is the bound
column. if you don't want to see the primary key field in the
Click to expand...
droplist,
you
can set its' column width to 0".
change your code to FindFirst on the primary key field.

hth


I have a form that contains only a combo box. Once a selection (Last
Name)
is selected from the combo box, it then brings up a detailed customer
record.

The code for the combo box is:

*******************************************
Private Sub cboCompany_AfterUpdate()

' Record Selection
If Not IsNull(Me.cboCompany) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[Last] = """ & Me.cboCompany & """"

If rs.NoMatch Then
MsgBox "Is this a New Record?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


' Open sfrmCustomers
On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "sfrmContacts"

stLinkCriteria = "[Last]=" & "'" & Me![cboCompany] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click

End Sub
*******************************************

I now want allow to see the first name in addition to the lastname. I
increased the column count to 2 (on the Format tab) of the form.

So far so good... however, if I have multipe records where the last name
e.g. begins with "Smith" then I have will pull up the 1st record of
Click to expand...
all
of
the "Smith" records (even though I selected the e.g. 3rd Smith record.

My question is... how do I edit the function above so that it not only
"looks" at the first column (Last Name) but also the second column (First
Name).

Any advice is appreciated.
Click to expand...
Click to expand...
Click to expand...
 
fix the link criteria, from
stLinkCriteria = "[ContactID]=" & "'" & Me![cboCompany] & "'"
Click to expand...

to

stLinkCriteria = "[ContactID]=" & Me![cboCompany]


Tom said:
Douglas:

Thanks for sharing more info on this...

I have changed the value as you suggested... I now get the error that the
"OpenForm action was canceled."

I changed the following:

rs.FindFirst "[Last] = """ & Me.cboCompany & """"
to
rs.FindFirst "[ContactID] = " & Me.cboCompany

&

stLinkCriteria = "[Last]=" & "'" & Me![cboCompany] & "'"
to
stLinkCriteria = "[ContactID]=" & "'" & Me![cboCompany] & "'"


while "Last" = text data type
and "ContactID" = autonumber




--
Thanks,
Tom


Douglas J. Steele said:
If you're looking for a numeric value, change

rs.FindFirst "[Last] = """ & Me.cboCompany & """"

to

rs.FindFirst "[Last] = " & Me.cboCompany

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Tom said:
Tina:

Thanks for the info... I tried to now look for the record id (instead of
last name) but it doesn't seem to work since the primary key is an
autonumber. I now get a VBA error that indicates "data mismatch".

Is there a way around this?

--
Thanks,
Tom


search the recordset by the table's primary key field, instead of "Last
Name".
add the primary key field to the combo box, and make sure it is the bound
column. if you don't want to see the primary key field in the droplist,
you
can set its' column width to 0".
change your code to FindFirst on the primary key field.

hth


I have a form that contains only a combo box. Once a selection (Last
Name)
is selected from the combo box, it then brings up a detailed customer
record.

The code for the combo box is:

*******************************************
Private Sub cboCompany_AfterUpdate()

' Record Selection
If Not IsNull(Me.cboCompany) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[Last] = """ & Me.cboCompany & """"

If rs.NoMatch Then
MsgBox "Is this a New Record?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


' Open sfrmCustomers
On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "sfrmContacts"

stLinkCriteria = "[Last]=" & "'" & Me![cboCompany] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click

End Sub
*******************************************

I now want allow to see the first name in addition to the
Click to expand...
lastname.
I
increased the column count to 2 (on the Format tab) of the form.

So far so good... however, if I have multipe records where the
Click to expand...
last
name
e.g. begins with "Smith" then I have will pull up the 1st record
Click to expand...
of
all
of
the "Smith" records (even though I selected the e.g. 3rd Smith record.

My question is... how do I edit the function above so that it not only
"looks" at the first column (Last Name) but also the second column
(First
Name).

Any advice is appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Perfect!!! That works great. Thanks, Tina.

Tom


tina said:
fix the link criteria, from
stLinkCriteria = "[ContactID]=" & "'" & Me![cboCompany] & "'"
Click to expand...

to

stLinkCriteria = "[ContactID]=" & Me![cboCompany]


Tom said:
Douglas:

Thanks for sharing more info on this...

I have changed the value as you suggested... I now get the error that the
"OpenForm action was canceled."

I changed the following:

rs.FindFirst "[Last] = """ & Me.cboCompany & """"
to
rs.FindFirst "[ContactID] = " & Me.cboCompany

&

stLinkCriteria = "[Last]=" & "'" & Me![cboCompany] & "'"
to
stLinkCriteria = "[ContactID]=" & "'" & Me![cboCompany] & "'"


while "Last" = text data type
and "ContactID" = autonumber




--
Thanks,
Tom


Douglas J. Steele said:
If you're looking for a numeric value, change

rs.FindFirst "[Last] = """ & Me.cboCompany & """"

to

rs.FindFirst "[Last] = " & Me.cboCompany

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Tina:

Thanks for the info... I tried to now look for the record id
Click to expand...
Click to expand...
(instead
of
last name) but it doesn't seem to work since the primary key is an
autonumber. I now get a VBA error that indicates "data mismatch".

Is there a way around this?

--
Thanks,
Tom


search the recordset by the table's primary key field, instead of "Last
Name".
add the primary key field to the combo box, and make sure it is the
bound
column. if you don't want to see the primary key field in the droplist,
you
can set its' column width to 0".
change your code to FindFirst on the primary key field.

hth


I have a form that contains only a combo box. Once a selection (Last
Name)
is selected from the combo box, it then brings up a detailed customer
record.

The code for the combo box is:

*******************************************
Private Sub cboCompany_AfterUpdate()

' Record Selection
If Not IsNull(Me.cboCompany) Then

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Me.RecordsetClone
rs.FindFirst "[Last] = """ & Me.cboCompany & """"

If rs.NoMatch Then
MsgBox "Is this a New Record?"
Else
Me.Bookmark = rs.Bookmark
End If

Set rs = Nothing
End If


' Open sfrmCustomers
On Error GoTo Err_Command01_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "sfrmContacts"

stLinkCriteria = "[Last]=" & "'" & Me![cboCompany] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command01_Click:
Exit Sub

Err_Command01_Click:
MsgBox Err.Description
Resume Exit_Command01_Click

End Sub
*******************************************

I now want allow to see the first name in addition to the lastname.
I
increased the column count to 2 (on the Format tab) of the form.

So far so good... however, if I have multipe records where the last
name
e.g. begins with "Smith" then I have will pull up the 1st record of
all
of
the "Smith" records (even though I selected the e.g. 3rd Smith record.

My question is... how do I edit the function above so that it
Click to expand...
not
only
"looks" at the first column (Last Name) but also the second column
(First
Name).

Any advice is appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top