Query not populating form

[dboollu]

I have CustomerName query that retrieves the CustomerName, Address, City,
State, and Zip Code from the CustomerName table. CustomerName is a combo
box. How do I get this query to put all the corresponding fields into a form
automatically after selecting a CustomerName from the combo box? I thought
if I created a query on a single table then made it the RecordSource of the
form, assigned the Control Source of the control appropriately that the
fields would fill in. Obviously, I am missing something, because I can't
figure it out. Do I need a filter or something? If so, which one would I
use? Do I need to change anything on the property sheet of the form or
controls?

Thanks for any suggestions!!!

 

[Rick Brandt]

I have CustomerName query that retrieves the CustomerName, Address,
City, State, and Zip Code from the CustomerName table. CustomerName
is a combo box. How do I get this query to put all the corresponding
fields into a form automatically after selecting a CustomerName from
the combo box? I thought if I created a query on a single table then
made it the RecordSource of the form, assigned the Control Source of
the control appropriately that the fields would fill in. Obviously, I
am missing something, because I can't figure it out. Do I need a
filter or something? If so, which one would I use? Do I need to
change anything on the property sheet of the form or controls?

Thanks for any suggestions!!!

If you made the query the RecordSource of the form then it sounds like you want
the ComboBox to *navigate* the form to the existing record containing the
selected name. Is that correct? If so the ComboBox should be unbound and it
would execute code after a selection is made to navigate the form to the
matching record.

I suggest deleting the existing ComboBox and then add a new one using the
ComboBox wizard. One of the options in that wizard will build a navigating
ComboBox for you.

 

[dboollu]

Thanks for the suggestion!

I have tried putting my query as the record source of the form to no avail.
I have also setting it as the record source of the control. I tried building
the control myself and with the combo box wizard, and still was unable to get
it to retrieve the entire contents of the selected CustomerName column for
each customer. I have no problem getting the customer names to appear in the
combo and am able to select them, but no matter what I've 'set' the other txt
boxes Control Source to I either get the "#Name?" error (pretty sick of that
one) in the corresponding txt box in form view. I am able to get the data
into the fields sometimes. but it only displays the first address in the
first row from the CustomerName tbl. Then it never changes with the
selection of a different customer.

I am using Access 2003. I do not get an option to "navigate" when I proceed
through the combo box wizard. The only choices I get are: "I want the combo
box to look up values in a table or query," "I will type in the values I
want", and "Find a value on my form based on the value I selected on my
form." I have tried the first and last options and nothing happened. (Dumb
question) What "code" where you refering to? Something the wizard put in the
property sheet, or something I need to do programatically? If it's the
latter, then could you please give my and example of what I would use? I am
just getting back into this (quite unsuccessfully as of yet) and haven't
programmed in a few years.

 

[Linq Adams via AccessMonster.com]

Here's a step-by-step of what Rick was suggesting:

Create a form using the table/query that holds your data, displaying all the
fields you want present.

Add a combo box to your form. The Combobox Wizard will pop up

Select "Find a record based on the value I selected in my combobox."

Select the table or query your form is based on, click on the unique field
you're searching by (your CustomerName field, I assume) to move it to the
right side.

Hit Next.

Size the column appropriately.

Hit Next.

Name the combobox.

Hit Finish.

Now you can drop the combobox down and scroll down to the item to search by,
or you can start to enter the item, and the combobox will "autofill" as you
type. Hit <Enter> and the record will be retrieved.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com

 

[dboollu]

Please disregard what I just posted. I tried you suggestion again and chose
the last choice in the wizard. It worked after I deleted my first txt box.
Dummy that I am I neglected to do that first...

Thank you soooooo much for your help!! I don't even want to admit how long
I've been trying to get this to work!

I have one other question...I am adding three subforms. I am having trouble
figuring out what process I have to use to tie a CustomerName to a number
that this company uses as a sort of customer number called ACM#, to something
called a Melt #. So basically, the process is a user selects a CustomerName
whic in turn returns specifics about the customers' part order including the
customer number (ACM#) (that is all populated in the CustomerName_ACM# query
we talked about before). Now I am creating two subforms 1 containg results
of a chemical analysis on the part which results in 1 or more Melt# the other
containing the requirements for the part (basically the elements that make up
the part). I know that I need to create a query of some sort to relate the
CustomerName, ACM#, and Melt#. I'm not exactly sure how to do this. Do I
create another look up query like I did for the CustomerName? Or do I need
to pass some type of 'criteria' in the query or an expression of some sort in
the property sheet of a control?

Sorry to be such a pain, but I'm desperately trying to relearn this stuff!!

Thanks a again for helping out!!!

 

[dboollu]

Hi. Thank you soooooo much for your step-by-step help!! I don't even want to
admit how long I've been trying to get this to work!

I have one otheAr question...I am adding three subforms. I am having trouble
figuring out what process I have to use to tie the CustomerName combobox to
a number that is used as a sort of customer number called ACM#, to something
called a Melt # in a subform.

So basically, the process is a user selects a CustomerName
which in turn returns specifics about the customers' part order including the
customer number (ACM#) (that is all populated in the CustomerName_ACM# query
we talked about before). I need the selection of that CustomerName to then go
out and grab information from two different tables, and put that data into
two subforms. (I am using two subforms, because I need to do a side by side
comparison of returned results(stored in tblChemicalResults) vs the
requirements(stored in tblChemicalReqirements). So I am creating two subforms
1 containg results of a chemical analysis (data stored in
tblChemicalResults). The other containing the requirements for the part
(basically the elements that make up the part in tblChemicalRequirements).

I think I need to create a query of some sort to link the CustomerName to
the ACM#, and then the ACM# to the Melt#(info that is tied to both
tblChemicalResults and tblChemicalRequirements) . I'm not exactly sure how to
do this. Do I
create a look up query? Or do I need to pass some type of 'criteria' in the
query or an expression of some sort in the property sheet of a control?

Sorry to be such a pain, but I'm desperately trying to relearn this stuff!!

Again thanks a again for helping out!!!