Results 1 to 12 of 12
  1. #1
    HansBades is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    85

    Help with vba comboxbox value


    I have a form with two values. The first is a Parts Category and the second is the Parts Name. I have set it up so that when I choose a particular Parts Category in the first combobox it only shows me the Parts that are associated with it in the second combobox. I want to be able to add a new part in the form when it is not already in the list. So I created some vba code for a not in list event to add a new part that is not already in the list. But as every part needs a part category, the code needs to enter in a parts category id with the new part when I add one. What I want to do is add the new part with the parts category that is showing on the form in the first combobox. So if I want to add "chisel saw" as a part, I would go to the first combobox and choose the parts category "cutting tools". Then I would add chisel saw to the parts list. But every Part must have a Parts Category ID associated with it. So I need to add in the vba code where I update the parts field to update it with an associated parts category id. I can draw the category name from the form's first combo box but am having trouble drawing the category id associated with that name. I am trying to figure out the easiest and most efficient method I can determine the category id of the category showing so that I can add the new part with that category id. Any thoughts?

  2. #2
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Use the column number of whichever column in your combobox is the CategoryID. ie. Forms("MyFormName").MyComboboxName.Column(0)
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  3. #3
    HansBades is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    85
    Thank you. I tried your suggesttion. When I put in Column(0) it returns the name of the part. When I put in Column(1), hoping it would return the parts category id, I get an error message that it cannot return a null value.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    You have to bring in the number of fields you want to access?
    You also have to tell Access how many there are?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    There are 2 examples here for how to add items (plus an example of how to add to the values list so ignore that one if not using a values list). Note the shaded note about having to add two or more values in a record or table.
    https://docs.microsoft.com/en-us/off...obox.notinlist
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    here's a basic example adding the new part and category.
    Attached Files Attached Files
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  7. #7
    HansBades is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2021
    Posts
    85
    Thank you very much. I was able to get that working. When I am prompted with the question would I like to add it, I say yes. And it does add it to the table. But then it repeats and asks me if I want to add it to the list? And if I say no, it keeps repeating. And if I say yes, it tells me it is a duplicate value since it already added it to the table. Otherwise, it is working exactly as I had hoped. I am not sure why it keeps repeating the process.

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Could we please see your code?

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    Pyrgos is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2022
    Posts
    3
    Ik heb de nihil database gebruikt, want ik had ook zo'n vraag. Alleen in combo2 krijg ik het getal en niet de tekst. Graag hoor ik hoe dat komt.

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    @Pyrgos: you would get better answers if you start your own thread and use Google translate to post your question in English:
    I used the nil database, because I also had such a question. Only in combo2 do I get the number and not the text. I would like to hear how that is.
    You probably get the number because you didn't set up your combo2 right; it needs to have 2 columns with the first column the bound one and also hidden (column width=0").
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    Pyrgos is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2022
    Posts
    3
    Vlad,

    Het is dankzij je opmerking gelukt.

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Dual Column Comboxbox
    By dotcanada in forum Access
    Replies: 6
    Last Post: 10-13-2016, 03:48 PM
  2. Replies: 3
    Last Post: 09-16-2012, 12:34 AM
  3. Replies: 3
    Last Post: 09-11-2012, 10:14 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