Colin has attended:
Excel Intermediate course
Data for forms
I have a unique identifier, which I want to point to be able to be selected from a list.
When selecting the unique identifier (not a primary key) I would like selected information to come up in the form.
I.E when inputing the unique code of a branch, I would like the name of the store, address and branch manager to auto populate. Could you please advise.
RE: Data for forms
Hi Colin
I've set up a small example called testdropdown.
The form SalesInfo has a dropdown for selecting a Branch code. Once selected this populates the Banch name, address and manager.
The SalesInfo form stores data into the SalesInfo table. In each record once a Brance code is entered, the branch info is drawn from the Branches table.
However for this to work the Branch code in the Branches table has to be a Primary key. Removing the primary key prevents the SalesInfo form being updatable.
Would there be any way you could make your Branch code a primary key?
Doug
Best STL
Attached files...
RE: Data for forms
Hi Doug
Thanks for the response.
A few things I am not clear about.
1) There does not appear to be a primary key set in either of the tables. I can't see it in the design view, but can see it in the relationships?>
2) How do you set the dropdown table? and how do you get it to auto select the other data? In the design view of the branches table, there does not appear to be a setting in the field properties that stands out.
3) How do you launch a new form each time you want to input data? I.e. you will have to submit a new form each month?
4) I had set some relationships, 1 is to many (in my example) but had to undo as I had not set up my data correctly. If I set relationships, will this allow me to avoid using the primary key?
I thought I was getting somewhere, but have to go back to the beginning to move forward again!
Looking forward to your help.
Colin
RE: Data for forms
Hi Colin
I'll do my best to answer your questions
1) There does not appear to be a primary key set in either of the tables. I can't see it in the design view, but can see it in the relationships?>
There needs to be a primary key for the Branchcode field in the Branches table. I've resent my example again in case it wasn't set.
2) How do you set the dropdown table? and how do you get it to auto select the other data? In the design view of the branches table, there does not appear to be a setting in the field properties that stands out.
The combo box field is in the SalesInfo table on the Branch field.
To create the SalesInfo form use Create, More Forms, Form Wizard.
Choose the Branch field from the SalesInfo table
Choose the BranchName, Address, Manger from the Branches table. I added two more fields Date and Sales from the SalesIndo table to create some context.
3) How do you launch a new form each time you want to input data? I.e. you will have to submit a new form each month?
Click the new record button (>*) at the bottom on the navigation buttons to enter new blank record each month. Or add a form control command button onto your form to do the same. (In Form Design view the one marked xxxx. I've added an 'add new record' button on my example).
4) I had set some relationships, 1 is to many (in my example) but had to undo as I had not set up my data correctly. If I set relationships, will this allow me to avoid using the primary key?
Unfortunately there has to be a primary key in the Branches table for the form to update.
I thought I was getting somewhere, but have to go back to the beginning to move forward again!
Good to ask questions like you are doing!
Doug
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
