Missing a foreign key on a form on "double click"

[atol]

Hi,
Here is my dilemma and I have been scratching my head for quite some time.

I have two forms (related to two tables in one-to-many relationship). The forms are linked as form and subform and they work just fine.
However considering the fact that the subform is basically a data-entry form, and to avoid any data entry issues, I want that form to exist as a separate form and to be able to open it in a “double-click” event from the first (parent) form. The issue I face here is that on “double click” event, the foreign key does not generate any number, and shows only “0”, thus not letting me save any data in that entry form.

Is there some solution on that? I am using Access 2000.


Any help is greatly appreciated!

Rgds,
Atol

 

[RuralGuy]

You may wish to change your approach. You do not actually get a new ForeignKey for the LinkChildField until the record is dirty.

 

[atol]

RuralGuy,
Thanks for your reply. Can you please elaborate a bit? Not sure what exactly I need to do. Thanks again!

Atol

 

[RuralGuy]

You will need to pass the ForeignKey to the next form. One way is in the OpenArgs argument of the OpenForm command.

 

[atol]

RG,
I think I found one of your old posts on this topic.
http://www.access-programmers.co.uk/forums/showthread.php?t=141797&highlight=record+dirty

Is that OpenArgs what you are referring to?
Thanks.

 

[RuralGuy]

You've got it and that is the thread is the same situation you have.

 

[atol]

Nice! Thanks again RG! A Big Help !

 

[atol]

RG,
I guess I am not doing something right since I can't get this working...here are my steps and let me know which step I am messing up:
1. On my main form I have the following code on DblClick:
Private Sub Form_DblClick(Cancel As Integer)
DoCmd.OpenForm "sfrmPaymentEntryTEST", acNormal, , "[pkPositionID] = " &
Me.pkPositionID

Dim InComingID As Long
If Not IsNull(Me.OpenArgs) Then
InComingID = Me.OpenArgs
End If
End Sub

On the subform, I have this code:
Private Sub Form_BeforeInsert(Cancel As Integer)
[fkPositionID] = InComingID
End Sub


My main forms Primary key is pkPositionID, and on the subform I have fkPositionID

Please let me know what I am doing wrong here…..?

Best,
Atol

 

[RuralGuy]

Try the following:

Private Sub Form_DblClick(Cancel As Integer)

   DoCmd.OpenForm "sfrmPaymentEntryTEST", , , , acFormAdd, , Me.pkPositionID

End Sub

'-- On the subform you should have:
Private Sub Form_BeforeInsert(Cancel As Integer)

   If Not IsNull(Me.OpenArgs) Then
      [fkPositionID] = Me.OpenArgs
   End If

End Sub

BTW, it eliminates a lot of confusion if you will name the FK field the same as the PK field in the other table. It is then easier to tell it is a FK and should be the same value as the PK in the other table.

 

[atol]

RG,
You made my day !!!!! Can't say "Thank you" enough!!! Works like a dream!

Best,
Atol

 

[RuralGuy]

Glad you got it sorted and thanks for posting back with your success.