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
4226 Views

Similar Articles

[PageSpeed] 5

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:

Query for latest salary
Hi, I have 2 tables with EmpID, Date and Salary. Can i make a query which will pick the salary with the latest date for each EmpID? Thanks in advance for any help Ramesh Thanks Maurice. How do i use the max function on both the tables? i need the latest considerign the union of the two tables. Ramesh "Maurice" <Maurice@discussions.microsoft.com> wrote in message news:E9EB696E-0D13-4CCD-8B28-A0D73A593086@microsoft.com... > You could use MAX([Datefield]) in your query where datefield is the field > you > use for the date... > > Maurice > > &qu...

Open third-party form with VBA
Is it possible to use VBA to open a third-party form from a GP form? My specific example is I want to open a vendor cost inquiry screen that's in the Myridas dictionaries using a button on the GP Item Price List Maintenance screen. Is this possible? GP 9. Thanks in advance. -Joe Yes, it is possible. Add a reference to the Myridas project in your Dynamics GP project in VBA. Take a look at my 3-day VBA Workshop at http://dynamicsgpblogster.blogspot.com/2009/02/this-week-free-visual-basic-for.html for the technique I used. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Glo...

Sorting Count Values in Pivot Table (Excel 2007)
I would like to be able to sort a pivot table by it's count values. I'm using Excel 2007. I'm looking to sort by Current & Expired Suppliers by count of location. For example, for Current Supplier 12345 , I'd like to see the largest count first in each subgroup (Current, Expired) which would be 32, 11, 7 and so on. And then the same for Supplier 22345. I used to be able to do this feature in Excel 2003 through the Pivot Table menu bar (using Sort), but I cannot find this feature in Excel 2007 and I have looked through the HELP and other tutorials. Any ...

Change Query Field Name
Hello, I have a query with the field name "LastName". When I pull a report and export it to excel at the top of the column it come out as "LastName". How can I change it in the query so when I export to excel the column heading says "Customer/Entity Name? Thanks!! Use an alias: SELECT LastName AS [Customer/Entity Name] FROM TableName; -- Ken Snell <MS ACCESS MVP> "Stockwell43" <Stockwell43@discussions.microsoft.com> wrote in message news:D8C930C5-902F-415B-B3C9-F82BE6DF4FD9@microsoft.com... > Hello, > > I have a query...

Zero Values
I have a simple formula that inserts a value in a cell from one spreadsheet into the cell of another spreadsheet. If the source cell doesn't have a value, the destination cell has a zero in the cell. I would like to display a blank if the source cell doesn't contain a value - any ideas? Paul C. Peterson =if(a1 = "", "", a1) -- HTH... Jim Thomlinson "Paul Peterson - Velox Consulting, LLC" wrote: > I have a simple formula that inserts a value in a cell from one spreadsheet > into the cell of another spreadsheet. If the sou...

Merging cells with the same values
I am looking to merge all the horizontal cells in a sheet that display the same value answer as the cell directly along side it. The values are generated from a lookup formular that expresses the answers as words. Is it possible to get a group of cells alongside each other to merge and display the same answer automatically. Thanks ...

Mixed cell displays--values and formulas showing on same worksheet
I the same sheet of the same workbook, some of my cells are displaying the value (for calculated values), whilst others are displaying the formula. Originally, only the values were displaying. I don't know what I did to get this...I think the green indicator appeared on one of my cells, and when I clicked on it I chose one of the options it displayed. How do I get all my cells to appear consistently again? This is Excel 2007 Try pressing ONCE the keys combination: CTRL + TILDE [TILDE is the ~ key] Micky "Compu Geek" wrote: > I the same sheet of...

Retrieving Property Values without Loading Referenced Assemblies
I have some classes in an assembly that inherit from a base abstract class. To load some of the properties that come from the abstract class into a configuration database, I dynamically load them from the assembly (on disk) using Activator.CreateInstance. This way we don't have to hard code this info somewhere to present to the user on an aspx page. Works great when I had one class in the assembly. Now that this is being used more and more classes are being added and more referenced assemblies are being added I'm running into a major issue. Activator.CreateInstance w...

web query on EXCEL does not work. Why?
In November 05 the link to the financial query in Excel stopped working. The error message says" ...unable to open http//investor.msn.com/external/excel....." I've tried it on several different computers. What am I missing? Not sure if it is just a typo but shouldn't > http//investor.msn.com/external/excel....." be > http://investor.msn.com/external/excel....." (ie a missing colon) -- JulieD julied at hctsReMoVeThIs dot net dot au "jimf" wrote: > In November 05 the link to the financial query in Excel stopped working. The > error...

Value Based Colorization of Dialog Controls
I'm currently working with an MFC dialog box that displays numerical data dynamically from a remote server in a series of text control boxes. I'd like to be able to change the background color of these boxes based upon the values in the boxes. So far, I've been able to change the default color of the background of the entire dialog box and modify all text colors. However, modifying colors for specific boxes and making changes based on values called from those boxes is a bit challenging for me. If anyone could give me any tips or ideas on how I might approach this, I would be very ...

axis value extension
How can I extend the x and y axis value in Excel charts? (These are the titles next to the axis, which appear to cut off if want to write anything long) Cheers J Jose, One option is to delete the titles and replace them with textboxes from the drawing toolbar. The textboxes allow much more flexibility. ---- Regards, John Mansfield http://www.pdbook.com "Jose Mourinho" wrote: > How can I extend the x and y axis value in Excel charts? (These are the > titles next to the axis, which appear to cut off if want to write anything > long) > > Cheers > J >...

Breakdown accounts should NOT use absolute value of balance
Currently when using variable allocations, breakdown accounts only computes using the absolute value. We urgently need to capability to have balance specific breakdown accounts. In other words, negative and positive breakdown balances are summarized into a net balance. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and t...

Creating a select query from multiple fields on the same table
Hi all, I've created a table for stores, the primary key is Store Name. I've created another table for input of deliveries to stores on an employee's shift. This is set up as Load 1,2 and 3 with Drop 1,2,3 and 4 in each load. Each drop is validated from the store name field in the store table. Each drop has an arrival and departure time. The relationship set up between the store and deliveries table is a one to many from store name in the stores table to the Load 1, drop 1 field in the deliveries table. The query i want to create is by store, summarising dura...

FRX exported data in report has cells that don't have a value
I have a customer that just integrated FRX with their Microsoft GP accounting system. He indicated that the reports that are generated from FRX have data in cell's that can't be included or manipulated in other ways. Its like the cell visually has a value in it but they are unable to use that value for any other calculations. Does this make sense? How can we make sure that all information exported or reported out of GP with FRX is available for use with other calculations? Is this a bug? By design or do I need to talk with a firm who implements and supports GP and FRX? ...

Label a Percentage Bar with Value
This has to be simple... With Excel 2003, I have a cluster column chart with bars comparing the percentage of total projects assigned (red bar) with the percentage of projects completed (green bar) for various employees. So the bars represent percentages, but I'd like to label the "Total" bar with the actual number of projects. I'm sure this can be done; I remember doing it myself long ago. Many thanks If I understand correctly: right click a Total bar on the chart on the shortcut menu use Format Data Series open the Data Label tab and check the Value box best wishes...

How do I set up a click "column title" and auto arrange data form.
Hi you may provide some more details what you're trying to achieve. also post your question as body of the message and not as the subject title -- Regards Frank Kabel Frankfurt, Germany "Max" <Max@discussions.microsoft.com> schrieb im Newsbeitrag news:76EAD1F9-5D7E-4F7B-99C4-8B8991410FA9@microsoft.com... > ...

Reading an XML file with " and ' as attribute value quotes
Hello, I am currently working on a build solution for multiple c#/vb projects and I'm nearly done. There is one single problem remaining: In case one of the project settings contains a " (double quote ;-), the attribute's value within the project file will automatically be surrounded by ' (single quotes). So far so bad - A XmlDocument.Load() call will now fail with a syntax error exception because there are two quote characters used within the Xml. How would it be possible to read such a miss formed XML-file? Is it possible to write such a smart XmlReader or is there a much...

Exch2003: Query-based dist groups ?
Hi, I'm trying to set up query-based distribution groups on an Exchange 2003 server. So far no problems in collecting most of the data I need - but is it somehow possible to specify in the filter, that disabled accounts should be omitted from the distribution-group ?? /Brian ...

Auto fill form from a worksheet
Using Excel 2000 Professional for work I have a worksheet (Log) that has 7 columns, the first column is a list of pre-set numbers for reports. The second worksheet is an Incident report form. 6 of the 7 colums on the log are also on the report form. We open the log sheet which has pre set Report numbers, then fill in type of incident, location of incident, start date and time. Then open the report form and add the same information into the form plus more information not on the Log. Is there any way to link the 2 worksheets so as the information is added on the Log it will au...

Setting A Label's Visibilty Based On A Text Box Value
Hello All, "Access 07" I'm trying to set a Label's visibility based on a Text Box having data present. I'm using the following code without any success. I just can't figure out why this is not working. Private Sub Detail_Paint() If PhoneAlt.Value = Null Then Label_AltPhone.IsVisible = False (tried this with Visible instead of IsVisible too) Else Label_AltPhone.IsVisible = True End If End Sub TIA Jim Instead of trying to show/hide the label, right-click it an Change To | Text Box. It then has a Control Source property. Set this to: =I...

Business Portal Query Security
I need to create payroll & HR queries for users, but I do not want them to have the ability to modify queries or to create new queries. Patti, Thanks for using the newsgroups. I wouldn't suggest tring to secure your data by not allowing users to create or modify queries. The best way to secure your data is through the data permissions. Only give users data permissions they should have. You can create new data permissions and take away properties if appropriate. Finally, BP has out of the box data permissions called "XXXX-Restricted Self". This means the user can...

"Query cannot be edited by the Query Wizard"
Hi. User gets the message, "Query cannot be edited by the Query Wizard" when editing a query. After clicking OK, MS Query appears and the query is editable. The switch that controls this option appears to be- Data Menu, Get External Data, New Database Query, checkbox "Use the Query Wizard to create/edit queries" at the bottom. This is checked. The problem follows the user to a different workstation as well. Thanks in advance for an advice. ...

How do I duplicate value in multiple worksheets in same workbook?
When I change the value in Worksheet A, Cell C4 I want it to als change the value in Cell D8 in Worksheet B. How do I do this -- jwnorri ----------------------------------------------------------------------- jwnorris's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2932 View this thread: http://www.excelforum.com/showthread.php?threadid=49042 In D8 of worksheet B enter =worksheetA!C4 Gord Dibben Excel MVP On Sat, 3 Dec 2005 12:03:17 -0600, jwnorris <jwnorris.1zh7im_1133633401.2853@excelforum-nospam.com> wrote: > >When I change the value in ...

Text Box values based on Combo Box value
On 'frmAllRepairs' I have two combo boxes - the first 'cboRepairCategory' determines the list for the second:- 'cboRepairType' using the following code: Private Sub cboRepairCategory_AfterUpdate() On Error Resume Next Select Case cboRepairCategory.Value Case "Grips" cboRepairType.RowSource = "tblGrips" Case "Loft and Lie" cboRepairType.RowSource = "tblLoftAndLie" Case "ReGlue" cboRepairType.RowSource = "tblReGlue"...

Future Value function with differenct cash flows
Excel does not seem to have a FV function or variant that allows you to compute the FV of different cash flows at regular periodic intervals given a constant rate. Is there such a function? I can think of several applications where the FV of CF's are needed wherein CF's are at both even and uneven intervals or said another way, at set periods versus random periods and at different CF amounts. There are several work around solutions I can think of, but they tend to be cumbersome. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggest...