Validation Rule? question

[Guest]

I think what I want to ask has to do with the validation rule in my table, but it may be more complex than that. Could anyone help out on this?
My database is designed to track the maintenance activities on around 50 cars in our fleet. What I want to tell it is...
When entering data (on a form that goes directly to the table), in the Odometer Reading field I want it to not allow and alert the user if the entry that they put in is <= the maximum odometer reading already in the database for that particular vehicle.

I can't put a "no duplicates" on the odometer read field, because of the fact that there are 50 cars and they may end up with the same od read on more than one car... but I'm not sure how to tell it to look at the unique identifier (VIN #) and then look at the od read ...???
I'm rather a novice at the coding, SQL, and stuff like that, so if I need that, I'm usually asking for help... but I'm learning!!! Thanks to this group!!!

Thanks in advance for your help on this one. I'm almost to a point where we can start using this, but I still have some fine tuning on a couple of things. This one solves a big portion of it.

 

[Allen Browne]

Use the BeforeUpdate event procedure of the form where entries are made.

Something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant

If Me.NewRecord And Not (IsNull(Me.[CarID]) Or IsNull(Me.[Odometer]))
Then
strWhere = "([CarID] = " & Me.[CarID] & ") AND ([Odometer] = " &
Me.[Odometer]) & ")"
varResult = DLookup("Odometer", "MyTable", strWhere)
If varResult > Me.[Odometer] Then
MsgBox "You have a record for this car at " & Me.[Odometer]
Cancel = True
End If
End If
End Sub


Note: If CarID is a Text type field, you need extra quotes, i.e.:
strWhere = "([CarID] = """ & Me.[CarID] & """) AND ([Odometer] = " &
Me.[Odometer]) & ")"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I think what I want to ask has to do with the validation rule in my table,

but it may be more complex than that. Could anyone help out on this?

My database is designed to track the maintenance activities on around 50

cars in our fleet. What I want to tell it is...

When entering data (on a form that goes directly to the table), in the

Odometer Reading field I want it to not allow and alert the user if the
entry that they put in is <= the maximum odometer reading already in the
database for that particular vehicle.

I can't put a "no duplicates" on the odometer read field, because of the

fact that there are 50 cars and they may end up with the same od read on
more than one car... but I'm not sure how to tell it to look at the unique
identifier (VIN #) and then look at the od read ...???

I'm rather a novice at the coding, SQL, and stuff like that, so if I need

that, I'm usually asking for help... but I'm learning!!! Thanks to this
group!!!

Thanks in advance for your help on this one. I'm almost to a point where

we can start using this, but I still have some fine tuning on a couple of
things. This one solves a big portion of it.

 

[Guest]

I've copied over the code and modified it to fit my fields and the appropriate table. I have an error message occuring on the first If statement. It's stating that it is expecting a Then or Goto at the Me.NewRecord portion of the code. ???Help??

----- Allen Browne wrote: ----

Use the BeforeUpdate event procedure of the form where entries are made

Something like this

Private Sub Form_BeforeUpdate(Cancel As Integer
Dim strWhere As Strin
Dim varResult As Varian

If Me.NewRecord And Not (IsNull(Me.[CarID]) Or IsNull(Me.[Odometer])
The
strWhere = "([CarID] = " & Me.[CarID] & ") AND ([Odometer] = "
Me.[Odometer]) & ")
varResult = DLookup("Odometer", "MyTable", strWhere
If varResult > Me.[Odometer] The
MsgBox "You have a record for this car at " & Me.[Odometer
Cancel = Tru
End I
End I
End Su


Note: If CarID is a Text type field, you need extra quotes, i.e.
strWhere = "([CarID] = """ & Me.[CarID] & """) AND ([Odometer] = "
Me.[Odometer]) & ")

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.htm
Reply to group, rather than allenbrowne at mvps dot org

I think what I want to ask has to do with the validation rule in my table

but it may be more complex than that. Could anyone help out on this

My database is designed to track the maintenance activities on around 5

cars in our fleet. What I want to tell it is..

When entering data (on a form that goes directly to the table), in th

Odometer Reading field I want it to not allow and alert the user if th
entry that they put in is <= the maximum odometer reading already in th
database for that particular vehiclefact that there are 50 cars and they may end up with the same od read o
more than one car... but I'm not sure how to tell it to look at the uniqu
identifier (VIN #) and then look at the od read ...??

I'm rather a novice at the coding, SQL, and stuff like that, so if I nee

that, I'm usually asking for help... but I'm learning!!! Thanks to thi
group!!we can start using this, but I still have some fine tuning on a couple o
things. This one solves a big portion of it

 

[Guest]

A compile error occured on the first If statment. It says that at the Me.NewRecord it's expecting Then or GoTo. ???HELP???

----- Allen Browne wrote: -----

Use the BeforeUpdate event procedure of the form where entries are made.

Something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant

If Me.NewRecord And Not (IsNull(Me.[CarID]) Or IsNull(Me.[Odometer]))
Then
strWhere = "([CarID] = " & Me.[CarID] & ") AND ([Odometer] = " &
Me.[Odometer]) & ")"
varResult = DLookup("Odometer", "MyTable", strWhere)
If varResult > Me.[Odometer] Then
MsgBox "You have a record for this car at " & Me.[Odometer]
Cancel = True
End If
End If
End Sub


Note: If CarID is a Text type field, you need extra quotes, i.e.:
strWhere = "([CarID] = """ & Me.[CarID] & """) AND ([Odometer] = " &
Me.[Odometer]) & ")"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I think what I want to ask has to do with the validation rule in my table,

but it may be more complex than that. Could anyone help out on this?

My database is designed to track the maintenance activities on around 50

cars in our fleet. What I want to tell it is...

When entering data (on a form that goes directly to the table), in the

Odometer Reading field I want it to not allow and alert the user if the
entry that they put in is <= the maximum odometer reading already in the
database for that particular vehicle.fact that there are 50 cars and they may end up with the same od read on
more than one car... but I'm not sure how to tell it to look at the unique
identifier (VIN #) and then look at the od read ...???

I'm rather a novice at the coding, SQL, and stuff like that, so if I need

that, I'm usually asking for help... but I'm learning!!! Thanks to this
group!!!we can start using this, but I still have some fine tuning on a couple of
things. This one solves a big portion of it.

 

[Allen Browne]

The code assumes that the form is bound to a table.

You also need to replace "CarID" and "Odometer" etc with the actual names of
your fields.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

A compile error occured on the first If statment. It says that at the

Me.NewRecord it's expecting Then or GoTo. ???HELP???

----- Allen Browne wrote: -----

Use the BeforeUpdate event procedure of the form where entries are made.

Something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant

If Me.NewRecord And Not (IsNull(Me.[CarID]) Or IsNull(Me.[Odometer]))
Then
strWhere = "([CarID] = " & Me.[CarID] & ") AND ([Odometer] = " &
Me.[Odometer]) & ")"
varResult = DLookup("Odometer", "MyTable", strWhere)
If varResult > Me.[Odometer] Then
MsgBox "You have a record for this car at " & Me.[Odometer]
Cancel = True
End If
End If
End Sub


Note: If CarID is a Text type field, you need extra quotes, i.e.:
strWhere = "([CarID] = """ & Me.[CarID] & """) AND ([Odometer] = " &
Me.[Odometer]) & ")"

I think what I want to ask has to do with the validation rule in my

table,
but it may be more complex than that. Could anyone help out on this?

My database is designed to track the maintenance activities on

around 50
cars in our fleet. What I want to tell it is...

When entering data (on a form that goes directly to the table), in

the
Odometer Reading field I want it to not allow and alert the user if the
entry that they put in is <= the maximum odometer reading already in the
database for that particular vehicle. of the
fact that there are 50 cars and they may end up with the same od read on
more than one car... but I'm not sure how to tell it to look at the unique
identifier (VIN #) and then look at the od read ...???

I'm rather a novice at the coding, SQL, and stuff like that, so if

I need
that, I'm usually asking for help... but I'm learning!!! Thanks to this

group!!!

where
we can start using this, but I still have some fine tuning on a couple of
things. This one solves a big portion of it.