Need help for VBA script in ACCESS

[kft10 via AccessMonster.com]

Hi

I am trying to write VBA script in Access 2003 like this:

Private Sub ProgramList_AfterUpdate()
Dim prog As Variant
Dim stYTD As Variant
If Me.ProgramList = 1 Then prog = "JPAAF" Else If Me.ProgramList = 2 Then
prog = "JPAAE" Else If Me.ProgramList = 3 Then prog = "JPAAH" Else If Me.
ProgramList = 4 Then prog = "JPABX" Else If Me.ProgramList = 5 Then prog =
"JPABR" Else If Me.ProgramList = 6 Then prog = "JPAAW" Else If Me.ProgramList
= 7 Then prog = "JPAAT" Else If Me.ProgramList = 8 Then prog = "JPABS" Else
If Me.ProgramList = 9 Then prog = "JPABK" Else If Me.ProgramList = 10 Then
prog = "JPAA3" Else If Me.ProgramList = 11 Then prog = "JPAA1" Else If Me.
ProgramList = 12 Then prog = "JPABA" Else prog = "JPAAI"

stYTD = DLookup("[Start_Date]", "[Projection]", _
"[Program_Code] = " & prog)
End Sub

Notes: [Start_date] and [Program_code] are 2 field in Projection table.

But Access alway gives me an error message says 'you cancelled the previous
operation'. Is anyone in forum can fix my script? Thank in advance.

Rgds,
KF

 

[Mike Painter]

You have no End If
Since prog is a string you must quote it.
When you exit the subroutine you will loose stYTD so you will have to deal
with that also.
A better way would be to add the numerical values to the Projection table
and get rid of the If statement entirely.
This looks like something a related table should be used for.

 

[Douglas J. Steele]

I'm assuming that that's not your actual code, since the way it's presented
certain isn't valid.

The error message you're encountering will occur if you've got an incorrect
field or table name in your DLookup statement. Are you positive that the
names of the fields are Start_Date and Program_Code (with underscores) and
not Start Date and Program Code (with spaces)? If there are spaces, my
advice would be to remove the spaces. If you cannot or will not, you need to
put square brackets around them:

stYTD = DLookup("[Start Date]", "[Projection]", _
"[Program Code] = " & prog)

 

[kft10 via AccessMonster.com]

Thank you for the answer. Sorry, I just got back to work recently. So, I
can't respond this answer before. I am sure I have the correct field with '_'
(no space). And I always have the same problem when writing Dlookup VBA
script with conditional. I just wrote the script like:

SeatTable# = Me.Seating_Table_
Me.TotalSeat = DCount("[Seating_table#]", "[Gala Activity]", "[SeatTable#]=
[Seating_Table#]")

And the same message came up "you cancelled the previous operation". Please
help me to solve this problem. Thank you so much for your help.
Rgds,
KF

I'm assuming that that's not your actual code, since the way it's presented
certain isn't valid.

The error message you're encountering will occur if you've got an incorrect
field or table name in your DLookup statement. Are you positive that the
names of the fields are Start_Date and Program_Code (with underscores) and
not Start Date and Program Code (with spaces)? If there are spaces, my
advice would be to remove the spaces. If you cannot or will not, you need to
put square brackets around them:

stYTD = DLookup("[Start Date]", "[Projection]", _
"[Program Code] = " & prog)

Hi

[quoted text clipped - 26 lines]

Rgds,
KF

 

[Douglas J. Steele]

Double check that your field and table names are correct.

That very misleading error message usually occurs when you've mistyped
something.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thank you for the answer. Sorry, I just got back to work recently. So, I
can't respond this answer before. I am sure I have the correct field with
'_'
(no space). And I always have the same problem when writing Dlookup VBA
script with conditional. I just wrote the script like:

SeatTable# = Me.Seating_Table_
Me.TotalSeat = DCount("[Seating_table#]", "[Gala Activity]",
"[SeatTable#]=
[Seating_Table#]")

And the same message came up "you cancelled the previous operation".
Please
help me to solve this problem. Thank you so much for your help.
Rgds,
KF

I'm assuming that that's not your actual code, since the way it's
presented
certain isn't valid.

The error message you're encountering will occur if you've got an
incorrect
field or table name in your DLookup statement. Are you positive that the
names of the fields are Start_Date and Program_Code (with underscores) and
not Start Date and Program Code (with spaces)? If there are spaces, my
advice would be to remove the spaces. If you cannot or will not, you need
to
put square brackets around them:

stYTD = DLookup("[Start Date]", "[Projection]", _
"[Program Code] = " & prog)

Hi

[quoted text clipped - 26 lines]

Rgds,
KF