none
Access 2013 Form using VBA to allow Data from a Combo box list to be inserted into the form's current record. RRS feed

  • Question

  • I have a form where the Detail refers to named fields in a new record to be generated in a table (A).
    the Detail also has a Combo box which gives me a drop-down list of available data of records in Table (B) via a sorted Query on Table (B) and separated into Named Columns.

    Using the .columns(n) facility of the Combo box, I have displayed the data I need for the new record in Table (A) as several named fields in the Form's Detail (such as [CB-A1], [CB-B2] and [CB-C3])

    I believe I can use the Me! facility of VBA to pass specific data from the named fields derived from the Combo box into the named fields (such as [ItemID] and [LocID]) of the new record for Table (A). I also need to pass (e.g) a date and a Week-Number from another open Form to the new record, though I'm not quite sure how to code this.  If I specify an (OnEnter) subroutine for a field shown in the Detail, which is not included in the Tab Order but to which the Cursor is moved with a Mouse Click, then I believe I can pass each piece of required data within that one subroutine.
    I can then save the new record and start on another one . . .

    Any help would be much appreciated, thank you.


    John Budding

    Sunday, October 19, 2014 1:05 AM

Answers

  • Hi John,

    Glad you got it working, and thanks for posting your feedback.

    Below article explains the uses between Bang vs Dot in VBA:

    Bang and Dot

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    • Marked as answer by johnbudding Wednesday, October 22, 2014 12:30 AM
    Tuesday, October 21, 2014 4:17 AM
    Moderator
  • Hi John,

    Are the Text Boxes you are using in the Detail Unbound or Bound?

    Bound means to a Table, and Unbound not to a Table?

    If Unbound, you can derive the Data from a Combo box to the Text Box by using a Statement like this in the After Update event of your Combo box:

    Private Sub YourComboBoxName_AfterUpdate()
    
    Me.YourTextBoxName = Me.YourComboBoxName.Column(1)
    
    
    End Sub

    Note: The numbering of the Columns in the Combo box start at 0. So your first column is 0, the second is 1 etc.

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    • Marked as answer by johnbudding Tuesday, October 21, 2014 3:36 AM
    Monday, October 20, 2014 4:54 PM
    Moderator

All replies

  • Hi John,

    Are the Text Boxes you are using in the Detail Unbound or Bound?

    Bound means to a Table, and Unbound not to a Table?

    If Unbound, you can derive the Data from a Combo box to the Text Box by using a Statement like this in the After Update event of your Combo box:

    Private Sub YourComboBoxName_AfterUpdate()
    
    Me.YourTextBoxName = Me.YourComboBoxName.Column(1)
    
    
    End Sub

    Note: The numbering of the Columns in the Combo box start at 0. So your first column is 0, the second is 1 etc.

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    • Marked as answer by johnbudding Tuesday, October 21, 2014 3:36 AM
    Monday, October 20, 2014 4:54 PM
    Moderator
  • Thank you. 
    I wanted to be certain that the data I had picked was appropriate, so I moved the Columns from the Combo Box into named fields on the Form Detail so I could view them in full length and from those fields into the bound boxes detailing the new record.
    I was then able to complete the variable data for the new record and then save it.

    I found that Me! seems to work in VBA just as well as Me.   when referring to the open Form . . .


    John Budding

    Tuesday, October 21, 2014 3:49 AM
  • Hi John,

    Glad you got it working, and thanks for posting your feedback.

    Below article explains the uses between Bang vs Dot in VBA:

    Bang and Dot

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    • Marked as answer by johnbudding Wednesday, October 22, 2014 12:30 AM
    Tuesday, October 21, 2014 4:17 AM
    Moderator