Ok, here is the code:
Private Function Oldest_Bill_BeforeUpdate(Cancel As Integer)
Dim varMaxDate As Variant
varMaxDate = DMax("[Oldest Bill]", "BR Daily Inventory Table", "[Branch
Code] = """ & Me.[Branch Code] & """")
If Not IsNull(varMaxDate) Then
If Me.[Oldest Bill] < varMaxDate Then
MsgBox "Date Entered For Branch " & Me.[Branch Code] & " Must be Equal to or
Greater than " & varMaxDate
Cancel = True
End If
End If
End Sub
This is the error: (same as before)
* The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
* There may have been an error evluating the function, event or macro.
It's not like a run time error, it's just a message box that pops up so it
doesn't refer to a specific line. When I click for help and I get this:
Visual Basic for Applications (VBA) encountered a problem while attempting
to access a property or method. The problem may be one of the following:
A reference is missing.
For help restoring missing references, see the Microsoft Knowledge Base
article 283806.
An Expression is misspelled.
Check all expressions used in event properties for correct spelling.
A user-defined function is declared as a sub or as a private function in a
module.
Expressions can resolve a user-defined function only if the function is
declared as one of the following:
A public function in a module
A public or private function in a code module of the current form or report
Security in Access is set to Medium or High and the Microsoft Jet 4.0 SP8
update is not installed.
A more recent verion of Jet 4.0 must be installed for Access to function
properly when security is set to Medium or High. To obtain the latest version
of Microsoft Jet, go to Windows Update.
Thanks.
--
Sherry N.
Klatuu said:
No, just send me the code as it is now. Tell me what error you are getting
and point out the line where the error occurs.
--
Dave Hargis, Microsoft Access MVP
:
No, sorry. I was just describing where I put the code so you would know
whether or not it was in the right place.
Would it help if I gave you all of the code I have in the form? Maybe it
conflicts with some other code I have going on?
Thanks again, so much.
--
Sherry N.
:
So is it working or not?
--
Dave Hargis, Microsoft Access MVP
:
Hello,
I did put the code in the Before Update event of the text box properties.
I clicked the ... next the the Before Update property of the Oldest Bill
text box and choose code builder, I pasted the code, took out the Private Sub
Oldest_Bill_BeforeUpdate(Cancel As Integer) b/c I was getting an ambigous
error.
Again much appreciated.
--
Sherry N.
:
Did you put the code directly in the Before Update event text box in the
Properties Dialog for the control or is it in the form module?
I think you may have put it in the wrong place.
--
Dave Hargis, Microsoft Access MVP
:
I am sorry. I really appreciate all of your help. I put the code in the
Before Update of the Oldest Bill control and got the same error:
The expression On Open you entered as the event property setting produced
the following error: Procedure declaration does not match description of
event or procedure having the same name.
* The expression may not result in the name of a macro, the name of a
user-defined function, or [Event Procedure].
* There may have been an error evluating the function, event or macro.
Thanks.
--
Sherry N.
:
It goes in the Before Update of the Oldest Date control.
If you have an error, please post back with the error number and point out
the line it occurs on. If you are not getting an error, but rather not
getting the desired results, please be more descriptive. I can't see you
monitor from here <g>
--
Dave Hargis, Microsoft Access MVP
:
I'm sorry I am still having trouble? I put this code in the before update
property of the Branch Code text box.
Private Function Branch_Code_BeforeUpdate(Cancel As Integer)
Dim varMaxDate As Variant
varMaxDate = DMax("[Oldest Bill]", "BR Daily Inventory Table", "[Branch
Code] = """ & Me.[Branch Code] & """")
If Not IsNull(varMaxDate) Then
If Me.[Oldest Bill] < varMaxDate Then
MsgBox "Date Entered For Branch " & Me.[Branch Code] & " Must be Equal to or
Greater than " & varMaxDate
Cancel = True
End If
End If
End Sub
--
Thanks again,
Sherry N.
:
Okay, got it. It only take a tweek to the code I posted yesterday to filter
the DMax on the branch code:
Private Function txtAAA_BeforeUpdate(Cancel As Integer)
Dim varMaxDate As Variant
varMaxDate = DMax("[Oldest Date]","TableNameHere", "[Branch Code] = """
& Me.[Branch Code] & """")
If Not IsNull(varMaxDate) Then
If Me.[Oldest Date] < varMaxDate Then
MsgBox "Date Entered For Branch " & Me.[Branch Code ] & " Must
be Equal to or Greater than " & varMaxDate
Cancel = True
End If
End If
End Sub
--
Dave Hargis, Microsoft Access MVP
:
I'm sorry I know this must be confusing. It is true that AAA could be 7/7 and
BBB could be 7/6
The user will enter one record for each branch every day
Today user enters:
AAA in branch code and 7/7 in oldest date
BBB in branch code and 7/6 in oldest date
CCC in branch code and 7/8 in oldest date
etc.
When the user enters tomorrows records, I don't want them to be able to enter
7/6 for AAA or, 7/5 for BBB or 7/7 for CCC since the previous record for
those carriers already have later dates stored.
Hope this clarifies.
Thanks again.
--
Sherry N.
:
So let me see if I understand the rules correctly.
The oldest date is by Branch Code?
So that I could have 7/7 for branch AAA and 7/6 for branch BBB?
I don't quite understand the statement about
oldest date field for AAA today
Does that mean if the oldest date today is 7/7 I can't enter a date less
than that today, but tomorrow I could enter 7/5?
That doesn't seem right based on your statment that the user entered 7/7
yesterday, so today has to be at least 7/7?
Pardon my confusion.
If the only difference between what I posted and it needing to be by Branch
Code, then you only need to add that filtering to the DMax function I posted
yesterday.
--
Dave Hargis, Microsoft Access MVP
:
I'm sorry. I tried this several ways and I can't get it to work for me. Maybe
if I give more specific info.
I have a text box named Branch Code - the user enters the branch code
I have a text box named Oldest Date - the user enters the oldest date
I want to restrict the user from entering a date in the oldest date field
that is less than the previous date entered for the branch
For example:
Yesterday the user entered AAA in the branch field and entered 7/7 as the
oldest date so the user cannot enter 7/6 in the oldest date field for AAA
today.
Much appreciated
--
Sherry N.
:
Hello,
I want to restrict users from entering dates in a date field on my form if
the date is less than the one previously entered for a specific record.
For example, if I enter 7/9/08 in the date working on field for AAA today I
don't want the user to be able to enter 7/8/08 in the date working on field
for AAA tomorrow.
Much appreciated.
)