Results 1 to 15 of 15
  1. #1
    Join Date
    May 2009
    Posts
    48

    Unanswered: Using combo box to display selected data

    Hello all,
    I have Access 2003 and am new to databases and am looking for some assistance. I created a form with the wizard and a combo box under the assumption that I could select a product and all the information for that product would be displayed so I could edit it if necessary. After selecting the product, none of the other product-specific data is displayed. Can someone please tell me what I have done wrong?

    Thanks in advance!!
    Andrew
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2009
    Location
    RI
    Posts
    31
    You haven't done anything wrong. You need to use the Dlookup function on the fields other than product ID to display the relevant data (MS Access help explains DLookup very well with examples).

    You're better off setting your form's record source to a query, which uses a dlookup expression on those relevant fields to do that as opposed to using vba or macros. But as the saying goes, there is more than 1 way to skin that cat.

  3. #3
    Join Date
    May 2009
    Posts
    48
    Thanks rendrag!! Checking it out now.

    Andrew

  4. #4
    Join Date
    Apr 2009
    Location
    RI
    Posts
    31
    Quote Originally Posted by rendrag
    You're better off setting your form's record source to a query, which uses a dlookup expression on those relevant fields to do that as opposed to using vba or macros.
    I actually misspoke there. What I mean to say was that you're better off setting your form's recordsource to a query where you have established those dlookup functions, versus using vba or macros to accomplish that on the form itself.

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    That's an awful lot of work for something that Access will do for you in less than a minute!

    Delete your current combobox.

    Add a new combo box 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 or query the form is based on, click on the field you're searching by (ProductName) to the right side.

    Hit Next.

    Size the column appropriately.

    Hit Next.

    Name the combobox.

    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.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    May 2009
    Posts
    48
    Thanks Missinglinq!!! That is much easier and I can understand that better. I tried a few Dlookups variations but could not get anything to work.

    Andrew

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad we could help!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    May 2009
    Posts
    48
    I have a related question. I already have another form that is not related to the AddEditProducts table. I need to add a combo box to the other form that will lookup the Product Name from the AddEditProducts table and display the Unit Price. I tried
    Code:
    =Products!UnitPrice
    in the control source but that does not work, and the form will not allow me to add a combo box linked to the Products table in the manner described above. What do I need to do for this to work?

    I have attached a sample database. Basically, I need to be able to select the Product Name from the Table1 form and have the applicable Unit Price displayed on the Table1 form.

    Thanks,
    Andrew
    Attached Files Attached Files
    Last edited by drewship; 06-01-09 at 14:03. Reason: attached sample db

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    There's no reason that you shouldn't be able to, but you can't use the

    "Find a record based on the value I selected in my combobox."

    option! This time select

    "I want the combobox to look up the values in a table or query"

    then select the Products table and when the field box comes up, select ProductName and UnitPrice, in that order.

    Then use this code, substituting your textbox and combobox names for YourTextBox and ComboBoxName.

    Code:
    Private Sub ComboBoxName_AfterUpdate()
     Me.YourTextBox = Me.ComboBoxName.Column(2)
    End Sub
    If your table didn't have a Primary Key designated, the line would have been

    Me.YourTextBox = Me.ComboBoxName.Column(1)
    Last edited by Missinglinq; 06-01-09 at 14:14.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    Join Date
    May 2009
    Posts
    48
    I am unable to see the Table1 form in vbeditor. I created the combo box and test field as you recommended, I can only see the AddEditProducts form in vbeditor and added the code on that form (commented out) until I could move it to the Table1Form when I can get to it. Any suggestions on making the Table1Form visible in vbeditor?

    Thanks,
    Andrew
    Attached Files Attached Files

  11. #11
    Join Date
    Apr 2009
    Location
    RI
    Posts
    31
    to make it visible, find a control on your form, pick an event, (on load, on click, it matters little), choose [Event Procedure] and then click the [...] button. It should then be visible in the VB editor

  12. #12
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    To be honest, I have no idea what you mean by

    "I am unable to see the Table1 form in vbeditor. "

    You won't see any forms in the "vbeditor."

    From Design View for your Table1 form hit <Ctrl> + <G>. This'll take you to it's code module. Then paste this in:
    Code:
    Private Sub comboSelect1_AfterUpdate()
     Me.UnitPrice1 = Me.comboSelect1.Column(2)
    End Sub
    Code:
    Private Sub comboSelect2_AfterUpdate()
     Me.UnitPrice2 = Me.comboSelect2.Column(2)
    End Sub
    Now when you select an item fromeither combobox the unit price will appear in the appropriate textbox.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  13. #13
    Join Date
    May 2009
    Posts
    48
    Thanks rendrag and Missinglinq. I have a little experience with vbeditor and usually I can go to Macros, VBEditor to directly edit the code. That is where was looking to make the updates.

    Andrew

  14. #14
    Join Date
    May 2009
    Posts
    48
    Missinglinq and rendrag, I found that <Ctrl> + <G> is just another way to get into vbeditor. I have the same result as you can see in the first 2 screen shots I have uploaded. The Table1Form is not displayed thus I can not add any code to it.

    As I was writing this and fooling with the db, I somehow was able to get the Table1Form to show up in vbeditor and made the code additions as seen in the 3rd screenshot.

    Thanks for all your help!!
    Andrew
    Attached Files Attached Files

  15. #15
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Don't ever use the window you have showing to the left of the vbeditor, so never even thought of that when you said you couldn't 'see' the form in the edit window. I always go from Design View for the particular form; this keeps code from being wrongly added to the wrong form.

    I suspect the reason the form ddidn't show originally was because it had no code in its module.

    Glad you got ti working.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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