Pass a variable from one form to another

[melanie]

Okay,
I have researched all the posts on this topic. I found many, many
posts, but none that explain it to me on the dummy level that I
require! So, please help me, I know you guys have heard it all
before. Here goes:

I have 2 forms; Scorecards and Businesses
When someone is entering new scorecard info in the Scorecards Form,
they must choose an "AccountName" from a drop-down list. If the
business they try to type is an AccountName that is not in the list, a
dialog pops up and tells them the Business is not in the list and do
they want to add it. If they choose "yes" the Businesses Form opens up
and they can add the Business there. What I need to happen is when the
Businesses Form opens, it should grab the text they typed in the
"AccountName" control in the Scorecards form and place it into the
"Business" control in the now-open Businesses Form. Now, I have no idea
how to this, I think it involves a detail in my DoCmd.OpenForm thingy,
but I am not sure. ANOTHER catch is that the solution can't involve any
permanent relationship between the Scorecards form and the Businesses
form, because I also use the Businesses form to add business info from
many other forms in my database. In other words, I can't put anything
in the On Load of the Business form related to the Scorecard form,
because the Business form is opened by the DoCmd.OpenFOrm of many, many
other forms besides just Scorecards. See? WHat can I do here? Here is
what the code looks like that I am using right now:

Private Sub AccountName_NotInList(NewData As String, Response As
Integer)
Dim Result
Dim Msg As String
Dim CR As String

CR = Chr$(13)

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Ask the user if he or she wishes to add the new business.
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
' If the user chose Yes, start the Businesses form in data entry
' mode as a dialog form, passing the new business name in
' NewData to the OpenForm method's OpenArgs argument. The
' OpenArgs argument is used in Businesses form's Form_Load event
' procedure.
DoCmd.OpenForm "Businesses", , , , acAdd, acDialog, NewData
End If

' Look for the business the user created in the Businesses form.
Result = DLookup("[Business]", "Businesses", _
"[Business]='" & NewData & "'")
If IsNull(Result) Then
' If the business was not created, set the Response argument
' to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again!"
Else
' If the business was created, set the Response argument to
' indicate that new data is being added.
Response = acDataErrAdded
End If
End Sub


So far, when the Business table opens, the NewData is not passed into
the Business field. What have I done wrong?

Thanks so much!

 

[Marshall Barton]

I have 2 forms; Scorecards and Businesses
When someone is entering new scorecard info in the Scorecards Form,
they must choose an "AccountName" from a drop-down list. If the
business they try to type is an AccountName that is not in the list, a
dialog pops up and tells them the Business is not in the list and do
they want to add it. If they choose "yes" the Businesses Form opens up
and they can add the Business there. What I need to happen is when the
Businesses Form opens, it should grab the text they typed in the
"AccountName" control in the Scorecards form and place it into the
"Business" control in the now-open Businesses Form. Now, I have no idea
how to this, I think it involves a detail in my DoCmd.OpenForm thingy,
but I am not sure. ANOTHER catch is that the solution can't involve any
permanent relationship between the Scorecards form and the Businesses
form, because I also use the Businesses form to add business info from
many other forms in my database. In other words, I can't put anything
in the On Load of the Business form related to the Scorecard form,
because the Business form is opened by the DoCmd.OpenFOrm of many, many
other forms besides just Scorecards. See? WHat can I do here? Here is
what the code looks like that I am using right now:

Private Sub AccountName_NotInList(NewData As String, Response As
Integer)
Dim Result
Dim Msg As String
Dim CR As String

CR = Chr$(13)

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Ask the user if he or she wishes to add the new business.
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
' If the user chose Yes, start the Businesses form in data entry
' mode as a dialog form, passing the new business name in
' NewData to the OpenForm method's OpenArgs argument. The
' OpenArgs argument is used in Businesses form's Form_Load event
' procedure.
DoCmd.OpenForm "Businesses", , , , acAdd, acDialog, NewData
End If

' Look for the business the user created in the Businesses form.
Result = DLookup("[Business]", "Businesses", _
"[Business]='" & NewData & "'")
If IsNull(Result) Then
' If the business was not created, set the Response argument
' to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again!"
Else
' If the business was created, set the Response argument to
' indicate that new data is being added.
Response = acDataErrAdded
End If
End Sub

That part is fine, but you do need to put some code in the
Businesses form. You can check to see if the business name
has been passed as an argumnet and if the form is on a new
record (it was opened in add mode), so it should be pretty
safe when used by other forms.

Try something like this air code in the Businesses form's
Load event procedure:

If Len(Nz(Me.OpenArgs, "")) > 0 And Me.NewRecord Then
Me.txtBusinessName = Me.OpenArgs
End If

 

[melanie]

Okay,
Everything you guys gave me so far, the code for the scorecards form
and the corresponding code for the businesses form works fine; until i
actually say "yes" to add the new business. Now, it DOES put the
NewData into the Business control in the Businesses form, but when I
click the save and close button, I get the error "Access ...can't find
the form "Businesses" referred to..." in the Scorecards form. Here is
the line highlighted:

Forms![Businesses].Form.Business.Value =
Forms![frm_Scorecards_KBPA].Form.AccountName.Value

Now what did I mess up?
Here are the pieces of code so far:

In the Scorecards form for "On Not In List"

Private Sub AccountName_NotInList(NewData As String, Response As
Integer)
Dim Result
Dim Msg As String
Dim CR As String

CR = Chr$(13)

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Ask the user if he or she wishes to add the new business.
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
' If the user chose Yes, start the Businesses form in data entry
' mode as a dialog form, passing the new business name in
' NewData to the OpenForm method's OpenArgs argument. The
' OpenArgs argument is used in Businesses form's Form_Load event
' procedure.
DoCmd.OpenForm "Businesses", , , , acAdd, acDialog, NewData
Forms![Businesses].Form.Business.Value =
Forms![frm_Scorecards_KBPA].Form.AccountName.Value
End If

' Look for the business the user created in the Businesses form.
Result = DLookup("[Business]", "Businesses", _
"[Business]='" & NewData & "'")
If IsNull(Result) Then
' If the business was not created, set the Response argument
' to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again!"
Else
' If the business was created, set the Response argument to
' indicate that new data is being added.
Response = acDataErrAdded
End If
End Sub


And in the Businesses form for "Save and close form"
Private Sub savebusiness_Click()
On Error GoTo Err_savebusiness_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
DoCmd.Close acForm, "Businesses"

Exit_savebusiness_Click:
Exit Sub

Err_savebusiness_Click:
MsgBox Err.Description
Resume Exit_savebusiness_Click

End Sub

And also in the Businesses form for "On Load"

Private Sub Form_Load()
If Len(Nz(Me.OpenArgs, "")) > 0 And Me.NewRecord Then
Me.Business = Me.OpenArgs
End If
End Sub


Whew! That's a lot! Any suggestions? We are SO close!

Thanks!

I have 2 forms; Scorecards and Businesses
When someone is entering new scorecard info in the Scorecards Form,
they must choose an "AccountName" from a drop-down list. If the
business they try to type is an AccountName that is not in the list, a
dialog pops up and tells them the Business is not in the list and do
they want to add it. If they choose "yes" the Businesses Form opens up
and they can add the Business there. What I need to happen is when the
Businesses Form opens, it should grab the text they typed in the
"AccountName" control in the Scorecards form and place it into the
"Business" control in the now-open Businesses Form. Now, I have no idea
how to this, I think it involves a detail in my DoCmd.OpenForm thingy,
but I am not sure. ANOTHER catch is that the solution can't involve any
permanent relationship between the Scorecards form and the Businesses
form, because I also use the Businesses form to add business info from
many other forms in my database. In other words, I can't put anything
in the On Load of the Business form related to the Scorecard form,
because the Business form is opened by the DoCmd.OpenFOrm of many, many
other forms besides just Scorecards. See? WHat can I do here? Here is
what the code looks like that I am using right now:

Private Sub AccountName_NotInList(NewData As String, Response As
Integer)
Dim Result
Dim Msg As String
Dim CR As String

CR = Chr$(13)

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Ask the user if he or she wishes to add the new business.
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
' If the user chose Yes, start the Businesses form in data entry
' mode as a dialog form, passing the new business name in
' NewData to the OpenForm method's OpenArgs argument. The
' OpenArgs argument is used in Businesses form's Form_Load event
' procedure.
DoCmd.OpenForm "Businesses", , , , acAdd, acDialog, NewData
End If

' Look for the business the user created in the Businesses form.
Result = DLookup("[Business]", "Businesses", _
"[Business]='" & NewData & "'")
If IsNull(Result) Then
' If the business was not created, set the Response argument
' to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again!"
Else
' If the business was created, set the Response argument to
' indicate that new data is being added.
Response = acDataErrAdded
End If
End Sub

That part is fine, but you do need to put some code in the
Businesses form. You can check to see if the business name
has been passed as an argumnet and if the form is on a new
record (it was opened in add mode), so it should be pretty
safe when used by other forms.

Try something like this air code in the Businesses form's
Load event procedure:

If Len(Nz(Me.OpenArgs, "")) > 0 And Me.NewRecord Then
Me.txtBusinessName = Me.OpenArgs
End If

 

[melanie]

PLEASE!!!!!
Anyone out there?

Okay,
Everything you guys gave me so far, the code for the scorecards form
and the corresponding code for the businesses form works fine; until i
actually say "yes" to add the new business. Now, it DOES put the
NewData into the Business control in the Businesses form, but when I
click the save and close button, I get the error "Access ...can't find
the form "Businesses" referred to..." in the Scorecards form. Here is
the line highlighted:

Forms![Businesses].Form.Business.Value =
Forms![frm_Scorecards_KBPA].Form.AccountName.Value

Now what did I mess up?
Here are the pieces of code so far:

In the Scorecards form for "On Not In List"

Private Sub AccountName_NotInList(NewData As String, Response As
Integer)
Dim Result
Dim Msg As String
Dim CR As String

CR = Chr$(13)

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Ask the user if he or she wishes to add the new business.
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
' If the user chose Yes, start the Businesses form in data entry
' mode as a dialog form, passing the new business name in
' NewData to the OpenForm method's OpenArgs argument. The
' OpenArgs argument is used in Businesses form's Form_Load event
' procedure.
DoCmd.OpenForm "Businesses", , , , acAdd, acDialog, NewData
Forms![Businesses].Form.Business.Value =
Forms![frm_Scorecards_KBPA].Form.AccountName.Value
End If

' Look for the business the user created in the Businesses form.
Result = DLookup("[Business]", "Businesses", _
"[Business]='" & NewData & "'")
If IsNull(Result) Then
' If the business was not created, set the Response argument
' to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again!"
Else
' If the business was created, set the Response argument to
' indicate that new data is being added.
Response = acDataErrAdded
End If
End Sub


And in the Businesses form for "Save and close form"
Private Sub savebusiness_Click()
On Error GoTo Err_savebusiness_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
DoCmd.Close acForm, "Businesses"

Exit_savebusiness_Click:
Exit Sub

Err_savebusiness_Click:
MsgBox Err.Description
Resume Exit_savebusiness_Click

End Sub

And also in the Businesses form for "On Load"

Private Sub Form_Load()
If Len(Nz(Me.OpenArgs, "")) > 0 And Me.NewRecord Then
Me.Business = Me.OpenArgs
End If
End Sub


Whew! That's a lot! Any suggestions? We are SO close!

Thanks!

I have 2 forms; Scorecards and Businesses
When someone is entering new scorecard info in the Scorecards Form,
they must choose an "AccountName" from a drop-down list. If the
business they try to type is an AccountName that is not in the list, a
dialog pops up and tells them the Business is not in the list and do
they want to add it. If they choose "yes" the Businesses Form opens up
and they can add the Business there. What I need to happen is when the
Businesses Form opens, it should grab the text they typed in the
"AccountName" control in the Scorecards form and place it into the
"Business" control in the now-open Businesses Form. Now, I have no idea
how to this, I think it involves a detail in my DoCmd.OpenForm thingy,
but I am not sure. ANOTHER catch is that the solution can't involve any
permanent relationship between the Scorecards form and the Businesses
form, because I also use the Businesses form to add business info from
many other forms in my database. In other words, I can't put anything
in the On Load of the Business form related to the Scorecard form,
because the Business form is opened by the DoCmd.OpenFOrm of many, many
other forms besides just Scorecards. See? WHat can I do here? Here is
what the code looks like that I am using right now:

Private Sub AccountName_NotInList(NewData As String, Response As
Integer)
Dim Result
Dim Msg As String
Dim CR As String

CR = Chr$(13)

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Ask the user if he or she wishes to add the new business.
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
' If the user chose Yes, start the Businesses form in data entry
' mode as a dialog form, passing the new business name in
' NewData to the OpenForm method's OpenArgs argument. The
' OpenArgs argument is used in Businesses form's Form_Load event
' procedure.
DoCmd.OpenForm "Businesses", , , , acAdd, acDialog, NewData
End If

' Look for the business the user created in the Businesses form.
Result = DLookup("[Business]", "Businesses", _
"[Business]='" & NewData & "'")
If IsNull(Result) Then
' If the business was not created, set the Response argument
' to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again!"
Else
' If the business was created, set the Response argument to
' indicate that new data is being added.
Response = acDataErrAdded
End If
End Sub

That part is fine, but you do need to put some code in the
Businesses form. You can check to see if the business name
has been passed as an argumnet and if the form is on a new
record (it was opened in add mode), so it should be pretty
safe when used by other forms.

Try something like this air code in the Businesses form's
Load event procedure:

If Len(Nz(Me.OpenArgs, "")) > 0 And Me.NewRecord Then
Me.txtBusinessName = Me.OpenArgs
End If

 

[Marshall Barton]

Everything you guys gave me so far, the code for the scorecards form
and the corresponding code for the businesses form works fine; until i
actually say "yes" to add the new business. Now, it DOES put the
NewData into the Business control in the Businesses form, but when I
click the save and close button, I get the error "Access ...can't find
the form "Businesses" referred to..." in the Scorecards form. Here is
the line highlighted:

Forms![Businesses].Form.Business.Value =
Forms![frm_Scorecards_KBPA].Form.AccountName.Value

Now what did I mess up?
Here are the pieces of code so far:

In the Scorecards form for "On Not In List"

Private Sub AccountName_NotInList(NewData As String, Response As
Integer)
Dim Result
Dim Msg As String
Dim CR As String

CR = Chr$(13)

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Ask the user if he or she wishes to add the new business.
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
' If the user chose Yes, start the Businesses form in data entry
' mode as a dialog form, passing the new business name in
' NewData to the OpenForm method's OpenArgs argument. The
' OpenArgs argument is used in Businesses form's Form_Load event
' procedure.
DoCmd.OpenForm "Businesses", , , , acAdd, acDialog, NewData
Forms![Businesses].Form.Business.Value =
Forms![frm_Scorecards_KBPA].Form.AccountName.Value
End If

' Look for the business the user created in the Businesses form.
Result = DLookup("[Business]", "Businesses", _
"[Business]='" & NewData & "'")
If IsNull(Result) Then
' If the business was not created, set the Response argument
' to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again!"
Else
' If the business was created, set the Response argument to
' indicate that new data is being added.
Response = acDataErrAdded
End If
End Sub


And in the Businesses form for "Save and close form"
Private Sub savebusiness_Click()
On Error GoTo Err_savebusiness_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
DoCmd.Close acForm, "Businesses"

Exit_savebusiness_Click:
Exit Sub

Err_savebusiness_Click:
MsgBox Err.Description
Resume Exit_savebusiness_Click

End Sub

And also in the Businesses form for "On Load"

Private Sub Form_Load()
If Len(Nz(Me.OpenArgs, "")) > 0 And Me.NewRecord Then
Me.Business = Me.OpenArgs
End If
End Sub

That line can not work because the businesses form is closed
by the time that line executes.

Since the name was set in the Load event, the line would be
superfluous even if it did work.

Just delete the offending line.

 

[Marshall Barton]

PLEASE!!!!!
Anyone out there?

Patience. The folks out here don't work here and have to
find some spare time from real jobs to answer questions.

 

[melanie]

Thanks, works just fine with the line deleted!

Everything you guys gave me so far, the code for the scorecards form
and the corresponding code for the businesses form works fine; until i
actually say "yes" to add the new business. Now, it DOES put the
NewData into the Business control in the Businesses form, but when I
click the save and close button, I get the error "Access ...can't find
the form "Businesses" referred to..." in the Scorecards form. Here is
the line highlighted:

Forms![Businesses].Form.Business.Value =
Forms![frm_Scorecards_KBPA].Form.AccountName.Value

Now what did I mess up?
Here are the pieces of code so far:

In the Scorecards form for "On Not In List"

Private Sub AccountName_NotInList(NewData As String, Response As
Integer)
Dim Result
Dim Msg As String
Dim CR As String

CR = Chr$(13)

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

' Ask the user if he or she wishes to add the new business.
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
' If the user chose Yes, start the Businesses form in data entry
' mode as a dialog form, passing the new business name in
' NewData to the OpenForm method's OpenArgs argument. The
' OpenArgs argument is used in Businesses form's Form_Load event
' procedure.
DoCmd.OpenForm "Businesses", , , , acAdd, acDialog, NewData
Forms![Businesses].Form.Business.Value =
Forms![frm_Scorecards_KBPA].Form.AccountName.Value
End If

' Look for the business the user created in the Businesses form.
Result = DLookup("[Business]", "Businesses", _
"[Business]='" & NewData & "'")
If IsNull(Result) Then
' If the business was not created, set the Response argument
' to suppress an error message and undo changes.
Response = acDataErrContinue
' Display a customized message.
MsgBox "Please try again!"
Else
' If the business was created, set the Response argument to
' indicate that new data is being added.
Response = acDataErrAdded
End If
End Sub


And in the Businesses form for "Save and close form"
Private Sub savebusiness_Click()
On Error GoTo Err_savebusiness_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
DoCmd.Close acForm, "Businesses"

Exit_savebusiness_Click:
Exit Sub

Err_savebusiness_Click:
MsgBox Err.Description
Resume Exit_savebusiness_Click

End Sub

And also in the Businesses form for "On Load"

Private Sub Form_Load()
If Len(Nz(Me.OpenArgs, "")) > 0 And Me.NewRecord Then
Me.Business = Me.OpenArgs
End If
End Sub

That line can not work because the businesses form is closed
by the time that line executes.

Since the name was set in the Load event, the line would be
superfluous even if it did work.

Just delete the offending line.