Alert (message)

[Emine]

This might be a little complicated to explain. I will try and do my best. I
have a request date, and I have a check-in date. How do I get an alert
message when the user types in a checkin date that is less then 14 days from
the requested date? I want it to say something like this

"Check in date is less then 14 days from the request date do you want to
continue?" If they select yes or no (I guess I will also need some sort of
"yes/no" button in order for them to continue).

However, if they say "no" they do not want to continue, I don't want that
record saved.

This might be over my head, but I really need someone to please help me with
this.

 

[Mrs. Ugh]

Emine-
See my response to Lisa's post "Validation before adding a record" from
12/9. You can do the same thing. You might want to ask the question as soon
as they type in the check-in date; in that case you would put your code in
the On Lost Focus event of whichever date field you enter last. It should
look something like this:

Dim stMsgText as string
If [CheckInDate]<=[RequestDate] +14 Then
stMsgText = "Check in date is less then 14 days from the request date do
you want to continue?"
If MsgBox(stMsgText, vbYesNo, "Continue") = vbNo
DoCmd.CancelEvent
End If

This will bring you back to the form and let you change the date or whatever.

Jill

 

[Emine]

Hi Mrs. Ugh,

Thank you so much, I've made a little correction to your code so that it
works.

Private Sub HotelCheckIn_LostFocus()
Dim stMsgText As String
If [HotelCheckIn] <= [RequestDate] + 14 Then
stMsgText = "Check in date is less then 14 days from the request date do
you want to continue?"
If MsgBox(stMsgText, vbYesNo, "Continue") = vbNo Then
DoCmd.CancelEvent
End If
End If

End Sub


However, the pop up works just fine, but nothing happens to the form. I
want the whole form refreshed and cleared of all data should they select "NO"
for that record and not save that record.

Can this be done?

Emine-
See my response to Lisa's post "Validation before adding a record" from
12/9. You can do the same thing. You might want to ask the question as soon
as they type in the check-in date; in that case you would put your code in
the On Lost Focus event of whichever date field you enter last. It should
look something like this:

Dim stMsgText as string
If [CheckInDate]<=[RequestDate] +14 Then
stMsgText = "Check in date is less then 14 days from the request date do
you want to continue?"
If MsgBox(stMsgText, vbYesNo, "Continue") = vbNo
DoCmd.CancelEvent
End If

This will bring you back to the form and let you change the date or whatever.

Jill

This might be a little complicated to explain. I will try and do my best. I
have a request date, and I have a check-in date. How do I get an alert
message when the user types in a checkin date that is less then 14 days from
the requested date? I want it to say something like this

"Check in date is less then 14 days from the request date do you want to
continue?" If they select yes or no (I guess I will also need some sort of
"yes/no" button in order for them to continue).

However, if they say "no" they do not want to continue, I don't want that
record saved.

This might be over my head, but I really need someone to please help me with
this.

 

[John W. Vinson]

This might be a little complicated to explain. I will try and do my best. I
have a request date, and I have a check-in date. How do I get an alert
message when the user types in a checkin date that is less then 14 days from
the requested date? I want it to say something like this

"Check in date is less then 14 days from the request date do you want to
continue?" If they select yes or no (I guess I will also need some sort of
"yes/no" button in order for them to continue).

However, if they say "no" they do not want to continue, I don't want that
record saved.

This might be over my head, but I really need someone to please help me with
this.

This is not difficult... you will need to dip your toes into the VBA
Programming pool, but only in the shallow end! <g>

Let's say you are entering data on a form with controls named CheckinDate and
RequestDate. Open the form in design view, view its Properties, and view the
properties of the CheckinDate textbox. On the Events tab find the "Before
Update" event. Click the ... icon by it and choose Code Builder. Access will
give you two lines of code (the first and last in my example); you fill the
rest in:

Private Sub CheckinDate_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer ' define a variable to hold user's response
If Not IsNull(Me!RequestDate) Then ' see if there IS a RequestDate
' If so, see if the CheckInDate is less than 14 days after the
' RequestDate. Use -14 if you want to see if it's less than 14
' days *before* - not sure which you intended!
If Me!CheckInDate < DateAdd("d", 14, Me!RequestDate) Then
iAns = MsgBox("Check in date is less then 14 days from the request date" _
& " do you want to continue?", vbYesNo)
If iAns = vbNo Then
Cancel = True ' cancel the addition
Me!CheckInDate.Undo ' erase the date the user entered
End If
End If
End Sub

 

[Emine]

OMG IT WORKED! THANK YOU SOOOOOOOO MUCH!!!!

This is not difficult... you will need to dip your toes into the VBA
Programming pool, but only in the shallow end! <g>

Let's say you are entering data on a form with controls named CheckinDate and
RequestDate. Open the form in design view, view its Properties, and view the
properties of the CheckinDate textbox. On the Events tab find the "Before
Update" event. Click the ... icon by it and choose Code Builder. Access will
give you two lines of code (the first and last in my example); you fill the
rest in:

Private Sub CheckinDate_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer ' define a variable to hold user's response
If Not IsNull(Me!RequestDate) Then ' see if there IS a RequestDate
' If so, see if the CheckInDate is less than 14 days after the
' RequestDate. Use -14 if you want to see if it's less than 14
' days *before* - not sure which you intended!
If Me!CheckInDate < DateAdd("d", 14, Me!RequestDate) Then
iAns = MsgBox("Check in date is less then 14 days from the request date" _
& " do you want to continue?", vbYesNo)
If iAns = vbNo Then
Cancel = True ' cancel the addition
Me!CheckInDate.Undo ' erase the date the user entered
End If
End If
End Sub