Msg Box Error For Dates

[Leebarley]

Hi All,

I am trying to create a database where customers have specific delivery days ie Mon, Wed, Fri or Tue, Thur depending on the customer. I have setup a customer table which shows which days this customer has deliveries and created another table for order entry which pulls the customer info from the customer table but then has a date field to pick the delivery date.
What I would like to do is have a message box to appear in form for this table to show an error if the date chosen is not a specified delivery day for this customer.
I have attached a basic sample of the database I am trying to accomplish this on if needed, I assume that there will be some VBA coding do this somehow which I am not that familiar with.

Hope this makes sense as this is my first time on this forum?

Any help would be grately appreciated.

Many thanks,

Lee

 

[Ranman256]

Note: best practice, dont use spaces on field names. It will make queries, forms, life , easier.

in the DATE BEFOREUPDATE event, get the valid days from the combo box, then test what the user entered....

Private Sub Delivery_Date_BeforeUpdate(Cancel As Integer)
Dim vValidDays, vDow

vValidDays = CustomerID.Column(2)
vdow = Format(Controls("Delivery Date"), "dddd")

If InStr(vValidDays, vdow) = 0 Then
   MsgBox "Must be: " & vbCrLf & vValidDays, vbCritical, "Invalid delivery day"
   Cancel = True
End If
End Sub

 

[Leebarley]

Hi Ranman256,

Many thanks for responding so quickly.I have copied and pasted your code into the BEFOREUPDATE event in the DATE field in the form but this does not seem do anything when I run the form? I have looked at the coding and think I understand what it is relating too but cannot see anything that look incorrect although my knowledgement of VBA is very limited.
If you have any further ideas I would be grateful.

Many thanks

Lee Barley.

 

[Leebarley]

Hi Ranman256,

Managed to get this working with a slight tweak to the code (see below), excellent does exactly what was needed. Many thanks for your help.


Private Sub Delivery_Date_BeforeUpdate(Cancel As Integer)
Dim vValidDays As String
Dim vDow As String

vValidDays = CustomerID.Column(2)
vdow = Format(Controls("Delivery Date"), "dddd")

If InStr(vValidDays, vdow) = 0 Then
MsgBox "Must be: " & vbCrLf & vValidDays, vbCritical, "Invalid delivery day"

End If
End Sub

Again many thanks,

Lee.