Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    hhuuhn is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2009
    Posts
    14

    compile error variable not defined VB code, Access97

    Hello,
    what I intend to do is data quality cotrol whatever is selected from combobox( Yes, No, NA), if NA selected then the next several fields will be skipped (disabled). i use the follwoing code, it doesn't work, it works the same code in other field. i don't know why. i would appreciate if any one can help me find out. thanks a lot

    Private Sub change_smoking_AfterUpdate()
    If (Me![change smoking].Value <> "1") Or (Me![change smoking].Value <> "0") Then
    Me![Ceased smoking].Enabled = no
    Me![Ceased smoking].Value = " "
    Me![ReducedSmoking].Enabled = no


    Me![ReducedSmoking].Value = ""
    Me![IncreasedSmoking].Enabled = no
    Me![IncreasedSmoking].Value = ""
    Else
    Me![Ceased smoking].Enabled = (Me![change smoking].Value = "1") Or (Me![change smoking].Value = "0")
    Me![Ceased smoking].Value = " "
    Me![ReducedSmoking].Enabled = (Me![change smoking].Value = "1") Or (Me![change smoking].Value = "0")
    Me![ReducedSmoking].Value = ""
    Me![IncreasedSmoking].Enabled = (Me![change smoking].Value = "0") Or (Me![change smoking].Value = "1")
    Me![IncreasedSmoking].Value = ""
    End If
    End Sub

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Which line does the debugger highlight?

  3. #3
    rommelgenlight is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2009
    Posts
    77
    when you are using ".enabled", its value should only be true or false, yes or no, 0 or -1.

    you don't let ms access decide for itself to choose either true or false. you are to set it by yourself. on your example

    " Me![Ceased smoking].Enabled = (Me![change smoking].Value = "1") Or (Me![change smoking].Value = "0") "

    here you see you use "OR" for ms access to decide for itself whether to decide 1 or 0.
    you are to set it by yourself.

  4. #4
    hhuuhn is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2009
    Posts
    14
    Thanks.
    But when i put "False" instead of "no", it blocks all next 3 fields whether you select Yea, or no, or NA. When I use "no", it has error message and the code
    Private Sub change_smoking_AfterUpdate()
    has highlighted for debugging.
    I have used the same code many times in the same DB, I can't figure out why this one doesn't work.
    Thanks a lot for your help!

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Strange line to highlight. What error are you getting?

  6. #6
    hhuuhn is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2009
    Posts
    14
    the error message box show
    Compile error: variable not defined
    When I tick OK , Private Sub change_smoking_AfterUpdate() highlighted in yellow(debug) and the first no highlighted in blue as regular highlighter.
    I tried to put False instead of no in the code, no error message appear, but it disabled all next 3 fields regardless what selected....
    basically if Yes(1), or No(0) selected then the next 3 fields need to filled out, if NA(8) selected then no need to enter these 3 fields( skepped).

    I changed the code as below:
    Private Sub change_smoking_AfterUpdate()
    If Me![change smoking].Value = "8" Then
    Me![Ceased smoking].Enabled = False
    Me![Ceased smoking].Value = " "
    Me![ReducedSmoking].Enabled = False
    Me![ReducedSmoking].Value = ""
    Me![IncreasedSmoking].Enabled = False
    Me![IncreasedSmoking].Value = ""
    Else
    .........
    (the rest code same as previous)
    also doesn't work...

    Thank you so much RuralGuy !

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I would comment out this entire procedure and type it in again to see if there is some corruption.

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I forgot to say: Start the entire event coding sequence again from the "..." button.

  9. #9
    rommelgenlight is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2009
    Posts
    77
    can you tell us what now is your whole afterupdate() code! By the way, is your change_smoking field is a text type format or a number format?

    or you said, you have used your code on your other db's. on your present db, are the field names still the same as you used here on your code?

  10. #10
    hhuuhn is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2009
    Posts
    14
    OK,
    I removed all the code and started event procedure at "..." button and typed code cerycarefully to avoid typo, still blocked all 3 field regardless what selected. no error message.
    code below:
    Private Sub change_smoking_AfterUpdate()
    If Me![change smoking].Value = "8" Then
    Me![Ceased smoking].Enabled = False
    Me![Ceased smoking].Value = " "
    Me![ReducedSmoking].Enabled = False
    Me![ReducedSmoking].Value = ""
    Me![IncreasedSmoking].Enabled = False
    Me![IncreasedSmoking].Value = ""
    Else
    Me![Ceased smoking].Enabled = (Me![change smoking].Value = "1") Or (Me![change smoking].Value = "0")
    Me![ReducedSmoking].Enabled = (Me![change smoking].Value = "1") Or (Me![change smoking].Value = "0")
    Me![IncreasedSmoking].Enabled = (Me![change smoking].Value = "1") Or (Me![change smoking].Value = "0")
    Me![Ceased smoking].Value = " "
    Me![ReducedSmoking].Value = ""
    Me![IncreasedSmoking].Value = ""
    End If
    End Sub

    Any clue to try other coding?
    Thanks soooooo much!

  11. #11
    hhuuhn is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2009
    Posts
    14
    Change_smoking is a text format, but i linked with number in look up table (1=Yes,0=no,8=NA) I mean I have coded same way for many other fields in the same DB. i understand I need to change the field name all the time when do the different field...

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Quote Originally Posted by hhuuhn View Post
    OK,
    I removed all the code and started event procedure at "..." button and typed code cerycarefully to avoid typo, still blocked all 3 field regardless what selected. no error message.
    So at least we have resolved the error message? I'll take a look at the code now.

  13. #13
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Quote Originally Posted by hhuuhn View Post
    Change_smoking is a text format, but i linked with number in look up table (1=Yes,0=no,8=NA) I mean I have coded same way for many other fields in the same DB. i understand I need to change the field name all the time when do the different field...
    So the Change_smoking field is a Numeric field? What type? Integer, Long, Single, Double, Currency?

  14. #14
    rommelgenlight is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Mar 2009
    Posts
    77
    if it is a number format base on your lookup table, then your change_smoking.value must be a number and not text format like what you've done such as "8", "1", "0". it should only be 8,0 or 1. and like i've said you cannot make ms access decide for itself what to choose between two values there in your OR statement. you should assign one value.

  15. #15
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    It looks to me like Change_smoking can *only* be 0,1 or 8. Is that true? Having fun answering two people at the same time?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Variable Criteria
    By JamesLens in forum Queries
    Replies: 0
    Last Post: 01-02-2009, 04:55 PM
  2. Possible to store user-defined types in table?
    By Binky in forum Programming
    Replies: 0
    Last Post: 11-20-2008, 02:28 PM
  3. permision problem with access97
    By dourvas in forum Access
    Replies: 0
    Last Post: 11-04-2008, 06:07 AM
  4. Variable question
    By synapse5150 in forum Programming
    Replies: 1
    Last Post: 07-09-2008, 08:17 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums