Looping and Updating

[Dinger_80]

I am currently modifying a form on my access database. More specifically the on click event of a button on the form. The form is based on a Table that only has 3 fiels, ID, GoingToCal, and Location. ID is the autonumber. GoingToCal is a multiselect list box on my form. Location is just where the user is submitting the entry from. The table is simply a way to track what went out at what facility. Before I started messing around I was getting the results that I wanted, which was just a report with all items going out.

The issue that I am having is 2 part. The first error I have doesn't create a runtime error, it simply doens't work. What I am attempting to do is update a recordset of the items selected in the list box. The items are in another table, and I have based my recordset on a query. When I run my code I am expecting first for the specific record to be found(I think works), then update a yes no box to yes. The Yes No field is called StillOut. It is a part of the query. However it doesn't update the Yes No box to Yes, nor does it seem to update any of the boxes. So I am confused as to what I am doing wrong that the field for that record isn't updating.

My Second issue, which I know there can be a few possible ways to work around I just can't make some of them happen. I am using the FindFirst for how I narrow down my recordset to just the one record I want to update. It doesn't seem to like "," with it. I have tried many times when setting the OpenRecordset to the specific record using SQL and a mix of SQL and the Query but no matter how hard I try I can never return any results. It constantly says too few parameters. That is why I am using the FindFirst command.

What I need is advice on how to make the record update the Yes No box and how to specifically change just the records that have been selected in the form and a way to select those records. Here is the code that I am having issue with. As I said the rest worked fine, I just needed an update my supervisor asked for.

iCount = 0
            
    If Me.GoingToCal.ItemsSelected.Count <> 0 Then
        For Each oItem In Me.GoingToCal.ItemsSelected
            If iCount = 0 Then
                ListItem = ListItem & Me.GoingToCal.ItemData(oItem)
                CalEquipment = DLookup("ID", "GoingToCalQuery", "[ID] = " & ListItem)
                Set Rs = CurrentDb.OpenRecordset("Select * From GoingToCalQuery")
                Rs.FindFirst "ID = " & CalEquipment
                With Rs
                    .Edit
                    !StillOut = False
                    .Update
                End With
                iCount = iCount + 1
            Else
                ListItem = Me.GoingToCal.ItemData(oItem)
                CalEquipment = CalEquipment & ", " & DLookup("ID", "GoingToCalQuery", "[ID] = " & ListItem)
                Set Rs = CurrentDb.OpenRecordset("Select * From CalibratedEquipmentListTable")
                Rs.FindFirst "ID = " & CalEquipment
                    With Rs
                        .Edit
                        !StillOut = False
                        .Update
                    End With
                iCount = iCount + 1
            End If
        Next oItem
    End If

 

[vbaInet]

Do you know to use the IN keyword with the WHERE clause in an SQL statement?

 

[Dinger_80]

I know I have seen it used when using multiple tables but not when its only one table. I am just pulling a single record out of a table. Usually I just say where "ID = CalEquipment"

 

[vbaInet]

It has no relation to how many tables you're using. You can use it in a query with one or more tables. Think of it as an array with an OR predicate in a query.

Let me give you an example:

SELECT *
FROM Table1
WHERE ID IN(1,2,3)

This means, show all records where the ID is 1, 2 or 3.

If you're following what I'm getting at, what you need to do is build an IN() clause in code that you can use in a SQL statement to update your table.

UPDATE TableName
SET YesNoField = Yes
WHERE FieldName IN('Value1','Value2')

So build the IN() part, concatenate it with the rest of the UPDATE string and execute the SQL.

 

[Dinger_80]

Ok so I tried to implement what you said and I don't think I am doing something right as it still says too few parameters. I also tried a more then a few different combinations to get the results I desired. CalEquipment does return a good value, as I used a msgbox to make sure it returned a value before using it in the SQL statement. Maybe you can provide some insight as to what the problem is? The Last line of code is the line that is the problem.

If iCount = 0 Then
                ListItem = ListItem & Me.GoingToCal.ItemData(oItem)
                CalEquipment = DLookup("ID", "GoingToCalQuery", "[ID] = " & ListItem)
                Set Rs = CurrentDb.OpenRecordset("Select * From CalibratedEquipmentListTable where ID IN(CalEquipment)")

 

[vbaInet]

To build the IN part of your SQL string (aircode):

dim strIn as string
dim lstItem as variant

for each lstItem in lstItem.itemsselected
    strIn = strIn & "'" & Me.YourListBox.itemdata(lstItem) & "',"
next

strIn = "(" & left(strIn, len(strIn) - 1) & ")"

This is assuming your data is Text. If it isn't, get rid of the single quotes.
strIN should return something like ('a','b','c')

 

[vbaInet]

And as already pointed out, you don't need a recordset or any of the other code you had in your original post. Just build the SQL string, use RunSQL or db.Execute to run the UPDATE statement.

 

[Dinger_80]

I am sorry, I am having some serious issue with getting this to work and some how I feel I have totally missed something. I still have my original code. I need what my original code does plus now update the records of the items in the list. The reason I need the other code is because after this a report is printed of items in the list. I tried all you suggested but I cant get the recordset to be set to any item that is on the list. If I break my code down this first part here works ok up till the point where it doesn't update.

If Me.GoingToCal.ItemsSelected.Count <> 0 Then
        For Each oItem In Me.GoingToCal.ItemsSelected
            If iCount = 0 Then
                ListItem = ListItem & Me.GoingToCal.ItemData(oItem)
                CalEquipment = DLookup("ID", "GoingToCalQuery", "[ID] = " & ListItem)
                Set Rs = CurrentDb.OpenRecordset("Select * From CalibratedEquipmentListTable")
                Rs.FindFirst "ID = " & CalEquipment
                With Rs
                    .Edit
                    !StillOut = False
                    .Update
                End With
                iCount = iCount + 1
            Else

After the top part executes, minus the no update, the second part kicks in. Since nothing I have tried to do will get me the individual records of the recordset I am not sure how to get the results I am looking for.

Else
                ListItem = Me.GoingToCal.ItemData(oItem)
                CalEquipment = CalEquipment & ", " & DLookup("ID", "GoingToCalQuery", "[ID] = " & ListItem)
                Set Rs = CurrentDb.OpenRecordset("Select * From CalibratedEquipmentListTable")
                Rs.FindFirst "ID = " & CalEquipment
                    With Rs
                        .Edit
                        !StillOut = False
                        .Update
                    End With
                iCount = iCount + 1
            End If
        Next oItem
    End If

This is where my code started from that gave me the records in the report that I needed to have.

If Me.GoingToCal.ItemsSelected.Count <> 0 Then
        For Each oItem In Me.GoingToCal.ItemsSelected
            If iCount = 0 Then
                ListItem = ListItem & Me.GoingToCal.ItemData(oItem)
                CalEquipment = DLookup("ID", "GoingToCalQuery", "[ID] = " & ListItem)
                iCount = iCount + 1
            Else
                ListItem = Me.GoingToCal.ItemData(oItem)
                CalEquipment = CalEquipment & ", " & DLookup("ID", "GoingToCalQuery", "[ID] = " & ListItem)
                iCount = iCount + 1
            End If
        Next oItem
    End If

I am not new new but obviously not as knowledged as I should be for tackling this harder level material. I thank you for all of your help and patience dealing with me. If there is a better way all together to simply update the items in a multiselect list box so that StillOut on the table will show true I am willing to endulge in any way possible.

 

[vbaInet]

I keep telling you don't need a recordset. You're clouding your mind with this convoluted method of using a recordset that's why you're having difficulty grasping what I'm getting across.

Queries are built for efficiently updating data with less fuss, not recordsets. There's a time and place for recordset, this just isn't one.

As already mentioned, work on forming the IN() string and I'll show you what next.

 

[Dinger_80]

Let me make sure I get this. So remove the recordset line of code and set up the SQLString that includes the IN() with it? So for example-

SQLString = "Select * From CalibratedEquipmentListTable where in(CalEquipment)"

So my code all together would look like -

If Me.GoingToCal.ItemsSelected.Count <> 0 Then
        For Each oItem In Me.GoingToCal.ItemsSelected
            If iCount = 0 Then
                ListItem = ListItem & Me.GoingToCal.ItemData(oItem)
                CalEquipment = DLookup("ID", "GoingToCalQuery", "[ID] = " & ListItem)
                SQLString = "Select * From CalibratedEquipmentListTable where in(CalEquipment)"
                iCount = iCount + 1
            Else

If that isn't what you mean I am completely lost and have not a clue as to what you are talking about then.

 

[vbaInet]

We'll get there.

Let's worry about the full picture later, because I don't want to confuse you.

First things first, generate your IN() string within the FOR..EACH loop. That's all I'm asking for at the moment. Remove everything else, e.g. the DLookup(). Go back to my post #6 for the syntax. This IN() string is the main part of your entire function so let's get it right first.

For further clarification, the values within the string IN() should be the selected ID values from your listbox.

 

[Dinger_80]

Ok now I better understand what you meant. I didn't realize we were creating the In() in the For... Each loop. I am just moving from beginner to more advanced stuff so I may be a little slow on the uptake. Here is the changes to the code now.

For Each oItem In GoingToCal.ItemsSelected
        CalEquipment = CalEquipment & "'" & Me.GoingToCal.ItemData(oItem) & "',"
    Next
    
    CalEquipment = "(" & Left(CalEquipment, Len(CalEquipment) - 1) & ")"CODE]

 

[vbaInet]

I'm hoping that CalEquipment is a variable?

Can you Debug.Print CalEquipment and paste the result here. Do you understand what I mean by this?

 

[Dinger_80]

CalEquipment is a string. Debug.Print doesn't mean as much as it should. From what I gathered is it "prints a message to the immediate window in the Visual Basic editor." For the life of me I have no understanding of its use or what it is doing. I tried to read up on it but nothing seemed to make sense, mostly because people said just as above which didn't do much to further explain its use.

 

[vbaInet]

It's easy. Under the View menu (where you write code) you'll find the Immediate Window submenu, click on that. It will appear just below the pane where code is written.

So now put Debug.Print here:

CalEquipment = "(" & Left(CalEquipment, Len(CalEquipment) - 1) & ")"
Debug.Print CalEquipment

Run the code and what's in CalEquipment will appear in the Immediate Window. Paste it here.

 

[Dinger_80]

Ahh. The lights went on. I usually don't look at the immediate window, which is why I couldn't figure it out. So yes when I do Debug.Print CalEquipment the Immediate window reads ('177','173','17','75'). This a good representation of what is actually selected in the multiselect list box.

 

[vbaInet]

Ok, it looks like this is a Number field. Go back to your code, remove the single quotes and include the IN() strin as well. Run it again and this time it should produce:
IN(177,173,17,75)

 

[Dinger_80]

Sorry I haven't gotten back, didn't get much of a chance to work on things till today. I changed the final CalEquipment to = "IN" & "(" & Left(CalEquipment, Len(CalEquipment) - 1) & ")" which after doing the Debug.Print I got the desired IN(177,173,17,75)

 

[Dinger_80]

Ok so I ran with all you have said and put this together and it seems to work. Here is the final code.

If IsNull(Me.GoingToCal) Then
    MsgBox "You have not selected any items in the equipment list. Please select the items used before continuing.", vbExclamation
    Exit Sub
Else
    For Each oItem In GoingToCal.ItemsSelected
        CalEquipment = CalEquipment & Me.GoingToCal.ItemData(oItem) & ","
    Next
    
    CalEquipment = "IN" & "(" & Left(CalEquipment, Len(CalEquipment) - 1) & ")"
    DoCmd.SetWarnings False
    DoCmd.RunSQL ("Update CalibratedEquipmentListTable Set StillOut = Yes Where ID " & CalEquipment)
    DoCmd.SetWarnings True
End If

 

[vbaInet]

Good job! I'm sure you can now appreciate that it feels and runs quicker.

Now if you don't want to fiddle with turning on and off the warnings, look into the CurrentDb's Execute method to fire your SQL.