newbie: form from multiple tables

  • Thread starter Thread starter moe
  • Start date Start date
M

moe

I have four tables: (key fields are noted with an asterisk)

TblCustomer
CustomerID*
CustomerFirstName
CustomerLastName

TblVendor
VendorID*
VendorFirstName
VendorLastName

TblBillingMain
BillID*
CustomerID
VendorID

TblBillingDetail
BillDetailID*
BillID
ServiceCode
Amount

I need to build a billing data entry form that contains
the following fields:

CustomerFirstName
CustomerLastName
VendorFirstName
VendorLastName
ServiceCode
Amount

The Customer and Vendor name fields (first and last) need
to be restricted to existing records and would preferably
come from drop downs. I know I need to build the billing
detail into a subform but I can't even get that far
because I can't figure out how to create the main part of
the billing form. (I have various wrong results but the
most common is that my fields vanish when I switch from
design view to form view.) I'm hoping someone can help
this newcomer with a roadmap. Thanks in advance.

moe
 
here's a good way to get started, at least. make sure your one-to-many
relationship between tblBillingMain and tblBillingDetail is set in the
Relationships window, with integrity enforced.
then click once on tblBillingMain (to select it, but don't open it), and
click the AutoForm button on the toolbar. the system will create a
mainform/subform of the two tables, with parent/child links already set.
open the new form in design view. select the CustomerID control and click
Format, Change, Combo Box from the menu bar. the RowSource of the combo box
needs to be either tblCustomer, or a query based on tblCustomer, or a SELECT
statement based on tblCustomer. set the LimitToList property to Yes.
do the same with the VendorID control, basing that combo box on tblVendor.
this should give you a basic working form/subform. from there, you can tweak
to suit.

hth
 
Back
Top