SubForm Linking

[Guest]

I am working on a database which involves 50+ vehicles, each of which can
carry from a list of 80+ items of equipment, and varying quantities of each
item. I have in mind a main form which shows the vehicle and driver details,
with a sub form which lists all the items of equipment that can be carried.
The end user would then select the actual items and quantities carried. My
problem is linking the two forms. Does anybody have any suggestions, please?
The obvious link would seem to be registration number, but if I add this
field to the equipment table I have to enter this information for each item
of equipment. I've also tried setting up a table with each item of equipment
as a field, but then I hit problems with calculations I am trying to build
into the database - i.e. qty x value, so that I can get it to put a value on
all the equipment carried by each vehicle.

Many thanks in advance for any help on this problem which I have been
pondering for some time now.
GeeJay

 

[PC Datasheet]

Set up tables that look like:

TblVehicle
VehicleID
RegistrationNum
Description
etc

TblItem
ItemID
ItemDescription

TblItenCarriedByVehicle
ItemCarriedByVehicle
VehicleID
ItemID
QuantityOfItem

Create a form/subform for data entry into TblItenCarriedByVehicle. Base the
form on a query based on TblVehicle so you can sort by Description
ascending. Be sure to include VehicleID in your query. Base the subform on
TblItenCarriedByVehicle. Use a hidden textbox for VehicleID, a combobox for
ItemID and a tectbox for QuantityOfItem. Use a query based on TblItem
sorting ItemDescription ascending for the rowsource of the ItemID combobox.
Design the combobox to have the value ItemID. For the subform control on the
main form, set the Linkmaster and LinkChild properties to VehicleID.

In the form/subform, after you select a Vehicle, Access will automatically
fill the hidden VehicleID textbox in the subform with the VehicleID of the
vehicle you selected in the main form when you selct an item or enter the
quantity.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Over 1000 Access users have come to me for help. My fees are very
reasonable."

 

[Guest]

Thank you so much for your response. However, I have to admit that I am
having trouble following your instructions. Is there any chance you could be
a bit more explicit for somebody who is fairly new to Access, but does
understand tables, queries, relationships, and forms. Maybe a line space
inbetween each set of instruction would help.

 

[PC Datasheet]

I presume you can set up the tables so I will start after that. See in-line.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Over 1000 Access users have come to me for help. My fees are very
reasonable.

Thank you so much for your response. However, I have to admit that I am
having trouble following your instructions. Is there any chance you could
be
a bit more explicit for somebody who is fairly new to Access, but does
understand tables, queries, relationships, and forms. Maybe a line space
inbetween each set of instruction would help.

1. Use the query wizard to create a query based on TblVehicle. Include all
the fields in the table. Set the sort on the Description field to ascending.
2. Use the form wizard to create a form named "MyMainForm" based on this
query. This form will be the maim form for the form/subform.

Be sure to include VehicleID in your query. Base the subform on3. Use the form wizard to create a form named "MySubform" based on
TblItenCarriedByVehicle. Include all the fields on the form.
a) Use a textbox for VehicleID and set the visible property to False
b) Select the ItemID textbox, Click on Format - Change To in the toolbar
at the top of the screen and select Combobox.
(1) Use the query wizard to create a query based on TblItem and
include all the fields. Set the ItemDescription field sort to ascending.
(2) Go back to "MySubform" and select the combobox
(3) Set the rowsource property of the combobox to the query in (1).
(4) Set the bound column to 1, Column Count to 2 and Column Widths
to 0;1.5
c) Use a tectbox for QuantityOfItem.


Use a hidden textbox for VehicleID, a combobox for4. Open "MyMainForm" in design view. Click on Windows - Tile Vertically in
the toolbar at the top of the screen. Click and drag "MySubform" and drop it
in the detail section of "MyMainform". This makes "MyMainForm" the main form
and "MySubform" the subform.
5. Select the funny looking object (actually a subform control) that
appeared in "MyMainform". Open properties, go to the data tab and set the
Linkmaster and LinkChild properties to VehicleID.

OK to go!!!

 

[Guest]

Thank you so much for the help given on this problem. It has worked
beautifully and I now have the beginnings of a really useful database.