criteria syntax in a query

[Guest]

I have a form [frm_NewQuote] which assignes [OrderInfoID]. I have another
form [frm_QuoteDetails] that opens with [frm_NewQuote]. The [OrderInfoID]
automatically updates into [frm_QuoteDetails]. Now in [frm_QuoteDetails] I
have a combobox [Combo40]. What I want to do is on click for [Combo40] I want
to run a query [qryLU_armrest]. the Query consists of [LUID] which is an
autonumber, and [ModelID] and [ArmrestID] which are both numbers that come
from other tables that assign these numbers. I want [qryLU_Armrest] to search
based on the [ModelID] in [frm_NewQuote].

So when I click the combobox on my subform I want it to run a query based on
the modelID from the main form so that my options are only relevant to this
specific Model number.

I can make the query run on click, however my syntax in my query is stumping
me a little.

Under [ModelID] in my query, I tried entering =[frm_NewQuote].[ModelID] but
when I open my form all together it tries to run the query and it pulls up a
promt box asking me for the value of ModelID.

Any suggestions.
Thanks in Advance.
Travis

 

[Jeff L]

Your syntax isn't quite right. It should be
Forms![frm_NewQuote]![ModelID].

Hope that helps.

 

[Guest]

Use the full specifier, and change the dot operator (.) to bang (!):

=Forms![frm_NewQuote]![ModelID]

Hope that helps.
Sprinks

 

[Guest]

My form [frm_QuoteDetails] is a subform in [frm_NewQuote]
The syntax works with 2 exceptions.

1. When I open my form the query runs and it's fine, however if I change the
ModelID of [frm_NewQuote], the query doesn't re-run. so my combobox doesn't
change options. So even if I just go to the next record or a new record and
change the ModelID, i'm still only going to get the same option when I first
opened it.

2. If I have multiple records in my [frm_NewQuote] with the same ModelID
then the query returnes the sames options however many times that ModelID is
present in the form.

Thanks in advance.
Travis

Use the full specifier, and change the dot operator (.) to bang (!):

=Forms![frm_NewQuote]![ModelID]

Hope that helps.
Sprinks

I have a form [frm_NewQuote] which assignes [OrderInfoID]. I have another
form [frm_QuoteDetails] that opens with [frm_NewQuote]. The [OrderInfoID]
automatically updates into [frm_QuoteDetails]. Now in [frm_QuoteDetails] I
have a combobox [Combo40]. What I want to do is on click for [Combo40] I want
to run a query [qryLU_armrest]. the Query consists of [LUID] which is an
autonumber, and [ModelID] and [ArmrestID] which are both numbers that come
from other tables that assign these numbers. I want [qryLU_Armrest] to search
based on the [ModelID] in [frm_NewQuote].

So when I click the combobox on my subform I want it to run a query based on
the modelID from the main form so that my options are only relevant to this
specific Model number.

I can make the query run on click, however my syntax in my query is stumping
me a little.

Under [ModelID] in my query, I tried entering =[frm_NewQuote].[ModelID] but
when I open my form all together it tries to run the query and it pulls up a
promt box asking me for the value of ModelID.

Any suggestions.
Thanks in Advance.
Travis

 

[Jeff L]

1. When I open my form the query runs and it's fine, however if I
change the
ModelID of [frm_NewQuote], the query doesn't re-run. so my combobox
doesn't
change options. So even if I just go to the next record or a new record
and
change the ModelID, i'm still only going to get the same option when I
first
opened it.

Answer: In the After Update event of the ModelId, put
Me.frm_QuoteDetails.Requery

2. If I have multiple records in my [frm_NewQuote] with the same
ModelID
then the query returnes the sames options however many times that
ModelID is
present in the form.

Answer: Look at your query design, right click the window where your
tables are and select Properties, then change Unique Values to Yes.

Hope that helps!

 

[Guest]

That's all works almost perfect....the part where I entered
Me.frm_QuoteDetails.Requery
into the afterupdate event is giving me some problems.
It update the query nicely but if I try to change the ModelID even while
entering a new record, it gives me an error message "Microsoft Access can't
find the macro 'Me'"

Do you know why it does that.
It makes me hit undo just to get out of the Model ID box.

Thank you very much for helping me.
Everything so far has worked great.

 

[Jeff L]

I'm not sure you did it quite right. My fault, I didn't explain it.
In the After Update event, you should have [Event Procedure] in that
box. Now to the right of the box there should be a button. Click the
button. You should have a code window that opens that looks like:
Private Sub ModelId_AfterUpdate

End Sub

Put Me.frm_QuoteDetails.Requery in between those two lines. Sorry I
messed you up.

That's all works almost perfect....the part where I entered
Me.frm_QuoteDetails.Requery
into the afterupdate event is giving me some problems.
It update the query nicely but if I try to change the ModelID even while
entering a new record, it gives me an error message "Microsoft Access can't
find the macro 'Me'"

Do you know why it does that.
It makes me hit undo just to get out of the Model ID box.

Thank you very much for helping me.
Everything so far has worked great.

1. When I open my form the query runs and it's fine, however if I
change the
ModelID of [frm_NewQuote], the query doesn't re-run. so my combobox
doesn't
change options. So even if I just go to the next record or a new record
and
change the ModelID, i'm still only going to get the same option when I
first
opened it.

Answer: In the After Update event of the ModelId, put
Me.frm_QuoteDetails.Requery

2. If I have multiple records in my [frm_NewQuote] with the same
ModelID
then the query returnes the sames options however many times that
ModelID is
present in the form.

Answer: Look at your query design, right click the window where your
tables are and select Properties, then change Unique Values to Yes.

Hope that helps!

 

[Guest]

this may be neverending.
Now It all appears to work fine, excpet when I enter a new record and
refresh my form data, The query won't return anything unless it's a modelID
that's already been used.

I'm not sure you did it quite right. My fault, I didn't explain it.
In the After Update event, you should have [Event Procedure] in that
box. Now to the right of the box there should be a button. Click the
button. You should have a code window that opens that looks like:
Private Sub ModelId_AfterUpdate

End Sub

Put Me.frm_QuoteDetails.Requery in between those two lines. Sorry I
messed you up.

That's all works almost perfect....the part where I entered
Me.frm_QuoteDetails.Requery
into the afterupdate event is giving me some problems.
It update the query nicely but if I try to change the ModelID even while
entering a new record, it gives me an error message "Microsoft Access can't
find the macro 'Me'"

Do you know why it does that.
It makes me hit undo just to get out of the Model ID box.

Thank you very much for helping me.
Everything so far has worked great.

1. When I open my form the query runs and it's fine, however if I
change the
ModelID of [frm_NewQuote], the query doesn't re-run. so my combobox
doesn't
change options. So even if I just go to the next record or a new record
and
change the ModelID, i'm still only going to get the same option when I
first
opened it.

Answer: In the After Update event of the ModelId, put
Me.frm_QuoteDetails.Requery

2. If I have multiple records in my [frm_NewQuote] with the same
ModelID
then the query returnes the sames options however many times that
ModelID is
present in the form.

Answer: Look at your query design, right click the window where your
tables are and select Properties, then change Unique Values to Yes.

Hope that helps!

 

[Jeff L]

Did you put in quote details for the new ModelID too? If you didn't
then your query will not return anything because there is nothing to
return.

By the way, how are you "entering a new record?". If you are only
entering a new modelID and no details, your subform won't show any
data.

this may be neverending.
Now It all appears to work fine, excpet when I enter a new record and
refresh my form data, The query won't return anything unless it's a modelID
that's already been used.

I'm not sure you did it quite right. My fault, I didn't explain it.
In the After Update event, you should have [Event Procedure] in that
box. Now to the right of the box there should be a button. Click the
button. You should have a code window that opens that looks like:
Private Sub ModelId_AfterUpdate

End Sub

Put Me.frm_QuoteDetails.Requery in between those two lines. Sorry I
messed you up.

That's all works almost perfect....the part where I entered
Me.frm_QuoteDetails.Requery
into the afterupdate event is giving me some problems.
It update the query nicely but if I try to change the ModelID even while
entering a new record, it gives me an error message "Microsoft Access can't
find the macro 'Me'"

Do you know why it does that.
It makes me hit undo just to get out of the Model ID box.

Thank you very much for helping me.
Everything so far has worked great.

:

1. When I open my form the query runs and it's fine, however if I
change the
ModelID of [frm_NewQuote], the query doesn't re-run. so my combobox
doesn't
change options. So even if I just go to the next record or a new record
and
change the ModelID, i'm still only going to get the same option when I
first
opened it.

Answer: In the After Update event of the ModelId, put
Me.frm_QuoteDetails.Requery

2. If I have multiple records in my [frm_NewQuote] with the same
ModelID
then the query returnes the sames options however many times that
ModelID is
present in the form.

Answer: Look at your query design, right click the window where your
tables are and select Properties, then change Unique Values to Yes.

Hope that helps!