Subform Linking To Multiple Master Fields Optional

Hi,
            I posted a question yesterday which Karl helped me, but I want 
to take it further by linking the master fields to the subform where 
whichever control I place a value I want it to return in the subform 
accordingly.
Let me clarify on the setup:

The subform is based on a query named ACCOUNTS
with the fields:
Name
Company
Arrival (which is date field)
Departure (which is date field)
Charge

In the Form named ACCOUNTS SEARCH I have controls named:
Pick Company (Which is a combo box)
Pick Arrival  (which is date field)
Pick Departure  (which is date field)


Now the criteria is set in the controls Pick Company, Pick Arrival and
Pick Departure to filter out in the subform with the records I want, where 
on the subform the:
Link Child Field: [COMPANY];[ARRIVAL];[DEPARTURE]
Master Child Field:[PICK COMPANY];[PICK ARRIVAL];[PICK DEPARTURE]

The query however has in it's criteria field in the fields Company, Arrival 
and Departure the following:
[Forms]![ACCOUNTS SEARCH]![PICK COMPANY]
[Forms]![ACCOUNTS SEARCH]![PICK ARRIVAL]
[Forms]![ACCOUNTS SEARCH]![PICK DEPARTURE]

which is fine because it returns me a Report based on the query.

The problem is that in my Form ACCOUNTS SEARCH I have to place a value in 
all controls Company, Arrival and Departure to return the result in the 
subform.
I want to have a choice of either placing lets say Company and Departure to 
return all relevant results or Arrival and Departure and so on.

Much Appreciated
Ange




0
Ange
2/6/2008 8:20:36 AM
access.forms 6864 articles. 2 followers. Follow

1 Replies
935 Views

Similar Articles

[PageSpeed] 26

Ange, it would be better to take a different approach. What you are doing is 
possible, but very messy to build and maintain, and quite inefficient to 
execute.

A better solution would be to build the WhereCondition string for OpenReport 
based only on the controls that have a value.

Here's a downloadable example of how to do that
    Search form - Handle many optional criteria
at:
    http://allenbrowne.com/ser-62.html

That example sounds like it is quite similar to yours, but places the search 
boxes in the Form Header section so you don't need a subform. It illustrates 
how to filter the form based on any combination of criteria the user 
chooses. And the Trouble-shooting And Extending part talks about how to 
apply the results to a report.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ange Kappas" <angekap@hol.gr> wrote in message
news:fobqkm$ufq$1@newsmaster.pub.dc.hol.net...
> Hi,
>            I posted a question yesterday which Karl helped me, but I want 
> to take it further by linking the master fields to the subform where 
> whichever control I place a value I want it to return in the subform 
> accordingly.
> Let me clarify on the setup:
>
> The subform is based on a query named ACCOUNTS
> with the fields:
> Name
> Company
> Arrival (which is date field)
> Departure (which is date field)
> Charge
>
> In the Form named ACCOUNTS SEARCH I have controls named:
> Pick Company (Which is a combo box)
> Pick Arrival  (which is date field)
> Pick Departure  (which is date field)
>
>
> Now the criteria is set in the controls Pick Company, Pick Arrival and
> Pick Departure to filter out in the subform with the records I want, where 
> on the subform the:
> Link Child Field: [COMPANY];[ARRIVAL];[DEPARTURE]
> Master Child Field:[PICK COMPANY];[PICK ARRIVAL];[PICK DEPARTURE]
>
> The query however has in it's criteria field in the fields Company, 
> Arrival and Departure the following:
> [Forms]![ACCOUNTS SEARCH]![PICK COMPANY]
> [Forms]![ACCOUNTS SEARCH]![PICK ARRIVAL]
> [Forms]![ACCOUNTS SEARCH]![PICK DEPARTURE]
>
> which is fine because it returns me a Report based on the query.
>
> The problem is that in my Form ACCOUNTS SEARCH I have to place a value in 
> all controls Company, Arrival and Departure to return the result in the 
> subform.
> I want to have a choice of either placing lets say Company and Departure 
> to return all relevant results or Arrival and Departure and so on.
>
> Much Appreciated
> Ange
>
>
>
> 

0
Allen
2/6/2008 12:55:19 PM
Reply:

Similar Artilces:

automatically update field
Hi I have a combo box with two columns - product & stock code. When I select a product I want the the stock code column which I have in my table to automatically populate. Is this possible to have on the actual main table? -- Thank you for your help Yes - you could use the AfterUpdate event of the combo to set the value of the control bound to the field holding the stock code. But - why ? You already have the stock code so it seems you want to store it again (twice in the same table or - even worse - in another table). Both of these are very bad ideas. You should use the rel...

Wrong contact appears in TO: field
When typing a few letters in the To: field using Outlook 2K3, the wrong contact appears. A BUSINESS FAX shows up for the contact name instead of an e-mail address. I remember there is a way to clear some quick list Outlook uses, but forgot how to get there. Any help is appreciated...Thanks. -Joe Hi Joe, please look here http://home.arcor.de/andreas.roeder-privat/OL_Prog.htm#1 -- Ich habe nichts gemacht, gestern gings noch! Bitte in den Newsgroup antworten damit jeder etwas davon hat. Bravestar@Datenschutzministerium.de "Joe (CJ)" <anonymous@discussions.microsoft.com> ...

Multiple instances
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Email Client: Exchange Hi, Just researching converting to Mac, have finally had enough of windows. I have 3 separate businesses all running on their own exchange server / domain. Is it possible to have 3 instances of entourage open and connected to 3 different accounts at the same time? Hi, even though I am no Exchange expert, I think that you can create as many Exchange accounts as you want in any edition of Entourage 2008 other than the Home & Student Edition (which does not support Exchange at all). On 27/11/0...

Excel increasing slowdown, in multiple versions
I've Excel VBA macros which repetitively open 100+ data files (no more than 4 open at once), do heavy number- crunching, save results, then do it all over again a few hundred times with slight variations in parameters. It takes a week to run one of these, and using the Timer function I discovered that the time taken to do one iteration slowly increases from 10 minutes to over an hour (even though the data and calculations are the same) and despite all effort to close files as soon as finished, etc. I use Excel 2000 (9.0) and read in the Microsoft knowledge base that there are memo...

repost; Automatically updating formulae in multiple sheets
I list sales of 20+ products each week for the year A2-A23 are the products and B-BA are the colums for each week I have multiple sheets that have the same sales results in the same format foreach previous year 2004;2003;2002;2001;2000 Each week I want to compare total sales in the year to date i.e weeks 1-20 with weeks 1-20 in each previous year. so next week it will be 1-21. I have on each sheet a cumulative total for each of the 20 products in the year to date i.e Cell A25=SUMB2:BA2 and Cell A26=SUMB3:BA3 and so on for each product. This means each week I have to alter the formula in C...

Linked Worksheets
I have a number of editable workbooks which feed into a protected workbook. When the users update the editable spreadsheets, they click save and move to the next one, update, save etc etc. Then when I go into the protected workbook it asks me if I want to update, which I choose to do, but for some reason, the data previewed with that message then changes back to data which isn't the most recent data and I have to go in to update and save all the other workbooks again before my protected workbook shows the most recent data. Is that how it's supposed to work? Thanks in advance ...

refering to a name range area as a reference value multiple times on a worksheet at different locations
i was wondering if i had posted this in the right section as it could apply to both please could you take a look at the following post http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/f088a5737dd26570 thank you Is there some reason you can't post your question here instead of asking us to look elsewhere? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Blinds Nottingham" <bradrail.blinds.awnings@googlemail.com> wrote in message news:d8768e43-e035-45eb-856a-0ccdc843441d@g1g2000yqi.googlegroup...

Check store option hanging
Hi all Check store option hanging and i need the any solution to this problems and i'm sent the Question =================================== some time These problems occur in Machines 1- Hanging application HQServer.exe in Machine server RMS 2- Check store option is hanging and HQ client is Hanging and not work I hope you will help me find a solution to this problems thanks Best regards =================================== We have Mr. Terrible Tom graciously responded to that and I appreciate and thank him very much but i can not find a solutio...

"Show Placeholders" in options/view menu does'nt work. #2
I have a macro that places a large number of pictures on a spreadsheet. I would like the pictures to show as "placeholders" to speed up viewing/editing. The only problem is that this feature (under Tools/Options/View) does not work. All the images are being display normally. I have tried this in Excel 2000/2003/XP with the same result. This feature works great in MS Word. Please help. To see what I'm talking about. Go to Tools/Options/View and check the "Show Placeholders" option. Now insert a picture or clipart. The image will be displayed normally instead of just a b...

wrong subform total
I have a subform called "InvoiceDetailssubform" and a main form called "invoices details" in the subform there is a field Expenseitemamount so to get total of this field ,i have places a text box in the footer of the subform , which i called it "Expense Totals" now i want to get this figure for each record of the form " invoices details" but unfortunatley am i getting wrong figure , i get the total amount of all records in the source table of the subform . although i am using the same way in another main and subform , and getting correct results. w...

Using variable in field name
Hi. I have a table whose fields are named boolType00, boolType01, boolType02, ..., boolType19. I need to make a loop and real all of the 20 fields. Something like: Dim i1 As Integer i1=0 While i1 < 20 Debug.Print MyTable!boolType"i1" i1=i1+1 Wend Is there a way to do it? Thanks H. Martins If you OpenRecordset, you can refer to the field in the recordset like this: Debug.Print rs.Fields("boolType" & i1) If you need help with using a recordset, see: http://allenbrowne.com/func-DAO.html#DAORecordsetExample Lots of repeating fields like that usua...

Relative addressing in external links
On a weekly basis the company at which I am on assignment downloads data from a huge database which they then cut and paste into various Excel reports. I want to replace this cut and paste process using external links from the reports to the downloaded data files (which are created by .csv to ..xls). Once this is set up, theoretically, I will be able to download a new set of data files and then when I open the Excel reports the cells in the reports will update automatically with no cutting and pasting between files. The only rub is that sometimes a row or column might be added within th...

Combo look up, format from number to text field
Hi I was given this code to look up on two combo box name Customer Number Posting Date Period which then a third combo box would look up on these two combo box an sum the value Amount field in a table for example Customer Number C00001 Posting Date 2009/07 Amount £5000 This is my code so far =DSum("Amount", " July 09 - On Billings and Adjustments Data Query ", " Format (Customer No#, "text) = " & Format (cboCustomerNumber.Value, "text") & " AND Format(Posting Date Period, "...

what happened to[LINK] chart-wrksht,when not same page? (same Wrkb
I have a chart, on separate pg...same Wrkbk. Selcting a Series - does not 'outline' it's data returning to the Wrksht. How do you EDIT? hawk12@peoplepc.com Hi, Simply selecting the series will not change sheet to show you data as you have discovered. Try using the Source Data dialog, this will allow you to swap sheets and see and adjust the data used. Cheers Andy Boswell wrote: > I have a chart, on separate pg...same Wrkbk. Selcting a Series - does not > 'outline' it's data returning to the Wrksht. > How do you EDIT? > > hawk12@peoplepc.com &...

multiple duplication of records
Could anyone assist me with the following:- I have been using the quick reference search tool that can be found at www.access-programmers.co.uk/forums/showthread.php?t=120366 <http://www.access-programmers.co.uk/forums/showthread.php?t=120366> (cool search tool.zip) Using only one table with no problems however I am now trying to use it in a relationship configuration Two tables linked back to the main table via one too many links. What is now happening is that multiple duplication of records are being displayed. An example If you have one table with two records the listbo...

Prefill Multiple Macro Buttons
I created a template and created multiple macro buttons where the user can click and type the appropriate information. There are several macro buttons that are the same and the user would have to key the information in several times. For example: [Click and type company name]. Is there a way to have the user type the company name once in that macro buttun and it prefills all the macro buttons that have [Click and type company name]? Thanks! Jlo See http://gregmaxey.mvps.org/Repeating_Data.htm -- <>>< ><<> ><<> <>>< ><...

Multiples of Same Email Addresses for a Contact
Somehow I have ended up with 2 - 4 of the same email address for every contact. When I enter a name in the To: line I get a box asking me to choose which of the email addresses I want to use. But, they are all the same. I have tried to locate the file with multiple entries, but when I open Contacts there is only on email listed. Where can I find the multiple entries and delete them? Thanks. That special list of contacts is known as the auto-complete feature. Windows Mail does not auto-complete from your regular contacts. It uses a separate list of the last 29 recipients you&#...

Message Journaling / Multiple Recipients
Through these usergroups, I have been shown how to activate message journaling, and also how to set up a user so that their Outlook can check more than one mailbox. I'm using Exchane 2003. I have a mailbox, 'masterfile', that is the recipient of the Journaling. I do not have any one user's Outlook set up to check the account, as 2 or 3 individuals need access to it, so I have just given the OWA login information. However, the main bossman wants to have the masterfile info delivered to his Outlook, like it 'used to be' with the Linux server. How can I accomplish ...

Multiple IMAP, POP and Hotmail accounts
OK, I've played with this for months, and I'm really tired of this crappy piece of Outlook software, but forced to use it. -*- Using Outlook 2002 -*- Situation: 4 Email accounts One Pop account to Yahoo Two IMAP accounts One Hotmail account Problem #1: When sending, only one, or none, but never all of the accounts show up under the accounts button when I send email. Problem #2: When trying to set a default account in the email accounts, on IMAP account can't be set, but on every other press of the default button it bounces to another account. (Not every clic...

one mdb form with multiple tables?
I have inherited an Access front end that is an mdb with a SQL Server backend. I need one of the forms to save data to multiple tables. In turn, this form would also need to load its data from these different tables. I know that saving/loading data for one form to/from multiple tables is an easy task with an adp. But is possible to do that with an mdb form? And how would I go about doing that? Thanks in advance for your help. The only way a form can interact with more than one table is to use a query that joins the tables together. Unfortunately, usually doing that results in a query that&...

adding user defined fields in RM Detail Aged TB
Hi! Is it possible to include the fields entered in Sales User-Defined Fields Entry window in the RM Detail Aged TB - Options report in report writer? Thanks, Mel ...

put more than 255 field on report from two tables
Hi, My project is in MS Access 2002. In that I want to generate Report which contain fields 258.I can put 255 fields but how can i add more 3 fields. when i generate report for 258 fields than it will show msg too many fields. how can i solve this problem? It is a bit unusual that you would need to use that many fields. However, you can get around this limitation by using subreports. -- Duane Hookom Microsoft Access MVP If I have helped you, please help me by donating to UCP http://www.access.hookom.net/UCP/Default.htm "billy_pit" wrote: > Hi, > My project is in MS Ac...

To field is blank when a message is sent and saved to sent items
I have a user who recently was upgraded to Outlook 2003 for Outlook 2002. He has a third party app that sends out legal documents. What it does is compose the email and put the attachments in it and puts the messages in his draft folder. He reviews them and sends them out. Since going to Outlook 2003 the emails are in his Sent Items but the TO field is blank. When they were in his draft folder the TO field had the email address in it. if you bring up the email and click on resend it shows the email address in the TO field. My question is what would cause the email address to be hidden or disa...

Lost link bar
I scrolled a link bar down out of the page frame in FrontPage 2003 for Windows and can't get it to come back. Can somebody tell me how to get the link bar back up into the page frame? Post a URL of the page using the link bar and the link that is supposed to show - so we can see what is wrong -- _____________________________________________ SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ] "Warning - Using the F1 Key will not break anything!" (-; _____________________________________________ "Dick Johnson" <user@msgroups.net/> wrote in m...

Mailbox Backup options
I am about to move from two standalone tape drives to an autoloader to manager backup jobs....no more tape jockying... For information I am using Veritas, upgrading this week to v10. I am wanting to change my Exchange backups from backing up the entire mailbox store to backing up mailboxs' individually for quicker mail recovery. I was once told that even when doing mailbox by mailbox backups I should still backup the entire mailstore in case a mass restore needs to be done. Is this true? I hate to duplicate efforts like this. Any input is appreciated Individual mailbox backup (aka ...