I am using MS ACCESS 2003

I have a main form and a subform that pulls up an existing case for the user 
to update the information already entered. The subform is designed and opens 
up in form view not in datasheet view.

The subform knows which case to pull in based on the case number on the main 
form

The textboxes on the main form are bound fields from a query. The user 
enters an ID which is how the main form query knows which record to pull and 
it works.
 
The textboxes on the subform are bound with data from a different query (so 
I have a query for each form)

The query tied to the record source of the subform prompts for a case number 
which is already in the main form.

PROBLEM

I will like to pass the case number from the main form to the QUERY that 
populates the subform without seeing the prompt come up.

So how can I pass the case number from the main form after it opens so that 
when both the main and subform are open all the data is populated without the 
user entering a matching case number for the subform query to pull the 
corresponding data from a different table.

This is what I have done for the where criteria in the subform query

SELECT t_mytable.case_nbr, t_mytable.code, t_mytable.added_by
FROM t_mytable
WHERE (((t_mytable.case_nbr)=[Forms]![sub_frm_update]![case_nbr]));

Please note that I have also tried it with [frm_MAINFORM_lookup]![case_nbr]

I get prompted for the case number by the subquery criteria and I don’t want 
it.

Thanks.

ecwhite wrote:

>I am using MS ACCESS 2003
>
>I have a main form and a subform that pulls up an existing case for the user 
>to update the information already entered. The subform is designed and opens 
>up in form view not in datasheet view.
>
>The subform knows which case to pull in based on the case number on the main 
>form
>
>The textboxes on the main form are bound fields from a query. The user 
>enters an ID which is how the main form query knows which record to pull and 
>it works.
> 
>The textboxes on the subform are bound with data from a different query (so 
>I have a query for each form)
>
>The query tied to the record source of the subform prompts for a case number 
>which is already in the main form.
>
>PROBLEM
>
>I will like to pass the case number from the main form to the QUERY that 
>populates the subform without seeing the prompt come up.
>
>So how can I pass the case number from the main form after it opens so that 
>when both the main and subform are open all the data is populated without the 
>user entering a matching case number for the subform query to pull the 
>corresponding data from a different table.
>
>This is what I have done for the where criteria in the subform query
>
>SELECT t_mytable.case_nbr, t_mytable.code, t_mytable.added_by
>FROM t_mytable
>WHERE (((t_mytable.case_nbr)=[Forms]![sub_frm_update]![case_nbr]));
>
>Please note that I have also tried it with [frm_MAINFORM_lookup]![case_nbr]


Whenever you are prompted for something in a query or
report, it means that Access can not find that something in
the query's field list or in the report.  Typically, it
because there is a misspelled name somewhere in whatever you
are prompted to enter.

-- 
Marsh
MVP [MS Access]

Hello Marshall,

In this case it is prompting me to enter the case number to run the query. I 
think the problem is both queries run before it gets the value from the form. 
Where do i put the code for the second query so that it will be able to get 
the value from the main form after the main form opens if you think my syntax 
is correct.

Thanks.

"Marshall Barton" wrote:

> ecwhite wrote:
> 
> >I am using MS ACCESS 2003
> >
> >I have a main form and a subform that pulls up an existing case for the user 
> >to update the information already entered. The subform is designed and opens 
> >up in form view not in datasheet view.
> >
> >The subform knows which case to pull in based on the case number on the main 
> >form
> >
> >The textboxes on the main form are bound fields from a query. The user 
> >enters an ID which is how the main form query knows which record to pull and 
> >it works.
> > 
> >The textboxes on the subform are bound with data from a different query (so 
> >I have a query for each form)
> >
> >The query tied to the record source of the subform prompts for a case number 
> >which is already in the main form.
> >
> >PROBLEM
> >
> >I will like to pass the case number from the main form to the QUERY that 
> >populates the subform without seeing the prompt come up.
> >
> >So how can I pass the case number from the main form after it opens so that 
> >when both the main and subform are open all the data is populated without the 
> >user entering a matching case number for the subform query to pull the 
> >corresponding data from a different table.
> >
> >This is what I have done for the where criteria in the subform query
> >
> >SELECT t_mytable.case_nbr, t_mytable.code, t_mytable.added_by
> >FROM t_mytable
> >WHERE (((t_mytable.case_nbr)=[Forms]![sub_frm_update]![case_nbr]));
> >
> >Please note that I have also tried it with [frm_MAINFORM_lookup]![case_nbr]
> 
> 
> Whenever you are prompted for something in a query or
> report, it means that Access can not find that something in
> the query's field list or in the report.  Typically, it
> because there is a misspelled name somewhere in whatever you
> are prompted to enter.
> 
> -- 
> Marsh
> MVP [MS Access]
> .
> 

I have this resolved. I created a textbox at the head section of the main 
form. Declared a public variable, set the value of the public variable to the 
case number from the main form and referenced it from the query as  

[Forms]![frm_MAINFORM_lookup]![case_nbr]

I hope this helps someone.

ecwhite.


"ecwhite" wrote:

> I am using MS ACCESS 2003
> 
> I have a main form and a subform that pulls up an existing case for the user 
> to update the information already entered. The subform is designed and opens 
> up in form view not in datasheet view.
> 
> The subform knows which case to pull in based on the case number on the main 
> form
> 
> The textboxes on the main form are bound fields from a query. The user 
> enters an ID which is how the main form query knows which record to pull and 
> it works.
>  
> The textboxes on the subform are bound with data from a different query (so 
> I have a query for each form)
> 
> The query tied to the record source of the subform prompts for a case number 
> which is already in the main form.
> 
> PROBLEM
> 
> I will like to pass the case number from the main form to the QUERY that 
> populates the subform without seeing the prompt come up.
> 
> So how can I pass the case number from the main form after it opens so that 
> when both the main and subform are open all the data is populated without the 
> user entering a matching case number for the subform query to pull the 
> corresponding data from a different table.
> 
> This is what I have done for the where criteria in the subform query
> 
> SELECT t_mytable.case_nbr, t_mytable.code, t_mytable.added_by
> FROM t_mytable
> WHERE (((t_mytable.case_nbr)=[Forms]![sub_frm_update]![case_nbr]));
> 
> Please note that I have also tried it with [frm_MAINFORM_lookup]![case_nbr]
> 
> I get prompted for the case number by the subquery criteria and I don’t want 
> it.
> 
> Thanks.
> 
> 

Ahhh, I think I see what you are doing.  The problem appears
to be that subforms are loaded before their main form is
established so you can not refer to a mainform control until
after the main form is loaded..

In general, you should be using the subform control's Link
Master/Child properties to link the subform records to a
value on the main form.  Remove the criteria from the
subform's record source and try setting both the
LinkMaster/Child properties to case_nbr
-- 
Marsh
MVP [MS Access]

ecwhite wrote:
>In this case it is prompting me to enter the case number to run the query. I 
>think the problem is both queries run before it gets the value from the form. 
>Where do i put the code for the second query so that it will be able to get 
>the value from the main form after the main form opens if you think my syntax 
>is correct.
>
>
>"Marshall Barton" wrote:
>
>> ecwhite wrote:
>> 
>> >I am using MS ACCESS 2003
>> >
>> >I have a main form and a subform that pulls up an existing case for the user 
>> >to update the information already entered. The subform is designed and opens 
>> >up in form view not in datasheet view.
>> >
>> >The subform knows which case to pull in based on the case number on the main 
>> >form
>> >
>> >The textboxes on the main form are bound fields from a query. The user 
>> >enters an ID which is how the main form query knows which record to pull and 
>> >it works.
>> > 
>> >The textboxes on the subform are bound with data from a different query (so 
>> >I have a query for each form)
>> >
>> >The query tied to the record source of the subform prompts for a case number 
>> >which is already in the main form.
>> >
>> >PROBLEM
>> >
>> >I will like to pass the case number from the main form to the QUERY that 
>> >populates the subform without seeing the prompt come up.
>> >
>> >So how can I pass the case number from the main form after it opens so that 
>> >when both the main and subform are open all the data is populated without the 
>> >user entering a matching case number for the subform query to pull the 
>> >corresponding data from a different table.
>> >
>> >This is what I have done for the where criteria in the subform query
>> >
>> >SELECT t_mytable.case_nbr, t_mytable.code, t_mytable.added_by
>> >FROM t_mytable
>> >WHERE (((t_mytable.case_nbr)=[Forms]![sub_frm_update]![case_nbr]));
>> >
>> >Please note that I have also tried it with [frm_MAINFORM_lookup]![case_nbr]
>> 
>> 
>> Whenever you are prompted for something in a query or
>> report, it means that Access can not find that something in
>> the query's field list or in the report.  Typically, it
>> because there is a misspelled name somewhere in whatever you
>> are prompted to enter.