finding a record based on a combo box choice

[Heidi]

I have a simple form that has DealerName (a text field), LicenseID (a number
field), and address info. I want to make a combo box that selects a record
on my form based on the choice picked from the combo box. I went through the
wizard's prompts, but when I try the combo box out, the dealer name and
subsequent license/address info doesn't show up on my form. Here is the code
the wizard created:

Private Sub Combo22_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LicenseID] = " & Str(Nz(Me![Combo22], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Any ideas?

 

[Allen Browne]

Suggestions:

1. Make sure Combo22 is unbound (nothing in its Control Source property.)

2. Try the code in this link:
http://allenbrowne.com/ser-03.html

This improves on the Wizard's attempt in several ways:
a) Explicitly saves before the move.
b) Declares the specific kind of object.
c) Test NoMatch instead of EOF (which is not right)
d) Gives a message if the match is not found.

 

[Ryan]

The first thing I would do is Name my combo box something other than the
default name of Combo22. When your app grows good naming of controls will be
important. Here is the code I use to find specific records.

Private Sub Combo22_AfterUpdate
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "TheNameOfYourForm"

stLinkCriteria = "[LicenseID]=" & "'" & Me![Combo22] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

 

[Linq Adams via AccessMonster.com]

You do realize that you have to have textboxes on your form bound to these
fields, don't you?

 

[Heidi]

Thanks, Allen. I change my combo box name to the name in your code and tried
your code... changing CustomerID to LicenseID. I get the message, "Not
found: filtered?" each time and don't know why. Both the form and the combo
box are pulling license info from the same table, so I'm not sure why it
won't recognize any of the license id's? Yes, the combo box is unbound. Any
guesses?

Suggestions:

1. Make sure Combo22 is unbound (nothing in its Control Source property.)

2. Try the code in this link:
http://allenbrowne.com/ser-03.html

This improves on the Wizard's attempt in several ways:
a) Explicitly saves before the move.
b) Declares the specific kind of object.
c) Test NoMatch instead of EOF (which is not right)
d) Gives a message if the match is not found.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a simple form that has DealerName (a text field), LicenseID (a
number
field), and address info. I want to make a combo box that selects a
record
on my form based on the choice picked from the combo box. I went through
the
wizard's prompts, but when I try the combo box out, the dealer name and
subsequent license/address info doesn't show up on my form. Here is the
code
the wizard created:

Private Sub Combo22_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LicenseID] = " & Str(Nz(Me![Combo22], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Any ideas?

 

[Heidi]

Thanks, Ryan. I tried your code too... I got a run time error 2501 which
said "The OpenForm action was cancelled." I'm guessing I don't really want
docmd.OpenForm because the form is already open... I just want it to display
all the license and address info for the license I picked in the combo box.
What do you think?

The first thing I would do is Name my combo box something other than the
default name of Combo22. When your app grows good naming of controls will be
important. Here is the code I use to find specific records.

Private Sub Combo22_AfterUpdate
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "TheNameOfYourForm"

stLinkCriteria = "[LicenseID]=" & "'" & Me![Combo22] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
--
Please remember to mark this post as answered if this solves your problem.

I have a simple form that has DealerName (a text field), LicenseID (a number
field), and address info. I want to make a combo box that selects a record
on my form based on the choice picked from the combo box. I went through the
wizard's prompts, but when I try the combo box out, the dealer name and
subsequent license/address info doesn't show up on my form. Here is the code
the wizard created:

Private Sub Combo22_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LicenseID] = " & Str(Nz(Me![Combo22], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Any ideas?

 

[Heidi]

Yes, the license and address information are bound... the only unbound field
is the combo box.

 

[Allen Browne]

Your results would suggest that the combo's bound column does not contain
the correct value to match the field you are searching.

In form design view, take a look at these properties of the combo:
Row Source
Column Count
Column Widths
Bound Column

The Row Source will be the name of a table or query, or a SQL statement. The
Column Count will tell you how many fields the combo is reading from the
table/query. The Column Widths will tell you wish columns the query is
showing you. The Bound Column will tell you which column contains the actual
value of the combo.

For example, say you see something like this:
Row Source SELECT TypeID, TypeName FROM tblType;
Column Count 2
Column Widths 0":1"
Bound Column 1
This would tell you that the combo reads 2 columns. The first one (TypeID)
is zero-width, so it shows the TypeName instead. However, the bound column
is the first one, so the actual value of the combo is not what you see in
the combo.

That's the kind of thing that could be causing it not to match.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen. I change my combo box name to the name in your code and
tried
your code... changing CustomerID to LicenseID. I get the message, "Not
found: filtered?" each time and don't know why. Both the form and the
combo
box are pulling license info from the same table, so I'm not sure why it
won't recognize any of the license id's? Yes, the combo box is unbound.
Any
guesses?

Suggestions:

1. Make sure Combo22 is unbound (nothing in its Control Source property.)

2. Try the code in this link:
http://allenbrowne.com/ser-03.html

This improves on the Wizard's attempt in several ways:
a) Explicitly saves before the move.
b) Declares the specific kind of object.
c) Test NoMatch instead of EOF (which is not right)
d) Gives a message if the match is not found.

I have a simple form that has DealerName (a text field), LicenseID (a
number
field), and address info. I want to make a combo box that selects a
record
on my form based on the choice picked from the combo box. I went
through
the
wizard's prompts, but when I try the combo box out, the dealer name and
subsequent license/address info doesn't show up on my form. Here is
the
code
the wizard created:

Private Sub Combo22_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LicenseID] = " & Str(Nz(Me![Combo22], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Heidi]

I checked all that you said to -- the SQL in the combo box is correct and is
pulling from the correct table. The form itself is pulling from that same
table, so the LicenseIDs do match. In the combo box, the column count is 1
and when I look at what the SQL it shows only the column labeled LicenseID.
The column width is 1" and the bound column is 1, indicating the actual value
is LicenseID. Looks like I've done everything right.

One thought... if I go to type in your code instead of just doing a copy and
past, the auto assist (or whatever you call it) doesn't pop up when I type
"rs.FindFirst"... usually if I write something like DoCmd... the auto assist
pops up to give me options and prompts me as to what to put in next. Does
that mean I have some kind of reference missing??? That happened before -- I
got some code from a newsgroup and needed the "Microsoft Active X Data
Objects 2.8 Library" checked off in my references to get the code "Dim
cnMARVIN As Connection" to work. Do you think it could be that?

Your results would suggest that the combo's bound column does not contain
the correct value to match the field you are searching.

In form design view, take a look at these properties of the combo:
Row Source
Column Count
Column Widths
Bound Column

The Row Source will be the name of a table or query, or a SQL statement. The
Column Count will tell you how many fields the combo is reading from the
table/query. The Column Widths will tell you wish columns the query is
showing you. The Bound Column will tell you which column contains the actual
value of the combo.

For example, say you see something like this:
Row Source SELECT TypeID, TypeName FROM tblType;
Column Count 2
Column Widths 0":1"
Bound Column 1
This would tell you that the combo reads 2 columns. The first one (TypeID)
is zero-width, so it shows the TypeName instead. However, the bound column
is the first one, so the actual value of the combo is not what you see in
the combo.

That's the kind of thing that could be causing it not to match.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen. I change my combo box name to the name in your code and
tried
your code... changing CustomerID to LicenseID. I get the message, "Not
found: filtered?" each time and don't know why. Both the form and the
combo
box are pulling license info from the same table, so I'm not sure why it
won't recognize any of the license id's? Yes, the combo box is unbound.
Any
guesses?

Suggestions:

1. Make sure Combo22 is unbound (nothing in its Control Source property.)

2. Try the code in this link:
http://allenbrowne.com/ser-03.html

This improves on the Wizard's attempt in several ways:
a) Explicitly saves before the move.
b) Declares the specific kind of object.
c) Test NoMatch instead of EOF (which is not right)
d) Gives a message if the match is not found.

I have a simple form that has DealerName (a text field), LicenseID (a
number
field), and address info. I want to make a combo box that selects a
record
on my form based on the choice picked from the combo box. I went
through
the
wizard's prompts, but when I try the combo box out, the dealer name and
subsequent license/address info doesn't show up on my form. Here is
the
code
the wizard created:

Private Sub Combo22_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LicenseID] = " & Str(Nz(Me![Combo22], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Allen Browne]

Are you certain you used this line at the top of the procedure:
Dim rs As DAO.Recordset
If you did, the Intellisense should know about FindFirst.
(If you just used Object, it won't.)

Can you actually step back through the records in your from to find the
desired record? Or is there some reason it might not be there (e.g. if you
set the form's DataEntry property to Yes, it won't be.)

Just above the rs.FindFirst line, add:
Debug.Print xxxxxx
where the xxxx represents exactly the same thing as you have after FindFirst
on the next line. When it fails, open the Immediate Window (Ctrl+G) and see
what comes out there. Then create a query that has the same criteria, and
see if that works.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I checked all that you said to -- the SQL in the combo box is correct and
is
pulling from the correct table. The form itself is pulling from that same
table, so the LicenseIDs do match. In the combo box, the column count is
1
and when I look at what the SQL it shows only the column labeled
LicenseID.
The column width is 1" and the bound column is 1, indicating the actual
value
is LicenseID. Looks like I've done everything right.

One thought... if I go to type in your code instead of just doing a copy
and
past, the auto assist (or whatever you call it) doesn't pop up when I type
"rs.FindFirst"... usually if I write something like DoCmd... the auto
assist
pops up to give me options and prompts me as to what to put in next. Does
that mean I have some kind of reference missing??? That happened
before -- I
got some code from a newsgroup and needed the "Microsoft Active X Data
Objects 2.8 Library" checked off in my references to get the code "Dim
cnMARVIN As Connection" to work. Do you think it could be that?

Your results would suggest that the combo's bound column does not contain
the correct value to match the field you are searching.

In form design view, take a look at these properties of the combo:
Row Source
Column Count
Column Widths
Bound Column

The Row Source will be the name of a table or query, or a SQL statement.
The
Column Count will tell you how many fields the combo is reading from the
table/query. The Column Widths will tell you wish columns the query is
showing you. The Bound Column will tell you which column contains the
actual
value of the combo.

For example, say you see something like this:
Row Source SELECT TypeID, TypeName FROM tblType;
Column Count 2
Column Widths 0":1"
Bound Column 1
This would tell you that the combo reads 2 columns. The first one
(TypeID)
is zero-width, so it shows the TypeName instead. However, the bound
column
is the first one, so the actual value of the combo is not what you see in
the combo.

That's the kind of thing that could be causing it not to match.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen. I change my combo box name to the name in your code and
tried
your code... changing CustomerID to LicenseID. I get the message, "Not
found: filtered?" each time and don't know why. Both the form and the
combo
box are pulling license info from the same table, so I'm not sure why
it
won't recognize any of the license id's? Yes, the combo box is
unbound.
Any
guesses?

:

Suggestions:

1. Make sure Combo22 is unbound (nothing in its Control Source
property.)

2. Try the code in this link:
http://allenbrowne.com/ser-03.html

This improves on the Wizard's attempt in several ways:
a) Explicitly saves before the move.
b) Declares the specific kind of object.
c) Test NoMatch instead of EOF (which is not right)
d) Gives a message if the match is not found.

I have a simple form that has DealerName (a text field), LicenseID (a
number
field), and address info. I want to make a combo box that selects a
record
on my form based on the choice picked from the combo box. I went
through
the
wizard's prompts, but when I try the combo box out, the dealer name
and
subsequent license/address info doesn't show up on my form. Here is
the
code
the wizard created:

Private Sub Combo22_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LicenseID] = " & Str(Nz(Me![Combo22], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Ryan]

Im sorry, I forgot to mention that I make a form that only has my filter
criteria. For example, I would create a form called DealerNameSearch. On
that form I would add my combo box dropdown, and either in the after update
even of the combo box, or the on click event of a button on the form is where
I would use the code I gave you.

Private Sub Combo22_AfterUpdate
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "TheNameOfYourForm"

stLinkCriteria = "[LicenseID]=" & "'" & Me![Combo22] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
--
Please remember to mark this post as answered if this solves your problem.

Thanks, Ryan. I tried your code too... I got a run time error 2501 which
said "The OpenForm action was cancelled." I'm guessing I don't really want
docmd.OpenForm because the form is already open... I just want it to display
all the license and address info for the license I picked in the combo box.
What do you think?

The first thing I would do is Name my combo box something other than the
default name of Combo22. When your app grows good naming of controls will be
important. Here is the code I use to find specific records.

Private Sub Combo22_AfterUpdate
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "TheNameOfYourForm"

stLinkCriteria = "[LicenseID]=" & "'" & Me![Combo22] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
--
Please remember to mark this post as answered if this solves your problem.

I have a simple form that has DealerName (a text field), LicenseID (a number
field), and address info. I want to make a combo box that selects a record
on my form based on the choice picked from the combo box. I went through the
wizard's prompts, but when I try the combo box out, the dealer name and
subsequent license/address info doesn't show up on my form. Here is the code
the wizard created:

Private Sub Combo22_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LicenseID] = " & Str(Nz(Me![Combo22], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Any ideas?

 

[Heidi]

Yes, Dim rs As DAO.recordset is at the very top of the procedure but I don't
get the intellisense help.

Yes, if I just scroll through the form using the navigation buttons, I see
all of the correct dealer licenses.

Yes, I did what you told me about the immediate window and was able to pull
of the correct data using that criteria.

Does it have to do with the fact that LicenseID is a number field and not a
text field?

Are you certain you used this line at the top of the procedure:
Dim rs As DAO.Recordset
If you did, the Intellisense should know about FindFirst.
(If you just used Object, it won't.)

Can you actually step back through the records in your from to find the
desired record? Or is there some reason it might not be there (e.g. if you
set the form's DataEntry property to Yes, it won't be.)

Just above the rs.FindFirst line, add:
Debug.Print xxxxxx
where the xxxx represents exactly the same thing as you have after FindFirst
on the next line. When it fails, open the Immediate Window (Ctrl+G) and see
what comes out there. Then create a query that has the same criteria, and
see if that works.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I checked all that you said to -- the SQL in the combo box is correct and
is
pulling from the correct table. The form itself is pulling from that same
table, so the LicenseIDs do match. In the combo box, the column count is
1
and when I look at what the SQL it shows only the column labeled
LicenseID.
The column width is 1" and the bound column is 1, indicating the actual
value
is LicenseID. Looks like I've done everything right.

One thought... if I go to type in your code instead of just doing a copy
and
past, the auto assist (or whatever you call it) doesn't pop up when I type
"rs.FindFirst"... usually if I write something like DoCmd... the auto
assist
pops up to give me options and prompts me as to what to put in next. Does
that mean I have some kind of reference missing??? That happened
before -- I
got some code from a newsgroup and needed the "Microsoft Active X Data
Objects 2.8 Library" checked off in my references to get the code "Dim
cnMARVIN As Connection" to work. Do you think it could be that?

Your results would suggest that the combo's bound column does not contain
the correct value to match the field you are searching.

In form design view, take a look at these properties of the combo:
Row Source
Column Count
Column Widths
Bound Column

The Row Source will be the name of a table or query, or a SQL statement.
The
Column Count will tell you how many fields the combo is reading from the
table/query. The Column Widths will tell you wish columns the query is
showing you. The Bound Column will tell you which column contains the
actual
value of the combo.

For example, say you see something like this:
Row Source SELECT TypeID, TypeName FROM tblType;
Column Count 2
Column Widths 0":1"
Bound Column 1
This would tell you that the combo reads 2 columns. The first one
(TypeID)
is zero-width, so it shows the TypeName instead. However, the bound
column
is the first one, so the actual value of the combo is not what you see in
the combo.

That's the kind of thing that could be causing it not to match.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen. I change my combo box name to the name in your code and
tried
your code... changing CustomerID to LicenseID. I get the message, "Not
found: filtered?" each time and don't know why. Both the form and the
combo
box are pulling license info from the same table, so I'm not sure why
it
won't recognize any of the license id's? Yes, the combo box is
unbound.
Any
guesses?

:

Suggestions:

1. Make sure Combo22 is unbound (nothing in its Control Source
property.)

2. Try the code in this link:
http://allenbrowne.com/ser-03.html

This improves on the Wizard's attempt in several ways:
a) Explicitly saves before the move.
b) Declares the specific kind of object.
c) Test NoMatch instead of EOF (which is not right)
d) Gives a message if the match is not found.

I have a simple form that has DealerName (a text field), LicenseID (a
number
field), and address info. I want to make a combo box that selects a
record
on my form based on the choice picked from the combo box. I went
through
the
wizard's prompts, but when I try the combo box out, the dealer name
and
subsequent license/address info doesn't show up on my form. Here is
the
code
the wizard created:

Private Sub Combo22_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LicenseID] = " & Str(Nz(Me![Combo22], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Allen Browne]

I don't know what else to suggest, Heidi. You may possibly have a problem
with References if it doesn't understand the methods and properties of a
DAO.Recordset:
http://allenbrowne.com/ser-38.html

Your code uses no quote delimiters: that's correct if LicenseID is a Number
field.

 

[Heidi]

Thanks for trying, Allen. I've tried multiple computers and multiple
databases -- it seems to work for certain databases and not others. I've
tried creating a totally new database with only the one table and the
simplest form, and can't make it work.

One more question: I could possibly make the combo box work by pulling
dealer name (test field) and matching it with the form dealer name. The
wizard works except when my dealer name has an ' in it, like Art's Lobster.
How do I change the code you gave me from your website to accomodate for this
punctuation?

 

[Heidi]

Thanks for trying Ryan!

Im sorry, I forgot to mention that I make a form that only has my filter
criteria. For example, I would create a form called DealerNameSearch. On
that form I would add my combo box dropdown, and either in the after update
even of the combo box, or the on click event of a button on the form is where
I would use the code I gave you.

Private Sub Combo22_AfterUpdate
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "TheNameOfYourForm"

stLinkCriteria = "[LicenseID]=" & "'" & Me![Combo22] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
--
Please remember to mark this post as answered if this solves your problem.

Thanks, Ryan. I tried your code too... I got a run time error 2501 which
said "The OpenForm action was cancelled." I'm guessing I don't really want
docmd.OpenForm because the form is already open... I just want it to display
all the license and address info for the license I picked in the combo box.
What do you think?

The first thing I would do is Name my combo box something other than the
default name of Combo22. When your app grows good naming of controls will be
important. Here is the code I use to find specific records.

Private Sub Combo22_AfterUpdate
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "TheNameOfYourForm"

stLinkCriteria = "[LicenseID]=" & "'" & Me![Combo22] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
--
Please remember to mark this post as answered if this solves your problem.


:

I have a simple form that has DealerName (a text field), LicenseID (a number
field), and address info. I want to make a combo box that selects a record
on my form based on the choice picked from the combo box. I went through the
wizard's prompts, but when I try the combo box out, the dealer name and
subsequent license/address info doesn't show up on my form. Here is the code
the wizard created:

Private Sub Combo22_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LicenseID] = " & Str(Nz(Me![Combo22], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Any ideas?

 

[Heidi]

Nevermind, Allen... I read the web page again that you referenced and found
it at the bottom. IT WORKS GREAT!!! Thank you so much!

 

[Bret A.]

Hi Heidi, Is the [LicenseID] a unique value? If so, you can try another less
sophisticated approach. Make a new unbound form and place a lookup combo box
on it that looks up the dealer, include whatever fields you need in order to
select the right dealer. Make sure that the [LicenseID] is the field stored
for future.

Next add your normal form to this new form as a subform. The wizard will
mess up the child and parent link fields, but you can edit them after it
inserts the subform. It would be something like [Combo1]-Master,
[LicenseID]-child.

Thanks for trying Ryan!

Im sorry, I forgot to mention that I make a form that only has my filter
criteria. For example, I would create a form called DealerNameSearch. On
that form I would add my combo box dropdown, and either in the after update
even of the combo box, or the on click event of a button on the form is where
I would use the code I gave you.

Private Sub Combo22_AfterUpdate
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "TheNameOfYourForm"

stLinkCriteria = "[LicenseID]=" & "'" & Me![Combo22] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
--
Please remember to mark this post as answered if this solves your problem.

Thanks, Ryan. I tried your code too... I got a run time error 2501 which
said "The OpenForm action was cancelled." I'm guessing I don't really want
docmd.OpenForm because the form is already open... I just want it to display
all the license and address info for the license I picked in the combo box.
What do you think?

:

The first thing I would do is Name my combo box something other than the
default name of Combo22. When your app grows good naming of controls will be
important. Here is the code I use to find specific records.

Private Sub Combo22_AfterUpdate
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "TheNameOfYourForm"

stLinkCriteria = "[LicenseID]=" & "'" & Me![Combo22] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
--
Please remember to mark this post as answered if this solves your problem.


:

I have a simple form that has DealerName (a text field), LicenseID (a number
field), and address info. I want to make a combo box that selects a record
on my form based on the choice picked from the combo box. I went through the
wizard's prompts, but when I try the combo box out, the dealer name and
subsequent license/address info doesn't show up on my form. Here is the code
the wizard created:

Private Sub Combo22_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LicenseID] = " & Str(Nz(Me![Combo22], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Any ideas?

 

[Heidi]

Thanks, Bret... but I got Allen's code to work on a text field (DealerName)
instead of the LicenseID field (yes, it is a unique value). Not sure why it
works on the text and not the number field, but if it works, I'm happy!

Hi Heidi, Is the [LicenseID] a unique value? If so, you can try another less
sophisticated approach. Make a new unbound form and place a lookup combo box
on it that looks up the dealer, include whatever fields you need in order to
select the right dealer. Make sure that the [LicenseID] is the field stored
for future.

Next add your normal form to this new form as a subform. The wizard will
mess up the child and parent link fields, but you can edit them after it
inserts the subform. It would be something like [Combo1]-Master,
[LicenseID]-child.

Thanks for trying Ryan!

Im sorry, I forgot to mention that I make a form that only has my filter
criteria. For example, I would create a form called DealerNameSearch. On
that form I would add my combo box dropdown, and either in the after update
even of the combo box, or the on click event of a button on the form is where
I would use the code I gave you.

Private Sub Combo22_AfterUpdate
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "TheNameOfYourForm"

stLinkCriteria = "[LicenseID]=" & "'" & Me![Combo22] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
--
Please remember to mark this post as answered if this solves your problem.


:

Thanks, Ryan. I tried your code too... I got a run time error 2501 which
said "The OpenForm action was cancelled." I'm guessing I don't really want
docmd.OpenForm because the form is already open... I just want it to display
all the license and address info for the license I picked in the combo box.
What do you think?

:

The first thing I would do is Name my combo box something other than the
default name of Combo22. When your app grows good naming of controls will be
important. Here is the code I use to find specific records.

Private Sub Combo22_AfterUpdate
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "TheNameOfYourForm"

stLinkCriteria = "[LicenseID]=" & "'" & Me![Combo22] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
--
Please remember to mark this post as answered if this solves your problem.


:

I have a simple form that has DealerName (a text field), LicenseID (a number
field), and address info. I want to make a combo box that selects a record
on my form based on the choice picked from the combo box. I went through the
wizard's prompts, but when I try the combo box out, the dealer name and
subsequent license/address info doesn't show up on my form. Here is the code
the wizard created:

Private Sub Combo22_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LicenseID] = " & Str(Nz(Me![Combo22], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Any ideas?

 

[Jane Frounfelker]

Any combo box that I create will not find a match if the field being searched is an integer. I was totally amazed when I copied data to a test database and coverted the interger field to text and combo boxes started to work. In testing I used wizards to create my form and combo boxes and based them on the same table. When I added the If rs.nomatch... I continually get the error message box when searching for and interger value. This is a big problem for me since most of my primary keys are integers or long integers. My pc has Office 2007 and 2003 installed but I primarily use 2003. Full version for 2003 is 11.8166.8221 sp 3. This problem just started for me, last week everything worked fine.

 

[John W. Vinson]

Any combo box that I create will not find a match if the field being searched is an integer. I was totally amazed when I copied data to a test database and coverted the interger field to text and combo boxes started to work. In testing I used wizards to create my form and combo boxes and based them on the same table. When I added the If rs.nomatch... I continually get the error message box when searching for and interger value. This is a big problem for me since most of my primary keys are integers or long integers. My pc has Office 2007 and 2003 installed but I primarily use 2003. Full version for 2003 is 11.8166.8221 sp 3. This problem just started for me, last week everything worked fine.

Please post your code. Line breaks are nice too.