Pass ID from one table to the next for data entry?

[briananderson]

Pass ID from one form to the next for data entry?

I feel like this is a simple answer, but I'm rather a newbie at all this and I am having a difficult time trying to search for the answer.

I want to link multiple forms together using the same record.

I have a series of questions that have multiple answers, based on a flowchart, almost like a "Choose Your Own Adventure". Each question has a yes or no answer (buttons for both).

If the user selects yes or no buttons, data is automatically inserted into a table (Answers), then the current form closes and opens up a new form with a new set of questions depending on which button the user clicks.

When I move onto the next form, it doesn't carry over the same primary key id as I had in the previous form, and it creates a new record. I want these questions to be associated with one pk. I've been trying all sorts of things and so far, I havent got it right.

I've tried to use page breaks (and there's too many questions to fit in the 22" max form). I've tried using tabs, and there would be just too many tabs.

I know I have to pass the primary key from one form to the next, but I'm just not sure how to handle this.

Any help would be wonderful!

Thanks!

 

[RuralGuy]

Have you used the OpenArgs argument of the OpenForm command before?

 

[briananderson]

No, not yet. But am looking forward to it! :-D

 

[missinglinq]

Here's a simple example of FormA calling FormB and going to a matching record, if it exists. If a matching record doesn't exist, it'll simply open on the first record. RecordID can be any field that is unique to a given record, of course. This code works whether RecordID is Text or Numeric.

In your calling form:

Private Sub OpenFormBNumeric_Click()
   DoCmd.OpenForm "FormB", , , , , , Me.RecordID
End Sub

In FormB, where RecordID Field holds the value you're passing:

Private Sub Form_Load()
  If Len(Nz(Me.OpenArgs, "")) > 0 Then
    RecordID.SetFocus
    DoCmd.FindRecord Me.OpenArgs
  End If
End Sub

You post sounds as if a record already exists in your second form for the PK. If that's correct, the above should work. If you need to open a new record in the second form, with the same PK, that'll only take a minor modification.

Linq

 

[briananderson]

Thanks for the help! I'll try this out shortly. Actually, I am trying to open it in a new record in the second form. What is the minor modification?

 

[RuralGuy]

I'm more familiar with the DAO approach to the syncronization. Plus I do not know how DoCmd.FindRecord reports a no find. If the RecordID is numeric then something like the following should work:

Private Sub Form_Load()
   If Not IsNull(Me.OpenArgs) Then
      ' Find the record that matches the passed argument.
      Me.RecordsetClone.FindFirst "[RecordID] = " & Me.OpenArgs
      If Not Me.RecordsetClone.NoMatch Then
         Me.Bookmark = Me.RecordsetClone.Bookmark
      Else
         DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
         Me.RecordID = Me.OpenArgs
      End If
   End If
End Sub

 

[briananderson]

Thank you to all who helped me on this! I took a little bit of both to make it work.

The next problem I'm having is when I start a new record in FORM A, which creates a new RecordID, but I need it to save all the information inputed in the form, close it, and then open FORM B with the newly created RecordID from FORM A.

Again, I'm a little green at this... thanks in advance!

 

[RuralGuy]

This is the same issue we have been working on for the rest of the thread. The OpenArgs argument will work for this issue too. Just a little confused by your question!

 

[missinglinq]

I'm confused by it too, Brian! When you move to FormB the new record in FormA should be saved automatically without you having to save it and close the form and re-open it.

 

[briananderson]

I'll have to take a deeper look into this, because its not working correctly for me. I'll see what I can figure out. I'll let you know.

Thanks for the help!

 

[briananderson]

I just added Me.Refresh to the button code, and it works great now. Thanks!