Multiple tables

[the bard]

the bard

Lying in bed this morning thinking about this (sad I know), it suddenly came to me what the problem is. The form I created which I thought worked was exactly the same as yours, although I created it in a different way. Both work when they pull information from just one table, but fail when trying to get information from two tables. Back to the drawing board!

 

[the bard]

the bard

I think the answer might be a sub-form but have not got anywhere yet.

 

[neileg]

Depends on the tables, but normally you would use a query to link the tables and base the form on that.

 

[the bard]

Can manage to get all wanted information from Sales table and from Address table (or query), but not at the same time. That is, changing order number on order form will change Sales data to that contained on next order, but will not change corresponding Address data.

 

[neileg]

Without seeing your database, I don't think I can help any more.

 

[the bard]

I even tried a simple 'mock up'. First table 'Sales' to include (say) order number, customer's order number, item1, item2. Second table 'Address' to include AddessID, Address1, Address2 and Address3. I entered two lines of data in Sales using a look-up to locate the appropiate AddressID, and two addresses in Address. I then created a Query extracting all this information. I created a Form based on the Query which showed the look up, but no Sales details. It is the same problem all along, I can get the data from either one table, but not both at the same time.

 

[the bard]

the bard

Could I prevail upon you to create a small mock-up? A Sales table containing perhaps an order number, a couple of items etc, and an Address table containing 2 addresses. What I am trying to do is then create an Order form which will 'pick up' the Order and Address details, or, include just the Order details with a look-up to pick the required address(es) laying the address out as if addressing a letter. I have tried queries, sub-forms etc but can only get either, not both. I can open all previous Access versions.
Your help will be greatly appreciated.

 

[neileg]

I'll see what I can do. Trouble is I'm hitting my busy period so you may need to be patient.

 

[the bard]

the bard

You will never believe this, but I really think I have 'cracked it'! I imported your form and added it as a sub-form into my existing form. A rather clumsy way of doing it, but it seems to work. Formatting (particularly size) is difficult and I would welcome any better ways you can suggest when you have time.

 

[the bard]

the bard

Now set up exactly as I want with order details entering automatically and addresses entering from sub-form EXCEPT!

In 'Print preview' it is perfect but when I actually print the address in address sub-form does not print out - the address block is blank. Also, I cannot limit it to printing just the one order, it prints all others with black backgrounds.

 

[the bard]

I have tried a different approach: as your form worked, instead of trying to incorporate it, I have used it 'directly' and built the rest of my form around it. This works ok except for 2 problems: on one form I need 2 addresses which I have not yet managed. Secondly, I must have the ability to edit the forms. I will mainly need to do this for a new, one-off customer whose address I do not need to save. I cannot achieve a blank address on the form to fill in, Access shows an error message.

 

[the bard]

Forms

Thanks to your help, now up and running apart from one small problem when you have the time. Now I must tackle VBA!