Need help with forms and data.

[badz2801]

Hello

I am trying to setup a simple database with forms. Currently I believe my database is correctly designed however I am having trouble getting data displayed correctly on the form.

My database has the following structure:

Parent table: Job
Column 1: JobID - primary key, autonumber.
Column 2: JobNumDes - text box

Child table: JobNumber
Column 1: JobNumberID, primary key, autonumber.
Column 2: JobID - number.
Column 3: ItemNumber - number
Column 4: Submitted - text list box, self defined values
Column 4: ToShop - text list box, self defined values
...etc

Relationships:
Parent table -> Child table
JobID --------------> JobID - 1 to Many

I have a main form, and a subform. The main form has a list box of job names. I would like to use the List box for JobNumDes to display records in the subform.

Currently, when I use this config the data appears however when it change to a different job number "JobNumDes" it does not update the values in the subform. Any idea on how to fix this? I am not sure if I need to use a querie, or link them in some other way. If possible are there any links available on a tutorial for this?

So basically, I want to be able to select from a dropdown list box by the job number, which then has the data (item number, submitted, to shop, etc) displayed in the subform.

Thanks

Ian

 

[lightray]

Hi, a combo box can use a query design as the row source type. create two columns the JobID and the JobDes. Format it so the Column count is 2 and set the first column width to 0cm. this should just show the JobDes but will link via the JobID.
Hope that is of help.

 

[CEH]

I would take a second look at your DB design. Look up some info on normalization. It appears you have repeated and/or unnecessary fields in table two. Something like.... Table one.. JobID (PK) Job Description,(maybe more... "Address" "City" so on) and Second table "ItemID" (PK) JobID (FK), "ItemDesription", "submitted".... so on... Relate one to many on JobID
Design a query using both tables, base you form on that query. If you use the wizard I believe it will ask you if you want form with subform.

 

[badz2801]

Lightray - your idea worked like a charm. Thank you for your help, im new to access so I had no idea you could do that.

CEH - I read a few articles on normalization, im not sure that I have violated the rules. I checked my database against several microsoft designed access templates and the structure seems similar.

This is just a small database to track missing projects, so its not a super critical component. Do you have any good links on normalization? Thanks for your help again.

Ian

 

[lightray]

Your Welcome, glad to be of help.