Variable not defined

[AHopper]

I am including code below that is giving me a problem
that I don't understand. It is included in the
BeforeUpdate event of a text box. When I run the code I
get the following error:
Compile error:
Variable no defined
When I debug "PreviousSkidExists is highligted in the
If PreviousSkidExists >=1 Then statement

Just before this is
If Nz(PreviousSkidExists) < 1 Then
and it accepts this (or it seems to)

I have tried
If Nz(PreviousSkidExists) >=1 Then but I still get the
compile error.
I have placed Rem in front of the code involved.

I think I have declared the variable at the beginning
Dim PreviousSkidExists As Single

I know that "PreviousSkidExists" is getting a value
because I have used a MsgBox to display the results. The
answer was 1 for my test information.

There is more code at the end that I did not include
since it is not related to this problem.

I have spent about 5 hours on this problem and I am still
where I started.

Thank you for any help you can give.

Allan


Private Sub VIJLowNumber_BeforeUpdate(Cancel As Integer)
Dim Answer As String
Dim LowDuplicate As String
Dim HighDuplicate As String
Dim SkidExists As Integer
Dim SkidLowRange As Single
Dim SkidHighRange As Single
Dim JobExists As Single
Dim JobHighRange As Single
Dim JobHighSkid As Single
Dim PreviousSkidExists As Single
Dim PreviousSkidHighRange As Single

If Nz(Me.JobNumber) >= 1 Then
JobExists = (Nz(DCount("(Nz
([JobNumber]))", "PressProduction", "[JobNumber]=" & Nz
(Me.JobNumber) & "And [PassNumber]=" & 1)))
End If
If Nz(JobExists) >= 1 And Nz(Me.VIJLowNumber) >= 1 Then
LowDuplicate = (Nz(DCount("(Nz
([VIJLowNumber]))", "PressProduction", "[JobNumber]=" & Nz
(Me.JobNumber) & "And [VIJLowNumber]=" & Nz
(Me.VIJLowNumber) & "And [PassNumber]=" & 1)))
HighDuplicate = (Nz(DCount("(Nz
([VIJHighNumber]))", "PressProduction", "[JobNumber]=" &
Nz(Me.JobNumber) & "And [VIJHighNumber]=" & Nz
(Me.VIJLowNumber) & "And [PassNumber]=" & 1)))
If Nz(LowDuplicate) >= 1 Then
MsgBox "VIJ " & Nz(Me.VIJLowNumber) & " is stored """ &
LowDuplicate & """ time(s) as the Pass 1 VIJ Low Number."
End If
If Nz(HighDuplicate) >= 1 Then
MsgBox "VIJ " & Nz(Me.VIJLowNumber) & " is stored """ &
HighDuplicate & """ time(s) as the Pass 1 VIJ High
Number."
End If
End If
If Nz(JobExists) >= 1 Then
If Nz(Me.JobNumber) >= 1 And Nz(Me.SkidNumber) > 1 And Nz
(Me.SkidNumber) < 900 And Nz(Me.PassNumber) = 1 Then
PreviousSkidExists = (Nz(DCount("(Nz
([SkidNumber]))", "PressProduction", "[JobNumber]=" & Nz
(Me.JobNumber) & "And [SkidNumber]=" & Nz(Me.SkidNumber) -
1)))
If Nz(PreviousSkidExists) < 1 Then
Answer = MsgBox(" Skid # "" & Nz(Nz(Me.SkidNumber) - 1)
& "" has not been entered, do you want to continue?",
vbYesNo + vbQuestion)
If Answer = vbNo Then
Cancel = True
Me.VIJLowNumber.Undo
Exit Sub
End If
If Answer = vbYes Then
End If
End If
Rem If PreviousSkidExits >= 1 Then
Rem PreviousSkidHighRange = (Nz(DMax("(Nz
([VIJHighNumber]))", "PressProduction", "[JobNumber]=" &
Nz(Me.JobNumber) & "And [SkidNumber]=" & Nz
(Me.SkidNumber) - 1 & "And [PassNumber]=" & 1)))
Rem If Nz(JobExists) >= 1 And Nz(Me.SkidNumber) >= 1 And
Nz(Me.SkidNumber) < 900 And Nz(Me.PassNumber) = 1 And Nz
(Me.VIJLowNumber) <> Nz(PreviousSkidHighRange) + 1 Then
Rem Answer = MsgBox(" Job " & Nz
(Me.JobNumber) & ", Skid " & Nz(Nz(Me.SkidNumber) - 1)
& " has a VIJ High Number """ & PreviousSkidHighRange
& """." & vbCrLf & "This entry """ & Nz(Me.VIJLowNumber)
& """ is not the next VIJ number """ &
PreviousSkidHighRange + 1 & """." & vbCrLf & "
Do you want to continue?", vbYesNo + vbQuestion)
Rem If Answer = vbNo Then
Rem Cancel = True
Rem Me.VIJLowNumber.Undo
Rem Exit Sub
Rem End If
Rem If Answer = vbYes Then
Rem End If
Rem End If
Rem End If
End If

If Nz(Me.JobNumber) >= 1 And Nz(Me.SkidNumber) > 1 And Nz
(Me.SkidNumber) > 899 And Nz(Me.PassNumber) = 1 Then
PreviousSkidExists = (Nz(DCount("(Nz
([SkidNumber]))", "PressProduction", "[JobNumber]=" & Nz
(Me.JobNumber) & "And [SkidNumber]=" & Nz(Me.SkidNumber) -
1)))
If Nz(PreviousSkidExists) < 1 Then
Answer = MsgBox(" Skid # "" & Nz(Nz(Me.SkidNumber) - 1)
& "" has not been entered, do you want to continue?",
vbYesNo + vbQuestion)
If Answer = vbNo Then
Cancel = True
Me.VIJLowNumber.Undo
Exit Sub
End If
If Answer = vbYes Then
End If
End If
Rem If PreviousSkidExits >= 1 Then
Rem PreviousSkidHighRange = (Nz(DMax("(Nz
([VIJHighNumber]))", "PressProduction", "[JobNumber]=" &
Nz(Me.JobNumber) & "And [SkidNumber]=" & Nz
(Me.SkidNumber) - 1 & "And [PassNumber]=" & 1)))
Rem If Nz(JobExists) >= 1 And Nz(Me.SkidNumber) >= 1 And
Nz(Me.SkidNumber) > 899 And Nz(Me.PassNumber) = 1 And Nz
(Me.VIJLowNumber) <> Nz(PreviousSkidHighRange) + 1 Then
Rem Answer = MsgBox(" Job " & Nz
(Me.JobNumber) & ", Skid " & Nz(Nz(Me.SkidNumber) - 1)
& " has a VIJ High Number """ & PreviousSkidHighRange
& """." & vbCrLf & "This entry """ & Nz(Me.VIJLowNumber)
& """ is not the next VIJ number """ &
PreviousSkidHighRange + 1 & """." & vbCrLf & "
Do you want to continue?", vbYesNo + vbQuestion)
Rem If Answer = vbNo Then
Rem Cancel = True
Rem Me.VIJLowNumber.Undo
Rem Exit Sub
Rem End If
Rem If Answer = vbYes Then
Rem End If
Rem End If
Rem End If
End If
End If

 

[Graham Mandeno]

Hi Allan

It's funny how sometimes you can stare at something for hours and see what
you expect to see and not what is there.

Here is your line of code:

Rem If PreviousSkidExits >= 1 Then

You are missing an "s" from "Exists".

Also, as PreviousSkidExists is declared as a Single, it can never be Null,
so you do not need to use the Nz function.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am including code below that is giving me a problem
that I don't understand. It is included in the
BeforeUpdate event of a text box. When I run the code I
get the following error:
Compile error:
Variable no defined
When I debug "PreviousSkidExists is highligted in the
If PreviousSkidExists >=1 Then statement

Just before this is
If Nz(PreviousSkidExists) < 1 Then
and it accepts this (or it seems to)

I have tried
If Nz(PreviousSkidExists) >=1 Then but I still get the
compile error.
I have placed Rem in front of the code involved.

I think I have declared the variable at the beginning
Dim PreviousSkidExists As Single

I know that "PreviousSkidExists" is getting a value
because I have used a MsgBox to display the results. The
answer was 1 for my test information.

There is more code at the end that I did not include
since it is not related to this problem.

I have spent about 5 hours on this problem and I am still
where I started.

Thank you for any help you can give.

Allan

[snip]

 

[A Hopper]

Graham,
Your are right it is funny but I wasn't laughing a few
hours ago. I have typed those lines several times. I cut
and pasted. I came to the conclusion that there must be
something huge coding error I was making, or maybe a bug
in Access that I didn't know about, or worse still maybe
my database was corrupt. Sure puts me in my place.
I should have posted hours ago, but I like to try before
I bother others.
Thank you very much for being there, now I can go home
and get a good nights rest.

Allan

-----Original Message-----
Hi Allan

It's funny how sometimes you can stare at something for hours and see what
you expect to see and not what is there.

Here is your line of code:

Rem If PreviousSkidExits >= 1 Then

You are missing an "s" from "Exists".

Also, as PreviousSkidExists is declared as a Single, it can never be Null,
so you do not need to use the Nz function.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am including code below that is giving me a problem
that I don't understand. It is included in the
BeforeUpdate event of a text box. When I run the code I
get the following error:
Compile error:
Variable no defined
When I debug "PreviousSkidExists is highligted in the
If PreviousSkidExists >=1 Then statement

Just before this is
If Nz(PreviousSkidExists) < 1 Then
and it accepts this (or it seems to)

I have tried
If Nz(PreviousSkidExists) >=1 Then but I still get the
compile error.
I have placed Rem in front of the code involved.

I think I have declared the variable at the beginning
Dim PreviousSkidExists As Single

I know that "PreviousSkidExists" is getting a value
because I have used a MsgBox to display the results. The
answer was 1 for my test information.

There is more code at the end that I did not include
since it is not related to this problem.

I have spent about 5 hours on this problem and I am still
where I started.

Thank you for any help you can give.

Allan

[snip]


.