Subform Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using Access 2002 and am trying to create the following:

I have a main form that is populated from a Customer table (CustID, etc).
I have an Allergy table (AllergyID, Allery)
and a matching table (CID, AID)

So for each Customer I want to display his/her allergies in a subform that
is located on the Customer form.

I also need to be able to update/add/delete Allergies from the Allergy
subform in the Customer form.

How do I do this?

TIA,
 
so what you have are two parent tables - tblCustomer and tblAllergy, and one
linking (child) table to connect the two parent tables - tblCustAllergies.
so far so good (hopefully, you did *not* use lookup fields in
tblCustAllergies. if you did, recommend you change them to text or number
fields, as appropriate.)

create a main form bound to tblCustomer. create a subform bound to
tblCustAllergies. the main form and subform should be linked on the primary
key field (CustID) and foreign key field (CID).

in the subform, field AID should be bound to a combo box, rather than a
textbox. the combo box RowSource should be a query or SELECT statement
pulling records from tblAllergy, such as

SELECT AllergyID, Allergy FROM tblAllergy ORDER BY Allergy

now, for each customer record, you can add, change, or delete related
records in the CustAllergies subform.

hth
 
tina,

This worked fine except that
1) I now have two columns in the subform, one for CustomerID and one listing
the allergy.

2) I am getting an allergy list like 2, 5, 8, 8, Aspirin, Bezoide blah, Latex

What would I have to post to ask for your continued assistance?

Thanks, Joe

tina said:
so what you have are two parent tables - tblCustomer and tblAllergy, and one
linking (child) table to connect the two parent tables - tblCustAllergies.
so far so good (hopefully, you did *not* use lookup fields in
tblCustAllergies. if you did, recommend you change them to text or number
fields, as appropriate.)

create a main form bound to tblCustomer. create a subform bound to
tblCustAllergies. the main form and subform should be linked on the primary
key field (CustID) and foreign key field (CID).

in the subform, field AID should be bound to a combo box, rather than a
textbox. the combo box RowSource should be a query or SELECT statement
pulling records from tblAllergy, such as

SELECT AllergyID, Allergy FROM tblAllergy ORDER BY Allergy

now, for each customer record, you can add, change, or delete related
records in the CustAllergies subform.

hth
Click to expand...
 
well, you don't need to have a control bound to CustomerID, on the subform.
to be clear: you do need the CustomerID field in the subform's
RecordSource, you just don't need a control on the subform for that field.
the reason is that when you select an allergy from the combo box droplist,
Access will automatically update the CustomerID field in that record with
the correct value from the parent record in the main form.

as for the combo box: if you set the RowSource to the SELECT statement i
suggested (using the correct fieldnames and table name, of course) then set
the following combo box properties as

ColumnCount: 2
Column Widths: 0";1"
Bound Column: 1
ListWidth: 1.25
LimitToList: Yes

now, when you look at the droplist in form view, you'll only see the names
of the allergies. but the actual AllergyID value will be saved in the AID
field in tblCustAllergies.

hth


Joe said:
tina,

This worked fine except that
1) I now have two columns in the subform, one for CustomerID and one listing
the allergy.

2) I am getting an allergy list like 2, 5, 8, 8, Aspirin, Bezoide blah, Latex

What would I have to post to ask for your continued assistance?

Thanks, Joe
Click to expand...
 
tina,

please see below.

tina said:
well, you don't need to have a control bound to CustomerID, on the subform.
to be clear: you do need the CustomerID field in the subform's
RecordSource, you just don't need a control on the subform for that field.
the reason is that when you select an allergy from the combo box droplist,
Access will automatically update the CustomerID field in that record with
the correct value from the parent record in the main form.
Click to expand...

okay, I'll make this change.
as for the combo box: if you set the RowSource to the SELECT statement i
suggested (using the correct fieldnames and table name, of course) then set
the following combo box properties as

ColumnCount: 2
Column Widths: 0";1"
Bound Column: 1
ListWidth: 1.25
LimitToList: Yes

now, when you look at the droplist in form view, you'll only see the names
of the allergies. but the actual AllergyID value will be saved in the AID
field in tblCustAllergies.
Click to expand...

I'll try to be clearer. I've already made the above changes. The 2, 5, 8,
8, Aspirin, Bezoide, Latex are the actual items listed in the dropdown list.
The first item in the list (it has ListIndex=0) is "2" and not "Aspirin."

They appear as :
2
5
8
8
Aspirin
Bezoide
Latex

And not as :

2 Aspirin
5 Ben...
8 Latex

Any other ideas?
 
Back
Top