Autoincrement PK on Main Form when new record is added in subform

[Fish218]

Here's my problem. I have a two tables that I created a Main/Subform from. The first tbl_CommNet is data regarding a commercial net set:



MAIN FORM:
CommCollectionID (PK)
CommFishID <-lookup field in table
GridFished <-lookup field in table
SetTyp <-lookup field in table
Material <-lookup field in table
Mesh

The other table tbl_CommCatch is what was caught in the net:

SUBFORM:
CommCatchID (PK)
CommCollectionID (FK)
LiftDate
FeetFromSurface
LengthNet
PoundsCaught

I put the main form fields in the header so I only have to enter them once. Each record in the subform is a day's catch which comes from a monthly report. I want to be able to enter all the data in the subform without having to manually update the main form. In essence I want the main form PK to autoincrement when I add a new record to the subform. Is this possible?????

[RuralGuy]

To start with, you aren't using "Lookup Fields" in your tables are you? http://access.mvps.org/access/lookupfields.htm

[Fish218]

That helps explain some of the issues I've been having. This is my first Access project and I'm not a programmer, which is making the whole process even more difficult.

I'll change the fields back to text boxes in my tables. Any suggestions on autoincrementing PK in the main form after updating record in subform?

[RuralGuy]

MainForm/SubForm combinations are usually for One to Many relationships. I don't really understand why you want to advance the RecordCount of the MainForm just because the many side has added another record.

[Fish218]

Initially both of the tables i'm working with were in one table. I wanted to put the fields that didn't change with every record entry into the header. I wanted the other fields to be a sub datasheet. I couldn't figure out how to get the fields in the header to copy to the subdatasheet everytime i added a new record in the sub datasheet. Someone suggested breaking the tables apart and creating a form/subform that way. So here I am...

[RuralGuy]

My problem is in trying to understand why you need a new record *every* time something is caught in the net unless there are fields in the 1st table that really belong in the second table. 1st table describes the net and the 2nd table describes what and where and when something was caught. Maybe I just don't understand what you are working with.

[Fish218]

On the commercial fishing monthly report these fields don't change for the entire month:

Name
Grid
Month
Year
Net Type
Mesh Size
Net Material
Value ($/Lb)

The following field MAY change on a daily basis:

Day of Month
Feet from Surface
Total Feet of Net
Nights Set
Pounds Caught
Other species caught

On my frmCommCatch, I want to have those field that don't change for a monthly report to automatically populate the sub-datasheet which I want to consist of the fields that may change daily. That way when someone is entering data from a report submitted by a commercial fisher, the user does not have to leave the subdatasheet each time a new record is added for each day of the month. See attached Report Form.

[RuralGuy]

That is a conventional one-to-many relationship. Why are you wanting the "one" side of the relationship to increment when you add a record to the "many" side?

[Fish218]

Good question. I was stuck into thinking that one record had to be one "net lift". I could make a record be "one's day catch"? Do I run into problems splitting a date into three fields (month, day, year)? I want data entry to be fast and efficient and having to click a calendar date takes time.

That gets back to one of my original dillemas. How do I get fields in a table to autopopulate from values in a list box without having to leave the sub-datasheet?

[Fish218]

...and how do I get my PK in the header to autoincrement from my subdatasheet...

[orange]

As RuralGuy has alluded, I think you may have a structure or concepts problem with the 1:M.
You may want to review Normalization and ER diagrams.

I posted a list of free videos on Normalization and E-R diagrams in
post #15 at
https://www.accessforums.net/forms/ii...orm-21571.html

The presenter goes through an example with Orders and LineItems that may help you.

[Fish218]

Thanks for the information. The videos were helpful in determining normalization. I went through my db and checked for normalization, but again, this is my first db design. Could you take a look at my relationship table and tell me if it's normalized. Any table with "Code" after it is a lookup table.