From Form to Form

[BabyATX13 via AccessMonster.com]

I am having a problem switching from one form to another from a prompt. Here
is my problem: I have an Invoice Form with a Customer Field (combo box) that
looks up CompanyName from a Customer Table, if the CompanyName is not in the
Customer Table then user is prompted [“NewData” is not an available Company
Name! Do you want to add it? Click yes to add it or No to re-type it.] If no
is clicked user returns to Invoice Form to renter the name, if yes is clicked
CompanyName field is cleared, the Invoice Form is closed, and the Customer
Form is opened. The problem is when the Customer Form is opened I get the
following error: [The text you entered isn’t an item in the list. Select an
item from the list, or enter text that matches one of the list items.] How
can I keep this error from showing up when the Customer Form is opened?

Thank you in advance
KB

 

[RuralGuy]

I am having a problem switching from one form to another from a prompt. Here
is my problem: I have an Invoice Form with a Customer Field (combo box) that
looks up CompanyName from a Customer Table, if the CompanyName is not in the
Customer Table then user is prompted [“NewData” is not an available Company
Name! Do you want to add it? Click yes to add it or No to re-type it.] If no
is clicked user returns to Invoice Form to renter the name, if yes is clicked
CompanyName field is cleared, the Invoice Form is closed, and the Customer
Form is opened. The problem is when the Customer Form is opened I get the
following error: [The text you entered isn’t an item in the list. Select an
item from the list, or enter text that matches one of the list items.] How
can I keep this error from showing up when the Customer Form is opened?

Thank you in advance
KB

Why are you closing the "Invoice" Form? Why are you clearing the
CompanyName field? You want to get back there when the Customer form
is complete don't you? Just open the Customer form as acDialog. Post
your "NotInList" code and we'll make the adjustments to eliminate the
error.
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[BabyATX13 via AccessMonster.com]

RG,
Some answers:
Why are you closing the "Invoice" Form?
Because if I don’t the CompanyName field will not update until I do.

Why are you clearing the CompanyName field?
Because if I don’t I get the prompt twice.

Just open the Customer form as acDialog.
Forgive my ignorance, but, how?

 

[RuralGuy]

From VBA Help:
DoCmd.OpenForm(FormName, View, FilterName, WhereCondition, DataMode,
WindowMode, OpenArgs)

If WindowMode = acDialog then execution halts in this form until the
next form is closed or made invisible.

Are you opening the Customer form in the "NotInList" event? If so
then please post that code from Private Sub ... End Sub

RG,
Some answers:
Why are you closing the "Invoice" Form?
Because if I don’t the CompanyName field will not update until I do.

Why are you clearing the CompanyName field?
Because if I don’t I get the prompt twice.

Just open the Customer form as acDialog.
Forgive my ignorance, but, how?

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[BabyATX13 via AccessMonster.com]

My Original Code:

Private Sub CompanyName_NotInList(NewData As String, Response As Integer)
'Adds item to list
Dim strMsg As String
Dim stDocName As String
Dim stLinkCriteria As String
'Display system message
strMsg = "'" & NewData & "' is not an available Company Name!" & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to add it?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add it or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
'Clear CompanyName Field
CompanyName.Undo
'Close the form
Call Exit_Click
'Open the Customer Form in add mode
stDocName = "Customers"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If

End Sub

 

[BabyATX13 via AccessMonster.com]

I found this particular piece of code and I wasn't sure what went where.
KB

 

[RuralGuy]

Changes in line:

My Original Code:

Private Sub CompanyName_NotInList(NewData As String, Response As Integer)
'Adds item to list
Dim strMsg As String
Dim stDocName As String
Dim stLinkCriteria As String
'Display system message
strMsg = "'" & NewData & "' is not an available Company Name!" & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to add it?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add it or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else

'-- Don't do either of the next two lines of code!

'Clear CompanyName Field

'-- CompanyName.Undo

'Close the form

'-- Call Exit_Click

'Open the Customer Form in add mode
stDocName = "Customers"
DoCmd.OpenForm stDocName, , , , acFormAdd, acDialog, NewData

'-- Unnecessary If...Else...End If now
'-- If Err Then
'-- MsgBox "An error occurred. Please try again."
'-- Response = acDataErrContinue
'-- Else
'-- But you need this line so the ComboBox will requery.
Response = acDataErrAdded
'-- End If

End If

End Sub

Then on the Open event of the "Customers" form you have something
like:

If Not IsNull(Me.OpenArgs) Then
'-- Form is being opened from a form passing the New Customer Name
txtCustomerName = Me.OpenArgs '-- Initialize the Customer Name
End If

Use *your* control names but that should give you the idea and
eliminate all of your errors. Post back if you have additional
questions or problems. You will no longer need a "New Customer"
button!

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[BabyATX13 via AccessMonster.com]

Hu?????

'-- Unnecessary If...Else...End If now
'-- If Err Then
'-- MsgBox "An error occurred. Please try again."
'-- Response = acDataErrContinue
'-- Else
'-- But you need this line so the ComboBox will requery.
Response = acDataErrAdded
'-- End If

 

[BabyATX13 via AccessMonster.com]

Changes in line:

'-- Unnecessary If...Else...End If now
'-- If Err Then
'-- MsgBox "An error occurred. Please try again."
'-- Response = acDataErrContinue
'-- Else
'-- But you need this line so the ComboBox will requery.
Response = acDataErrAdded
'-- End If



Then on the Open event of the "Customers" form you have something
like:

If Not IsNull(Me.OpenArgs) Then
'-- Form is being opened from a form passing the New Customer Name
txtCustomerName = Me.OpenArgs '-- Initialize the Customer Name
End If

I tried this code every which way I could think of and nothing worked the way
it is supposed to work and I am still getting the error [The text you entered
isn’t an item in the list. Select an item from the list, or enter text that
matches one of the list items.] when the customer form opens.
KB

Use *your* control names but that should give you the idea and
eliminate all of your errors. Post back if you have additional
questions or problems. You will no longer need a "New Customer"
button!

From VBA Help:
DoCmd.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, [quoted text clipped - 9 lines]
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[BabyATX13 via AccessMonster.com]

Changes in line:

I don't understand what you mean here

'-- Unnecessary If...Else...End If now
'-- If Err Then
'-- MsgBox "An error occurred. Please try again."
'-- Response = acDataErrContinue
'-- Else
'-- But you need this line so the ComboBox will requery.
Response = acDataErrAdded
'-- End If



Then on the Open event of the "Customers" form you have something
like:

If Not IsNull(Me.OpenArgs) Then
'-- Form is being opened from a form passing the New Customer Name
txtCustomerName = Me.OpenArgs '-- Initialize the Customer Name
End If

I tried this code every which way I could think of and nothing worked the way
it is supposed to work and I am still getting the error [The text you entered
isn’t an item in the list. Select an item from the list, or enter text that
matches one of the list items.] when the customer form opens.
KB

Use *your* control names but that should give you the idea and
eliminate all of your errors. Post back if you have additional
questions or problems. You will no longer need a "New Customer"
button!

From VBA Help:
DoCmd.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, [quoted text clipped - 9 lines]
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[RuralGuy]

Here's the complete SubRoutine:

Private Sub CompanyName_NotInList(NewData As String, _
Response As Integer)
'-- Adds item to list
Dim strMsg As String
Dim stDocName As String
'-- Display system message
strMsg = "[" & NewData & "] is not an available Company Name!" & _
vbCrLf & vbCrLf & _
"Do you want to add it?" & vbCrLf & vbCrLf & _
"Click Yes to add it or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbYes Then
'-- Open the Customer Form in add mode
stDocName = "Customers"
DoCmd.OpenForm stDocName, , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

Does that make sense BabyATX13?

<snip>


_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[BabyATX13 via AccessMonster.com]

Yes that makes perfect sense thank you )

K Board

Here's the complete SubRoutine:

Private Sub CompanyName_NotInList(NewData As String, _
Response As Integer)
'-- Adds item to list
Dim strMsg As String
Dim stDocName As String
'-- Display system message
strMsg = "[" & NewData & "] is not an available Company Name!" & _
vbCrLf & vbCrLf & _
"Do you want to add it?" & vbCrLf & vbCrLf & _
"Click Yes to add it or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbYes Then
'-- Open the Customer Form in add mode
stDocName = "Customers"
DoCmd.OpenForm stDocName, , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

Does that make sense BabyATX13?

<snip>

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[BabyATX13 via AccessMonster.com]

That was quite different than what you told me before.
At any rate this one works thank you for all of your help, I really do
appreciate it.

Thanks Again
K Board )

Here's the complete SubRoutine:

Private Sub CompanyName_NotInList(NewData As String, _
Response As Integer)
'-- Adds item to list
Dim strMsg As String
Dim stDocName As String
'-- Display system message
strMsg = "[" & NewData & "] is not an available Company Name!" & _
vbCrLf & vbCrLf & _
"Do you want to add it?" & vbCrLf & vbCrLf & _
"Click Yes to add it or No to re-type it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbYes Then
'-- Open the Customer Form in add mode
stDocName = "Customers"
DoCmd.OpenForm stDocName, , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

Does that make sense BabyATX13?

<snip>

_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[RuralGuy]

You're welcome. Glad I could help.
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.