Subform tied to dropdown box?

[BillC]

Hi, I've never created anything using Access before but am not afraid to try new things. I think this can be done but not sure. I have a master and several other tables. I would like to create forms (acutally one form at a time) that has all data in the master table and depending on what choice is made from a dropbox, have a subform open. Each dropdown choice is to have its own associated subform. Can it be done? I'm almost complete with my tables and will be moving into making the forms shortly. Any pointers to reference sites?

[June7]

I am sure could be done but why? What is chosen in the combobox? What are the subforms for?

Will need to build code. Review http://office.microsoft.com/en-us/ac...010341717.aspx

Consider form/subform arrangement, possibly with a tab control and subforms can be on pages of tab control.

[BillC]

Thanks June7, Dropbox will be locations for inspection purposes. The subforms will be inspection items specific to that dropbox location. Instead of attempting to make 1 form fit all.

[June7]

The subforms would all be bound to the same source data just displaying different fields? A single subform with tab control seems like reasonable approach.

Starting to sound like data structure is not normalized. Want to provide db for analysis? Follow instructions at bottom of my post.

[BillC]

Yes/maybe same master data source, just picking different fields. Whichever route is clearer and will easier accomodate future changes. I'll have to look that up, single subform with tab control, that is. I am in the design phase now. I am uploading what I have. Please remember I am a newbie to designing a database.

There are a few purposes for this data base, The first is to collect all information from ongoing inspections. If possible I would like the Master table to contain the basic information only (maybe possible, maybe not). The core info are the first 14 columns of Master, I began adding fields after thinking it may be easier to have all data in one table. The subforms and other tables are to (1) ease the collection of data (2) allow for a printable form for inspection use tailored to individual area and (3) ease data entry. Can the subforms be tied to the master with the same auto generated key?

My thinking is to be able to have a form created that depends on the location picked in a dropbox in the Location field. Person would complete printed form and submit for data entry. The data will be used to 1) quantify any relationship between customer returns and inspections or lack thereof. 2) as an audit tool to quantify to management specifics that are or are not being met. Those forms and queries are yet to be developed.

My apologies for the tome. As you see my journey is only beginning, has been for quite a while and I am now convinced of the need for this. The company will not spend the money on software and does not see the value in anything computerized unless it is running machinery. Whatever guidance you can give me is appreciated. I'm off to investigate subform with tab control now.

Thanks,
Bill

The data to be collected

The subforms would all be bound to the same source data just displaying different fields? A single subform with tab control seems like reasonable approach.

Starting to sound like data structure is not normalized. Want to provide db for analysis? Follow instructions at bottom of my post.

[BillC]

OK, now I understand subform with tab control. So I will have the Master, with all the fields and the subform with tabs? Sounds like a giant excel spreadsheet? The more thought out it seems that the best way will be to have the master table containing common information and each other table containing information specific to that location. If we can link the tables by autogenerated key then maybe the tabs can call fields from the individual tables? When a location is chosen from the Location dropdown or choose field can that key number be assigned to one of the other tables? I think the answer is yes.
Let me work in that direction. I am stuck on this dropdown/choose box idea because it is clean in implementation.

[June7]

I looked at your db. Not really understanding data structure. What is being inspected? What are the data relationships? Are Master and Locations a one-to-one relationship? If so, could be just one table.

The Location field in Master is a text type. However, the primary key in Locations is an autonumber. This is a conflict in field types. The tables will not join in a query.

Also, the Locations table has text fields for Pem, Welding, DrillandTap, etc. However, again the primary keys from each of the related tables is an autonumber. Primary and foreign key fields must be same type.

Autonumber is long integer so the foreign key field must be number not text type.

Form/subform arrangement will synchronize auto saving primary key from main form to foreign key field of subform. However, your data structure does not fit this configuration.

Each of the 'subtables' is actually set to be a 'lookup' table. The primary keys of subtables are saved into fields of Locations table. Each of the fields in Locations table would be a combobox to select desired record and save pk from associated lookup table.

Here are good primers on database principles
http://forums.aspfree.com/microsoft-...es-208217.html
http://allenbrowne.com/casu-06.html

[BillC]

OK, so now it is painfully obvious how much I do not know about database programming. I will take your suggestion and create one large table, the relationships are one to one. What are being inspected are parts during production. Locations are where they are in the workflow. Thanks June7

[BillC]

I'm trying this again. Rewrote the database to one master. Noticed when I created relationships for yesno and accnacc tables to master that the auto lookup defined the data type in the master as number, when it is text in the table. The lookup data is number and the yesno table value is text. Will this be an issue before I go further? Begin again?

[June7]

Yes, the different datatypes will be an issue. As stated, PK/FK fields must be same kind of data.

For short and simple values like Yes/No/NA, I would not save the numeric ID, I would save the actual descriptive value in the text field. Having to join tables in queries to retrieve the descriptive value can become tiresome. Yes/No datatype field can be set for triple state but I haven't used. I use Yes/No fields for data entry sparingly, only when absolutely have to.

When options are limited and will NEVER change (Male/Female, Yes/No, etc), I wouldn't even bother with a lookup table. I would just build these options in combobox RowSource property - I have done this for even more than 3 values. This applies to the Yes/No/NA as well as Acceptable/NotAcceptable/NA.

The LocationList table makes sense. However, the question is: Save ID (meaning table join in query required) or description?

Unless you are really concerned about hitting the Access 2gb file size limit, conservation of storage by saving ID instead of longer description is not mandatory. The benefit is not having to join tables every time you want to view the descriptive values.

It is a balancing act between data normalization and ease of data entry/output. Splitting this table to multiple tables (as you previously had) can be justified if each record will not always have data for all the fields. Or the one big 'flat' file could possibly work for your needs even if it is not 'true' normalization. Conceivable that a 1 to many structure could be advantageous. I just don't know enough about your business and data flow.

Recommend no special characters in names. Better would be JobNum.

[BillC]

Thanks June7, Here is a copy of what I have come up with! Really appreciate your help. I have some other questions on this but will open a new thread if answers don't come across my path.

[June7]

I see you changed comboboxes to ValueList instead of Table/Query as RowSource. However, field CorrectHoleSize is still number type with query of YesNoList as RowSource. Is this intentional? Change this and those two lookup tables can go away.

TestForm looks good. Although I think you could compact it a bit more - smaller textboxes, tighter spacing - try to reduce dimensions to maybe eliminate scrolling.

[BillC]

Thanks, the CorrectHoleSize data type is now RowSource, I did not even think about eliminating the two lookup tables yet :-) thanks for the suggestion! I am going to give it a weeks worth of data to make sure all the fields are working as they should. Then it's off to learning a bit about reports :-) .

[June7]

You mean the data type is now text with ValueList as RowSource?