[Micromax]

The attached database has the tables and form and subform
The purpose, the answer I am trying to find is 1. why is the autonumber in the main form not incrementing? 2. I require a unique identifier, in this case GLID control to show the GLID for each journal entry?

The norm would be to group these entries into a General Ledger with AccountID's for each.
I will have to cross that bridge when I get to it. For now, I need an entry that consistes of more than 1 debit or credit combined to have 1 Identifier(GLID) for each entry. Not record.
Thanks

[ScottGem]

OK, I know what the problem is and I know of one way to get around it, but I'm not sure WHY this is happening.

The problem is that you are not entering any new data in the GLTransaction record. Because the other two controls are being populated with default values, Access is not detecting that data has been inserted in the record. So its kind of in limbo.

What I did was two fold. First I removed the Default value from the Sequence control. Next I added a Save button with the code:
[Code]
Private Sub cmdSave_Click()
On Error Go to Err_cmdSave_Click


Me.Sequence = Nz(DMax("Sequence", "tblGLTransaction")) + 1
DoCmd.RunCommand acCmdSaveRecord

Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub
[\Code]

So this Save button, works to commit the record which assigns the autonumber. From there, any records entered in the subform have the current GLTransactionID as the FK.

Another way around this would be if there was any additional data that needs to be in the GLTransaction record. If there is you can add the fields and bind controls to them. As soon as you enter data into those controls directly (not using code or default values) the Before Insert condition is triggered and the Autonumber assigned.

By the way GLID and DateTimeStamp should NOT be in the Transactions table, that's just redundant data.

[Micromax]

This worked Scott. I'm not sure if the user will understand the Save Button to be pressed before entering the detail. I knew that was a problem last night when I change the Sequence number in the table and the autonumber went to 2.

[Micromax]

I deleted the fields as mentioned. Now the only thing is I'm shaking my head because the GLTransaction is unique to the entire entry. So much for a GLID composed of a date and sequence number. I have to think if I really nedd the sequence number?

[Micromax]

Thanks Scott. It does work, and I figured out late last night it wouldn't update unless I had another field on the main form.

The save button could be somewhat confusing to a user, but does work. Now that the GLTransaction is unique to each entry on the subform I just shake my head about needing the sequence and datetimestamp field concotenated.

I appreciate your time and effort Scott.
Thanks a lot, You're a Good Man!

Robert

[Micromax]

Thanks Scott. Read my answer to the question. I posted there because of limited character space here,

[ScottGem]

Yeah, they changed the site recently but there are different skins that people can use. The new skin can be confusing about how to answer. The Comments are not made for followups, using the Answer options is what that's for. Comments is just for things like a thank you.

If your SOLE purpose is to group like transactions together, then you might not need the sequencing. But if you want to have more of a identifying ID for the grouped transactions then that works.

[Micromax]

Yes, I thought about that. It would have been nice to have that identifier with the date and sequence, The Save button can be risky. Each time user clicks on it, up goes the sequence. User must be very careful. Not sure to use it. Thanks Scott

[Micromax]

Scott's answer is correct. That is the work around if the record will not move due to no data entry on the main form. So Access doesn't know a new record wishes to be created. The one thing that may not be appealing is the nature of the command button if it is pressed more than once. The sequence number will increment each time the command button is clicked. This could leave a bit of a messy situation for missing sequence numbers that don't have an entry.

I would recomment to keep the foreign key as an identifier, not too fanc though, and include another field on the main form so the record will be activated. Sorry, but rmove the command button, unless a msgbox can appear to alert user that the record has incremented before data has been entered on the subform.

Just an opinion of course,

Robert

[ScottGem]

You can protect against duplication by checking to see if Sequence = 0 or null. If it does, then you increment, if not you post a message that a Sequence # has already been generated.

[Micromax]

Again, you are right. I just posted on MS. Feel free to read. Dare-Devil gave me some sound advice. Maybe not what I wanted to hear, but I don't know. Fell a bit hurt.

[ScottGem]

Yeah, I saw what he posted. While he's right to some extent, I think he went over the top.

[Micromax]

Scott, I just came back from a showing, I had a death in the familly. Maybe 'I'm a little touchy. Refresher course, not a bad idea. Hey, he may be right. Take care, Rob appreciated your help.