Validation Code

[brucey54]

Hi folks, need some help with validation code!!

The validation code will be embedded into a command button, when pressed the code will check a date field within a table, if it has tomorrows date it will not update the table!!

I need something like this I think??

Private Sub Command29_Click()
If [Mealdate] = tomorrows date cancel update query
Else
Run update query
End IF

 

[brucey54]

Hi folks, need some help with validation code!!

The validation code will be embedded into a command button, when pressed the code will check a date field within a table, if it has tomorrows date it will not update the table!!

I need something like this I think??

Private Sub Command29_Click()
If [Mealdate] = tomorrows date cancel update query
Else
Run update query
End IF

This is what I have so far...

Private Sub Command29_Click ()
On Error GoTo Err_Command29_Click
If DLookUp("[ReOrderDate]" = "date() + 1", Then Cancel
Else
DoCmd.RunMacro stDocName
End If
Err_Command29_Click:
MsgBox Err.Description
Resume Exit_Command29_Click
End Sub

 

[pr2-eugin]

You could try the DateAdd function..

Private Sub Command29_Click()
    If DCount("*", "yourTableNameHere", "[ReOrderDate] = " & Format(DateAdd("d", 1, Date()), "\#mm\/dd\/yyyy\#")) <> 0 Then
        MsgBox "The date is not valid, enter a new date and try again..", vbCritical
        Me.[Mealdate] = vbNullString
    Else
       [COLOR=Green] 'Run update query[/COLOR]
    End IF
End Sub

 

[brucey54]

Thanks Paul, i need to study your code, i really need to get my head round VBA, any good books/sites you would recommend.

 

[brucey54]

Hi folks, need some help with this code, not really sure what's wrong with it!!!

Private Sub Command29_Click()
If DCount("*", "TblReOrderDate", "[ReOrderDate] = "Date()" <> 0 Then
Cancel

MsgBox "The date is not valid, enter a new date and try again..", vbCritical
Me.[Mealdate] = vbNullString

Else
stDocName = "McrReOrderDietPlanCRX"
DoCmd.RunMacro stDocName
End If
End Sub

 

[pr2-eugin]

Use the same Format I gave you in Post#3, without the DateAdd part..

Private Sub Command29_Click()
    If DCount("*", "TblReOrderDate", "[ReOrderDate] = " & Format(Date(), "\#mm\/dd\/yyyy\#")) <> 0 Then
        [COLOR=Green][B]'Cancel - Does nothing here.. No need this line..[/B][/COLOR]
        MsgBox "The date is not valid, enter a new date and try again..", vbCritical
        Me.[Mealdate] = vbNullString
    Else
        stDocName = "McrReOrderDietPlanCRX"
        DoCmd.RunMacro stDocName
    End If
End Sub

For learning the functions in VBA, I would suggest..
http://www.functionx.com/vbaccess2007/index.htm (Best one if you are a beginner)
http://www.techonthenet.com/access/functions/index_alpha.php

Good tips from Intermediate-Advanced user:
http://www.allenbrowne.com/tips.html

 

[brucey54]

Hi Folks, looking for some advice, I have a form with a textbox start date and a textbox end date, I would like to run some code that will check the date field within a table, if no dates within this range then run macro i.e.

StartDate 19/11/2013 EndDate 22/11/2013

If 19/11/2013 OR 20/11/2013 OR 21/11/2013 is not in table Then

Run Macro

Else
Msg “Date 19/11/2013 is already in table, but 20/11/2013 and 21/11/2013 have been addeded”

I’m open to ideas how to achieve this!!

 

[pr2-eugin]

Similar coding, but would requier a Loop I would say, first find the difference between the two dates, and loop through to till the end date and then provide a summary string, the following should do the trick..

Private Sub Command29_Click()
    Dim startDate As Date, endDate As Date, tmpDate As Date
    Dim loopCtr As Long, dayCtr As Long, errStr As String, validStr As String
    
    startDate = InputBox("Enter the Start Date")
    endDate = InputBox("Enter the End Date")
    dayCtr = DateDiff("d", startDate, endDate)
    For loopCtr = 0 To dayCtr
        tmpDate = DateAdd("d", loopCtr, startDate)
        If DCount("*", "TblReOrderDate", "[ReOrderDate] = " & Format(tmpDate, "\#mm\/dd\/yyyy\#")) <> 0 Then
            errStr = errStr & tmpDate & ", "
        Else
            validStr = validStr & tmpDate & ", "
            stDocName = "McrReOrderDietPlanCRX"
            DoCmd.RunMacro stDocName
        End If
    Next
    
    If Len(errStr & vbNullString) <> 0 Then
        errStr = "Date(s) : " & Left(errStr, Len(errStr)-2) & " already exists."
    End If
    
    If Len(validStr & vbNullString) <> 0 Then
        validStr = "Date(s) : " & Left(validStr, Len(validStr)-2) & " have been added." 
    End If
    
    MsgBox errStr & validStr, vbInformation
End Sub

PS: Next time, if you have a new question, create a New thread so you will attract a larger audience base.

 

[brucey54]

Good morning Paul, thanks for your help again, just working through your code, well trying to get my head round it,lol.

 

[brucey54]

Hi Paul/folks can you help me with this, I need a piece of code that will ask for the Start date and End date,

The code will then run an update query that amount of time i.e.
If StartDate 25/11/2013 EndDate 30/11/2013,

The code will run the update Query 5 times and will also increment the date field within the Update query i.e.

Output - Update Query run 5 times

Date Meal
26/11/2013 Soup
27/11/2013 Bake Potato
28/11/2013 Toast & beans
29/11/2013 Cheese Burger
30/11/2013 Chips & fish fingers


Working with your code, this is what I have so far....

Private Sub PrintLabels_Click()

Dim startDate As Date, endDate As Date, tmpDate As Date
Dim loopCtr As Long, dayCtr As Long

startDate = InputBox("Enter the Start Date")
endDate = InputBox("Enter the End Date")
dayCtr = DateDiff("d", startDate, endDate)

For loopCtr = 0 To dayCtr

tmpDate = DateAdd("d", loopCtr, startDate)

If DCount("*", "TblReOrderDate", "[ReOrderDate] = " & Format(tmpDate, "\#mm\/dd\/yyyy\#")) <> 0 Then

stDocName = "McrPrint_Meal_Labels"
DoCmd.RunMacro stDocName


Else
stDocName = "McrUpdateDietPlan"
DoCmd.RunMacro stDocName

End If

End Sub