Synchronizing main frm and subfrms 1

[montrose]

I have searched all my books, Help, and these newsgroups for an example of how to do this, and I'm now more confused than ever. I just know there's someone out there that knows alot more than me. I have the following for a group that does business with companies who tend to change their names alot:

tblCompany CompanyID (PK, Autonum), OriginalDateEntered (abbreviated in the field!)

trelCompAcct CompID (LongInt), AcctNumID (LongInt)

tblCompAcctNum AcctNumID (PK, Autonum), AcctNum (text, alphanumeric & assigned by Comp)

trelCompName CompID (LongInt), NameID (LongInt)

tblCompName NameID (PK, Autonum), CompName (text)

trelCompAddr CompID (LongInt), AddrID (LongInt)

tblCompAddr AddrID (PK, Autonum), AddrLineOne, etc.

The Main frm is from tblComp. Subforms (CompName, CompAcctNum, CompAddr) are based on queries joining the trel to the related tbls. Master/Child links are to CompID. The main frm/fsub work great if you start by filling in the original date that this company was entered. But if you try to enter data into one of the subforms first, you can't because the CompID has obviously not been generated yet. I am lousy at vba, but I know there must be some event that will cause that main frm to generate its id on enter into a fsub. Can anyone point me to an example or coach me in the best way to do this? Greatly appreciate all suggestions.

 

[930driver]

I was able to duplicate your problem in a test db. I solved it as follows: If you make a change to the record in your main form and then save it that generates the ID. In order to make this seemless I created a sub which I call from the Enter event of each subform (see sample below). You'll have to change the names of the controls in the code to correspond to your stuff. I'll leave an error handler up to you as well.

Usage (put the following into the Enter event of each subform control on the main form):
CreateMainRecord Me.ActiveControl 'pass reference to current subform

Private Sub CreateMainRecord(sfrmControl As Control)
If IsNull(Me.txtCompanyID) Then 'see if there is an ID
Me.txtDate = Now 'make a change to the record
Me.txtDate.SetFocus 'move focus back to main form
DoCmd.Save 'save the record
sfrmControl.SetFocus 'go back to the subform
End If
End Sub

 

[montrose]

Thanks for getting me started! Can you get me a little further? I've tried your suggestion but am still not overcoming my vba dyslexia.

The main fmr (frmCompMain) has fields;
CompID
OrigDateCompEnt

The subform (fsubCompName) has fields;
CompID (linking)
CompNameID
CompNameType
CompName

This is what I put:

Private Sub fsubCompName_Enter()
Private Sub CreateMainRecord(fsubCompName As Control)
If IsNull(Me.txtCompID) Then 'see if there is an ID
Me.txtOrigDateCoEnt = Now 'make a change to the record
Me.txtOrigDateCoEnt.SetFocus 'move focus back to main form
DoCmd.Save 'save the record
fsubCompName.SetFocus 'go back to the subform
End If
End Sub

Whe I tried it, this is the message I got. “Compile error
Expected end sub"

I have great admiration for those with the magical ability of being able to write code. It's probably very obvious what I'm missing here. Thanks.

 

[930driver]

I should have been clearer. I shouldn't assume things.

Open the form code module and paste the following lines into the bottom of the module at the very end, except that you MUST change txtCompanyID and txtDate to the names of the actual corresponding controls on your form.

Private Sub CreateMainRecord(sfrmControl As Control)
If IsNull(Me.txtCompanyID) Then 'see if there is an ID
Me.txtDate = Now 'make a change to the record
Me.txtDate.SetFocus 'move focus back to main form
DoCmd.Save 'save the record
sfrmControl.SetFocus 'go back to the subform
End If
End Sub

You want to have this only once!

Now in the Enter event of each sub form call the sub as follows:

CreateMainRecord Me.ActiveControl 'pass reference to current subform

So your Enter event in it's entirety should look like the following:

Private Sub fsubCompName_Enter()
CreateMainRecord Me.ActiveControl 'pass reference to current subform
End Sub

 

[montrose]

Eureka!!! Thank you , thank you.........

I had to keep fiddlin' (being vba challenged) , before I realized you're using sfrm, I'm using fsub (found that a little easier to find in the maze), and obviously being the experienced person that you are, you had TAGS on the field names (I keep telling myself I'll go back and do that, but so time consuming-should learn to do it in the first place!). Kept getting error messages and looking at the debug 'til I was getting crosseyed.

This is what I finally got to work:

Private Sub CreateMainRecord(fsub As Control)
If IsNull(Me.CompID) Then 'see if there is an ID
Me.OrigDateCompEnt = Now 'make a change to the record
Me.OrigDateCompEnt.SetFocus 'move focus back to main form
DoCmd.Save 'save the record
fsub.SetFocus 'go back to the subform
End If
End Sub

Private Sub sfrmCompName_Enter()
CreateMainRecord Me.ActiveControl 'pass reference to current subform
End Sub

I may re-post as I struggle with the error handling! You've re-instilled my faith that there's hope for me. By the way, sometimes 'assuming' often leads to our greatest learning opportunities! Big stars for you 930driver!!!! Hope someone else can learn from this posting.