not in list open a form

I am looking for code help to have a not in list event call up a form.
What my current code is the following: What I want to be able to do is
have it open my form that is frmRequestingAgency. Right now it will
add the Requesting Agency name, but in the actual table or form there
is alot of other info that needs to be filled in.


Private Sub Service_NotInList(NewData As String, Response As Integer)


Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' Is not in the Database!" & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Book Category...")
If i = vbYes Then
strSQL = "Insert Into tblRequestingAgency ([AgencyName]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

On 28 Dec 2006 13:34:47 -0800, wrote:

I am looking for code help to have a not in list event call up a form.
What my current code is the following: What I want to be able to do is
have it open my form that is frmRequestingAgency. Right now it will
add the Requesting Agency name, but in the actual table or form there
is alot of other info that needs to be filled in.

Private Sub Service_NotInList(NewData As String, Response As Integer)

Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' Is not in the Database!" & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Book Category...")
If i = vbYes Then
strSQL = "Insert Into tblRequestingAgency ([AgencyName]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub


Private Sub Service_NotInList(NewData As String, Response As Integer)

If MsgBox("'" & NewData & "' Is not in the Database!" & vbCrLf &
vbNewLine & "Do you want to add it?", vbExclamation + vbYesNo) = vbYes
Then
DoCmd.OpenForm "FormName", , , , , acDialog
Response = acDataErrAdded
End If
End Sub

Note: vbCr is not usable by itself in Access.
You need the carraige return AND the line space together (in that
order).
You can use chr(13) & chr(10)
or vbCrLf
or vbNewLine
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

"fredg" wrote in message


Note: vbCr is not usable by itself in Access.
You need the carraige return AND the line space together (in that
order).

Have you tried it, Fred? The last time I looked, the MsgBox function
accepted vbCr as equivalent to vbCrLf. I haven't checked in Access 2003
or 2007, though.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

On Thu, 28 Dec 2006 17:23:31 -0500, Dirk Goldgar wrote:

"fredg" wrote in message


Note: vbCr is not usable by itself in Access.
You need the carraige return AND the line space together (in that
order).

Have you tried it, Fred? The last time I looked, the MsgBox function
accepted vbCr as equivalent to vbCrLf. I haven't checked in Access 2003
or 2007, though.

Hi, Dirk,
No I didn't try it, and now that you mentioned it, I do remember that
chr(13) by itself (as well as chr(10) by itself) will work in a
MsgBox. I also tried them independently in an InputBox and they work
individually there as well.
But not, AFAIK, otherwise in Access.

Thanks for the reminder.
I think for myself, I'd rather use them both together to keep within
the expected Access VBA coding requirements.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Ya enter this following code to my not in list code...did not work.

Private Sub Service_NotInList(NewData As String, Response As Integer)

If MsgBox("'" & NewData & "' Is not in the Database!" & vbCrLf &
vbNewLine & "Do you want to add it?", vbExclamation + vbYesNo) = vbYes
Then
DoCmd.OpenForm "FormName", , , , , acDialog
Response = acDataErrAdded
End If
End Sub

I got this code to open the form, but when I click no to adding it to
the database...so a mistype, it still opens the form when I click no.
What do I have wrong?


Private Sub Service_NotInList(NewData As String, Response As Integer)

If MsgBox("The Agency Entered is not in database, would you like to
add it?", _
vbYesNo) Then
DoCmd.OpenForm "frmRequestingAgency", , , , acFormAdd, acDialog,
NewData
Response = acDataErrAdded
Else
DoCmd.Close

End If


End Sub

"winty03" wrote in message
oups.com
I got this code to open the form, but when I click no to adding it to
the database...so a mistype, it still opens the form when I click no.
What do I have wrong?
[...]
If MsgBox("The Agency Entered is not in database, would you like to
add it?", _
vbYesNo) Then

You're not comparing the result of the MsgBox function to anything.
That means that its return value will be evaluated as a truth value,
which will be understood as "false" if it is zero, "true" if it is
anything else. Your call to MsgBox will return either vbYes (= 6) or
vbNo (=7). Both of those values are nonzero, so the If statement
interprets the condition as true. Hence, the code continues into the
dependent block, and the form is opened.

Try this instead:

If MsgBox( _
"The Agency Entered is not in database, " & _
"would you like to add it?", _
vbYesNo) _
= vbYes _
Then

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

On 28 Dec 2006 14:57:14 -0800, winty03 wrote:

I got this code to open the form, but when I click no to adding it to
the database...so a mistype, it still opens the form when I click no.
What do I have wrong?

Private Sub Service_NotInList(NewData As String, Response As Integer)

If MsgBox("The Agency Entered is not in database, would you like to
add it?", _
vbYesNo) Then
DoCmd.OpenForm "frmRequestingAgency", , , , acFormAdd, acDialog,
NewData
Response = acDataErrAdded
Else
DoCmd.Close

End If

End Sub

You didn't write the MsgBox() function correctly.
You left off the criteria (=vbYes )

If MsgBox("The Agency Entered is not in database, would you like to
add it?", _
vbYesNo) = vbYes Then

DoCmd.OpenForm "frmRequestingAgency", , , , acFormAdd, acDialog,
NewData
Response = acDataErrAdded
Else
Me!Service = Me!Service.OldValue
DoCmd.Close
End If

Note: I also added another line to the Else portion of the code to
return the incorrect value back to whatever it was before you entered
it in the combo box.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

This is my code under my "not in list".....

Private Sub Service_NotInList(NewData As String, Response As Integer)
If MsgBox("The Agency Entered is not in database, would you like to
add it?", _
vbYesNo) = vbYes Then

DoCmd.OpenForm "frmRequestingAgency", , , , acFormAdd, acDialog,
NewData
Response = acDataErrAdded
Else
Me!Service = Me!Service.OldValue
DoCmd.Close
End If



Its still not working, but I think I am doing something wrong because
the first part of the code.........If MsgBox("The Agency Entered is not
in database, would you like to
add it?", _
vbYesNo) = vbYes Then

DoCmd.OpenForm "frmRequestingAgency", , , , acFormAdd, acDialog,

is all red. Any suggestions?

"winty03" wrote in message
oups.com
This is my code under my "not in list".....

Private Sub Service_NotInList(NewData As String, Response As Integer)
If MsgBox("The Agency Entered is not in database, would you like to
add it?", _
vbYesNo) = vbYes Then

DoCmd.OpenForm "frmRequestingAgency", , , , acFormAdd, acDialog,
NewData
Response = acDataErrAdded
Else
Me!Service = Me!Service.OldValue
DoCmd.Close
End If



Its still not working, but I think I am doing something wrong because
the first part of the code.........If MsgBox("The Agency Entered is
not in database, would you like to
add it?", _
vbYesNo) = vbYes Then

DoCmd.OpenForm "frmRequestingAgency", , , , acFormAdd, acDialog,

is all red. Any suggestions?

Is that If statement actually broken onto a second line after "would you
like to"? It shouldn't be. That's the only thing I can see wrong with
it. When I paste it into a module and remove that line break, so it
only breaks after the line-continuation character "_", it seems to be
okay.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)