+ Post New Thread
Results 1 to 14 of 14
Office Software Thread, Access - Again - AfterUpdate issue in Technical; Code: Private Sub cboCategories_2_AfterUpdate() Me!BuildingID = cbocatagories_2.Column(1) Me!Building = cbocatagories_2.Column(2) End Sub This fails with a "variable not defined" error. ...
  1. #1

    CHR1S's Avatar
    Join Date
    Feb 2006
    Location
    Birmingham
    Posts
    4,389
    Thank Post
    1,489
    Thanked 437 Times in 286 Posts
    Rep Power
    195

    Access - Again - AfterUpdate issue

    Code:
    Private Sub cboCategories_2_AfterUpdate()
    Me!BuildingID = cbocatagories_2.Column(1)
    Me!Building = cbocatagories_2.Column(2)
    End Sub
    This fails with a "variable not defined" error.

    I'm asking it to write the values in two columns of a selection in a combo box. I'm not even sure if the above code should work as I pilfered it from another website! ha

    Edit - The "cbocatagories_2" is what is highlighted with the error.
    Last edited by CHR1S; 7th March 2013 at 03:35 PM.

  2. #2

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,182
    Thank Post
    1,285
    Thanked 1,029 Times in 729 Posts
    Rep Power
    658
    Quote Originally Posted by CHR1S View Post
    Edit - The "cbocatagories_2" is what is highlighted with the error.
    It's a typo - cboCategories_2

    PS - VBA automatically matches the capitalisation if you get the correct spelling, so if your variable name doesn't match the original in terms of capital letter placement, you know you have a typo

  3. Thanks to LosOjos from:

    CHR1S (7th March 2013)

  4. #3

    CHR1S's Avatar
    Join Date
    Feb 2006
    Location
    Birmingham
    Posts
    4,389
    Thank Post
    1,489
    Thanked 437 Times in 286 Posts
    Rep Power
    195
    I see the 3 hours sleep I had last night is taking its toll!

    Thank you @LosOjos

    Its now writing to the table, just the wrong things, something for me to sort tomorrow it seems

  5. #4

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,182
    Thank Post
    1,285
    Thanked 1,029 Times in 729 Posts
    Rep Power
    658
    Quote Originally Posted by CHR1S View Post
    Its now writing to the table, just the wrong things, something for me to sort tomorrow it seems
    haha, one step at a time!

  6. Thanks to LosOjos from:

    CHR1S (8th March 2013)

  7. #5

    CHR1S's Avatar
    Join Date
    Feb 2006
    Location
    Birmingham
    Posts
    4,389
    Thank Post
    1,489
    Thanked 437 Times in 286 Posts
    Rep Power
    195
    I may have to add "I hate Access" back to my sig!

  8. #6

    CHR1S's Avatar
    Join Date
    Feb 2006
    Location
    Birmingham
    Posts
    4,389
    Thank Post
    1,489
    Thanked 437 Times in 286 Posts
    Rep Power
    195
    Data type mismatch in criteria
    Code:
    Private Sub cboProducts_2_AfterUpdate()
    Me!BuildingID = cboCategories_2.Column(1)
    Me!Building = cboCategories_2.Column(2)
    Me!Assets.Building.SetFocus
    Me!Assets.Building.Dropdown
    Me!RoomID = cboProducts_2.Column(2)
    Me!Room = cboProducts_2.Column(1)
    End Sub
    The cascading combos work, just not the data entry. Confused
    Last edited by CHR1S; 8th March 2013 at 02:04 PM.

  9. #7

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,182
    Thank Post
    1,285
    Thanked 1,029 Times in 729 Posts
    Rep Power
    658
    Quote Originally Posted by CHR1S View Post
    Code:
    Private Sub cboProducts_2_AfterUpdate()
    Me!BuildingID = cboCategories_2.Column(1)
    Me!Building = cboCategories_2.Column(2)
    Me!Assets.Building.SetFocus
    Me!Assets.Building.Dropdown
    Me!RoomID = cboProducts_2.Column(2)
    Me!Room = cboProducts_2.Column(1)
    End Sub
    The cascading combos work, just not the data entry. Confused
    At a glance, do you need to add .Value to some of those? i.e.

    Code:
    Me!BuildingID = cboCategories_2.Column(1).Value
    My thinking is you're trying to pass a Column object as opposed to the value held in the column...

    EDIT: Just checked some of my own code and that .Value is not necessary. So next step is, are you trying to pass a string to a column that only accepts integers? You can convert the string to an int with CInt, like so:

    Code:
    Me!BuildingID = CInt(cboCategories_2.Column(1))
    Last edited by LosOjos; 8th March 2013 at 02:26 PM.

  10. Thanks to LosOjos from:

    CHR1S (8th March 2013)

  11. #8

    CHR1S's Avatar
    Join Date
    Feb 2006
    Location
    Birmingham
    Posts
    4,389
    Thank Post
    1,489
    Thanked 437 Times in 286 Posts
    Rep Power
    195
    Ive removed all the code, still get the error. Weird!
    Bound columns are correct and database is expecting a string and getting a string.
    Last edited by CHR1S; 8th March 2013 at 02:38 PM.

  12. #9

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,182
    Thank Post
    1,285
    Thanked 1,029 Times in 729 Posts
    Rep Power
    658
    Quote Originally Posted by CHR1S View Post
    Ive removed all the code, still get the error. Weird!
    Very... which line is highlighted when you enter debug?

  13. Thanks to LosOjos from:

    CHR1S (8th March 2013)

  14. #10

    CHR1S's Avatar
    Join Date
    Feb 2006
    Location
    Birmingham
    Posts
    4,389
    Thank Post
    1,489
    Thanked 437 Times in 286 Posts
    Rep Power
    195
    Quote Originally Posted by LosOjos View Post
    Very... which line is highlighted when you enter debug?
    db.Execute sSQL, dbFailOnError

    Part of my error handling code, which worked and hasn't been changed

  15. #11

    CHR1S's Avatar
    Join Date
    Feb 2006
    Location
    Birmingham
    Posts
    4,389
    Thank Post
    1,489
    Thanked 437 Times in 286 Posts
    Rep Power
    195
    Just wiped the record im using for testing and the error has gone.

    Not sure what was in it that it didnt like as its a combo box selection thats not changed.

    Weird

  16. #12

    CHR1S's Avatar
    Join Date
    Feb 2006
    Location
    Birmingham
    Posts
    4,389
    Thank Post
    1,489
    Thanked 437 Times in 286 Posts
    Rep Power
    195
    Back to the issue at hand -

    Code:
    Private Sub cboProducts_2_AfterUpdate()
    Me.Assets.Building = Me.cbocatagories_2.Column(1)
    Me.Assets.Building.SetFocus
    Me.Assets.Building.Dropdown
    Me.Assets.Room = Me!cboProducts_2.Column(2)
    End Sub
    The BuildingID and RoomID are filled correctly as they are the bound and the control source.
    Room and Building (their text names) are not filled in at all.

  17. #13

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,182
    Thank Post
    1,285
    Thanked 1,029 Times in 729 Posts
    Rep Power
    658
    What are you actually trying to do, populate entries in the database based on selections made in combo boxes?

    If so, it might be easier to cut out the middle man and write straight to the DB.

  18. Thanks to LosOjos from:

    CHR1S (8th March 2013)

  19. #14

    CHR1S's Avatar
    Join Date
    Feb 2006
    Location
    Birmingham
    Posts
    4,389
    Thank Post
    1,489
    Thanked 437 Times in 286 Posts
    Rep Power
    195
    Quote Originally Posted by LosOjos View Post
    What are you actually trying to do, populate entries in the database based on selections made in combo boxes?

    If so, it might be easier to cut out the middle man and write straight to the DB.
    To be fair, I may not even need to store that information in the table itself.

    I can write a query into the reports that run to poll the room and building tables and populate the values at report time.

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 0
    Last Post: 15th November 2011, 09:19 AM
  2. Access Reading Tests Issues
    By ChrisH in forum Educational Software
    Replies: 1
    Last Post: 13th October 2011, 06:11 PM
  3. ePortal - external access data protection issues
    By cheredenine in forum MIS Systems
    Replies: 3
    Last Post: 1st May 2009, 08:44 PM
  4. Ranger issue?! BBC site closes down whenever it is accessed
    By IT_Man_Dan in forum Network and Classroom Management
    Replies: 12
    Last Post: 1st February 2009, 05:53 PM
  5. WDS issue (Again!) - Hidden files?
    By Chufty in forum Windows
    Replies: 1
    Last Post: 5th September 2007, 08:04 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •