vbYesNo If Expression for MsgBox

[rebecky via AccessMonster.com]

I have this code in the before update event of a text box called
"PositionType" on a subform containing information about Positions, which is
in a Main Form containing information about employers. It works just fine
but I need to have a vbyesno msg box where if
they say yes they exit sub and are allowed to continue entering the position
and if they say no, then cancel and undo. I cannot figure out how to get the
"If vbyes exit sub" part into the expression.....
If Not IsNull(DLookup("[PositionType]", "JobOrderInfo", "[EmployerID] = " &
Me.EmployerID & " AND [Positiontype] = """ & Me.PositionType & """")) Then
MsgBox [Form_EmployerPositions1].Employer_Name & " Has An Existing
Posting For " & Me.PositionType & "Is This the Exact Same Position? If not,
continue adding, if so please just change the number of slots", vbYesNo, "TED
version 2.1"

Cancel = True
Me.Undo
End If

Can anyone help?

Thank you

 

[Klatuu]

If Not IsNull(DLookup("[PositionType]", "JobOrderInfo", "[EmployerID] =
" & Me.EmployerID & " AND [Positiontype] = """ & Me.PositionType & """"))
Then
If MsgBox ([Form_EmployerPositions1].Employer_Name & " Has An
Existing Posting For " & Me.PositionType & _
"Is This the Exact Same Position? If not,continue adding, if
so please just change the number of slots", vbYesNo, "TED version 2.1") =
vbNo Then
Cancel = True
Me.Undo
Else
Exit Sub
End If
End If

The above code, if I understand your question, will Cancel the update if the
user answers no.

 

[BruceM]

You need to test the choice the user makes.

Dim strMsg as String, strTitle as String

strMsg = [Form_EmployerPositions1].Employer_Name & _
" Has An Existing Posting For " & _
Me.PositionType & _
"Is This the Exact Same Position? If not, continue adding, " & _
"if so please just change the number of slots"
strTitle = "TED version 2.1"

If Not IsNull(DLookup("[PositionType]", "JobOrderInfo", "[EmployerID] = " &
_
Me.EmployerID & " AND [Positiontype] = """ & Me.PositionType & """"))
Then
If MsgBox strMsg, vbYesNo, strTitle = vbNo Then
Cancel = True
Me.Undo
End If
End If

Use of the Dim strMsg as String etc. is optional, but I find it makes the
code easier to read.

 

[scott04]

You can always try something like this.....

Dim intResponse As String
intResponse = MsgBox("Whatever you want the message to state type here?",
vbYesNo, "Whatever you would like the title enter here")
If intResponse = vbYes Then

DoCmd.enter your commands here
Else
Cancel = True
me.undo

End If
End Sub

 

[BruceM]

I'm curious as to why the Exit Sub line if vbYes, since there is no more
code after that (other than closing the If functions).

If Not IsNull(DLookup("[PositionType]", "JobOrderInfo", "[EmployerID] =
" & Me.EmployerID & " AND [Positiontype] = """ & Me.PositionType & """"))
Then
If MsgBox ([Form_EmployerPositions1].Employer_Name & " Has An
Existing Posting For " & Me.PositionType & _
"Is This the Exact Same Position? If not,continue adding,
if so please just change the number of slots", vbYesNo, "TED version 2.1")
= vbNo Then
Cancel = True
Me.Undo
Else
Exit Sub
End If
End If

The above code, if I understand your question, will Cancel the update if
the user answers no.

I have this code in the before update event of a text box called
"PositionType" on a subform containing information about Positions, which
is
in a Main Form containing information about employers. It works just
fine
but I need to have a vbyesno msg box where if
they say yes they exit sub and are allowed to continue entering the
position
and if they say no, then cancel and undo. I cannot figure out how to get
the
"If vbyes exit sub" part into the expression.....
If Not IsNull(DLookup("[PositionType]", "JobOrderInfo", "[EmployerID] = "
&
Me.EmployerID & " AND [Positiontype] = """ & Me.PositionType & """"))
Then
MsgBox [Form_EmployerPositions1].Employer_Name & " Has An Existing
Posting For " & Me.PositionType & "Is This the Exact Same Position? If
not,
continue adding, if so please just change the number of slots", vbYesNo,
"TED
version 2.1"

Cancel = True
Me.Undo
End If

Can anyone help?

Thank you

 

[rebecky via AccessMonster.com]

Thanks a million! Works perfectly and I can FINALLY move on!!!

If Not IsNull(DLookup("[PositionType]", "JobOrderInfo", "[EmployerID] =
" & Me.EmployerID & " AND [Positiontype] = """ & Me.PositionType & """"))
Then
If MsgBox ([Form_EmployerPositions1].Employer_Name & " Has An
Existing Posting For " & Me.PositionType & _
"Is This the Exact Same Position? If not,continue adding, if
so please just change the number of slots", vbYesNo, "TED version 2.1") =
vbNo Then
Cancel = True
Me.Undo
Else
Exit Sub
End If
End If

The above code, if I understand your question, will Cancel the update if the
user answers no.

I have this code in the before update event of a text box called
"PositionType" on a subform containing information about Positions, which

[quoted text clipped - 23 lines]

Thank you

 

[rebecky via AccessMonster.com]

Just one more tiny cosmetic detail.....How do I get a space between the
PositionType and "Is........
Right now it goes like this:
211 Info already has a position listing for Office AssistantIs this..........

Thank you
rebecky
Me.PositionType & _"Is This the Exact Same Position?

If Not IsNull(DLookup("[PositionType]", "JobOrderInfo", "[EmployerID] =
" & Me.EmployerID & " AND [Positiontype] = """ & Me.PositionType & """"))
Then
If MsgBox ([Form_EmployerPositions1].Employer_Name & " Has An
Existing Posting For " & Me.PositionType & _
"Is This the Exact Same Position? If not,continue adding, if
so please just change the number of slots", vbYesNo, "TED version 2.1") =
vbNo Then
Cancel = True
Me.Undo
Else
Exit Sub
End If
End If

The above code, if I understand your question, will Cancel the update if the
user answers no.

I have this code in the before update event of a text box called
"PositionType" on a subform containing information about Positions, which

[quoted text clipped - 23 lines]

Thank you

 

[BruceM]

Just add the space to the text:
PositionType & " Is........
or maybe
PositionType & ". Is........

Note that the underscore is just to make the line easier to read. These are
exactly the same in terms of output:
PositionType & ". Is........

PositionType & _
". Is........

I illustrated this in my first response. The syntax is ampersand space
underscore (& _), then go to a new line. The underscore is simply a line
continuation character that tells Access there is more on the next line.
Without the underscore the text would just keep marching rightward across
your screen, forcing you to scroll back and forth to read long text.

BTW, you can force the message box text onto a new line by using the vbCrLf
(Carriage Return/Line Feed) constant:
PositionType & "." & vbCrLf & _
"Is........

This forces the line starting with "Is" to the next line in the message box.
Note that I added the period after PositionType before going to a new line.

As I understand you can use vbNewLine instead of vbCrLf.

Just one more tiny cosmetic detail.....How do I get a space between the
PositionType and "Is........
Right now it goes like this:
211 Info already has a position listing for Office AssistantIs
this..........

Thank you
rebecky
Me.PositionType & _"Is This the Exact Same Position?

If Not IsNull(DLookup("[PositionType]", "JobOrderInfo", "[EmployerID] =
" & Me.EmployerID & " AND [Positiontype] = """ & Me.PositionType & """"))
Then
If MsgBox ([Form_EmployerPositions1].Employer_Name & " Has An
Existing Posting For " & Me.PositionType & _
"Is This the Exact Same Position? If not,continue adding,
if
so please just change the number of slots", vbYesNo, "TED version 2.1") =
vbNo Then
Cancel = True
Me.Undo
Else
Exit Sub
End If
End If

The above code, if I understand your question, will Cancel the update if
the
user answers no.

I have this code in the before update event of a text box called
"PositionType" on a subform containing information about Positions,
which

[quoted text clipped - 23 lines]

Thank you

 

[Klatuu]

It was an example as requested by the OP. I have no idea what other code
may be in her procedure.

I'm curious as to why the Exit Sub line if vbYes, since there is no more
code after that (other than closing the If functions).

If Not IsNull(DLookup("[PositionType]", "JobOrderInfo", "[EmployerID]
= " & Me.EmployerID & " AND [Positiontype] = """ & Me.PositionType &
"""")) Then
If MsgBox ([Form_EmployerPositions1].Employer_Name & " Has An
Existing Posting For " & Me.PositionType & _
"Is This the Exact Same Position? If not,continue adding,
if so please just change the number of slots", vbYesNo, "TED version
2.1") = vbNo Then
Cancel = True
Me.Undo
Else
Exit Sub
End If
End If

The above code, if I understand your question, will Cancel the update if
the user answers no.

I have this code in the before update event of a text box called
"PositionType" on a subform containing information about Positions,
which is
in a Main Form containing information about employers. It works just
fine
but I need to have a vbyesno msg box where if
they say yes they exit sub and are allowed to continue entering the
position
and if they say no, then cancel and undo. I cannot figure out how to
get the
"If vbyes exit sub" part into the expression.....
If Not IsNull(DLookup("[PositionType]", "JobOrderInfo", "[EmployerID] =
" &
Me.EmployerID & " AND [Positiontype] = """ & Me.PositionType & """"))
Then
MsgBox [Form_EmployerPositions1].Employer_Name & " Has An Existing
Posting For " & Me.PositionType & "Is This the Exact Same Position? If
not,
continue adding, if so please just change the number of slots", vbYesNo,
"TED
version 2.1"

Cancel = True
Me.Undo
End If

Can anyone help?

Thank you

 

[rebecky via AccessMonster.com]

Thank you. This will come in very handy indeed!

Just add the space to the text:
PositionType & " Is........
or maybe
PositionType & ". Is........

Note that the underscore is just to make the line easier to read. These are
exactly the same in terms of output:
PositionType & ". Is........

PositionType & _
". Is........

I illustrated this in my first response. The syntax is ampersand space
underscore (& _), then go to a new line. The underscore is simply a line
continuation character that tells Access there is more on the next line.
Without the underscore the text would just keep marching rightward across
your screen, forcing you to scroll back and forth to read long text.

BTW, you can force the message box text onto a new line by using the vbCrLf
(Carriage Return/Line Feed) constant:
PositionType & "." & vbCrLf & _
"Is........

This forces the line starting with "Is" to the next line in the message box.
Note that I added the period after PositionType before going to a new line.

As I understand you can use vbNewLine instead of vbCrLf.

Just one more tiny cosmetic detail.....How do I get a space between the
PositionType and "Is........

[quoted text clipped - 32 lines]