not in list open a form

C

chriswinter

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
 
F

fredg

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
 
D

Dirk Goldgar

fredg said:
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.
 
F

fredg

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.
 
W

winty03

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
 
W

winty03

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
 
D

Dirk Goldgar

winty03 said:
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
 
F

fredg

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.
 
W

winty03

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?
 
D

Dirk Goldgar

winty03 said:
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.
 
W

winty03

Your right I didn't see that word wrap, but the DoCmd.OpenForm line is
still red and erroring..I have this code.


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


End Sub
 
D

Dirk Goldgar

winty03 said:
Your right I didn't see that word wrap, but the DoCmd.OpenForm line is
still red and erroring..I have this code.

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

Again, the only error I can see in it is that it should all be on one
line, with no line breaks. Is it broken onto multiple lines in your
original code? Any statement that is going to be broken onto multiple
lines needs to use a line-continuation character at the end of each
broken line.
 
W

winty03

Is the code suppose to close the entire form? I thought when you
clicked no that it would bring you back to your form where you entered
the data and beable to correct say a mistype. Now when I click close
it sends back out of the form I was enter data into.
 
D

Dirk Goldgar

winty03 said:
Is the code suppose to close the entire form? I thought when you
clicked no that it would bring you back to your form where you entered
the data and beable to correct say a mistype. Now when I click close
it sends back out of the form I was enter data into.

That's what that code says to do:
Else
Me!Service = Me!Service.OldValue
DoCmd.Close

That doesn't make sense to me, but that's what you had written. If you
don't want to close the form, remove the line ...
DoCmd.Close

.... from the Else clause.
 
W

winty03

perfect thanks! the code I was using before this was giving me the
form even if i clicked no to adding it. working perfect thanks! dirk
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top