passing from one form to the next

[AccessIdiot]

I feel really dumb asking this but I'm not sure why it isn't working.

I have a form that has a text field. You type a number into the text field, it gets concatenated with a string, then gets put into the table as a string in the field "Survey_Num ".

Press a button on the form and another form pops up. There is a "Survey_Num " field in the table behind this form as well and they are tied in a one-to-many relationship.

I want the value on the first form to show up in the field on the 2nd form.

I am guessing that it is not showing up because technically the value hasn't entered the table from the first form.

How do I force Access to enter the data when I press the button to go to the second form? I've tried using a close command to close the first form when the 2nd form is opening, thinking that will enter the info into the table but no dice.

Seems simple but I am stumped - any ideas?

Any code I can provide?

Form 1 = frm_Survey with unbound field txt_SurveyNum where user enters an integer and invisible field txt_SurveyNumID that is bound to Survey_Num of Survey table.

txt_SurveyNum has LostFocus event:

Private Sub txt_SurveyNum_LostFocus()
    Me.txt_SurveyNumID = Me.OpenArgs & Me.txt_SurveyNum
End Sub

The OpenArgs is passed by another form that opens frm_Survey.

Form 2 = frm_Replicate with Survey_Num field and control source is field of same name in table. Because it is in a relationship with the field of same name in the other table I didn't think I'd have to put any special query or code on it?

thanks in advance for any help!
melissa

[AccessIdiot]

*with sheepish grin* what's the code for the Open form event?
I have this

Private Sub Form_Open(Cancel As Integer)
    Me.Survey_Num = Me.OpenArgs
End Sub

but I get an error

also can you explain the difference between the form load event and the form open event?

[Rabbit]

You'll have to put it in the On Load event.

On Open occurs when the form opens but before the first record is displayed.
On Load occurs when the form opens and after the first record is displayed.

So what's happening is that you're trying to assign the value to something that doesn't exist yet.

[AccessIdiot]

Thank you for the explanation. Now I have this on frm_Survey:

stDocName = "frm_Replicate"
    DoCmd.OpenForm stDocName, , , , , , Me.Survey_Num
    DoCmd.GoToRecord , , acNewRec

and this on frm_Replicate:

Private Sub Form_Load()
    Me.Survey_Num = Me.OpenArgs
End Sub
 
Private Sub btnNewReplicate_Click()
On Error GoTo Err_btnNewReplicate_Click
 
    DoCmd.GoToRecord , , acNewRec
    Me.Survey_Num = Me.OpenArgs
 
Exit_btnNewReplicate_Click:
    Exit Sub
 
Err_btnNewReplicate_Click:
    MsgBox Err.Description
    Resume Exit_btnNewReplicate_Click
 
End Sub

but I am getting an error message "Can't go to specified record"

[AccessIdiot]

Ugh I have to head home but I'll be back tomorrow!

Thanks for your help Rabbit, as always. :-)

[AccessIdiot]

I got it to work - thank you so much! This is what finally did it:

on frm_Survey

Private Sub txt_SurveyNum_LostFocus()
    Me.txt_SurveyNumID = Me.OpenArgs & Me.txt_SurveyNum
End Sub
------------------------------------------------------------
Private Sub btn_AddReplicate_Click()
On Error GoTo Err_btn_AddReplicate_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "frm_Replicate"
    DoCmd.OpenForm stDocName, , , , , , Me.Survey_Num
    'DoCmd.GoToRecord , , acNewRec
 
    DoCmd.Close acForm, "frm_Survey"
 
Exit_btn_AddReplicate_Click:
    Exit Sub
 
Err_btn_AddReplicate_Click:
    MsgBox Err.Description
    Resume Exit_btn_AddReplicate_Click
 
End Sub

and on frm_Replicate:

Private Sub Form_Current()
    Me.Survey_Num = Me.OpenArgs
End Sub
-------------------------------------------------
Private Sub btnNewReplicate_Click()
On Error GoTo Err_btnNewReplicate_Click
 
    DoCmd.GoToRecord , , acNewRec
    Me.Survey_Num = Me.OpenArgs
 
Exit_btnNewReplicate_Click:
    Exit Sub
 
Err_btnNewReplicate_Click:
    MsgBox Err.Description
    Resume Exit_btnNewReplicate_Click
 
End Sub

I have continued questions about how to work backwards - to go from frm_Replicate back to frm_Survey and still have the 'TR' in the form but that's a question for a separate post when I get to it.

Thanks so much for all your help!
melissa :-)

[Rabbit]

Glad you got it working. Sorry I didn't respond earlier, I just got into the office.

[AccessIdiot]

No worries. You know how satisfying it is when you finally figure out something yourself!