Hayley Baxter
Registered User.
- Local time
- Today, 23:14
- Joined
- Dec 11, 2001
- Messages
- 1,607
I'm sorry what you on about??
Ban me i ask to many questions!
- Thread starter lynsey2
- Start date
Hayley Baxter
Registered User.
- Local time
- Today, 23:14
- Joined
- Dec 11, 2001
- Messages
- 1,607
Tay are you using access 97? as I think this sample may have been in 2000 if so I can convert for you.
Hay
Hay
Hayley Baxter
Registered User.
- Local time
- Today, 23:14
- Joined
- Dec 11, 2001
- Messages
- 1,607
Try this Tay
Hayley Baxter
Registered User.
- Local time
- Today, 23:14
- Joined
- Dec 11, 2001
- Messages
- 1,607
Tay you're very welcome. Hope it helped!
lynsey2
Registered User.
- Local time
- Today, 23:14
- Joined
- Jun 18, 2002
- Messages
- 439
I FOUND THIS ON THE WEB LAST NIGHT AND THOUGHT IT WOULD HELP OTHERS ALONG WITH THE SAMPLE:
Combo 2- Using AutoLookup
AutoLookup can be used with combo or list boxes to display related data when you select a value in a list. It occurs when you use a multiple-table query to automatically fill in certain field values for a new or updated record. When you enter a value in the join field in the query or in a form based on the query, Microsoft Access looks up and fills in existing information related to that value. For example, if you know the value in the join field (DrugID) between the Drugs table and the Treatment table, you could enter the Drug ID and see the information displayed for that drug from the Drugs table. If no matching information is found, Microsoft Access displays an error message when the focus leaves the record.
Autolookup works if:
the query includes a one-to-many join
The join field on the "one" side of the relationship must have a unique index
The join field you add to the design grid must come from the table on the "many" side of the one-to-many relationship. (In other words, the foreign key for that table otherwise the treatments table can not be updated). For AutoLookup to work, this field can not be a primary key and its Indexed property can not be set to Yes (No Duplicates). For example, in a query that includes the Drugs and Treatments tables, the DrugID field from the Treatments table must be used, not the one from the Drugs table.
The value you enter in the join field from the "many" side must already exist in the join field from the "one" side.
Note that if you want to use an unbound combo box to select records, then it is not the solution because the combo box also requires the one side of the one-to-many relationship to be a query field. The combo box used here for drugid is merely to display the drug details and update drugid in the treatment table, not to filter treatment records based on that drug
The following form will display treatment records with related patient and drug details and uses a combo box to select the Drugs available listed by their names. When you select a drug (which sets the DrugID value in the Treatment table), other fields such as the drug's name and cost per packet are displayed correctly for the corresponding drug. (The form will display the same patient three times if they take three different drugs.)
create query Drugs Autolookup with a one-to-many relationship Include fields from tables Patients, Treatment and Drugs (for example treatment.*, drugs.[Drug name], drugs.[Cost per packet], Patients.[Family name], Patients.[Given name], Patients.Initials, Patients.Gender, Patients.[Date of Birth]) but be sure to include the linking value DrugID from the "many" table Treatment (not the "one" table Drugs), since this is the table in which you want to edit it. When you modify this field on the "many" side of the join, the output information fetched from the "one" side of the join is automatically refreshed (ie [Drug Name] and [Cost per packet]).
create a form using the form wizard based on the Drugs autolookup query
the wizard will ask if the form is to be viewed by Drugs, Treatment or Patient- choose Treatments to get a single record form (choosing Drugs will create a master detail form with Treatment and Patient data in the subform but since it does not have Drugs.DrugID available will not work; choosing Patients will create a master detail form with Treatment and Drugs data in the subform in which case the DrugID field could be a combo box)
create a combo box for DrugID based on the Drugs table
open the form and watch the drugs details change as a different drug is chosen from the combo box
When the value of the join field from the "many" side of the relationship is added or changed in a record, Microsoft Access automatically finds and displays the associated values from the table on the "one" side of the relationship.
While you can always update the join field from the "many" side of a relationship, you can update the join field from the "one" side only if you enabled cascading updates when defining the relationship between the tables.
Combo 2- Using AutoLookup
AutoLookup can be used with combo or list boxes to display related data when you select a value in a list. It occurs when you use a multiple-table query to automatically fill in certain field values for a new or updated record. When you enter a value in the join field in the query or in a form based on the query, Microsoft Access looks up and fills in existing information related to that value. For example, if you know the value in the join field (DrugID) between the Drugs table and the Treatment table, you could enter the Drug ID and see the information displayed for that drug from the Drugs table. If no matching information is found, Microsoft Access displays an error message when the focus leaves the record.
Autolookup works if:
the query includes a one-to-many join
The join field on the "one" side of the relationship must have a unique index
The join field you add to the design grid must come from the table on the "many" side of the one-to-many relationship. (In other words, the foreign key for that table otherwise the treatments table can not be updated). For AutoLookup to work, this field can not be a primary key and its Indexed property can not be set to Yes (No Duplicates). For example, in a query that includes the Drugs and Treatments tables, the DrugID field from the Treatments table must be used, not the one from the Drugs table.
The value you enter in the join field from the "many" side must already exist in the join field from the "one" side.
Note that if you want to use an unbound combo box to select records, then it is not the solution because the combo box also requires the one side of the one-to-many relationship to be a query field. The combo box used here for drugid is merely to display the drug details and update drugid in the treatment table, not to filter treatment records based on that drug
The following form will display treatment records with related patient and drug details and uses a combo box to select the Drugs available listed by their names. When you select a drug (which sets the DrugID value in the Treatment table), other fields such as the drug's name and cost per packet are displayed correctly for the corresponding drug. (The form will display the same patient three times if they take three different drugs.)
create query Drugs Autolookup with a one-to-many relationship Include fields from tables Patients, Treatment and Drugs (for example treatment.*, drugs.[Drug name], drugs.[Cost per packet], Patients.[Family name], Patients.[Given name], Patients.Initials, Patients.Gender, Patients.[Date of Birth]) but be sure to include the linking value DrugID from the "many" table Treatment (not the "one" table Drugs), since this is the table in which you want to edit it. When you modify this field on the "many" side of the join, the output information fetched from the "one" side of the join is automatically refreshed (ie [Drug Name] and [Cost per packet]).
create a form using the form wizard based on the Drugs autolookup query
the wizard will ask if the form is to be viewed by Drugs, Treatment or Patient- choose Treatments to get a single record form (choosing Drugs will create a master detail form with Treatment and Patient data in the subform but since it does not have Drugs.DrugID available will not work; choosing Patients will create a master detail form with Treatment and Drugs data in the subform in which case the DrugID field could be a combo box)
create a combo box for DrugID based on the Drugs table
open the form and watch the drugs details change as a different drug is chosen from the combo box
When the value of the join field from the "many" side of the relationship is added or changed in a record, Microsoft Access automatically finds and displays the associated values from the table on the "one" side of the relationship.
While you can always update the join field from the "many" side of a relationship, you can update the join field from the "one" side only if you enabled cascading updates when defining the relationship between the tables.
