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.

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:

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.

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

Think you replied to the wrong post! Performance wise my database is running pretty smooth. It's just the combo-boxes I'm having trouble with.

Sorry - that's what I get for having too many windows open...

Does the combo box have an appropriate record for any Qty that could be returned from the strSQLIEUQTY?

You can put a debug statement right before the .Value=rsIEUQTY!QTY statement, like this:
Debug.Print rsIEUQTY!QTY

Then you will see (in the immediate window of the code pane) what the value was when you get the error. Then you can end the code and see if that value is in the combo box. Since I don't know the source of the combo box, I can't tell if the value would exist. If the combo box doesn't have the QTY in it, then what do you want to happen?

Let us know!
- Daryl

When I use debug the proper values come up in the Immediate window. The only values that don't display are those where there's a record in tblIEU.

What if you don't set the value when QTY is zero? You can add an inner IF test like this:

If rsIEUQTY.RecordCount > 0 Then
IF rsIEUQTY!QTY > 0 Then
.Value = rsIEUQTY!QTY
End If
End If

- Daryl

We already tried that. The problem arises any time you try and have rsIEUQTY!QTY return a value. It'll return the value fine in the Immediate frame on Debug, but when it tries to apply the value to the combo box it freaks out. I don't know if there's an issue with rsIEUQTY!QTY sytax or something? It's really weird.

I think we tested the .RecordCount before setting the value, but we didn't test the !QTY, so it might still be worth testing the nested IF.

I don't think the issue is in rsIEUQTY!QTY syntax, but rather with the combo box. That is why I have asked for the row source of the combo box.

If you want to test your theory on the syntax, you can easily Dim an integer variable to hold the value, like this:

Dim intQTY as Integer
If rsIEUQTY.RecordCount > 0 Then
intQTY = rsIEUQTY!QTY
.Value = intQTY
End If

Then can you post the row source of the combo box that fails?

- Daryl

Here's what I tried:


And it simply says "item not found in this collection" upon the loading of the first combo box. It doesn't print out the Debug either.

It gives the same error on the line



as well.

As I suspected. You are trying to set the .Value of the combobox, but I suspect there are no rows in the combo box. So either add the nested IF so you don't try to set the .Value when QTY is zero, or please post your row source for the combo box so we can see why it might be empty. Here is the code to skip setting the .Value if the QTY is zero:



- Daryl

I get the same error trying the code you posted. My row source is a value list:

"1";"2";"3";"4";"5";"6";"7";"8";"9";"10"

..with no default value.

Your combo box has string variables, but you are trying to assign a numeric value to it. Access usually converts these without a problem, but you may want to clean it up. You can either change the value list to integers (1;2;3;...10) or set the .Value to the quantity changed to a string, like this:

.Value = CStr(rsIEUQTY!QTY)

That still shouldn't cause the problem, so let's see what is up with the combo box. Add these in your code, right before the ".Value =" line:
Debug.Print .Name & "-" & .BoundColumn & "-" & .ControlType & "-" & .Enabled & "-" & .ItemData(0)
Debug.Print .ListCount & "-" & .LimitToList & "-" & .Locked & "-" & .RowSource

Then let us know what shows up in the immediate window.

- Daryl

I had to put the debugs in front of the If statements, otherwise they wouldn't print anything out. Here's what came out:

Those look good to me...

I feel I must be missing something obvious. Can you zip your database and upload it? It needs to be in A2003 or earlier format, and please make sure any sensitive data is removed..

Thanks,
- Daryl

I had to change a memo field to append no, but it shouldn't effect the problem I'm having.

OK, the problem is in the rsIEUQTY.QTY. Since we have changed QTY to use nz(QTY,1), it is coming back as a different name. We can use an alias for this, say nzQTY, which we can use as the recordset fieldname. Try this:

With Me("cbo" & xIEU)
strSQLIEUQTY = "SELECT nz(QTY,1) AS nzQTY FROM tblIEU WHERE PersonnelID = 289 And IEUID = " & rsIEU!IEUID
Set rsIEUQTY = CurrentDb.OpenRecordset(strSQLIEUQTY)
Debug.Print .Name & "-" & .BoundColumn & "-" & .ControlType & "-" & .Enabled & "-" & .ItemData(0)
Debug.Print .ListCount & "-" & .LimitToList & "-" & .Locked & "-" & .RowSource
If rsIEUQTY.RecordCount > 0 Then
.Value = CStr(rsIEUQTY!nzQTY)
End If
.Visible = True
End With

Whew!

- Daryl

Holy smokes, it worked! I added a default value of "1" and it works exactly how I want it to. Thanks SOOOOOOO much, all the help is greatly appreciated!

I'm glad it is finally working!
- Daryl