Auto-populate form question

[revnice]

I want to book a customer into a training course.

I want my Booking Input Form to get all the customer information from the Customers table as soon as I enter the Customer Number.

Once the form auto populates, I add the start and end date. Then I want all the info in the form (Customer and dates) to go to a Bookings table.

The Customers table exists and has good data and I have a blank Bookings table ready to receive a booking.

How do I get my form to fetch most of its data from the Customers table?

Thanks - rev

[trb5016]

There's an important question you need to ask yourself here regarding design.

Do you need to track the history of a customers data as different bookings occur?

For Example:

In your customers table you have Johns Smith, and his address is 123 North Road

He makes a booking for England and comes back.

Now John's Address changes to 780 South Road.

He then makes another booking for Antarctica.

When you need a report of this data. Do you need to see that John's address was on North Road when he went to England and on South Road when he went to Antarctica.

OR do you just need to know his current address?


If you need a history you're doing it the correct way and we can help you with the auto populating stuff.

If you don't need a history what you want to do is in your booking table just have a field that stores your Customers ID.

[revnice]

trb!

First, thanks for getting back to me so quickly!

I need a record of the booking so that other users can answer customer questions about it. i.e.

John calls and books the course. Two days later, Fred calls from the same company and wants to know the start date.

At that point, I would query the Bookings table.

That's pretty much it. Cusinfo wouldn't change, the address and contact etc are all stored in the cus table.

I just don't want to enter all the Cusinfo fields when taking the booking. I need them displayed just to make sure the data is correct.

[trb5016]

Okay, then here's how I'm picturing this.

Your Booking table contains the following fields. CustomerNumber (I assume this is the primary key in the customers table, or at least restricted to being unique?) and then all the other fields you have for booking.

Now on for your forms recordsource you want to make a query that joins Booking and Customers on the CustomerNumber field.

On your form place all the fields you need. (Possibly consider locking and disabling those related to Customer if you just want them to be viewable rather than editable)

Then on your form you can make a combobox that lets the user choose a customer number, you can also have it display perhaps the customer name to help in the choice.

Once that value is selected by the user the forms recordset should have all the information related to customer and Booking in one "Flat" design and all the controls you have for customer should populate immediately.

[revnice]

tr

You're a godamn genius!

"Your Booking table contains the following fields."
Yes, exactly right.

The part I was missing was making the join in the recordsource. I haven't tried it yet but it sounds perfect.

Assuming I've finished and I press enter (or whatever) to save the record, how does the data know where to go?

i.e. How/where do you specify the destination? (I'm very new to this)

Thanks!

[trb5016]

Well that's the beauty of setting a joined query as the record source, once you do that you can just drag and drop from "Add existing fields" then access knows to put the booking data into the booking table, and just leave the customer info alone (unless you want the user to be able to edit it.)

You might run into "Recordset is not updateable" but I think you should be okay.

[revnice]

Fantastic. Thanks so much!

Marking it Solved

rev

[revnice]

trb5016 helped me out with this but I found I was unable to make the join!

Either I don't understand what I'm supposed to be doing or I'm in the wrong place.

I'm trying to make a form that populates all the CUS info as soon as I enter their CUS_ID.

The two tables (that's all there is) are Customers and Bookings. I want to enter a customer number in the form, have all the cus data auto populate, add the start and end dates and save it to the Bookings table.

I have the two tables related by primary key. All fields in related tables are 'available' and 'A relationship already exists' if I try to add more relationships.

When I enter 0006 as the cus_num in the form, I don't get the cus_info from the 6th row of the cus table, instead nothing happens. The data from row 1 is still displayed. ControlSource is set to CUS_ID.

Am I supposed to build an Expression? I tried it but entering a cusnum gives me 'Cannot be edited, bound by Expression.'

I suspect my tables don't have a column in common. CUS_ID exists in both, but they're not linked in any way that I know of. How do I include a column from one table in another? Can I drag it or point to it somewhere?

Thanks - rev

[trb5016]

When your making your query, try clicking on the CUS_ID in one table and dragging it to the CUS_ID in the second table.

[revnice]

tr!

Thank God you're back.

Dragging the CUS_ID, is that going to create a relationship or add the column to the other table?

Just to confirm my imaginings, are these steps correct?

* One source table (Customers) and one destination table (Bookings) must be joined by Primary Key. (Done)

* A query must exist that essentially says 'get me cusinfo that corresponds to this cusnum.'

I've done this but running it gives me all rows, I haven't figured out how to get data for one specific cusnum only.

* When creating the form I assign that query to the cusnum field via the RecordSource

I might have misunderstood you here. I can't seem to find the correct clicks to achieve this. The Record Source drop down only lists other columns - am I supposed to assign the query inside the Expression Builder?

Thanks!

[trb5016]

Ah, I don't think I'm explaining myself clearly. Can you post your database? (Remove all sensitive data first if you do)

[revnice]

Sure, do I just export the tables to spreadsheets?

There are only two tables, one's empty and the other just has a few dummy rows.

So much misery from so little!

[trb5016]

You can just make a copy of your database file. Then go in and delete the data from the tables.

Zip the file and then you can upload it here

[revnice]

Cool, I'm all over it.

[revnice]

Here it is. Just to restate it:

* Open form, enter the CusNum, cusinfo auto-populates from Customers table
* Add date range and a couple of other things
* Save it - all the form data gets saved to Bookings table.

I've included a pic of the attempted form and you'll be able to see what's going on from the column names.

Thanks!