Tables Queries Criteria oh my!

[Donalbain]

I have built a database to keep a track of the various items in our
works inventory. To keep track of this information I have a number of
tables, obviously.. they are as follows:

Product Info:-- This holds all the information about the actual
product, for instance the name of it (lipstick for example) who
invented it, what its properties are

Batches Made:-- This is where we store the information about each batch
of the product. It has infor like when it was made, how much was made
and the like
Items made:-- This is where we record that we have turned bulk lipstick
into actual lipstick samples. It records when we made the samples and
how many we make from each batch of bulk

Transactions Out-- This is where we store the information about the
samples we send out to customers. It records how many samples we send,
when we sent them, who we sent them to and so on..
Now, everything is working fine except for ONE query. It is a query
that enables you to see what Transactions Out have been performed on a
particular Product. I have built it as a query with a criteria that
gets its information from a combobox on a form. The combobox gets its
available data directly from the Product Info table. However.. the
query only seems to accept the criteria if one particular product is
chosen in the combobox. If you select that product (which is product ID
1) it works and shows you all the transactions that have been performed
on that particular lipstick. However, if you select a different product
in the combobox, the query refuses to give any results and claims that
the criteria is not being met at all. BUT, if you delete the criteria
completely, then it happily brings up a list of ALL the transactions
for all the products. Why is this happenning?

Cheers for any help you may be able to give...

[Donalbain]

Do*******@gmail.com wrote:

I have built a database to keep a track of the various items in our
works inventory. To keep track of this information I have a number of
tables, obviously.. they are as follows:

Product Info:-- This holds all the information about the actual
product, for instance the name of it (lipstick for example) who
invented it, what its properties are

Batches Made:-- This is where we store the information about each batch
of the product. It has infor like when it was made, how much was made
and the like
Items made:-- This is where we record that we have turned bulk lipstick
into actual lipstick samples. It records when we made the samples and
how many we make from each batch of bulk

Transactions Out-- This is where we store the information about the
samples we send out to customers. It records how many samples we send,
when we sent them, who we sent them to and so on..
Now, everything is working fine except for ONE query. It is a query
that enables you to see what Transactions Out have been performed on a
particular Product. I have built it as a query with a criteria that
gets its information from a combobox on a form. The combobox gets its
available data directly from the Product Info table. However.. the
query only seems to accept the criteria if one particular product is
chosen in the combobox. If you select that product (which is product ID
1) it works and shows you all the transactions that have been performed
on that particular lipstick. However, if you select a different product
in the combobox, the query refuses to give any results and claims that
the criteria is not being met at all. BUT, if you delete the criteria
completely, then it happily brings up a list of ALL the transactions
for all the products. Why is this happenning?

Cheers for any help you may be able to give...

OK.. I have fixed the query.. the query now reads the value from the
combobox fine.. however, the form that is populated from the Query is
still not working, it will only show results if the Product selected is
the first one. However, the query is showing the correct results with
ANY product selected..

[Tim Marshall]

Do*******@gmail.com wrote:

OK.. I have fixed the query.. the query now reads the value from the
combobox fine.. however, the form that is populated from the Query is
still not working, it will only show results if the Product selected is
the first one. However, the query is showing the correct results with
ANY product selected..

Your form has a record source which is only populated when the form is
initially opened. I'm assuming you want things such that when the combo
box is changed, the records shown on the form will change. To do this
you need to "requery" the form. This requires some coding, which is
relatively simple to do, if you've not done any VBA coding before.

In the design view of the form that contains the combo box, you need to
have the combo box perform a "requery" on the form in question whenever
the combo box value is changed or _updated_.

Click on the combo box and make sure the properties window is displayed
(if it is not, you can click View->Properties or press alt-enter). The
title bar of the properties window will read something like: "Combo Box:
MyComboName", indicating the name of the cmobo box in question.

Click the event tab.

Put your cursor in the After Update event and click the little build
button that appears at the right.

A "Choose Builder" pop up window appears. Select "Code Builder" and
press OK.

The Micorosft Visual Basic editor window will open and you'll be at
something that looks like:

Private Sub MyComboName_AfterUpdate()

End Sub

You'll want to do one of two things. If the combo box is on the same
form as the one in which the records appear, do this:

Private Sub MyComboName_AfterUpdate()

Me.Requery

End Sub

If the form with the records is on a different form from that of the
combo box, do this:

Private Sub MyComboName_AfterUpdate()

Forms!frmMyFormName.Requery

End Sub

Save it and you'll find things work the way you want them to.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me