Combo Box Search Subform not working properly....HELP!

H

Horselover

Hi,

I have a combo box that is part of my main form; I am using it as a search
tool to locate records in the database. It uses LastName, FirstName, and
CustomerNumber. The problem I'm having with it is that when there are
several customers with the same last name, it will only allow me to select
the first one from the combo box; if I try to select any of the others, it
only brings up the first one; for instance, I have Emily, John and Susan
Andrews; if I click on Susan or John Andrews, it brings up Emily and will not
allow me to select the other two. I have Next and Previous Record buttons on
the form to allow the end user to go to the other records, but this is making
me crazy! Any ideas? THANK YOU!
 
K

KARL DEWEY

Is CustomerNumber the bound column or is the LastName the bound column? I
think it need to be the CustomerNumber.
 
H

Horselover

Dave,
Here's the code:
Private Sub Combo101_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Last Name] = '" & Me![Combo101] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Karl,
Yes, CustomerNumber is the bound column.
 
K

Klatuu

You problem is here:
rs.FindFirst "[Last Name] = '" & Me![Combo101] & "'"

You are always looking only at the last name. There are a couple of
options. If you have a primary key field, you need to include it in the
rowsource of your combo. Otherwise, you could search on first and last
names. For example purposers, I will assume last name is the first column
(0) and first name is the second (1):
rs.FindFirst "[Last Name] = """ & Me![Combo101] & """ [First Name] = """
& Me![Combo101].Column(0) & """

Note I changed the quote marks. When you are dealing with names, you are
going to run in to O'Reilly or O'Brien sooner or later, and it will get
confused.
--
Dave Hargis, Microsoft Access MVP


Horselover said:
Dave,
Here's the code:
Private Sub Combo101_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Last Name] = '" & Me![Combo101] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Karl,
Yes, CustomerNumber is the bound column.

KARL DEWEY said:
Is CustomerNumber the bound column or is the LastName the bound column? I
think it need to be the CustomerNumber.
Click to expand...
Click to expand...
 
H

Horselover

Hi Dave,

I am trying to put this into the code, but since I'm not all that Visual
Basic Literate (okay, not at all!), could you please write out the whole
thing so I can copy and paste it? It keeps giving me different errors that I
have no idea how to interpret or fix.... latest was "Compile Error - Syntax
Error". I really appreciate your help with this!


Klatuu said:
You problem is here:
rs.FindFirst "[Last Name] = '" & Me![Combo101] & "'"

You are always looking only at the last name. There are a couple of
options. If you have a primary key field, you need to include it in the
rowsource of your combo. Otherwise, you could search on first and last
names. For example purposers, I will assume last name is the first column
(0) and first name is the second (1):
rs.FindFirst "[Last Name] = """ & Me![Combo101] & """ [First Name] = """
& Me![Combo101].Column(0) & """

Note I changed the quote marks. When you are dealing with names, you are
going to run in to O'Reilly or O'Brien sooner or later, and it will get
confused.
--
Dave Hargis, Microsoft Access MVP


Horselover said:
Dave,
Here's the code:
Private Sub Combo101_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Last Name] = '" & Me![Combo101] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Karl,
Yes, CustomerNumber is the bound column.

KARL DEWEY said:
Is CustomerNumber the bound column or is the LastName the bound column? I
think it need to be the CustomerNumber.
--
KARL DEWEY
Build a little - Test a little


:

Hi,

I have a combo box that is part of my main form; I am using it as a search
tool to locate records in the database. It uses LastName, FirstName, and
CustomerNumber. The problem I'm having with it is that when there are
several customers with the same last name, it will only allow me to select
the first one from the combo box; if I try to select any of the others, it
only brings up the first one; for instance, I have Emily, John and Susan
Andrews; if I click on Susan or John Andrews, it brings up Emily and will not
allow me to select the other two. I have Next and Previous Record buttons on
the form to allow the end user to go to the other records, but this is making
me crazy! Any ideas? THANK YOU!
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

I have no way to test your code from here. All you need to do is replace the
original line:

rs.FindFirst "[Last Name] = '" & Me![Combo101] & "'"

With this:

rs.FindFirst "[Last Name] = """ & Me![Combo101] & """ [First Name] = """
& Me![Combo101].Column(1) & """

I don't know the name of the field for first name, you will have to change
it if it is not correctr.

If you are getting a compile error, let me know which line of code it is on,
please.

--
Dave Hargis, Microsoft Access MVP


Horselover said:
Hi Dave,

I am trying to put this into the code, but since I'm not all that Visual
Basic Literate (okay, not at all!), could you please write out the whole
thing so I can copy and paste it? It keeps giving me different errors that I
have no idea how to interpret or fix.... latest was "Compile Error - Syntax
Error". I really appreciate your help with this!


Klatuu said:
You problem is here:
rs.FindFirst "[Last Name] = '" & Me![Combo101] & "'"

You are always looking only at the last name. There are a couple of
options. If you have a primary key field, you need to include it in the
rowsource of your combo. Otherwise, you could search on first and last
names. For example purposers, I will assume last name is the first column
(0) and first name is the second (1):
rs.FindFirst "[Last Name] = """ & Me![Combo101] & """ [First Name] = """
& Me![Combo101].Column(0) & """

Note I changed the quote marks. When you are dealing with names, you are
going to run in to O'Reilly or O'Brien sooner or later, and it will get
confused.
--
Dave Hargis, Microsoft Access MVP


Horselover said:
Dave,
Here's the code:
Private Sub Combo101_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Last Name] = '" & Me![Combo101] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Karl,
Yes, CustomerNumber is the bound column.

:

Is CustomerNumber the bound column or is the LastName the bound column? I
think it need to be the CustomerNumber.
--
KARL DEWEY
Build a little - Test a little


:

Hi,

I have a combo box that is part of my main form; I am using it as a search
tool to locate records in the database. It uses LastName, FirstName, and
CustomerNumber. The problem I'm having with it is that when there are
several customers with the same last name, it will only allow me to select
the first one from the combo box; if I try to select any of the others, it
only brings up the first one; for instance, I have Emily, John and Susan
Andrews; if I click on Susan or John Andrews, it brings up Emily and will not
allow me to select the other two. I have Next and Previous Record buttons on
the form to allow the end user to go to the other records, but this is making
me crazy! Any ideas? THANK YOU!
Click to expand...
Click to expand...
Click to expand...
 
H

Horselover

Okay, I copied it - (and you have the First Name field name correct, thanks!)

I'm now getting "Runtime Error 3077: Syntax Error (Missing Operator) in
Expression". When I click Debug, it highlights the new line of code in
yellow. Is there something else I should do to see the exact error?


Klatuu said:
I have no way to test your code from here. All you need to do is replace the
original line:

rs.FindFirst "[Last Name] = '" & Me![Combo101] & "'"

With this:

rs.FindFirst "[Last Name] = """ & Me![Combo101] & """ [First Name] = """
& Me![Combo101].Column(1) & """

I don't know the name of the field for first name, you will have to change
it if it is not correctr.

If you are getting a compile error, let me know which line of code it is on,
please.

--
Dave Hargis, Microsoft Access MVP


Horselover said:
Hi Dave,

I am trying to put this into the code, but since I'm not all that Visual
Basic Literate (okay, not at all!), could you please write out the whole
thing so I can copy and paste it? It keeps giving me different errors that I
have no idea how to interpret or fix.... latest was "Compile Error - Syntax
Error". I really appreciate your help with this!


Klatuu said:
You problem is here:
rs.FindFirst "[Last Name] = '" & Me![Combo101] & "'"

You are always looking only at the last name. There are a couple of
options. If you have a primary key field, you need to include it in the
rowsource of your combo. Otherwise, you could search on first and last
names. For example purposers, I will assume last name is the first column
(0) and first name is the second (1):
rs.FindFirst "[Last Name] = """ & Me![Combo101] & """ [First Name] = """
& Me![Combo101].Column(0) & """

Note I changed the quote marks. When you are dealing with names, you are
going to run in to O'Reilly or O'Brien sooner or later, and it will get
confused.
--
Dave Hargis, Microsoft Access MVP


:

Dave,
Here's the code:
Private Sub Combo101_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Last Name] = '" & Me![Combo101] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Karl,
Yes, CustomerNumber is the bound column.

:

Is CustomerNumber the bound column or is the LastName the bound column? I
think it need to be the CustomerNumber.
--
KARL DEWEY
Build a little - Test a little


:

Hi,

I have a combo box that is part of my main form; I am using it as a search
tool to locate records in the database. It uses LastName, FirstName, and
CustomerNumber. The problem I'm having with it is that when there are
several customers with the same last name, it will only allow me to select
the first one from the combo box; if I try to select any of the others, it
only brings up the first one; for instance, I have Emily, John and Susan
Andrews; if I click on Susan or John Andrews, it brings up Emily and will not
allow me to select the other two. I have Next and Previous Record buttons on
the form to allow the end user to go to the other records, but this is making
me crazy! Any ideas? THANK YOU!
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

I am sorry, I left something out. It should be:
rs.FindFirst "[Last Name] = """ & Me![Combo101] & """ AND [First Name]
= """
& Me![Combo101].Column(1) & """

--
Dave Hargis, Microsoft Access MVP


Horselover said:
Okay, I copied it - (and you have the First Name field name correct, thanks!)

I'm now getting "Runtime Error 3077: Syntax Error (Missing Operator) in
Expression". When I click Debug, it highlights the new line of code in
yellow. Is there something else I should do to see the exact error?


Klatuu said:
I have no way to test your code from here. All you need to do is replace the
original line:

rs.FindFirst "[Last Name] = '" & Me![Combo101] & "'"

With this:

rs.FindFirst "[Last Name] = """ & Me![Combo101] & """ [First Name] = """
& Me![Combo101].Column(1) & """

I don't know the name of the field for first name, you will have to change
it if it is not correctr.

If you are getting a compile error, let me know which line of code it is on,
please.

--
Dave Hargis, Microsoft Access MVP


Horselover said:
Hi Dave,

I am trying to put this into the code, but since I'm not all that Visual
Basic Literate (okay, not at all!), could you please write out the whole
thing so I can copy and paste it? It keeps giving me different errors that I
have no idea how to interpret or fix.... latest was "Compile Error - Syntax
Error". I really appreciate your help with this!


:

You problem is here:
rs.FindFirst "[Last Name] = '" & Me![Combo101] & "'"

You are always looking only at the last name. There are a couple of
options. If you have a primary key field, you need to include it in the
rowsource of your combo. Otherwise, you could search on first and last
names. For example purposers, I will assume last name is the first column
(0) and first name is the second (1):
rs.FindFirst "[Last Name] = """ & Me![Combo101] & """ [First Name] = """
& Me![Combo101].Column(0) & """

Note I changed the quote marks. When you are dealing with names, you are
going to run in to O'Reilly or O'Brien sooner or later, and it will get
confused.
--
Dave Hargis, Microsoft Access MVP


:

Dave,
Here's the code:
Private Sub Combo101_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Last Name] = '" & Me![Combo101] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Karl,
Yes, CustomerNumber is the bound column.

:

Is CustomerNumber the bound column or is the LastName the bound column? I
think it need to be the CustomerNumber.
--
KARL DEWEY
Build a little - Test a little


:

Hi,

I have a combo box that is part of my main form; I am using it as a search
tool to locate records in the database. It uses LastName, FirstName, and
CustomerNumber. The problem I'm having with it is that when there are
several customers with the same last name, it will only allow me to select
the first one from the combo box; if I try to select any of the others, it
only brings up the first one; for instance, I have Emily, John and Susan
Andrews; if I click on Susan or John Andrews, it brings up Emily and will not
allow me to select the other two. I have Next and Previous Record buttons on
the form to allow the end user to go to the other records, but this is making
me crazy! Any ideas? THANK YOU!
Click to expand...
Click to expand...
Click to expand...
 
H

Horselover

AWESOME! Thank you!! It works!! I SO appreciate your help!
Have a GREAT DAY!! :)

Klatuu said:
I am sorry, I left something out. It should be:
rs.FindFirst "[Last Name] = """ & Me![Combo101] & """ AND [First Name]
= """
& Me![Combo101].Column(1) & """

--
Dave Hargis, Microsoft Access MVP


Horselover said:
Okay, I copied it - (and you have the First Name field name correct, thanks!)

I'm now getting "Runtime Error 3077: Syntax Error (Missing Operator) in
Expression". When I click Debug, it highlights the new line of code in
yellow. Is there something else I should do to see the exact error?


Klatuu said:
I have no way to test your code from here. All you need to do is replace the
original line:

rs.FindFirst "[Last Name] = '" & Me![Combo101] & "'"

With this:

rs.FindFirst "[Last Name] = """ & Me![Combo101] & """ [First Name] = """
& Me![Combo101].Column(1) & """

I don't know the name of the field for first name, you will have to change
it if it is not correctr.

If you are getting a compile error, let me know which line of code it is on,
please.

--
Dave Hargis, Microsoft Access MVP


:

Hi Dave,

I am trying to put this into the code, but since I'm not all that Visual
Basic Literate (okay, not at all!), could you please write out the whole
thing so I can copy and paste it? It keeps giving me different errors that I
have no idea how to interpret or fix.... latest was "Compile Error - Syntax
Error". I really appreciate your help with this!


:

You problem is here:
rs.FindFirst "[Last Name] = '" & Me![Combo101] & "'"

You are always looking only at the last name. There are a couple of
options. If you have a primary key field, you need to include it in the
rowsource of your combo. Otherwise, you could search on first and last
names. For example purposers, I will assume last name is the first column
(0) and first name is the second (1):
rs.FindFirst "[Last Name] = """ & Me![Combo101] & """ [First Name] = """
& Me![Combo101].Column(0) & """

Note I changed the quote marks. When you are dealing with names, you are
going to run in to O'Reilly or O'Brien sooner or later, and it will get
confused.
--
Dave Hargis, Microsoft Access MVP


:

Dave,
Here's the code:
Private Sub Combo101_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Last Name] = '" & Me![Combo101] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Karl,
Yes, CustomerNumber is the bound column.

:

Is CustomerNumber the bound column or is the LastName the bound column? I
think it need to be the CustomerNumber.
--
KARL DEWEY
Build a little - Test a little


:

Hi,

I have a combo box that is part of my main form; I am using it as a search
tool to locate records in the database. It uses LastName, FirstName, and
CustomerNumber. The problem I'm having with it is that when there are
several customers with the same last name, it will only allow me to select
the first one from the combo box; if I try to select any of the others, it
only brings up the first one; for instance, I have Emily, John and Susan
Andrews; if I click on Susan or John Andrews, it brings up Emily and will not
allow me to select the other two. I have Next and Previous Record buttons on
the form to allow the end user to go to the other records, but this is making
me crazy! Any ideas? THANK YOU!
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

Glad I could help. Sorry I slowed you down with the typo.
--
Dave Hargis, Microsoft Access MVP


Horselover said:
AWESOME! Thank you!! It works!! I SO appreciate your help!
Have a GREAT DAY!! :)

Klatuu said:
I am sorry, I left something out. It should be:
rs.FindFirst "[Last Name] = """ & Me![Combo101] & """ AND [First Name]
= """
& Me![Combo101].Column(1) & """

--
Dave Hargis, Microsoft Access MVP


Horselover said:
Okay, I copied it - (and you have the First Name field name correct, thanks!)

I'm now getting "Runtime Error 3077: Syntax Error (Missing Operator) in
Expression". When I click Debug, it highlights the new line of code in
yellow. Is there something else I should do to see the exact error?


:

I have no way to test your code from here. All you need to do is replace the
original line:

rs.FindFirst "[Last Name] = '" & Me![Combo101] & "'"

With this:

rs.FindFirst "[Last Name] = """ & Me![Combo101] & """ [First Name] = """
& Me![Combo101].Column(1) & """

I don't know the name of the field for first name, you will have to change
it if it is not correctr.

If you are getting a compile error, let me know which line of code it is on,
please.

--
Dave Hargis, Microsoft Access MVP


:

Hi Dave,

I am trying to put this into the code, but since I'm not all that Visual
Basic Literate (okay, not at all!), could you please write out the whole
thing so I can copy and paste it? It keeps giving me different errors that I
have no idea how to interpret or fix.... latest was "Compile Error - Syntax
Error". I really appreciate your help with this!


:

You problem is here:
rs.FindFirst "[Last Name] = '" & Me![Combo101] & "'"

You are always looking only at the last name. There are a couple of
options. If you have a primary key field, you need to include it in the
rowsource of your combo. Otherwise, you could search on first and last
names. For example purposers, I will assume last name is the first column
(0) and first name is the second (1):
rs.FindFirst "[Last Name] = """ & Me![Combo101] & """ [First Name] = """
& Me![Combo101].Column(0) & """

Note I changed the quote marks. When you are dealing with names, you are
going to run in to O'Reilly or O'Brien sooner or later, and it will get
confused.
--
Dave Hargis, Microsoft Access MVP


:

Dave,
Here's the code:
Private Sub Combo101_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Last Name] = '" & Me![Combo101] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Karl,
Yes, CustomerNumber is the bound column.

:

Is CustomerNumber the bound column or is the LastName the bound column? I
think it need to be the CustomerNumber.
--
KARL DEWEY
Build a little - Test a little


:

Hi,

I have a combo box that is part of my main form; I am using it as a search
tool to locate records in the database. It uses LastName, FirstName, and
CustomerNumber. The problem I'm having with it is that when there are
several customers with the same last name, it will only allow me to select
the first one from the combo box; if I try to select any of the others, it
only brings up the first one; for instance, I have Emily, John and Susan
Andrews; if I click on Susan or John Andrews, it brings up Emily and will not
allow me to select the other two. I have Next and Previous Record buttons on
the form to allow the end user to go to the other records, but this is making
me crazy! Any ideas? THANK YOU!
Click to expand...
Click to expand...
Click to expand...
 
H

Horselover

Oh that's okay - this database is still in process, so I was doing other
things as I was trying to get it to work! :)
Take care!


Klatuu said:
Glad I could help. Sorry I slowed you down with the typo.
--
Dave Hargis, Microsoft Access MVP


Horselover said:
AWESOME! Thank you!! It works!! I SO appreciate your help!
Have a GREAT DAY!! :)

Klatuu said:
I am sorry, I left something out. It should be:
rs.FindFirst "[Last Name] = """ & Me![Combo101] & """ AND [First Name]
= """
& Me![Combo101].Column(1) & """

--
Dave Hargis, Microsoft Access MVP


:

Okay, I copied it - (and you have the First Name field name correct, thanks!)

I'm now getting "Runtime Error 3077: Syntax Error (Missing Operator) in
Expression". When I click Debug, it highlights the new line of code in
yellow. Is there something else I should do to see the exact error?


:

I have no way to test your code from here. All you need to do is replace the
original line:

rs.FindFirst "[Last Name] = '" & Me![Combo101] & "'"

With this:

rs.FindFirst "[Last Name] = """ & Me![Combo101] & """ [First Name] = """
& Me![Combo101].Column(1) & """

I don't know the name of the field for first name, you will have to change
it if it is not correctr.

If you are getting a compile error, let me know which line of code it is on,
please.

--
Dave Hargis, Microsoft Access MVP


:

Hi Dave,

I am trying to put this into the code, but since I'm not all that Visual
Basic Literate (okay, not at all!), could you please write out the whole
thing so I can copy and paste it? It keeps giving me different errors that I
have no idea how to interpret or fix.... latest was "Compile Error - Syntax
Error". I really appreciate your help with this!


:

You problem is here:
rs.FindFirst "[Last Name] = '" & Me![Combo101] & "'"

You are always looking only at the last name. There are a couple of
options. If you have a primary key field, you need to include it in the
rowsource of your combo. Otherwise, you could search on first and last
names. For example purposers, I will assume last name is the first column
(0) and first name is the second (1):
rs.FindFirst "[Last Name] = """ & Me![Combo101] & """ [First Name] = """
& Me![Combo101].Column(0) & """

Note I changed the quote marks. When you are dealing with names, you are
going to run in to O'Reilly or O'Brien sooner or later, and it will get
confused.
--
Dave Hargis, Microsoft Access MVP


:

Dave,
Here's the code:
Private Sub Combo101_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Last Name] = '" & Me![Combo101] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Karl,
Yes, CustomerNumber is the bound column.

:

Is CustomerNumber the bound column or is the LastName the bound column? I
think it need to be the CustomerNumber.
--
KARL DEWEY
Build a little - Test a little


:

Hi,

I have a combo box that is part of my main form; I am using it as a search
tool to locate records in the database. It uses LastName, FirstName, and
CustomerNumber. The problem I'm having with it is that when there are
several customers with the same last name, it will only allow me to select
the first one from the combo box; if I try to select any of the others, it
only brings up the first one; for instance, I have Emily, John and Susan
Andrews; if I click on Susan or John Andrews, it brings up Emily and will not
allow me to select the other two. I have Next and Previous Record buttons on
the form to allow the end user to go to the other records, but this is making
me crazy! Any ideas? THANK YOU!
Click to expand...
Click to expand...
Click to expand...
 
Top