I'm trying to get this loop to work and it's not cooperating. I want it to show the values from tblIEU where there are entries for that specific ID and PersonnelID and where there isn't an entry to show a 1.



It's looping through just fine if there's a value in tblIEU, however when there isn't a value, it's returning an error saying "Object Required".

For example:

There will be entries on ID 1, 2, 4 in tblIEU. It'll loop through and display the proper quantities in the combo-boxes for IDs 1 and 2, but once it gets to 3 and discovers there's no entry for it listed in tblIEU it errors out. If the result for a specific ID/PersonnelID is NULL I want it to display "1" in the combo-box.

I would change the row source of the combo box. For example, if the combo box row source was this:

SELECT QTY FROM tblIEU WHERE PersonnelID=" & Me!txtID & " AND IEUID=" & rsIEU!IEUID

I would change it to this, using nz to change the nulls to 1:

SELECT nz(QTY,1) FROM tblIEU WHERE PersonnelID=" & Me!txtID & " AND IEUID=" & rsIEU!IEUID

You wouldn't need to loop through the code as you show - make a similar change to the row source of the combo box itself, and don't bother with looping through the recordset. If you need help, post the row source of your combo box.

- Daryl

I tried:


And I'm getting "Item not found in this collection".

I didn't mention this before, because I didn't think it mattered, but I have 19 total combo boxes, each loading unbound, loading off another loop. I went ahead and attached the entire database so you can see it. It's frmHome and under lstNameList_DblClick sub.

This post has been edited by voodoort: Apr 6 2011, 09:52 AM

I don't usually download zip files, so this is based on your post...

What line produces the error? (If you hit Ctrl-Break when you see the error, Access may take you right to the line.) I doubt it is from the nz() function, unless you are maybe using a different back-end database?

You can also add this right before the Set statement:
Debug.Print strSQLIEUQTY

Then you will see the SQL that your statment produces in the immediate window of the code pane. Copy/Paste that into your next posting.

- Daryl

I tried Debug.Print and the SQL statement looks good.


Is an example.

I tried the Control+Break and it just put a stop at the end of the Sub.

In all, no help at all, lol. Sorry!

Put a breakpoint in right after the debug.print (which you can comment out for now). Then try again. When the code gets to the breakpoint, step through the code until you get to the line that causes the error. Let us know what that line is (put that code in your next post).

- Daryl

This is the last line that goes through without the error:



I get the error when I step to the next line:

I don't think you can change the visibility of a control while it has the focus. You may need to set the focus on another control, then set the .Visible property to true, then you can bring the focus back if you want.

Does that help?

- Daryl P2000

Nope, same error. I put Me!lstNameList.SetFocus at the beginning of the With loop.

If you comment out the .Visible line, does everything else work on the form? Do we just need to make that combo box visible? If so, we may be able to use another event to turn the .visible true or false for the combo boxes. I just want to make sure everything else is good and we don't have some other issue.

- Daryl

Commenting out .Visible still gives me the error, however (as suspected) commenting out .Value = rsIEUQTY!QTY gives me the error. Even switching the Debug.Print to Debug.Print rsIEUQTY!QTY gives the same error. So something is fishy with rsIEUQTY!QTY, which is "created" from:


This post has been edited by voodoort: Apr 6 2011, 02:41 PM

How about adding this after your Set statement:
Debug.Print rsIEUQTY.RecordCount
Maybe there are no records that match the criteria.

The other thing you can do is copy the strSQLIEUQTY (if you still have the debug.print for that) from the immediate window into a blank SQL window, and switch to datasheet mode to see what is returned.

- Daryl

Here's the results I get from the RecordCount:



Which matches what should be showing up. The 1's indicate there's a record available for that particular part of the loop. Those parts are where numerical values other than "1" should be showing up in my combo boxes.

And I tried:



Which is a direct copy/paste from the Immediate window into a blank SQL Query and got back a result of "5", which is exactly what I wanted.

I tried:



Which is a direct copy/paste from the immediate window into another blank SQL Query and it gave back no result. Not a "0", but no entry at all. Which is what I wanted. There isn't an entry in tblIEU that matches that SQL statement. That's when the SQL statement errors out on me, when it gets to one there's no entry in tblIEU.

Basically, I have a form that has several checkboxes, lables, and combo-boxes created through a loop from another table, tblIEUItems. This table contains all the "items" to be issued to someone. When the form loads it loops through tblIEUItems and shows/hides the checkboxes, lables, and combo-boxes based on if there's an entry in the table (up to 20 items). The other table, tblIEU is the table that associates the person with the item from tblIEUItems. This is the table that should populate the value of the combo-box and the yes/no on the checkboxes (which works perfectly), as opposed to the other table, tblIEUItems populating the lables.

I uploaded a couple pictures for you. I know you're not keen on downloading files but maybe pictures? The first one shows the design view and the second one shows it in action. I currently have the .Value commented out for the combo-boxes otherwise it wouldn't load past the first lable.

OK, so what is happening is that if there are no records returned in the strSQLIEUQTY, then the recordset is empty. You cannot assign a .value without a record to assign the value to. That means when you get rsIEUQTY.RecordCount = 0, then you cannot assign the .Value (or any other field) a value.

The question is, what do you want to happen if there isn't a tblIEU record for this peron and this IEUID? Do you want to add a new record to tblIEU for this person and this IEUID?

- Daryl

p.s. The pictures are fine to add - they are not 'executable'.

What I'd like to have happen is the combo-boxes default to either 1 or 0, or even a blank. I really don't want to have an entry put into the table for each item if possible, that'll just clutter everything up.

Can you leave the combo box without a default value? What if you only set the value when there is a record? Like this:

With Me("cbo" & xIEU)
strSQLIEUQTY = "SELECT QTY FROM tblIEU WHERE PersonnelID=" & Me!txtID & " AND IEUID=" & rsIEU!IEUID
Set rsIEUQTY = CurrentDb.OpenRecordset(strSQLIEUQTY)
If rsIEUQTY.RecordCount > 0 Then
.Value = rsIEUQTY!QTY
End If
.Visible = True
End With

- Daryl

Same problem. There is definiately something wrong with the statement:



When I go to a record that doesn't have ANY items loaded against them, it loads just fine. It's when the form has to read the above code that it freaks out.

This post has been edited by voodoort: Apr 7 2011, 02:24 PM

How is the combo box related to the recordset? What is the row source for the combo box? Does the combo box have a record with the bound column equal to any Qty that you could give it?

- Daryl

The combo boxes are unbound. I'd have to associate a second table to the form in order to get it to bind to a recordset, which was giving me tons of problems previously.

I'm glad you have things working. As for it being slow, have you compacted and repaired lately? I assume you have primary keys on all your tables, and the foreign keys are set up properly. How big is your database?

Then if you post the code you think is slow, we may be able to spot something. Please copy/paste the entire procedure as is. Some of the code you have posted is not complete, so it is harder to spot an issue.

- Daryl