Need to do a date validation on a date entered into a form against two fields in another table

[Lori F]

I have a screen where I am entering transactions. I also have a table that is contains one record with a BegDate and EndDate. I want to make sure that when the date is entered (doing an event procedure On Change for that transaction date field) confirms that the TranDate is >= the BegDate and <= the EndDate. If not, I want to show a message box and put the focus back onto the TranDate field. I'm not very knowledgeable regarding Event Procedures. Can anyone help, or is there a better way for me to do this? I don't want to put a validation on the table itself because records from prior years need to exist. I just want to make sure that new records don't get entered into a date range that is currently not being processed.

[orange]

Can you provide an example or 2 of your BegDate and EndDate a well as TranDate?
Also, can you describe "transaction" as it applies to your code?
Why OnChange ? Perhaps consider BeforeUpdate?
Are you looking for a small function?
Are all the BegDate and EndDate values populated?
Looking for a little more context.

[June7]

Validation in table (if you mean setting ValidationRule) would not impact existing records but I never do these sort of settings in table anyway. You can use DLookup expressions to pull those BegDate and EndDate values into textboxes on form then reference those textboxes in TranDate textbox ValidationRule property and use ValidationText property to notify users. No VBA needed. Otherwise, code in textbox BeforeUpdate event to validate input. That code could either open a recordset or use DLookup expressions to pull BegDate and EndDate values or again have textboxes on form with DLookup expressions and code references those.

[Welshgasman]

As you are using these dates in your other posts, look them up once when you open the dB and put them into tempvars. Then use those from then on. The after update event of the control, or the before update of the form would be better events I believe.

[Lori F]

First table is BegEndDates. It will only ever have one record in it. It has BegDate and EndDate. Values respectively right now are 01/01/2023 and 12/31/2023. In the Transactions table, I'm entering various data. The date field in that table is TranDate. I open up the form "Enter New Transactions" and when I enter a TranDate, I want to verify that the date that I entered is within the date range I mentioned. I have several other fields on the form, but TranDate is the first field entered. So if I enter 01/05/2023, I want the focus to move to the next field and allow me to continue with my data entry. If, however, I enter 12/31/2022 or 01/05/2024, I want to give a message that indicates the date is outside the current processing period and then place the focus back onto the TranDate field. Here's what I have so far, but not sure I'm even on the right direction. I put the comment where I want to set the focus, but haven't figured out that command yet. If I test what I have so far, I get an error that says Run-time error '2458': The control number you specified is greater than the number of controls. The first If line is in yellow when I put it into debug. Sadly, I get the Run Time error even if I enter a valid date. UGH!

Dim LDate As Date
Dim RDate As Date
LDate = DLookup("EndDate", "BegEndDates")
RDate = DLookup("EndDate", "BegEndDates")


If Forms("Enter New Transactions").Controls(TranDate).Value < LDate Then
    MsgBox "TranDate is outside of the valid processing period."
    'set focus to TranDate on form'
End If


If Forms("Enter New Transactions").Controls(TranDate).Value > RDate Then
    MsgBox "TranDate is outside of the valid processing period."
    'set focus to TranDate on form'
End If


End Sub

[Micron]

DLookup without criteria returns a random record (usually the first it seems, but no guarantee of that) but with only 1 record, no issue there. However you are using the same field for both dates. Also, if you use that Controls syntax you must pass a variable without quotes, or the name of the control in "quotes". Bad idea to have spaces and special characters (save for underscore) in object names. You should be able to get away with the syntax of your forms reference, but not if you use the other syntax -

Forms!your form name here

will not work. Now you need [object delimiters for objects that have spaces in their names] like that long one.

[Lori F]

Shoot, I hadn't even seen that typo yet. So I fixed it to BegDate and EndDate. And yes, there will only ever be one record in that table.
I have fixed all those issues and made changes as you suggested removing spaces and special characters. The date validation does now work as expected giving the error when it is out of range and continuing when it is in range. The only thing I have left to figure out is how to set the focus back to the TranDate field when the error invoked. I cannot seem to find that command anywhere.

Private Sub TranDate_BeforeUpdate(Cancel As Integer)


Dim LDate As Date
Dim RDate As Date
LDate = DLookup("BegDate", "BegEndDates")
RDate = DLookup("EndDate", "BegEndDates")


If Forms("EnterNewTransactions").Controls(1).Value < LDate Then
    MsgBox "TranDate is outside of the valid processing period."
    'set focus to TranDate on form'
End If


If Forms("EnterNewTransactions").Controls(1).Value > RDate Then
    MsgBox "TranDate is outside of the valid processing period."
    'set focus to TranDate on form'
End If


End Sub

[davegri]

Taking into account what Micron and others have said, but keeping it as simple as possible, try this code:

Option Compare Database
Option Explicit


Private Sub TranDate_BeforeUpdate(Cancel As Integer)
    Dim LDate As Date
    Dim RDate As Date
    LDate = DLookup("BegDate", "BegEndDates")
    RDate = DLookup("EndDate", "BegEndDates")
    
    
    If Forms("Enter New Transactions").Controls("TranDate") < LDate Then
        MsgBox "TranDate is outside of the valid processing period."
        Cancel = True
        Me.Undo
    End If
    
    
    If Forms("Enter New Transactions").Controls("TranDate") > RDate Then
        MsgBox "TranDate is outside of the valid processing period."
        Cancel = True
        Me.Undo
    End If
End Sub

[June7]

Need to use the Cancel variable.

If Me.TranDate < LDate Or Me.TranDate > RDate Then
MsgBox "TranDate is outside of the valid processing period."
Cancel = True
Me.TranDate.Undo
End If

https://learn.microsoft.com/en-us/of...reupdate-event

Or use ValidationRule and ValidationText properties and forego VBA.

[Lori F]

That worked! I was needing those 2 lines 'cancel =true and me.undo'. Thanks so much! Thanks for your patience with this novice. Have a blessed day.

[Micron]

I personally would not use index value as in Controls(1) because 1 is a pointer and what pointers point to are subject to being changed. I'd use names instead but then again I'd use Forms!frmMyFormName.MyControlName
and not
Forms("frmMyFormName").Controls("myControlName") but that's just me.