Is this possible in a form? (1 Viewer)

[KadeFoster]

What i would like to know is:

Is making certain Cells appear when selecting an option from a drop down box possible. I am pulling the cells all from one table including the drop down selection. Example.

Its to do with the jobs I do at work so it is in regards to that as options.

[Drop Down Selection: Forklift]

Cells to appear on that selection:
[Troubleshooting: NumberField] [Tramming: NumberField] [Status: LookupField, Dropdown] [Comments: Memo]

[Drop Down Selection: Pull Testing]
[Hours: NumberField] [Location: Text] [Level: Text] [Comments: Memo]

There are about 5 or so different job selections that i would like to be able to add and the relevant fields.

Also would like to have an Add Job button so i could add another entry, and a delete button to remove the job entry.

Is this possible in a form and would using one table for all this info be the right way to go or should i create separate tables for all of the job types and link them in a form some how? There can be multiples of each JobType per day.

I hope i have explained this good enough.

 

[Alansidman]

These are called cascading combo boxes. Look at this thread.

http://www.access-programmers.co.uk/forums/showthread.php?t=223980

 

[spikepl]

Anything is possible. But it all starts with data structure which is what you need to design first. How data is stored is not determined by how it is displayed (like in Excel). Design your data structure first, and do not think about forms until the data strucure is done.

Which tables do you envisage (corresponding to your business objects), and how are they related? Eg: tables students, classes, studentsinclasses, and relations: one student can take many classes, one class can be attended by many students.

 

[KadeFoster]

I have some tables just not sure if they are right is all. I am working backwards from forms as i find it easier to understand when i am trying to reverse engineer it.

I wish my input data was classes and student info as it probably would be easier for me to understand. I just had a thought that i could create more tables then create forms which will appear by the selection of a tick box or something. Maybe easier and a better way to achieve what i want.

 

[Pat Hartman]

There are several different ways to go with this structure. You can use a single Job table with columns that are used depending on Job Type or you can create a more normalized structure that has a Job table that holds only the common fields and several other tables related 1-1 with Job that contain the fields specific for that job type. With this split structure, all other tables in the application would still relate to the job table. No tables except for Job would ever be joined to the Job specific data tables.

In either case, I would use a main form to show the Job common data and a subform to show the Job specific data. In the form's Current event, you would display the correct subform for that specific Job. In a new record where you are adding a Job, the subform would be blank until you selected a job type. Then in the AfterUpdate event of JobType, you would display the correct subform.

You would have to prevent changing Job Type once the record was saved so you could lock Job Type in the Form's current event unless this is a NewRecord.

 

[KadeFoster]

Cool thanks, I understand what you mean, I have created many tables, alot of the columns in each of the tables is the same. How ever i think i have just made a mess with my tables. I want to create a form that looks like a form not a DB as my supervisor has not idea and i would just like him to enter what is required press submit and done.

Would i need a column with the job types? Is there a chat facility in this forum as its easier to chat then post lol

 

[Pat Hartman]

I prefer combos since they just grow as you add more items but sometimes option groups are easier for users. Have the Job types in an Option group. Clicking in the option group will cause the correct subform to display. I don't see a way around this if you want to hide and show columns based on a Job, you need some way to make the decision.

Your forms don't need to look like tables and in fact should be based on queries so they can show "information" rather than raw data.