none
Displaying the contents of a combo box after inserting data. RRS feed

  • Question

  • Hello all!  Hope I can get some help!

    I am reading a table in my Access 2016 DB and trying to populate a combobox with the data, however, the data never displays when you click the view arrow on the side of the box.

    I tried entering the data into a list box and it went in just fine.  So I don't understand why it works for a list box and not a combo box.

    The following is the routine that I'm using (please excuse the commented out code used for debug).

    Private Sub effDateVIT1_GotFocus()
        Dim rs As dao.Recordset
        Dim db As dao.Database
        Dim strsql As String
        Dim cntr As Integer
        Dim itemcount As Integer
        Dim thedata As String
        
        'If (effDateVIT1.ListCount > 0) Then
        '    For itemcount = 1 To effDateVIT1.ListCount
        '        effDateVIT1.Rowsource.
        '    Next itemcount
        'End If
        
        Set db = CurrentDb
          
        cntr = 0
        strsql = "select vendoritemtable.[VIT Key], vendoritemtable.[effective date] from vendoritemtable where (vendoritemtable.[vendor name] like '" & keyvendor & "' and vendoritemtable.[product code] like '" & keypc & "');"
        Set rs = db.OpenRecordset(strsql)
        
        While rs.EOF = False
          cntr = cntr + 1
          thedata = RevDate(rs![Effective Date])
          effDateVIT1.AddItem thedata
    '      Listbox.AddItem thedata
          Debug.Print rs![Effective Date]
          rs.MoveNext
        Wend
        
    done:
        rs.Close
        Set rs = Nothing
    End Sub

    The following is some sample data; the "effective date" should list in the combo box:

    <tfoot></tfoot>
    VendorItemTable
    VIT Key Vendor Name Product Code Effective Date Base Cost Base Discount Base Rebate Frt Chrg 0-50 Frt Chrg 50+ Fuel SC Fuel SCpercent Tarp Chrg Scrap Chrg Additonal Cost Title Add Cost Remarks
    14 Steel Dynamics Structural Division W818 20180216 $16.00 ($1.00) ($1.00) $1.50 $1.78 $0.50 0 $1.00 $1.00 $0.00

    15 Steel Dynamics Structural Division W818 20180217 $17.00 ($1.00) ($1.25) $1.60 $1.89 $0.75 0 $1.10 $1.10 $0.00

    16 Steel Dynamics Structural Division W818 20180218 $18.00 ($1.00) ($1.50) $1.55 $1.83 $1.00 0 $1.20 $1.20 $0.00

    Thank you in advance for any help that you can give me.

    Robert

    Tuesday, February 20, 2018 7:23 PM

Answers

  • Hi,

    I ended up using the following SQL statement for the Row Source of the Combobox:

    SELECT vendoritemtable.[VIT Key],
    vendoritemtable.[effective date]
    FROM vendoritemtable
    WHERE (((vendoritemtable.[Vendor Name])=Forms!NewVendorItemForm.VendorNameVIT)
    And ((vendoritemtable.[Product Code])=Forms!NewVendorItemForm.ProductCodeVIT));

    Here's a video of my result:

    Hope it helps...

    • Marked as answer by CSC Robert Tuesday, March 6, 2018 3:27 PM
    Monday, March 5, 2018 9:58 PM

All replies

  • Hi Robert,

    May I ask why are you trying to use code to populate a Combobox? You should be able to simply assign the table's name to the Row Source of the Combobox. Have you tried doing it that way? If so, what happened?

    Tuesday, February 20, 2018 7:48 PM
  • The combo box didn't populate as I had expected.

    I have deleted the field several times and recreated it from scratch just in case I did something wrong during creation, however, I kept getting the same results.  So, I tried it programmatically.

    Tuesday, February 20, 2018 8:22 PM
  • Have you tried using the Wizard to create the Combobox?
    Tuesday, February 20, 2018 8:27 PM
  • I used the wizard the first couple of times I created the control.  Nothing showed.  Weird.

    Programmatically, when I load the control, the data appears in the Row Source, but not on the screen in the pull down.

    Tuesday, February 20, 2018 8:37 PM
  • Very interesting... You might try sharing a copy of your db so we can help you figure out what is happening with it.

    Do you think maybe your install of Access needs to be repaired? Try creating a blank new database and see if it has the same problem.

    Tuesday, February 20, 2018 8:51 PM
  • I did already.  This is the copy that I recreated.  I have my system manager reviewing the DB on his machine to see if it is machine related.  If not, then I'll recommend he reinstall office on my machine.

    On a side note, this is a fast machine...This is an 8 core machine with 16GB of memory and an SSD drive.  Any changes that I make to this database is painfully slow!!  Trying to get the property sheet to come up after changing fields can take 45-60 seconds.  I'm wondering if this installation is screwed up somehow.

    Tuesday, February 20, 2018 9:00 PM
  • Regarding the slow response when going to design view, maybe you could try this approach, which I read somewhere.

    Assuming you're working with a split database, try making a copy of your frontend file for development and then delete all linked tables from it. Then, import all the previously linked tables from the backend into local tables.

    See if the design response time is different from before.

    Cheers!

    Tuesday, February 20, 2018 9:25 PM
  • Found the problem with the system performance!!

    I had some security software on my system which is required by the bank for me to access their system.  When we removed the software, everything ran faster. 

    However, that didn't solve my problem with the lookup in the combo box.  That still doesn't work.  I used the wizard.  I tweaked the parameters.  Still nothing in the pull down.

    Wednesday, February 21, 2018 3:58 PM
  • Hi,

    Were you able to duplicate the problem with a new database file or using a different computer?

    Wednesday, February 21, 2018 4:01 PM
  • Just figured out the problem.  Box is listing values now. Selection criteria was stated incorrectly.

    Funny how things work when you state them correctly!!

    Thanks for all the help!!  Have a nice day!

    Wednesday, February 21, 2018 4:09 PM
  • Hi,

    Congratulations! Glad to hear you got it sorted out. Good luck with your project.

    Wednesday, February 21, 2018 4:17 PM
  • Nope...I was wrong.

    I think my problem is that I need clarification when it comes to doing this task:

    Can you read the current table you are working on to retrieve records that fit a certain criteria that was harvested from other tables?

    In the example above, I'm trying to read all the records associated with Steel Dynamics Structural Division.  The problem is that when I put another company into the table, its data comes out too.  If I use too complex selection criteria, I get nothing.  I'm almost back to writing code to load the combo box again.

    Wednesday, February 21, 2018 6:34 PM
  • Hi,

    You might start with creating a query first. Once you get the query returning the correct result you want to see, then you can use the Combo Wizard to use the query for your Combobox.

    Hope it helps...

    Wednesday, February 21, 2018 6:40 PM
  • Already thought of that.

    I created a query where it selected everything, then put selection criteria on.  Now it selects nothing.

    I believe the problem is my understanding of how to create the syntax for the selection criteria.  Even in the wizard it doesn't ask for criteria, only a global fetch.  You can go into the query builder, it doesn't make clear how to state your selection criteria.

    Wednesday, February 21, 2018 6:58 PM
  • Hi,

    If your query won't show the correct results you need, then the Wizard can't help you. If the query is empty, then the Combobox will be empty too.

    Can you post a screenshot of how you are setting up the criteria on your query?

    Also, it might help, can you post the SQL statement for your query? Thanks.

    Wednesday, February 21, 2018 7:08 PM
  • Base query:  SELECT vendoritemtable.[VIT Key], vendoritemtable.[effective date] FROM vendoritemtable;

    Additional table fields:   vendoritemtable:  [product code], [vendor name]

    Form fields:  vendornamevit, productcodevit

    need all effective dates where [vendor name] = vendornamevit and [product code] = productcodevit

    Everything I've tried hasn't worked which is why I'm asking for help from anyone smarter than me (there are many and it isnt' too difficult to be smarter).

    As I've said before, I think it's a syntax issue with me.  This is my first endeavor working with Access.  I've been away from SQL for 20 years.  It's beginning to come back slowly.

    Wednesday, February 21, 2018 8:09 PM
  • Hi,

    What is the name of your form? Try adding a reference to the form in your criteria. For example:

    Field: [vendor name]

    Criteria: Forms!FormName.vendornamevit

    The same for the other criteria but try it one at a time first.

    Are you not able to post screenshots? An image would help us understand the problem better.

    Wednesday, February 21, 2018 9:00 PM
  • The following is a snapshot of the field in question with the sql.

    If there is anything else you need, please let me know!!

    SELECT vendoritemtable.[VIT Key], vendoritemtable.[effective date] FROM vendoritemtable WHERE [VendorItemTable]![Vendor Name]=[Forms]![NewVendorItemForm]![VendorNameVIT] And [VendorItemTable]![Product Code]=[Forms]![NewVendorItemForm]![ProductCodeVIT];

    Thank you

    PS. It wouldn't let me send a screen snap. Something about verifying my account.  Thought I already did that.

    Wednesday, February 21, 2018 9:21 PM
  • Hi,

    Make sure the form is open with values in the Textboxes for the vendor name and product code. Then open the query and let us know if you get the right result.

    Wednesday, February 21, 2018 9:33 PM
  • There are values in the textboxes it uses.  They are filled in first and their values are valid.  NO, I don't get the correct result; in fact, I get nothing in the drop down.
    Wednesday, February 21, 2018 9:37 PM
  • Hi Robert,

    Let's take the dropdown out of the equation for now. Can you run the query and see if you get any results? If not, try modifying the query to use the same values on the form. For example, if the form has "vendorA" and "productB", try changing the criteria in the query to say:

    [Vendor Name]="vedorA" AND [Product Code]="productB"

    Then open the query to see if you get any results.

    Wednesday, February 21, 2018 9:54 PM
  • Did you by any chance use the 'lookup field' wizard when designing the VendorItemTable table?  If so, the actual values of columns will not always be what you see, but a hidden numeric value referencing a numeric key of the relevant referenced table.  Consequently, if you are referencing a parameter of text data type, the values will not match.  In such a context it is usual to reference a combo box as the parameter, rather than a text box.  The value of the combo box will be a hidden numeric value, achieved by setting the control's ColumnWidths property to zero.

    For reasons why the 'lookup field' wizard should be avoided like the plague, see:

    http://theaccessweb.com/lookupfields.htm


    Ken Sheridan, Stafford, England

    Wednesday, February 21, 2018 10:07 PM
  • Hi CSC,

    It would be helpful if you could share us a simple access database which could reproduce your issue, and then we could work closely with you, and provide the exact solution.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, February 22, 2018 6:17 AM
  • Sorry about the delay...I'm back now.

    As for your request, I created a query and the 4 lines that I have in the database are displayed.  So, we know we can query the database and get data.

    Thursday, March 1, 2018 3:34 PM
  • To keep things simple, I've included a abbreviated database:

    <tfoot></tfoot>
    VendorItemTable Query1
    Vendor Name Product Code Effective Date Base Cost
    Steel Dynamics Structural Division W818 20180216 $0.00
    Steel Dynamics Structural Division W818 20180217 $17.00
    Steel Dynamics Structural Division W818 20180218 $18.00
    Alro Steel Corp W818 20171111 $15.00

    I really don't want to be bogged down with the additional garbage.  If the query works here, It will work with all records (or really should work with all records).

    Thank you

    Thursday, March 1, 2018 3:42 PM
  • Hi,

    Okay, thanks. Now that we know the query works, can you create a new blank form and add a combobox to it using the Wizard and select the working query during the process? Does the combobox display the same set of data from the query?

    Thursday, March 1, 2018 3:51 PM
  • I really don't understand what's going on now!!

    The query works.  I put parameters in and the table comes up with the correct data.

    I put the query into a field on a form, nothing!!  It must be me.  I've got to be doing something wrong.

    Thursday, March 1, 2018 4:24 PM
  • I put parameters in and the table comes up with the correct data.

    What do you mean you put parameters in? Comboboxes cannot ask you for parameters. Try using a query that doesn't need parameters first just to make sure a combobox will display the same result. If not, then we can troubleshoot it. Otherwise, if you're using a parameterized query for the combo, then maybe we'll need to use code to assign the Row Source to the combobox with the parameters already resolved. How did you enter/put the parameters? Did you type them in, or are you still referring to a form textbox? If a form textbox, then it should be okay as long as the form is open. If so, it might be easier to troubleshoot if you create a new blank form and add the textboxes and combobox on this new form. Then, adjust the query to look in this new form for the parameters. Then just select the query as the Row Source for the Combobox.
    Thursday, March 1, 2018 4:31 PM
  • I wrote a query, attached it to the combo box, but it doesn't appear to display the dates like I wanted.  What I find interesting is that when I ask it to display the customer name, it does.  Really, what's the difference between a customer name string and a date string (and it is a date string, not a julian date or system date).
    Thursday, March 1, 2018 6:42 PM
  • Okay, it is getting really hard to find a solution without seeing what you're looking at. Are you able to share your database with us? If not, maybe try to work on posting some screenshots for us to see what you're working with.
    Thursday, March 1, 2018 6:47 PM
  • If you could explain to me how to do this, I'll do it.
    Thursday, March 1, 2018 6:53 PM
  • If you could explain to me how to do this, I'll do it.

    Hi,

    It's actually what I have been trying to do since the beginning. For example, try the following steps:

    1. Create a query without parameters and see if you can see records returned when you run it.

    2. Next, create a new blank form and add a combobox using the Wizard and selecting the working query for the combobox.

    If the query (without parameters) work by simply opening the query, then the combobox based on the same query should display some items as well.

    If the combobox still doesn't work, you might post the exact steps you used to create the query and the form and the combobox.

    Thanks.

    Thursday, March 1, 2018 6:56 PM
  • I did what you said.  Every time I enter the first two parameters into their fields, when I click on the combo box, a dialog box pops up and ask for the data I just entered in the fields on the form.  Once I fill in the dialog boxes it pops up, the combo box fills with the appropriate date data.
    Friday, March 2, 2018 8:13 PM
  • I did what you said.  Every time I enter the first two parameters into their fields, when I click on the combo box, a dialog box pops up and ask for the data I just entered in the fields on the form.  Once I fill in the dialog boxes it pops up, the combo box fills with the appropriate date data.

    Hi Robert,

    It seems we must not be speaking the same "language." You said you did what I said, but I said, numerous times not to use any parameters. I am doing this for troubleshooting purposes. If you can make a combobox work without parameters, then we can move on to making a combobox work with parameters.

    I also asked you to post the exact steps you used, if my instructions did not produce the correct results. So, can you please tell us every step you took in trying to follow my instructions?

    Again, I think it would go a little faster if you could share your database. You said to tell you what to do, but it is still proving to be not enough to get to a solution sooner.

    At the very least, telling us exactly what you did should help us see where you may have diverted from what we were trying to have you do instead.

    Friday, March 2, 2018 8:45 PM
  • Yes.  I was asking how to share the database with you, not how to make the changes.

    The following is the select statement in the row source field:

    SELECT [vendoritemtable].[effective date] FROM vendoritemtable WHERE [vendoritemtable].[vendor name] = 'steel dynamics structural division' and [vendoritemtable].[product code] = 'w818';

    The pull down displays the correct information.

    Friday, March 2, 2018 9:11 PM
  • Hi,

    If you're willing to share a copy of your database, that would be great. You can either upload it to a file sharing service like OneDrive or DropBox or GoogleDrive and post a link for download here, or you can email it to me directly (email address in my profile).

    Friday, March 2, 2018 9:40 PM
  • Hi,

    I ended up using the following SQL statement for the Row Source of the Combobox:

    SELECT vendoritemtable.[VIT Key],
    vendoritemtable.[effective date]
    FROM vendoritemtable
    WHERE (((vendoritemtable.[Vendor Name])=Forms!NewVendorItemForm.VendorNameVIT)
    And ((vendoritemtable.[Product Code])=Forms!NewVendorItemForm.ProductCodeVIT));

    Here's a video of my result:

    Hope it helps...

    • Marked as answer by CSC Robert Tuesday, March 6, 2018 3:27 PM
    Monday, March 5, 2018 9:58 PM