OnCurrent VBA error

V

Veus

Hi,

I have the following VBA code:

Private Sub Form_Current()
If Me.type = "Full-Day" Then
Me.roomCost = DLookup("[fullDayCost]", "rooms", "[ID] = [roomID]")
ElseIf Me.type = "Half-Day" Then
Me.roomCost = DLookup("[halfDayCost]", "rooms", "[ID] = [roomID]")
Else
If Me.roomCost = (DLookup("[halfDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]") * 2) > DLookup("[fullDayCost]",
"rooms", "[ID] = Forms![coursesVenue]![roomID]") Then
Me.roomCost = DLookup("[fullDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]")
Else
Me.roomCost = (DLookup("[halfDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]") * 2)
End If
End If
End Sub

I cant see for the life of me whats wrong.
Im checking the first ELSE statement.
Ive done some debugging:

? DLookup("[fullDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]")
400
? (DLookup("[halfDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]") * 2)
600
? (DLookup("[halfDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]") * 2) > DLookup("[fullDayCost]",
"rooms", "[ID] = Forms![coursesVenue]![roomID]")
True

If the IF statement returns True shouldnt Me.roomCost = fullDayCost?
At the moment no matter what I do it always returns halfDayCost*2
 
D

Douglas J. Steele

Are you sure that Me.type is returning what you think it is? What is it: a
combo box? Try putting the line of code

MsgBox Me.type

directly in front of the If statement.

I'm assuming roomID is a field on your form. If that's the case, you need to
keep those fully qualified references that you show elsewhere in your post:

Me.roomCost = DLookup("[fullDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![[roomID]")

or else ensure that only the value is in the DLookup statement, by putting
the reference to roomID outside of the quotes:

Me.roomCost = DLookup("[fullDayCost]", "rooms", "[ID] = " & [roomID])

That assumes that that ID is a numeric field. If it's text, you'll need
quotes around the value:

Me.roomCost = DLookup("[fullDayCost]", "rooms", "[ID] = " & Chr$(34) &
[roomID] & Chr$(34))
 
V

Veus

Hi,

Thank you for your response.
Are you sure that Me.type is returning what you think it is? What is it: a
combo box?
Click to expand...

Yes it is to both questions :)
I'm assuming roomID is a field on your form. If that's the case, you need to
keep those fully qualified references that you show elsewhere in your post:
Click to expand...

Ive filled in the full qualified references
That assumes that that ID is a numeric field. If it's text, you'll need
quotes around the value:
Click to expand...

ID is a numeric field.

Ive tried all the things youve suggested but it still shows up as
halfdaycost*2.
Even if type didnt return what i thought shouldnt it go to ELSE anyway?
Are you sure that Me.type is returning what you think it is? What is it: a
combo box? Try putting the line of code

MsgBox Me.type

directly in front of the If statement.

I'm assuming roomID is a field on your form. If that's the case, you need to
keep those fully qualified references that you show elsewhere in your post:

Me.roomCost = DLookup("[fullDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![[roomID]")

or else ensure that only the value is in the DLookup statement, by putting
the reference to roomID outside of the quotes:

Me.roomCost = DLookup("[fullDayCost]", "rooms", "[ID] = " & [roomID])

That assumes that that ID is a numeric field. If it's text, you'll need
quotes around the value:

Me.roomCost = DLookup("[fullDayCost]", "rooms", "[ID] = " & Chr$(34) &
[roomID] & Chr$(34))

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Veus said:
Hi,

I have the following VBA code:

Private Sub Form_Current()
If Me.type = "Full-Day" Then
Me.roomCost = DLookup("[fullDayCost]", "rooms", "[ID] = [roomID]")
ElseIf Me.type = "Half-Day" Then
Me.roomCost = DLookup("[halfDayCost]", "rooms", "[ID] = [roomID]")
Else
If Me.roomCost = (DLookup("[halfDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]") * 2) > DLookup("[fullDayCost]",
"rooms", "[ID] = Forms![coursesVenue]![roomID]") Then
Me.roomCost = DLookup("[fullDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]")
Else
Me.roomCost = (DLookup("[halfDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]") * 2)
End If
End If
End Sub

I cant see for the life of me whats wrong.
Im checking the first ELSE statement.
Ive done some debugging:

? DLookup("[fullDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]")
400
? (DLookup("[halfDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]") * 2)
600
? (DLookup("[halfDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]") * 2) > DLookup("[fullDayCost]",
"rooms", "[ID] = Forms![coursesVenue]![roomID]")
True

If the IF statement returns True shouldnt Me.roomCost = fullDayCost?
At the moment no matter what I do it always returns halfDayCost*2
Click to expand...
Click to expand...
 
U

UpRider

Would the below changes alter the intended logic? If not, it would simplify
debugging and the end result would be the same.
HTH, UpRider

Private Sub Form_Current()
If Me.type = "Full-Day" Then
Me.roomCost = DLookup("[fullDayCost]", "rooms", "[ID] = [roomID]")
exit sub
endif
If Me.type = "Half-Day" Then
Me.roomCost = DLookup("[halfDayCost]", "rooms", "[ID] = [roomID]")
exit sub
endif
If Me.roomCost = (DLookup("[halfDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]") * 2) > DLookup("[fullDayCost]",
"rooms", "[ID] = Forms![coursesVenue]![roomID]") Then
Me.roomCost = DLookup("[fullDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]")
exit sub
endif
Me.roomCost = (DLookup("[halfDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]") * 2)
End Sub
 
V

Veus

The * 2 stuff only needs to be worked out if Me.type does not equal
Full-Day or Half-Day.
How would this make debugging easier?
 
G

Guest

Try it like this

Select Case Me.Type
Case Is = “Full-Dayâ€
Me.roomCost = DLookup("[fullDayCost]", "rooms", "[ID] = [roomID]")
Case Is = “Half-Dayâ€
Me.roomCost = DLookup("[halfDayCost]", "rooms", "[ID] = [roomID]")
Case Else
If (DLookup("[halfDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]") * 2) > DLookup("[fullDayCost]", "rooms",
"[ID] = Forms![coursesVenue]![roomID]") Then
Me.roomCost = DLookup("[fullDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]")
Else
Me.roomCost = (DLookup("[halfDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]") * 2)
End If
End Select



Veus said:
The * 2 stuff only needs to be worked out if Me.type does not equal
Full-Day or Half-Day.
How would this make debugging easier?
Would the below changes alter the intended logic? If not, it would simplify
debugging and the end result would be the same.
HTH, UpRider

Private Sub Form_Current()
If Me.type = "Full-Day" Then
Me.roomCost = DLookup("[fullDayCost]", "rooms", "[ID] = [roomID]")
exit sub
endif
If Me.type = "Half-Day" Then
Me.roomCost = DLookup("[halfDayCost]", "rooms", "[ID] = [roomID]")
exit sub
endif
If Me.roomCost = (DLookup("[halfDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]") * 2) > DLookup("[fullDayCost]",
"rooms", "[ID] = Forms![coursesVenue]![roomID]") Then
Me.roomCost = DLookup("[fullDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]")
exit sub
endif
Me.roomCost = (DLookup("[halfDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]") * 2)
End Sub
Click to expand...
Click to expand...
 
V

Veus

Thank you all for the replies.

I realised that even though the code was in the Form_Current() event,
it wasnt updating on every record. It was somehow setting itself to one
value and never changing. I probably have got the use of VBA mixed up
with calculated fields.

So i converted it into a calculated field and even though there is a
slight delay before it appears in the record it is correct! Thanks
again.
Klatuu said:
Try it like this

Select Case Me.Type
Case Is = "Full-Day"
Me.roomCost = DLookup("[fullDayCost]", "rooms", "[ID] = [roomID]")
Case Is = "Half-Day"
Me.roomCost = DLookup("[halfDayCost]", "rooms", "[ID] = [roomID]")
Case Else
If (DLookup("[halfDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]") * 2) > DLookup("[fullDayCost]", "rooms",
"[ID] = Forms![coursesVenue]![roomID]") Then
Me.roomCost = DLookup("[fullDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]")
Else
Me.roomCost = (DLookup("[halfDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]") * 2)
End If
End Select



Veus said:
The * 2 stuff only needs to be worked out if Me.type does not equal
Full-Day or Half-Day.
How would this make debugging easier?
Would the below changes alter the intended logic? If not, it would simplify
debugging and the end result would be the same.
HTH, UpRider

Private Sub Form_Current()
If Me.type = "Full-Day" Then
Me.roomCost = DLookup("[fullDayCost]", "rooms", "[ID] = [roomID]")
exit sub
endif
If Me.type = "Half-Day" Then
Me.roomCost = DLookup("[halfDayCost]", "rooms", "[ID] = [roomID]")
exit sub
endif
If Me.roomCost = (DLookup("[halfDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]") * 2) > DLookup("[fullDayCost]",
"rooms", "[ID] = Forms![coursesVenue]![roomID]") Then
Me.roomCost = DLookup("[fullDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]")
exit sub
endif
Me.roomCost = (DLookup("[halfDayCost]", "rooms", "[ID] =
Forms![coursesVenue]![roomID]") * 2)
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DLookup Help 4
FilterOn Causing a Problem 3
Update Data in Table with Form 1
Trigger an Insert on a Subform 14
Check for duplicate before update 9
code worked in 2003 but not in 2007 version 6
OnCurrent event problem 3
Compile Error 2

Top