Need help for VBA script in ACCESS "kft10 via AccessMonster.com" <u45012[ at ]uwe> 12/3/2008 6:41:52 PM 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200812/1

Re: Need help for VBA script in ACCESS "Mike Painter" <mddotpainter[ at ]sbcglobal.net> 12/3/2008 7:18:25 PM 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. kft10 via AccessMonster.com wrote: [Quoted Text] > 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

Re: Need help for VBA script in ACCESS "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 12/3/2008 7:21:15 PM 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) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "kft10 via AccessMonster.com" <u45012[ at ]uwe> wrote in message news:8e1f10182c213[ at ]uwe... [Quoted Text] > 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 > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200812/1 >

Re: Need help for VBA script in ACCESS "kft10 via AccessMonster.com" <u45012[ at ]uwe> 12/11/2008 4:01:28 PM 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 Douglas J. Steele wrote: [Quoted Text] >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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200812/1

Re: Need help for VBA script in ACCESS "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 12/11/2008 9:14:10 PM 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 http://I.Am/DougSteele (no private e-mails, please) "kft10 via AccessMonster.com" <u45012[ at ]uwe> wrote in message news:8e823f07b2e68[ at ]uwe... [Quoted Text] > 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 > > Douglas J. Steele wrote: >>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 > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200812/1 >