Form Design – Basic Question (Sort of..) (Access2K)

Topic

New Reply

Ok, sales database. 3 tables – tblCustomers, tblOrders and tblOrderDetails.

I want to be able to open a form, combo box to select customer, which fills in name, address etc… Similar to Orders form in Northwind EXCEPT I want a blank order, with a details subform.

Am i right in saying i will need a form, subform and subsubform to achieve this???

Reply | Quote

Replies

I don’t know what you would need a subsubform for.

You can set the DataEntry property of the form to Yes; this will make the form open to a new (blank) record each time.

Reply | Quote

Ok, so am i trying to complicate things here??

Looking at it again, i guess i should do a lookup in the tblCustomers to retrieve the name and address etc. Then should i bind the form to a query source to save the invoice and invoice details to??

Reply | Quote

The setup would be:
– Create a query based on tblOrders and tblCustomers, joined on the CustomerID field. Double click the join, and specify that you want to display ALL records from tblOrders. You should use the CustomerID field from tblOrders in the query grid, not that from tblCustomers. You can add other fields from tblOrders and from tblCustomers to the query grid.
– A main form bound to this query. Put a combo box on the form bound to the CustomerID field, with tblCustomers as Row Source. The combo box should have (at least) two columns, with the first column hidden by setting its column width to 0.
– A subform bound to tblOrderDetails, linked to the main form on OrderID.
If the user selects a customer from the combo box, the other fields relating to the customer will be filled in automatically. You may want to lock the text boxes bound to those fields.

Reply | Quote

Hans.

Thank you for your time in replying. I am understanding what yousay, and think i became confused when i realised the source for the combo box would be different to that of the form.

So last step, would be to use an AfterUpdate event on the combo box, and use the recordset method? to collect the address etc data and insert it into the relevent text boxes..

Reply | Quote

No, if you set up the query as I described in my previous reply, and use it as record source of the main form, there is no need to use code.[indent]

---

If the user selects a customer from the combo box, the other fields relating to the customer will be filled in automatically.

---

[/indent]Simple as that.

Reply | Quote

Doh!!! Thank you!!

I set up the row source ok…. and I have to admit i did not read (correctly) your binding the combo box to the ID field…. Now it works!!!!

Thank you again!!!

Reply | Quote

Doh!!! Thank you!!

I set up the row source ok…. and I have to admit i did not read (correctly) your binding the combo box to the ID field…. Now it works!!!!

Thank you again!!!

Reply | Quote

No, if you set up the query as I described in my previous reply, and use it as record source of the main form, there is no need to use code.[indent]

---

If the user selects a customer from the combo box, the other fields relating to the customer will be filled in automatically.

---

[/indent]Simple as that.

Reply | Quote

Hans.

Thank you for your time in replying. I am understanding what yousay, and think i became confused when i realised the source for the combo box would be different to that of the form.

So last step, would be to use an AfterUpdate event on the combo box, and use the recordset method? to collect the address etc data and insert it into the relevent text boxes..

Reply | Quote

The setup would be:
– Create a query based on tblOrders and tblCustomers, joined on the CustomerID field. Double click the join, and specify that you want to display ALL records from tblOrders. You should use the CustomerID field from tblOrders in the query grid, not that from tblCustomers. You can add other fields from tblOrders and from tblCustomers to the query grid.
– A main form bound to this query. Put a combo box on the form bound to the CustomerID field, with tblCustomers as Row Source. The combo box should have (at least) two columns, with the first column hidden by setting its column width to 0.
– A subform bound to tblOrderDetails, linked to the main form on OrderID.
If the user selects a customer from the combo box, the other fields relating to the customer will be filled in automatically. You may want to lock the text boxes bound to those fields.

Reply | Quote

Ok, so am i trying to complicate things here??

Looking at it again, i guess i should do a lookup in the tblCustomers to retrieve the name and address etc. Then should i bind the form to a query source to save the invoice and invoice details to??

Reply | Quote

I don’t know what you would need a subsubform for.

You can set the DataEntry property of the form to Yes; this will make the form open to a new (blank) record each time.

Reply | Quote