Great thanks for the response I will take a look at the links you provided today.
Cheers
Ok, from looking at the links you sent me:
So I am really trying to use Table 1 (list of jobs with ‘job num’, ‘site name’ and ‘clinet name’) as a kind of filter table. Table 2 (List of fees) is really the table where I want all the data. So without using a subform, if I could just have a form where ‘list box 1’ allowed the user to select the ‘job number’ (primary key) from ‘Table 1’,
Then the next two boxes/fields were autofilled with the corresponding ‘site name’ and ‘client name’, or were listboxes which were filtered so that they only displayed the ‘site name’ and ‘client name’ from ‘Table 1’ based on the ‘job number selected’ at the top of the form.
The sample with listboxes are to demo that Table 1 data stays there but allows a direct connection to data in Table 2 without the need for duplication. Your Table 1 is not a filter table. Filter tables contain only one record. If you are determined to use your two table setup with duplicate data in each, then as stated before you need to use macros written specifically for your setup. It is not an efficient method and can lead to other problems as things may change.
Hi Thanks, so Table 1 is just for the purpose of providing information in a listbox in a form. So that users can only select ‘job numbers’, ‘site names’ or ‘client names’ that are in table 1 (i.e. cant put one that dosn’t exist in that table 1 into ‘Table 2’). ‘Table 2’ is where I want all the data stored.
The basic problem is I dont want users who are recording sales data to insert ‘job numbers’, ‘site names’ and ‘client names’ that should not go with each other. As each site has a specific client and job number.
But each job number and corresponding ‘client name’ and ‘site name’ need to be used multiple times (for each new item sold to that site)
They also when filling out the form, if say they put just the ‘job number’ in, need to be able to confirm the site name and client name (just in case they put the wrong job number in by accident).
so I though if they could select the job number (primary key table 1), then site name and client name (colmns 2 and 3 from table 1) would be displayed, and then also recorded into Table 2 when the form is saved. To me it seems like a very common task.
If you look at the sample, without duplicating data from one table to another, AND without the user needing to enter possible errors by duplicating entries the sample works. I have no idea why you think you need to duplicate data in the tables. Can you give a reason for this? Do you need to see a specific sample using your table design?
Sorry, excuse my potential newbie misunderstandings. I don’t actually know whether this can be done without duplicating data. But each site (‘site name’) has a specific ‘job number’ and ‘client’ attached to it, and users may need to make multiple entries under the same ‘site’. So how can I make it so that when a user is inserting a new sale to that site, the specific attributes to that site also get recorded but without error?
The common task is the method I have been describing and not the duplicate data method you have described. Your method is always advised against as it introduces duplicate data and more possibility for errors.
Yes - it is easily done without duplicating data. The records in Table 1 are “Linked” to the records in Table 2 via the Table 1 primary key. This is the common method used.
So two separate entries might look like this…
- SF2506 (job number), Meanwood Castle (site name), John Smith (client name), ballast mix (sale item) and £300 (price)
- SF2506 (job number), Meanwood Castle (site name), John Smith (client name), soil (sale item) and £500 (price)
Sorry but I may be missing a basic understanding here. To compliment the SQL method in your answer above, do you know of any other tutorials that might help me understand how to do this please?
Give me a bit of time to put together another sample based more upon your description. Will post as an edit in my answer.
thanks so much, I am getting quite frustrated with this because I know it must be simple and so I am missing something basic. Thanks Oliver.
Thanks so much for this, I will check it out shortly.
That looks really good so far, the use of the filter table seems to solve it.
I’m yet to apply to my database but thank you very much.
@oli1 This latest sample is actually the same sample (I just copied) pointed to originally (ShippingInvoice.odb). The only difference is table names and one less listbox.
If this has now answered your question please click on the 
(upper left area of answer).
Ok so I have the jobs number selection dropdown and client and site name text boxes set up as in yours, but the text boxes only update with the job number in the jobfilter table when I close and reopen the form??