Add/Update Subform VBA code

I can probably write you a query that will display what you want to display. However, it won't likely be updateable.
 
But surly to display information from 2 tables on one form you don't need there to be the foreign key in both tables? No?

Or am I wrong? Cos it would be simple to create a empty payment when the booking is made?

But I would also have to create a fake booking too.
 
Suppose you have two tables Customers and Orders. A customer could have, say, 10 orders. Here's one approach. First make sure the two tables are "actually" related (on CustomerID - but in your case it would be booking_ID). By "actually" related I mean go into Tools > Relationships and graphicallyd draw a line from one table to the other, linking on CustomerID. Next, create a form bound to the Customers table and a subform bound to the Orders subform. The user will probaly find that he can both (A) add a new customer when desired and (B) add a new order to the currently selected customer.

Another possible approach is to base a subform on a LEFT join. This would show all the customers, even those with no orders (in your case, all the booking_IDs, even those with no payments). I don't know if this subform would be updateable (I haven't tried it).

SELECT C.*, O.* FROM Customers as C
LEFT JOIN Orders as O
ON O.CustomerID = C.CustomerID

A left join displays both (A) pairings (matchups) between the two tables and (B) shows all the records in the lefthand table (the one in the FROM clause) even those that had no match in the righthand table.
 
So if you have 1 form, no subforms and 3 tables? It will onlt show information that have keys in all 3 tables?

If so, then is there any way around this?

I want to be able to view 'Customer Info', but if they don't have a booking, it just say 'booking_ID (New)'

So that I can use the same form for viewing customer details and booking and payment details? Can this be done?
 
I found myself a little frustrated trying to figure out your situation. I suggest you start a new thread where you upload your 2007 file and ask for help converting it to an MDB file. Then upload the MDB file here and maybe I can give it another look.
 

Users who are viewing this thread

Back
Top Bottom