Combo Box Function

[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.

 

[tina]

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]

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 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.

 

[Douglas J. Steele]

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 (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

 

[Tom]

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

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 (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 not only
"looks" at the first column (Last Name) but also the second column (First
Name).

Any advice is appreciated.

 

[tina]

fix the link criteria, from

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

to

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

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

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 (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 not only
"looks" at the first column (Last Name) but also the second column
(First
Name).

Any advice is appreciated.

 

[Tom]

Perfect!!! That works great. Thanks, Tina.

Tom

fix the link criteria, from

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

to

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

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

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

(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

not
only

"looks" at the first column (Last Name) but also the second column
(First
Name).

Any advice is appreciated.