Passing a value from subform or main form to subform query

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.


0
Utf
2/11/2010 4:17:01 PM
access.formscoding 7493 articles. 0 followers. Follow

4 Replies
3989 Views

Similar Articles

[PageSpeed] 7

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]
0
Marshall
2/11/2010 5:03:12 PM
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]
> .
> 
0
Utf
2/11/2010 5:56:04 PM
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.
> 
> 
0
Utf
2/11/2010 8:10:02 PM
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.
0
Marshall
2/11/2010 8:38:25 PM
Reply:

Similar Artilces:

Is It Possible To Pass Parameters to A Pass Through Query
My assumption is that's it's not. At work I use ODBC to connect to our oracle database with Access 2003. There are cases where using a pass through query runs much much faster and I then use it in a make table query to make a local table. In access you can use brackets [] to have it ask for input. May I assume there is no way to do anything like that in a pass through query? Create a PassThrough query and use code to assign the SQL to this query filtering it with a parameter, and then run it e.g Dim MyVariable As Integer MyVariable = InputBox("Please select a Number&quo...

Avoid drawing blank values
Hello, Can you tell me if there is a way to plot only non-blanked values from a table: F.e. if: X= 1,2,3,4 Y=10, ,10,10 (so no value for x=2) I want to obtain a continuous line going from the first point (1,10) to the third point (3,10) and then of course to (4,10). Thanks in advance for your help, Iv�n ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ On Mon, 10 Nov 2003 10:52:34 -0500, ivarela1 = <ivarela1.woqln@excelforum-nospam.com> wrote: > Can you t...

Pass multiple selected values from list box to table
How do I pass multiple selected values from a list box to a table? I've found code to use the values as criteria in a query but would now like to store the values in a table (at least temporarily) thanks in advance. jack Pass selected values from a list box to a table? I don't really think you want to do this. Can you describe more about what you are trying to accomplish? You just store raw data in a table. You don't store manipulated data in a table. Maybe you need a query to do what you want to do... -- Ryan--- If this information was helpful, please indi...

Require value for "regarding" in Outlook email promotion?
We want the Outlook client to require users to select a value (contact, account, etc.) for the "regarding" field when an email is promoted. If I customize the CRM “email” entity so that its “regardingobjectid” attribute is “Business Required”, will that do the trick? ...

Chart of frequences of values
Hello, How can I create chart of value�s frequences according to a frequenc table? (see attachement) Thanks a lot +------------------------------------------------------------------- |Filename: graph.JPG |Download: http://www.excelforum.com/attachment.php?postid=3691 +------------------------------------------------------------------- -- balcovj ----------------------------------------------------------------------- balcovja's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2623 View this thread: http://www....

Do not display Value label if value = zero
Hi I am putting together a pie chart, where values for some slices is 'zero'. The zero slice is not visible, but the label still appears '0%'. Is there any way of suppressing this label for the zero slice only (so the other slices still have value labels appearing) ? Many thanks for any help Format label, custom format: #;;; -- Best Regards, Luke M "LFNFan" <LFNFan@discussions.microsoft.com> wrote in message news:CC677569-1FB1-4108-8535-C5BDC6E1B825@microsoft.com... > Hi > > I am putting together a pie chart, where values ...

data entry in subform before mainform causes huge hassle, HELP!
Hello all i have an application where there are forms which have subforms where it is entirely possible for the user to start entering data in the subform before the main form. this is proving to be a real pain. as soon as a user enters something in the subform and then moves from it to the main form and types (cause the on dirty state), the entered data in the subform is saved and disappears. basically because the link between the mainform and the subform is an autonumber field on the main form, which doesnt have a value until the user types in the main form, the main form ...

Date query 01-21-10
I have a table which contains a field called "DUEDATE", data type is DATE/TIME, formatted as LONGDATE. I use it to enter in dates when projects are due. How can I write a query which returns "DUEDATE" items for the current date. Example: Today is January 21, 2010 and I want to retrieve everything from the table with "DUEDATE" of january 21,2010. If you only store the date part and only want to return records where the DueDate is the current date, set the criteria under DueDate to: =Date() Duane Hookom MS Access MVP "Iago" <Iago@d...

PivotTable buttons remembers old unactual values
I have discovered that buttons in the row area remembers old values not longer existing in the data. Ex: I have 4 persons with initials "aaa", "bbb", "ccc" and "ddd" I have the Initial button in the row area. When I click on the arrow of the "Initial"-button, it gives me the possibilities: "(Show all)", "aaa", "bbb", "ccc" and "ddd". If I change initial "ddd" to "xxx", refresh the pivot table and again click on the arrow of the "Initial"-button, it gives me the p...

Conditional values
I am looking for a formula that would accomplish the following: the entering of an alpha value in a cell would trigger populating the adjacent cell with a numeric value, i.e. entering 'Y' in cell A1 would automatically populate cell B1 with '1' (I use column B as a counter). Thank you. One way: B1: =IF(A1="Y",1,0) or, equivalently B1: =--(A1="Y") but depending on what you're doing, you can get a count of "Y"s in Column A using =COUNTIF(A:A,"Y") In article <E6DC0B82-515F-4488-AF0B-FE990B9BE714@microsoft.com&...

Uneditable Form
I set up a recurring email form with some code from slipstick systems used in conjection with task scheduler. It used to work fine. I've done something that changed this. I've changed and deleted some personal folders and I also attempted to edit the form. Anywhow I find now that I can find the form, but, when I select tools>forms>design a form and select it the form is sent rather that being brought up in a edit window. Does anyone have any ideas? Thanks in advance ...

Finding value
I have a user form where the user enters a site number. in the spreadsheet i have a range names "ValidSites". After the user enters a site number and clicks on a button, i would like to validate that that site appears in the ValidSites list. onclick event() if txtSiteNumber is found in Range(ValidSites) then msgbox("found") else msgbox("notfound") endif endsub what would be the code for the if statement? thanks in advance -- tkaplan ------------------------------------------------------------------------ tkaplan's Profile: http://www.excelforum.com/memb...

Data Dictionary with sample values
hi guys, I am going to create a data dictionary of our systems as I am sick of trying to find out what columns have been called in different tables. There is a hundred ways to do this but I am going to use INFORMATION_SCHEMA.COLUMNS to get the database, schema, table, column, type and length. So far so good. I also want the data dictionary to contain 'sample' data for each column so that you can see the types of values that are held in it. A query that looks something like this does the trick.. [code] SELECT TOP 5 * FROM ( SELECT Myfield as 'field',...

Use combo to display record on subform?
I am fine with using a combo to find a record on a form, and display all the details about the chosen record in a number of controls. But the very same design doesn't work if it is all on a subform on a tab page. I can select the value in the combo (the name, for example) but the address data doesn't pull through into the address controls. How can I achieve this? Many thanks CW CW, CW wrote: > But the very same design doesn't work ... What "very same design" is that? Can you give a bit more detail about what and how you are doing this please? -- Steve Schapel...

Listbox to subform refresh via code
I have a form with a listbox on it and an subform. The subform is queried by the selection in the listbox. All of this works!! I have added another feature on the form where the rows of the listbox can be reduced based on a search argument. I.E. if you want only people with the last name "Smith" in the listbox, you type Smith into a field and click a search button. I use this to alter the data source of the listbox to include only items with the name "Smith." That also works perfectly and a subsequent selection queries the subfiorm and works. What I want to...

How do I set the print range on a form. Regards
I've created a Timesheet on a form in Excel, but when I print, it only prints a third of the form Enter this into Excel help: Define or clear a print area on a worksheet This should give you a solution "Dave K" wrote: > I've created a Timesheet on a form in Excel, but when I print, it only prints > a third of the form In Excel 200, you would select the are to print, go to "File", "Print area", "Set print area". To make it easier and quicker to print in future, you can record a macro as you print the print area and att...

Form to query
Hello everybody, I have a question concerning the "Enter Parameter Value" window that opens when I run a query. I have asked this in the access.queries newsgroup but I couldn't get the answer. Is it possible to have that as a combobox so that a list of possibilities is shown? Another question has to do with the same query. This asks a date from and to (in the query criteria >[from] And [to]. I would like the selected period to be shown in the report. Is this possible (and if so... how can I achieve that)? Thanks for all the help you can give -- Lisa Save the Dogs Onlus...

CMFCPropertyGridCtrl
Hi All, I have an MFC application with a TreeView and a CDockablePane Properties Window. When I select a tree item - I display its properties in the properties window. That works. But I don't know how to change the tree item properties when I change the values in the properties window. My problem is in getting the values from the properties grid. I tried to override EndEditItem method like this: BOOL CMyMFCPropertyGridCtrl::EndEditItem(BOOL bUpdateData) { CMFCPropertyGridProperty* currProperty = GetCurSel(); if(currProperty) { //I don't like this implementa...

Setting a value on a main form from a subform
All, Am I correct in assuming the way to set the value of a control on the main form from a subform is: Me.Parent.ControlName.Value = "XXX" Obviously it isn't because I continue to receive errors when I try to access the control. Thanks in advance. - CES CES wrote: > All, > Am I correct in assuming the way to set the value of a control on the > main form from a subform is: > > Me.Parent.ControlName.Value = "XXX" > > Obviously it isn't because I continue to receive errors when I try to > access the control. > > Thanks in advance...

Formula to determine row of hi value filtered column
Hi all Trying to see if its possible for a formula to return the row # of the highest or lowest visible value in an autofiltered range. Thanks Richard -- rgarber50 ------------------------------------------------------------------------ rgarber50's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11350 View this thread: http://www.excelforum.com/showthread.php?threadid=401821 ...

How to pass an object from C++
Hello! We have a C# asp.net web application that is using a COM dll We use the tlbimp to be able to use the COM dll from C# asp.net web application The method InitRules shown below is located in the COM dll. It can be seen below the text Original. Method InitRules below is called from C# but we want to pass an object of type Handle_DS in addition to all the other parameters. You can see the modified InitRules below marked Modified Note the object of type Handle_DS is a C++ object that is created from C# asp.net application and pass into the InitRules I have also copied the whole idl fil...

Preventing user altering cell except to delete the value.
Is there a way to stop a user altering the value in a cell but still allow him to delete the value there-in? Rob One way: Put this event macro in your worksheet code module (right-click on the worksheet tab and choose View Code): Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const cVALUE As Double = 100 With Target If .Address(False, False) = "A1" Then _ If Not IsEmpty(.Value) Then .Value = cVALUE End With End Sub Change cVALUE and the address of your cell to suit. In article <uCkCXD4uDHA.1680@TK2MSFT...

Checking a forms fields for null
Hi All, I can think of a few ways to accomplish this, but would like to know of a better way. just like when you fill out forms on the web if the field is mandatory it pops up a msg say somthing like "This field isn't optional" if it is left blank. What is the best way to do this in access on a form? I'm using now: if ISNull(myFeild1) then msgbox"Please fill in myFeild1" end if If ISNull(myFeild2) then msgbox"Please fill in myFeild2" end if if ISNull(myFeild3) then msgbox"Please fill in myFeild3" end if I use s...

date diff expression in query
I am trying to calculate the difference between two dates 'End Date' and 'Date of Visit' using a query. This is the expression I used: TimeFrame:[Date of Visit] - [End Date]. However, 'End Date' is present in both tables I have used to create this query 'tbl_NewInfo' and 'tbl_Foloowup'. I want the 'End Date' from 'tbl_NewInfo'. How do i specify this in the expression and thereby prevent the error msg I am getting? Thankyou You must fully qualify the field name like: [Table Name].[Field Name] I use a naming convention that resu...

Making form using Excell Help ASAP!
I've made a form. This needs to be filled out online. I can freeze cells so the user cannot mess with them, but how do I form lines.? Say, a line above 'Customer Name' in order to answer that question. This is really getting to me b/c it seems like it should be so easy... Hi Amanda not really sure what you're after but if you click in a cell and choose format / cells and click on the border tab and then the top line icon does this give you what you want? Cheers JulieD "~Amanda~" <~Amanda~@discussions.microsoft.com> wrote in message news:26819D...