Displaying the contents of a combo box after inserting data.

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

Answer 1

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?

Answer 2

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.

Answer 3

Have you tried using the Wizard to create the Combobox?

Answer 4

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.

Answer 5

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.

Answer 6

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.

Answer 7

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!

Answer 8

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.

Answer 9

Hi,

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

Answer 10

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!

Answer 11

Hi,

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

Answer 12

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.

Answer 13

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...

Answer 14

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.

Answer 15

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.

Answer 16

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.

Answer 17

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.

Answer 18

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.

Answer 19

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.

Answer 20

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.

Answer 21

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.

Answer 22

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

Answer 23

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 [e-mail].

Answer 24

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.

Answer 25

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

Answer 26

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?

Answer 27

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.

Answer 28

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.

Answer 29

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).

Answer 30

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.

Answer 31

If you could explain to me how to do this, I'll do it.

Answer 32

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.

Answer 33

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.

Answer 34

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.

Answer 35

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.

Answer 36

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).

Answer 37

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...