Table/ Form/ Sub Form one to many relationship 2

[avgeorge]

Hi all,
Am trying to create a simple gui/form in access to input data into my table. the tables field are as follows:
serial # text 7 char
machine # number 1 char
non changing data 1 text 255 char
non changing data 2 text 255 char
changing data#1 number 2 char
changing data#2 text 255 char
changing data#3 text 255 char
changing data#4 date date
changing data#5 text 1 char

I've tried making a justified form off of the table. I made the main form with all non changing fields and the serial# and machine#. Then I made a subform which contained the serial # and the changing data fields. Goal is for user to insert new serial #'s in the main form and fill out the subform (all non- & changing data) for that particular serial#. Then, when needed, if that serial # were to come again, the subform should be able to insert more rows for the changing data fields per serial #, but also retain previously entered information for future retrieval. What im running into is that data gets overwritten in my table, and the subform is not allowing for more than one entry in the changing data fields. Another problem is that I only have one table, rather than two, which contains all my data. Ive messed around with primary keys, double primary keys, but all roads lead to a dead end for me. It seems like a simple task conceptually, which is why this bugs me. Not too familiar with Access, but have dabbled with it here and there. Can anyone help?

 

[PHV]

Take a look here:

http://www.abcdataworks.com/fundamentals.htm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

How are ya avgeorge . . . . .

First, [blue]keep all names short[/blue] (easily recognized abbreviations are perfect):
[tt] Instead of [blue]TableAccountsReceivables[/blue]
Use [blue]tblAcctRcv[/blue][/tt]

Do not use [blue]#[/blue]:
[tt] Instead of [blue]Serial#[/blue]
Use [blue]SerialNo[/blue][/tt]

Do not use [blue]spaces[/blue]. If you must have spaces, use an [blue]underscore[/blue]:
[tt] Instead of [blue]tbl Acct Rcv[/blue]
Use [blue]tblAcctRcv[/blue]
Or [blue]tbl_Acct_Rcv[/blue][/tt]

Now . . . you need two tables:

[tt][blue]MainTableName
*************
FieldName DataType FieldSize Indexed
---------- --------- --------- -------------------
SerialNo Text 7 Yes (No Duplicates) as PrimaryKey
MachineNo Number Integer No
NonChange1 Text 255 No
NonChange1 Text 255 No


SubTableName
************
Format/
FieldName DataType FieldSize Indexed
-------------- ---------- ------------ -------------------
PrimarykeyName AutoNumber Long Integer Yes (No Duplicates) as PrimaryKey
SerialNo Text 7 No
Change1 Number Integer No
Change2 Text 255 No
Change3 Text 255 No
Change4 Date Your Choice No
Change5 ? ? No[/blue][/tt]

Next, in the [blue]Realtionships Window[/blue], make a one to many relationship (left to right) from [blue]MainTable/SerialNo[/blue] to [blue]SubTable.SerialNo[/blue]. Set [purple]Referential Integrity[/purple].

Make your form with subform & check it out.

At your leisure, have a look at the following links. Should clear up some things . . . .

Normalizing Tables

Table Relationships

Fundamentals of Relational Database Design

See Ya! . . . . . .

 

[PHV]

How are you [blue]TheAceMan1[/blue] ...
I would suggest creating an index for the Foreign Key[tt]
SerialNo Text 7 Yes, Duplicates[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

Good to see ya [blue]PHV![/blue]

I'm fine . . . thank you.

Good Heads-up on the [purple]Index[/purple]. Actually, between you and me, an idexed foreign key is not suggestive. [blue]Its standard practice . . . .[/blue] That one did slip by me . . . .

You take care . . . . Ya Hear! ;-)

See Ya! . . . . . .

 

[avgeorge]

Yes, worked great. THanks PHV and TheAceMan1. Ended up having to break information into two tables to get it to work. I think I was just adamant in trying to get it to work with one table that I couldnt think outside of the box.
One 'enhancement' I just ran into was in the subform portion of a form that I created using both of these tables, is there a way that when a user inputs data for the subform, that the user HAS to enter in ALL the fields and can not leave any blank before adding a new entry into the subform?

 

[TheAceMan1]

[blue]is there a way that when a user inputs data for the subform, that the [purple]user HAS to enter in ALL the fields[/purple] and can not leave any blank before adding a new entry into the subform?[/blue]

Yes . . . see the [blue]BeforeUpdate[/blue] event of the form. You can validate all the fields and use the Cancel arguement to inhibit saving and maintain current focus.

[blue]I think I was just adamant in trying to get it to work with one table that I couldnt think outside of the box.[/blue]

Do take the time to checkout some of the references provided. The one provided by [blue]PHV[/blue] is an all around [blue]excellent synopsis[/blue]. The ones I provided are just more detailed and may get a bit confusing.

[purple]Get to know what to do[/purple] instead of wasting time guessing & brainstorming. Next time you attack the database, you'll find you have [blue]time for that Bud![/blue], instead of pulling your hair out . . . .

See Ya! . . . . . .