Passing a value from subform or main form to subform query

[ecwhite]

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.

 

[Marshall Barton]

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.

 

[ecwhite]

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.

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.

 

[ecwhite]

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.

 

[Marshall Barton]

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]

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.

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.