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

copying data between forms on DoCmd.OpenForm

P: n/a
Here's what I've got:

I've got a form with combo boxes. you can select already existing
company names or type in new ones. if you type in a new one, it
prompts you to double click the combo box if you want to add a new
company. when you double click it brings up the new company form so
you can enter the rest of the information besides just the name there.
This all works.

Here's what I want:

I want the new company that is typed in the first form (and causes the
double click prompt) to be copied into the new company form, so the
name doesn't have to be typed twice.

Form information:

First form is called 'Quote'
Combo Box I'm using is 'CompanyID'

Second form is 'Company'
Text Box that i want the info to go to is 'CompanyNickname'

I've tried using the Wherecondition in the docmd.openform but it
doesn't work.

Thanks in advance for the help and for posting the reply to the groups.

Here's the code:

Private Sub CompanyID_NotInList(NewData As String, Response As Integer)
MsgBox "Double-click this field to add an entry to the list."
Response = acDataErrContinue
End Sub
Private Sub CompanyID_DblClick(Cancel As Integer)
On Error GoTo Err_CompanyID_DblClick
Dim lngCompanyID As Long

If IsNull(Me![CompanyID]) Then
Me![CompanyID].Text = ""
Else
lngCompanyID = Me![CompanyID]
Me![CompanyID] = Null
End If
DoCmd.OpenForm "Company", , , "[CompanyNickname] = ' & Me!CompanyID
& '", , acDialog, "GotoNew"
Me![CompanyID].Requery
If lngCompanyID <> 0 Then Me![CompanyID] = lngCompanyID

Exit_CompanyID_DblClick:
Exit Sub

Err_CompanyID_DblClick:
MsgBox Err.Description
Resume Exit_CompanyID_DblClick
End Sub

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Use OpenArgs on the Load event of your Company form:

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
Me![CompanyNameField] = Me.OpenArgs
Forms![Company]![Address].SetFocus
End If
End Sub

I also fire the OpenForm directly from the combo box on the On Not In
List event when the item typed does not match anything in the list:

Private Sub CoID_NotInList(NewData As String, Response As Integer)
Dim result
Dim msg As String
Dim cr As String
cr = Chr$(13)
If NewData = "" Then Exit Sub
msg = "'" & NewData & "' is not in the list." & cr & cr
msg = msg & "Do you want to add it?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbYes Then
DoCmd.OpenForm "Company", , , , acAdd, acDialog, NewData
End If
result = DLookup("[CompanyNameField]", "Company", "[CompanyNameField]=
" & Chr(34) & NewData & Chr(34))
If IsNull(result) Then
Response = acDataErrContinue
MsgBox "Please try again!"
Else
Response = acDataErrAdded
End If
End Sub

Supply your own field and form names. Good luck!!

Nov 13 '05 #2

P: n/a
Thanks so much. i thought i replied yesterday, but apparently i'm
don't know how to use google groups correctly. It works beautifully
and i quit using my old code entirely. i trimmed out several things.
i didn't know what the result= line was for, so i took it out, and i
took out everything below that because it was causing 'text is not in
the list' errors to come up even though it was going into the creation.
here's what i have left. Thanks again.
Quote Form:

Private Sub CompanyID_NotInList(NewData As String, Response As Integer)
Dim result
Dim msg As String
Dim cr As String
cr = Chr$(13)
If NewData = "" Then Exit Sub

msg = "'" & NewData & "' is not in the list." & cr & cr

msg = msg & "Do you want to add it?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbYes Then
DoCmd.OpenForm "Company", , , , acAdd, acDialog, NewData
End If

End Sub
----
Company Form:

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then

Me![CompanyNickname] = Me.OpenArgs

Forms![quote]![CompanyID].SetFocus

End If
End Sub
------
Koontzie wrote:
Use OpenArgs on the Load event of your Company form:

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
Me![CompanyNameField] = Me.OpenArgs
Forms![Company]![Address].SetFocus
End If
End Sub

I also fire the OpenForm directly from the combo box on the On Not In
List event when the item typed does not match anything in the list:

Private Sub CoID_NotInList(NewData As String, Response As Integer)
Dim result
Dim msg As String
Dim cr As String
cr = Chr$(13)
If NewData = "" Then Exit Sub
msg = "'" & NewData & "' is not in the list." & cr & cr
msg = msg & "Do you want to add it?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbYes Then
DoCmd.OpenForm "Company", , , , acAdd, acDialog, NewData
End If
result = DLookup("[CompanyNameField]", "Company", "[CompanyNameField]= " & Chr(34) & NewData & Chr(34))
If IsNull(result) Then
Response = acDataErrContinue
MsgBox "Please try again!"
Else
Response = acDataErrAdded
End If
End Sub

Supply your own field and form names. Good luck!!


Nov 13 '05 #3

P: n/a
problem. i have the data entry form and then i have a data edit form.
i get an error when i apply the same script to the data edit form.

Is there a way to change the code below so it knows what form it's
coming from and then run the appropriate line? Either
Forms![quote]![CompanyID].SetFocus or Forms![Quote
Edit]![CompanyID].SetFocus

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then

Me![CompanyNickname] = Me.OpenArgs

Forms![quote]![CompanyID].SetFocus

End If
End Sub

Thanks.

beowulfs wrote:
Thanks so much. i thought i replied yesterday, but apparently i'm
don't know how to use google groups correctly. It works beautifully
and i quit using my old code entirely. i trimmed out several things.
i didn't know what the result= line was for, so i took it out, and i
took out everything below that because it was causing 'text is not in
the list' errors to come up even though it was going into the creation. here's what i have left. Thanks again.
Quote Form:

Private Sub CompanyID_NotInList(NewData As String, Response As Integer) Dim result
Dim msg As String
Dim cr As String
cr = Chr$(13)
If NewData = "" Then Exit Sub

msg = "'" & NewData & "' is not in the list." & cr & cr

msg = msg & "Do you want to add it?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbYes Then
DoCmd.OpenForm "Company", , , , acAdd, acDialog, NewData
End If

End Sub
----
Company Form:

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then

Me![CompanyNickname] = Me.OpenArgs

Forms![quote]![CompanyID].SetFocus

End If
End Sub
------
Koontzie wrote:
Use OpenArgs on the Load event of your Company form:

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
Me![CompanyNameField] = Me.OpenArgs
Forms![Company]![Address].SetFocus
End If
End Sub

I also fire the OpenForm directly from the combo box on the On Not In List event when the item typed does not match anything in the list:

Private Sub CoID_NotInList(NewData As String, Response As Integer)
Dim result
Dim msg As String
Dim cr As String
cr = Chr$(13)
If NewData = "" Then Exit Sub
msg = "'" & NewData & "' is not in the list." & cr & cr
msg = msg & "Do you want to add it?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbYes Then
DoCmd.OpenForm "Company", , , , acAdd, acDialog, NewData
End If
result = DLookup("[CompanyNameField]", "Company",

"[CompanyNameField]=
" & Chr(34) & NewData & Chr(34))
If IsNull(result) Then
Response = acDataErrContinue
MsgBox "Please try again!"
Else
Response = acDataErrAdded
End If
End Sub

Supply your own field and form names. Good luck!!


Nov 13 '05 #4

P: n/a
Private Sub Form_Load()
If IsNull(Me.OpenArgs) Then
Forms("Quote Edit").CompanyID.SetFocus
Else
Me.CompanyNickname = Me.OpenArgs
Forms("Quote").CompanyID.SetFocus
End If
End Sub
Darryl Kerkeslager
"beowulfs" <tr**@scheh.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
problem. i have the data entry form and then i have a data edit form.
i get an error when i apply the same script to the data edit form.

Is there a way to change the code below so it knows what form it's
coming from and then run the appropriate line? Either
Forms![quote]![CompanyID].SetFocus or Forms![Quote
Edit]![CompanyID].SetFocus

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then

Me![CompanyNickname] = Me.OpenArgs

Forms![quote]![CompanyID].SetFocus

End If
End Sub

Thanks.

Nov 13 '05 #5

P: n/a
Thanks for the code, but it doesn't work. it runs through first one
always. i need something that will determine which form is the
referrer. so like,
if form referrer is "Quote" then ' <-------don't know how to do this
If IsNull(Me.OpenArgs) Then
Forms("Quote").CompanyID.SetFocus
End If
Else If Form referrer is "Quote Edit" Then '<-------don't know how
to do this
If IsNull(Me.OpenArgs)Then
Forms("Quote Edit").CompanyID.SetFocus
End If
End If
Else
End If

I just don't know how to call up a form referrer property, if there
even is such a thing. Thanks again.

Darryl Kerkeslager wrote:
Private Sub Form_Load()
If IsNull(Me.OpenArgs) Then
Forms("Quote Edit").CompanyID.SetFocus
Else
Me.CompanyNickname = Me.OpenArgs
Forms("Quote").CompanyID.SetFocus
End If
End Sub
Darryl Kerkeslager
"beowulfs" <tr**@scheh.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
problem. i have the data entry form and then i have a data edit form. i get an error when i apply the same script to the data edit form.

Is there a way to change the code below so it knows what form it's
coming from and then run the appropriate line? Either
Forms![quote]![CompanyID].SetFocus or Forms![Quote
Edit]![CompanyID].SetFocus

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then

Me![CompanyNickname] = Me.OpenArgs

Forms![quote]![CompanyID].SetFocus

End If
End Sub

Thanks.


Nov 13 '05 #6

P: n/a
What my code assumed was that when you were opening the Form from Form
"QuoteEdit", that you would not use any OpenArgs, thus it would be Null
and this line would run:
Forms("Quote Edit").CompanyID.SetFocus
Perhaps you need to test for Len(Me.OpenArgs)=0 instead of IsNull, but the
principle is the same.
Darryl Kerkeslager

"beowulfs" <tr**@scheh.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
Thanks for the code, but it doesn't work. it runs through first one
always. i need something that will determine which form is the
referrer. so like,
if form referrer is "Quote" then ' <-------don't know how to do this
If IsNull(Me.OpenArgs) Then
Forms("Quote").CompanyID.SetFocus
End If
Else If Form referrer is "Quote Edit" Then '<-------don't know how
to do this
If IsNull(Me.OpenArgs)Then
Forms("Quote Edit").CompanyID.SetFocus
End If
End If
Else
End If

I just don't know how to call up a form referrer property, if there
even is such a thing. Thanks again.

Darryl Kerkeslager wrote:
Private Sub Form_Load()
If IsNull(Me.OpenArgs) Then
Forms("Quote Edit").CompanyID.SetFocus
Else
Me.CompanyNickname = Me.OpenArgs
Forms("Quote").CompanyID.SetFocus
End If
End Sub
Darryl Kerkeslager
"beowulfs" <tr**@scheh.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
problem. i have the data entry form and then i have a data edit

form. i get an error when i apply the same script to the data edit form.

Is there a way to change the code below so it knows what form it's
coming from and then run the appropriate line? Either
Forms![quote]![CompanyID].SetFocus or Forms![Quote
Edit]![CompanyID].SetFocus

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then

Me![CompanyNickname] = Me.OpenArgs

Forms![quote]![CompanyID].SetFocus

End If
End Sub

Thanks.

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.