Results 1 to 15 of 15
  1. #1
    spidge32 is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2021
    Posts
    58

    Combo Box, Filling in a field dependent on a combo box selection

    Hi, I'm new to Access and VBA. I have inherited a small project which has several Combo Boxes and I need to extract and complete the form with data from other tables dependent on the combo box selection. It is a very simple combo box table with 2 fields.



    WireCode, WireDescription.

    On the form is a combo box attached to the table wire.

    On the selection of the WireCode I need it to update a field on the form fldWdgWireDesc, this is the code I have tried in lots of variations but I cannot get past the error "Invalid Outside Procedure"
    This is the code.

    Private Sub_WireCombo_AfterUpdate


    [fldWdgWireDesc] = DLookup("[tblWire]![WireDescription]", "tblWire", "[Wirecode]=[fldWdgWire]")


    Me.Refresh



    endsub

    Any help would be appreciated.

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    The sub syntax has an underline that shouldn't be there:

    Private Sub WireCombo_AfterUpdate()

    and the dlookup:

    fldWdgWireDesc = DLookup("WireDescription", "tblWire", "Wirecode=" & fldWdgWire)

    Above assumes fldWdgWire is a number.


    The whole picture above may be the wrong approach. Generally, the purpose of the combobox is to do the lookup and find the desired value. Maybe the dlookup is redundant.
    Perhaps you should post your DB so that we can take a look and make more focused recommendations.

  3. #3
    spidge32 is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2021
    Posts
    58
    Thank you the quick reply, removed underline in sub.

    The Fields WireCode and WireDescription are both Short Text. Can you explain each element of the DLookup? To me it seems like this, but maybe I'm miss undetstanding the syntax. The DLookup function is used throughout the project to populate fields in forms so I would like to keep the code similar if possible.

    [fldWdgWireDesc] = DLookup("[tblWire]![WireDescription]", "tblWire", "[Wirecode]=[fldWdgWire]")

    Field to be Updated
    in Table being Editied Lookup Field with Data Lookup Master Combo Box Result
    Table Table Field
    Does this make sense, also if I uploaded the db do I upload the whole accb.db?

  4. #4
    spidge32 is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2021
    Posts
    58
    Sorry, when I posted it lost the layout.
    [fldWdgWireDesc] , fld being updated on formand underlying table
    = DLookup("[tblWire] the table being accessed by the DLookup
    ![WireDescription]" the field in the table to be found
    "tblWire", the table being accessed by the DLookup
    "[Wirecode]= [fldWdgWire]") the field (Primary Keyed) being searched for

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    The proper syntax when the lookup criteria is short text (a string):

    fldWdgWireDesc = DLookup("WireDescription", "tblWire", "Wirecode=
    '" & fldWdgWire & "'")

    Look carefully to see the red single quotes.

    Your description of the Dlookup syntax elements is correct.

    Yes, you would upload the whole db. See the instructions at the website header "How to Attach Files"

  6. #6
    spidge32 is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2021
    Posts
    58
    Hi,
    I added the line as you suggested and my code looks like this.

    Option Compare Database
    Option Explicit
    Private Sub WireCombo_AfterUpdate()


    fldWdgWireDesc = DLookup("WireDescription", "tblWire", "Wirecode='" & fldWdgWire & "'")

    endsub

    I now get the error Sub or Function not defined, any ideas? I have looked up similar posts but cannot find a definite answer.

  7. #7
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Could it be that you have a missing reference?
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  8. #8
    spidge32 is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2021
    Posts
    58
    Hi Bob,
    Not sure what you mean, fldWdgWireDesc is the field on the form, "WireDescription" is the field in the wire table "tblWire", "Wirecode='" & fldWdgWire & "'" where wirecode = fldWdgWire is the value returned to the combo box and fldWdgWire is the actual field for the value returned by the cobo box.
    This dLookup function is most confusing. I tried to copy a dLoookup which works on the form and just replaced the fields. This dLookup works.

    Private Sub BobbinCombox_AfterUpdate()
    [fldTrfLaminationPartNum] = DLookup("[tblBobbin]![lamination]", "tblBobbin", "[bobbin code]=[fldTrfBobbinCode]")
    [fldTrfStackHeight] = DLookup("[tblBobbin]![stack height]", "tblBobbin", "[bobbin code]=[fldTrfBobbinCode]")
    [fldLaminationGrade] = DLookup("[tblBobbin]![Lamination Grade]", "tblBobbin", "[bobbin code]=[fldTrfBobbinCode]")
    [fldCoilWrapper] = DLookup("[tblBobbin]![Coil Wrapper]", "tblBobbin", "[bobbin code]=[fldTrfBobbinCode]")
    [fldCoilWrapperQty] = DLookup("[tblBobbin]![Coil Wrapper Qty]", "tblBobbin", "[bobbin code]=[fldTrfBobbinCode]")
    [fldCoilUs] = DLookup("[tblBobbin]![Coil Us]", "tblBobbin", "[bobbin code]=[fldTrfBobbinCode]")
    [fldCoreBonding] = DLookup("[tblBobbin]![Laminated/Welded]", "tblBobbin", "[bobbin code]=[fldTrfBobbinCode]")
    [fldCoreVarnish] = DLookup("[tblBobbin]![Varnish]", "tblBobbin", "[bobbin code]=[fldTrfBobbinCode]")
    [fldCoreBrackets] = DLookup("[tblBobbin]![Brackets]", "tblBobbin", "[bobbin code]=[fldTrfBobbinCode]")
    [fldCoreBracketsQty] = DLookup("[tblBobbin]![Brackets Qty]", "tblBobbin", "[bobbin code]=[fldTrfBobbinCode]")
    [fldCoreTerminalRail] = DLookup("[tblBobbin]![Terminal Rail]", "tblBobbin", "[bobbin code]=[fldTrfBobbinCode]")
    [fldCoreTerminalRailQty] = DLookup("[tblBobbin]![Terminal Rail Qty]", "tblBobbin", "[bobbin code]=[fldTrfBobbinCode]")
    [fldCoreBonding] = DLookup("[tblBobbin]![Laminated/Welded]", "tblBobbin", "[bobbin code]=[fldTrfBobbinCode]")
    Me.Refresh




    End Sub

  9. #9
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Open the IDE
    Select Tools and then References
    Lok for MISSING in the list
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why does all this data have to be duplicated to another table instead of just saving a record ID?

    Instead of 13 DLookup() calls to table, could instead open a recordset of the single record and reference fields of recordset for each value to save.

    Another option is to include all fields in combobox RowSource and reference combobox columns for each value to save.

    I tested syntax without concatenation and discovered it worked. Interesting, never tried that before.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    spidge32 is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2021
    Posts
    58
    This database was created by someone else, there are 3 main tables, parts, winding header with 107 fields and termination which has 40 fields. There are lots of look ups from subsidiary tables which are saved in the winding header and termination tables. So I’m trying with my limited knowledge of access to add more descriptive fields on a report.

  12. #12
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Post #8 indicates to me that the form is unbound, and the dlookups are filling in the form's textboxes.
    Again, posting the DB would save a LOT of back and forth trying to define the problem.
    For example, it took 11 posts before it was disclosed that the problem area is actually a report.
    Last edited by davegri; 07-11-2021 at 09:16 AM. Reason: corrected post #, more clarif

  13. #13
    spidge32 is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2021
    Posts
    58

    Apologies

    Quote Originally Posted by davegri View Post
    Post #8 indicates to me that the form is unbound, and the dlookups are filling in the form's textboxes.
    Again, posting the DB would save a LOT of back and forth trying to define the problem.
    For example, it took 11 posts before it was disclosed that the problem area is actually a report.
    My apologies for not including that it was an DB entry form I'm working with. Yes is seems that the person who created the form and tables has 3 main tables and is using the form to update these tables with all the data pulled from the minor tables, which duplicates most fields by the potential 5000 master records in the Parts table.

    I couldn't zip the file below 4MB with all the data, so I will strip out all the redundant data and upload later today.

    If I normalize the data and create logical relationships this should stop the duplication of the data.

    My first question is does Access automatically fill data on a form if the relationships are created, or do you still have to update by dLookup after each change?

    Cheers

    Bill

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    There are several ways to display related data. First would be form/subform arrangement to display master and dependent records. Another is to join tables in form/report RecordSource and another uses combobox as described in post 10 - instead of VBA saving to table, just expression in textbox: =comboName.Column(1).

    DLookup is last resort option. Domain aggregate functions cause slow performance in queries and forms/reports.

    Saving duplicate data is justified in situation where data changes over time, such as a product price. Alternative is to create a new record when product price changes and 'deactivate' the old record.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    spidge32 is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2021
    Posts
    58
    Hi June7,

    I have normalised the data over the project and created the relationships, so I will now try and create the form/s RecordSource and combo boxes. What screwed me up was the layout of the tables and the use of syntax in DLookup.

    Cheers

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

Similar Threads

  1. Replies: 3
    Last Post: 07-09-2018, 05:12 PM
  2. Replies: 1
    Last Post: 09-25-2015, 09:05 AM
  3. Replies: 4
    Last Post: 06-01-2014, 09:09 PM
  4. Replies: 3
    Last Post: 01-23-2014, 01:19 AM
  5. Replies: 3
    Last Post: 02-26-2009, 10:17 AM

Tags for this Thread

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