Add new record in one to many recordset

[bigjohn99]

I have 2 tables set up in a one to many relationship. Table 1 contains only one AutoNum field as UserID. Table 2 has 2 fields with UserID as Number and Datestamp as Date. (These are just simplified examples and the relationship between tables are set to cascade update and delete).

I created a main form with subform. The main form contains just the UserID and the subform contains the UserID and Datestamp.

All the browsing and scrolling between records are working fine, but when I tried to add a new user, the main form's UserID field displayed the literal 'AUTO NUM' and the cursor jumped to subform's Datestamp field. I entered a date and then Save the update. The problem is Table 1's UserID field has not been incremented yet therefore Table 2's UserId is set to zero.

What is the proper way to insert a new record in this scenerio.

Thanks.

 

[willir]

If you have linked the subform properly, then Access is "smart" enough to grab the current autonumber.

Open the form in design mode. Make sure the properties window is open ("View" -> "Properties")

Select a field on the main form, and then select a field on the subform (following this two step approach ensures you look at the properties for the subform link and not the subform itself.)

Click on the "Data" tab of the properties window, and ensure the Master and Child fields are set.

The autonumber is aviable at the form level before writing to the table. So Access can use this when it creates the foreign key in the subform record.

Richard

 

[bigjohn99]

Hi Richard,

Thanks for the response! The linkage between these tables are set properly. That is why the viewing and scrolling through records are working okayed. The problem I am having is the Main Form has only one field which is USERID with AutoNum. When I am trying to add a new UserID record on the Main Form but since it doesn't have an updatable field to trigger a new UserID to be generated before the cursor jumps to the subform's Datestamp field, thus the UserID foreign key is set to zero.

A solution would be for me to create a dummy field in Table 1 and add it onto Main Form. When in ADD RECORD mode and a value is entered into this field, then the UserID will be incremented. But I think there might be a better solution than the one I came up with.

 

[PHV]

What does mean a table with only one autonumber column ?

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

 

[willir]

John

I too was a little puzzled about the design you depicted in your post. From what I gather...

Table1
UserID - primary key, autonumber

Table2
UserID - foreign key to Table1
DateStamp

Okay, here goes a little critic...
- What value does Table1 server - it just generates a unique primary key?
- Where is the primary key for Table2? Although not mandatory, it is generally a good idea to have a primary key.
- Is this an audit trail of some sort? Tracking when a user does something?

You got me stumped on the purpose of your project. (And that does not happen often)

Normally, you would have more than an autonumber single field table. For example, if you have UserID, then consider adding something like the User name to the table.

...Moving on
I understand your issue. Since you have no other fields on the form with the autonumber, there is nothing to start the OnInsert event.

Well, I was unsuccessful.


I added a command button and then tried...
- SQL INSERT command - nothing happened.
- DAO code...

Dim dbs As DAO.Database, rst As DAO.Recordset

Set dbs = CurrentDb()

Set rst = dbs.OpenRecordset("JohnsTestTable")

rst.AddNew
rst.Update

I think you answered your on question...

A solution would be for me to create a dummy field in Table 1

...But I would not call it an "dummy" field. Your Table1, from my perspective, is not really "normalized".

...Alternate approach
Don't use an autonumber. If you really must use a single numeric field, make it just a numeric field and then use code to calcualte the number...

MyNewNumber = (Max("[UserID]", "YourTable")) + 1

Richard

 

[bigjohn99]

Hi Richard,

This is just a simple database to track workload. Every hour on the hour the system generates an Unique number and all the workloads within the hour will be grouped by this unique number. As you see, the Unique number does not require any additional descriptions other than just for linking the working loads together.

I had use the manual generated number method before, but I thought the AutoNum method would be more robust.

 

[PHV]

Why not adding a column with the timeslot corresponding to the autonumber ?

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

 

[bigjohn99]

Thanks all for your suggestions. Great forum!!!