By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,652 Members | 1,842 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,652 IT Pros & Developers. It's quick & easy.

passing from one form to the next

AccessIdiot
100+
P: 493
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txt_SurveyNum_LostFocus()
  2.     Me.txt_SurveyNumID = Me.OpenArgs & Me.txt_SurveyNum
  3. 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
Mar 8 '07 #1
Share this Question
Share on Google+
16 Replies


Rabbit
Expert Mod 10K+
P: 12,365
Hmm, it might be because the field is invisible. I'm not sure as I haven't tested this but before you assign the value in the Lost Focus event, try making it visible, assign the value, and then make it invisible again.
Mar 8 '07 #2

AccessIdiot
100+
P: 493
I tried everything with the field visible but it doesn't show up on the 2nd form.
Mar 8 '07 #3

Rabbit
Expert Mod 10K+
P: 12,365
Are you trying to pass the concatenated string to yet another form?
Mar 8 '07 #4

AccessIdiot
100+
P: 493
Yes. Sort of. I would expect that since they are linked (Survey_Num is primary key of Survey table and foreign key in Replicate table) it would show up simply by being bound. Sorry, let me expand.

I have a switchboard with 3 buttons. Each button opens the same form but passes a different string via the OpenArgs option of the Open command. For example,
Expand|Select|Wrap|Line Numbers
  1.  stDocName = "frm_Survey"
  2.     DoCmd.OpenForm stDocName, , , , , , "TR"
.

On frm_Survey is the unbound textbox "txt_SurveyNum". The user enters a number there ("3"). Then on the hidden/invisible bound textbox ("txt_SurveyNumID") is the LostFocus event that concatenates the TR with 3 to make TR3. This is the value that is entered in the Survey_Num field of tbl_Survey.

frm_Survey has a button to open frm_Replicate. On the form is a bound textbox called "txt_SurveyNum". It is bound to the field Survey_Num on tbl_Replicate, which is acting as a foreign key to the Survey_Num field on tbl_Survey.

My hope is that when frm_Replicate is opened the value "TR3" shows up in the textbox that is bound to Survey_Num.

I hope this makes sense!
Mar 8 '07 #5

Rabbit
Expert Mod 10K+
P: 12,365
Yes. Sorry, let me expand.

I have a switchboard with 3 buttons. Each button opens the same form but passes a different string via the OpenArgs option of the Open command. For example,
Expand|Select|Wrap|Line Numbers
  1.  stDocName = "frm_Survey"
  2.     DoCmd.OpenForm stDocName, , , , , , "TR"
.

On frm_Survey is the unbound textbox "txt_SurveyNum". The user enters a number there ("3"). Then on the hidden/invisible bound textbox ("txt_SurveyNumID") is the LostFocus event that concatenates the TR with 3 to make TR3. This is the value that is entered in the Survey_Num field of tbl_Survey.

frm_Survey has a button to open frm_Replicate. On the form is a bound textbox called "txt_SurveyNum". It is bound to the field Survey_Num on tbl_Replicate, which is acting as a foreign key to the Survey_Num field on tbl_Survey.

My hope is that when frm_Replicate is opened the value "TR3" shows up in the textbox that is bound to Survey_Num.

I hope this makes sense!
When you open the form you're going to have to assign the concatenated variable.
Expand|Select|Wrap|Line Numbers
  1.  stDocName = "frm_Replicate"
  2.     DoCmd.OpenForm stDocName
  3.     Forms!frm_Replicate!Survey_Num = Me.Survey_Num
  4.     DoCmd.Close acForm, "frm_Survey"
Mar 8 '07 #6

AccessIdiot
100+
P: 493
When you open the form you're going to have to assign the concatenated variable.
Expand|Select|Wrap|Line Numbers
  1.  stDocName = "frm_Replicate"
  2.     DoCmd.OpenForm stDocName
  3.     Forms!frm_Replicate!Survey_Num = Me.Survey_Num
  4.     DoCmd.Close acForm, "frm_Survey"

Ahh! That works great! Can you explain to me what this line means?
Expand|Select|Wrap|Line Numbers
  1. Forms!frm_Replicate!Survey_Num = Me.Survey_Num
I assume it means that Survey_Num on frm_Replicate equals Surevy_Num on the current (frm_Survey) form?

Moving forward . . . on frm_Replicate I have a button to add a new record (I am going to disable the the standard Access built-in buttons on the form to really "dumb it down" for the user). How can I carry the value again to the next record? Basically I need to keep carrying "TR3" until the user hits the button to stop adding Replicates, closes it and goes back to frm_Survey to start a new Survey ("TR4").
Mar 9 '07 #7

Rabbit
Expert Mod 10K+
P: 12,365
Ahh! That works great! Can you explain to me what this line means?
Expand|Select|Wrap|Line Numbers
  1. Forms!frm_Replicate!Survey_Num = Me.Survey_Num
I assume it means that Survey_Num on frm_Replicate equals Surevy_Num on the current (frm_Survey) form?

Moving forward . . . on frm_Replicate I have a button to add a new record (I am going to disable the the standard Access built-in buttons on the form to really "dumb it down" for the user). How can I carry the value again to the next record? Basically I need to keep carrying "TR3" until the user hits the button to stop adding Replicates, closes it and goes back to frm_Survey to start a new Survey ("TR4").
Yes, that's exactly what it means.

In this case, rather than assigning the Survey_Num directly from the original form, pass it as in OpenArgs.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frm_Replicate",,,,,, Me.Survey_Num
Then in the on open of the form and when they click the new button, you can have it assign Me.OpenArgs to Me.Survey_Num.
Mar 9 '07 #8

AccessIdiot
100+
P: 493
Okay, so switch the code around a bit? Let me try that . . .
Mar 9 '07 #9

Rabbit
Expert Mod 10K+
P: 12,365
That's right, let us know how you get along.
Mar 9 '07 #10

AccessIdiot
100+
P: 493
*with sheepish grin* what's the code for the Open form event?
I have this
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.     Me.Survey_Num = Me.OpenArgs
  3. End Sub
but I get an error

also can you explain the difference between the form load event and the form open event?
Mar 9 '07 #11

Rabbit
Expert Mod 10K+
P: 12,365
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.
Mar 9 '07 #12

AccessIdiot
100+
P: 493
Thank you for the explanation. Now I have this on frm_Survey:
Expand|Select|Wrap|Line Numbers
  1. stDocName = "frm_Replicate"
  2.     DoCmd.OpenForm stDocName, , , , , , Me.Survey_Num
  3.     DoCmd.GoToRecord , , acNewRec
and this on frm_Replicate:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     Me.Survey_Num = Me.OpenArgs
  3. End Sub
  4.  
  5. Private Sub btnNewReplicate_Click()
  6. On Error GoTo Err_btnNewReplicate_Click
  7.  
  8.     DoCmd.GoToRecord , , acNewRec
  9.     Me.Survey_Num = Me.OpenArgs
  10.  
  11. Exit_btnNewReplicate_Click:
  12.     Exit Sub
  13.  
  14. Err_btnNewReplicate_Click:
  15.     MsgBox Err.Description
  16.     Resume Exit_btnNewReplicate_Click
  17.  
  18. End Sub
but I am getting an error message "Can't go to specified record"
Mar 9 '07 #13

AccessIdiot
100+
P: 493
Ugh I have to head home but I'll be back tomorrow!

Thanks for your help Rabbit, as always. :-)
Mar 9 '07 #14

AccessIdiot
100+
P: 493
I got it to work - thank you so much! This is what finally did it:

on frm_Survey
Expand|Select|Wrap|Line Numbers
  1. Private Sub txt_SurveyNum_LostFocus()
  2.     Me.txt_SurveyNumID = Me.OpenArgs & Me.txt_SurveyNum
  3. End Sub
  4. ------------------------------------------------------------
  5. Private Sub btn_AddReplicate_Click()
  6. On Error GoTo Err_btn_AddReplicate_Click
  7.  
  8.     Dim stDocName As String
  9.     Dim stLinkCriteria As String
  10.  
  11.     stDocName = "frm_Replicate"
  12.     DoCmd.OpenForm stDocName, , , , , , Me.Survey_Num
  13.     'DoCmd.GoToRecord , , acNewRec
  14.  
  15.     DoCmd.Close acForm, "frm_Survey"
  16.  
  17. Exit_btn_AddReplicate_Click:
  18.     Exit Sub
  19.  
  20. Err_btn_AddReplicate_Click:
  21.     MsgBox Err.Description
  22.     Resume Exit_btn_AddReplicate_Click
  23.  
  24. End Sub
and on frm_Replicate:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     Me.Survey_Num = Me.OpenArgs
  3. End Sub
  4. -------------------------------------------------
  5. Private Sub btnNewReplicate_Click()
  6. On Error GoTo Err_btnNewReplicate_Click
  7.  
  8.     DoCmd.GoToRecord , , acNewRec
  9.     Me.Survey_Num = Me.OpenArgs
  10.  
  11. Exit_btnNewReplicate_Click:
  12.     Exit Sub
  13.  
  14. Err_btnNewReplicate_Click:
  15.     MsgBox Err.Description
  16.     Resume Exit_btnNewReplicate_Click
  17.  
  18. 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 :-)
Mar 9 '07 #15

Rabbit
Expert Mod 10K+
P: 12,365
Glad you got it working. Sorry I didn't respond earlier, I just got into the office.
Mar 9 '07 #16

AccessIdiot
100+
P: 493
No worries. You know how satisfying it is when you finally figure out something yourself!
Mar 9 '07 #17

Post your reply

Sign in to post your reply or Sign up for a free account.