Results 1 to 7 of 7
  1. #1
    ryanmce92 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    48

    Only certain fields appearing in lookup

    Hi All,



    Ive been trying to find the records in my form based on the selection in a combo box and I have been using the following code:

    Code:
    Private Sub cboSupplierName_Change()
    tboStreetNo.Value = cboSupplierName.Column(1)
    tboAddress1.Value = cboSupplierName.Column(2)
    tboAddress2.Value = cboSupplierName.Column(3)
    tboCity.Value = cboSupplierName.Column(4)
    tboCounty.Value = cboSupplierName.Column(5)
    tboCode.Value = cboSupplierName.Column(6)
    cboCountry.Value = cboSupplierName.Column(7)
    tboName.Value = cboSupplierName.Column(8)
    txtNo.Value = cboSupplierName.Column(9)
    tboEmail.Value = cboSupplierName.Column(10)
    txtDelivery = cboSupplierName.Column(11)
    txtQuality.Value = cboSupplierName.Column(12)
    txtExp.Value = cboSupplierName.Column(13)
    End Sub
    I'm not sure why this only shows the street number of the Supplier, so it is only showing the first column, anyone have any ideas as to why this would be happening?. Any help would be greatly appreciated.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    This code is not finding anything, it is storing data.
    To filter records,
    use AFTERUPDATE() (not cboSupplierName_Change)
    to filter name records by
    Code:
    sub cboSupplierName_AFTERUPDATE()
       me.filter = "[name]=' " & cboSuplierName & " ' "
       me.filteron = true
    end sub

  3. #3
    ryanmce92 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    48
    I want this form to allow users to edit the data as well as view it, if they were to choose a supplier they could change phone number or name, would the code above allow me to do this?.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I am going to go out on a limb here and guess your tables are not normalized. You need to define your entities and create relationships between them that support a normalized data structure. If you do not have this you will not be able to create a graphical user interface with forms. If you do not have your Relational Database in order, you will struggle to create an application that users can interact with.

    I suggest you take a few minutes to look at this YouTube video and then follow up but researching Normalization Rules for relational databases.
    https://www.youtube.com/watch?v=-fQ-bRllhXc

  5. #5
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Why are you trying to re-invent the wheel, here? The Combobox Wizard will do this for you, in about minute or two! Here's a quick step-by-step:

    If you haven't already done so, create a Form based on your Table, including all the Fields you want displayed.

    Then simply:

    • Add a Combobox to your Form.
    • The Combobox Wizard will pop up
    • Select "Find a record based on the value I selected in my combobox."
    • From the Table the Form is based on, click on the Field you're searching by (a Field that is unique for each Record) to move it to the right side.
    • Hit Next.
    • Size the column appropriately.
    • Hit Next.
    • Name the Combobox something appropriate.
    • Hit Finish.

    Now you can drop the Combobox down and scroll down to the item to search by, or you can start to enter the item and the Combobox will "autofill" as you type. Hit <Enter> and the Record will be retrieved.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The code is probably setting value of data in record if the controls are bound, which I doubt you want to happen.

    Controls used to input filter/search criteria must be UNBOUND. So in addition to missinglinq post, review: http://www.allenbrowne.com/ser-62.html
    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.

  7. #7
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Consulting Allen Browne is an excellent idea; his website

    Allen Browne's Index Page

    is always my first stop, if I run into a problem! It's a good idea to bookmark the above URL.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

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

Similar Threads

  1. What to use instead of lookup fields?
    By alexjose in forum Forms
    Replies: 7
    Last Post: 07-03-2013, 06:39 PM
  2. LookUp Fields...?
    By djclntn in forum Database Design
    Replies: 19
    Last Post: 12-25-2012, 04:02 PM
  3. Lookup Fields
    By ericjsr in forum Programming
    Replies: 2
    Last Post: 07-16-2012, 09:15 AM
  4. calculated fields appearing in table
    By jamhome in forum Access
    Replies: 16
    Last Post: 07-19-2011, 02:57 PM
  5. Lookup Fields
    By mikel in forum Access
    Replies: 3
    Last Post: 03-03-2010, 07:56 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