display value from result of a query

N

Nancy

My form inputs into table A. The form has a combo box that lets you choose
the zip code from table B. I need to display the state for that zip code.

Question: do I use "me.dirty" on the zip code to run a query to get the
state info? What is the syntax for the running the query and then getting
the result to display on the form ???

Thanks in advance for your help .
 
D

David Lloyd

Nancy:

I don't know all of your specifics (like is the state located in Table B?),
so I'll make some general suggestions. One approach would be to use the
Change event of the combo box and then either use a query or the DLookup
function to find the state value. For example, if you have a textbox to
hold the state value on the form, the change event might look like:

Private Sub cmbZip_Change()
txtState = DLookup("[State]", "TableB", "Zip=" &
CLng(Forms!MyFormName!cmbZip))
End Sub

This assumes the state field is in the same table as the zip code.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


My form inputs into table A. The form has a combo box that lets you choose
the zip code from table B. I need to display the state for that zip code.

Question: do I use "me.dirty" on the zip code to run a query to get the
state info? What is the syntax for the running the query and then getting
the result to display on the form ???

Thanks in advance for your help .
 
M

Marshall Barton

David said:
Nancy:

I don't know all of your specifics (like is the state located in Table B?),
so I'll make some general suggestions. One approach would be to use the
Change event of the combo box and then either use a query or the DLookup
function to find the state value. For example, if you have a textbox to
hold the state value on the form, the change event might look like:

Private Sub cmbZip_Change()
txtState = DLookup("[State]", "TableB", "Zip=" &
CLng(Forms!MyFormName!cmbZip))
End Sub

This assumes the state field is in the same table as the zip code.


David, the value of the combo box has not been updated at
the time of the Change event, the AfterUpdate event is the
preferred event for this kind of thing.

Actually, if the state is in the same table as the zip code,
then the DLookup can be avoided by including the state field
as a hidden(?) column in the combo box's RowSource query.
Then the state text box could be set using:

txtState = cmbZip.Column(1)

(assuming that the zip is in the first column and the state
is in the second column).
 
N

Nancy

Good assumptions, and I like your solution ... thanks :)

BUT, I get a VB compile error "variable not defined"! Any idea on what I'm
missing? I am using a combo box to run the query that returns the correct
answer, but when update the ZIP and try to display the STATE in an unbound
text box, I get the error


THANKS again for the help :)
Nancy

Marshall Barton said:
David said:
Nancy:

I don't know all of your specifics (like is the state located in Table B?),
so I'll make some general suggestions. One approach would be to use the
Change event of the combo box and then either use a query or the DLookup
function to find the state value. For example, if you have a textbox to
hold the state value on the form, the change event might look like:

Private Sub cmbZip_Change()
txtState = DLookup("[State]", "TableB", "Zip=" &
CLng(Forms!MyFormName!cmbZip))
End Sub

This assumes the state field is in the same table as the zip code.


David, the value of the combo box has not been updated at
the time of the Change event, the AfterUpdate event is the
preferred event for this kind of thing.

Actually, if the state is in the same table as the zip code,
then the DLookup can be avoided by including the state field
as a hidden(?) column in the combo box's RowSource query.
Then the state text box could be set using:

txtState = cmbZip.Column(1)

(assuming that the zip is in the first column and the state
is in the second column).
 
D

David Lloyd

Marshall:

I think if you actually test the Change event for the combo box you will
find that it actually occurs after the contents of the combo box have
changed. I did test the code I gave the user, and it does work in the
Change event.

If you will notice in my original post that I did qualify it with "One
approach." If you want to offer another "approach" to the user, that is
great.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


David said:
Nancy:

I don't know all of your specifics (like is the state located in Table B?),
so I'll make some general suggestions. One approach would be to use the
Change event of the combo box and then either use a query or the DLookup
function to find the state value. For example, if you have a textbox to
hold the state value on the form, the change event might look like:

Private Sub cmbZip_Change()
txtState = DLookup("[State]", "TableB", "Zip=" &
CLng(Forms!MyFormName!cmbZip))
End Sub

This assumes the state field is in the same table as the zip code.


David, the value of the combo box has not been updated at
the time of the Change event, the AfterUpdate event is the
preferred event for this kind of thing.

Actually, if the state is in the same table as the zip code,
then the DLookup can be avoided by including the state field
as a hidden(?) column in the combo box's RowSource query.
Then the state text box could be set using:

txtState = cmbZip.Column(1)

(assuming that the zip is in the first column and the state
is in the second column).
 
D

David Lloyd

Nancy:

For the record, the DLookup function can also be put in the Control Source
property of the state textbox. I found this to be a bit slower than the
using the Change event, but I will pass it along for your information. I
have not compared it speed-wise to Marshall's alternative. For example:

=DLookUp("[State]","TableB","Zip=" & CLng([cmbZip]))

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


My form inputs into table A. The form has a combo box that lets you choose
the zip code from table B. I need to display the state for that zip code.

Question: do I use "me.dirty" on the zip code to run a query to get the
state info? What is the syntax for the running the query and then getting
the result to display on the form ???

Thanks in advance for your help .
 
M

Marshall Barton

David, I'm not trying to give you a hard time. I just
wanted to point out that the Change event fires on every
keystroke, possibly long before an item is selected and the
Value is set. Although clicking the mouse on an item may(?)
update the control's Value in time for the Change event,
that is not the Change event's intended purpose.
--
Marsh
MVP [MS Access]


David said:
I think if you actually test the Change event for the combo box you will
find that it actually occurs after the contents of the combo box have
changed. I did test the code I gave the user, and it does work in the
Change event.

If you will notice in my original post that I did qualify it with "One
approach." If you want to offer another "approach" to the user, that is
great.


This response is supplied "as is" without any representations or warranties.


David said:
Nancy:

I don't know all of your specifics (like is the state located in Table B?),
so I'll make some general suggestions. One approach would be to use the
Change event of the combo box and then either use a query or the DLookup
function to find the state value. For example, if you have a textbox to
hold the state value on the form, the change event might look like:

Private Sub cmbZip_Change()
txtState = DLookup("[State]", "TableB", "Zip=" &
CLng(Forms!MyFormName!cmbZip))
End Sub

This assumes the state field is in the same table as the zip code.


David, the value of the combo box has not been updated at
the time of the Change event, the AfterUpdate event is the
preferred event for this kind of thing.

Actually, if the state is in the same table as the zip code,
then the DLookup can be avoided by including the state field
as a hidden(?) column in the combo box's RowSource query.
Then the state text box could be set using:

txtState = cmbZip.Column(1)

(assuming that the zip is in the first column and the state
is in the second column).
 
M

Marshall Barton

Nancy, I don't see a variable in there. The only names used
are the text box and the combo box names. Those could cause
that error if one of them is misspelled, but that's
unlikely. Maybe the error is coming from something else???
--
Marsh
MVP [MS Access]

Good assumptions, and I like your solution ... thanks :)

BUT, I get a VB compile error "variable not defined"! Any idea on what I'm
missing? I am using a combo box to run the query that returns the correct
answer, but when update the ZIP and try to display the STATE in an unbound
text box, I get the error

David said:
I don't know all of your specifics (like is the state located in Table B?),
so I'll make some general suggestions. One approach would be to use the
Change event of the combo box and then either use a query or the DLookup
function to find the state value. For example, if you have a textbox to
hold the state value on the form, the change event might look like:

Private Sub cmbZip_Change()
txtState = DLookup("[State]", "TableB", "Zip=" &
CLng(Forms!MyFormName!cmbZip))
End Sub

This assumes the state field is in the same table as the zip code.
Marshall Barton said:
David, the value of the combo box has not been updated at
the time of the Change event, the AfterUpdate event is the
preferred event for this kind of thing.

Actually, if the state is in the same table as the zip code,
then the DLookup can be avoided by including the state field
as a hidden(?) column in the combo box's RowSource query.
Then the state text box could be set using:

txtState = cmbZip.Column(1)

(assuming that the zip is in the first column and the state
is in the second column).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top