Trying to loop through selected items in a listbox to update an access table

[RunTime91]

Hello...

The code below seems fairly simple but I can't get past an 'Object Required' error on the If Line... I'm fairly certain it has to do with Tbl_Associates

Because when I move the code to the 'Then' line it produces the same error...

Private Sub Command0_Click()
  
  Dim i As Integer  
  
    With Me.List1
      For i = 0 To .ListCount - 1
        
        If .Selected(i) Then
           
          If Tbl_Associates.[Agent Name] = .Column(0) And Tbl_Associates.Updated = .Column(1) Then
          Tbl_Associates.Approved = "Yes"
          End If
        End If
      
      Next I
      
      End With
      
    Exit Sub

[andy49]

Is your form bound to that table?

[RunTime91]

Hi Andy...

No, the form and table are stand alone objects...

[andy49]

The whole process will be much easier (and make more sense ) if form is bound to the table or a query which holds the info. Is this possible?

[RunTime91]

I'm sure I can as this is just a test db. But I'm not sure how I would do either at this point...If you can give me some guidance I'm sure I can do it...

Thanks Andy...

[andy49]

Ok try an update query
Dim MySQL
MySQL = “update tbl_associates set approved = “yes” where [agent name] = “ & column(0) & “ and [updated] = ‘“ & .column(1) & “‘“

Debug.print MySQL

Then check the immediate window to see whether MySQL is pulling in what you need

Post here if you want.

If it is right

Currentdb.execute MySQL

Not tested. Save copy of database.

You may need to adjust if agentname is a text field

I’ve asssumed updated is a text field.

[RunTime91]

Wow... Nearly home...

With a little tweaking this (below) worked perfectly insofar as it returned exactly what it should return....

Dim MySQL
        MySQL = "update tbl_associates set approved = ""yes"" where [agent name] = " & .Column(0,  i) & " and [updated] = '" & .Column(1,  i) & "'"

However, it did not populate the Approved field with "Yes"

Is it possible this may be due to the field labeled 'Updated' in the Where clause is a date field? (The Approved field which is being Set or Updated is a text field)

Do I need to specify a date Format on that Where criteria?

[andy49]

Spot on

It will depend on what values are stored in [updated] and column (1,i)

But certainly remove the extra single quotes ‘ which is only for a text field.

Can you post any of the debug lines. That would definitely help.

[RunTime91]

Andy, I'm still working on this. (still not able to get the 'Approved' field to populate with "Yes") As soon as I hit pay dirt I will post the code and show this thread as resolved.

[andy49]

Ok. Try
“where ....... and datediff(‘day’,[updated],”#” & .column(1,i) & “#=0”


(Is the difference between the two dates in days zero)

[andy49]

Is the approved a yes/no field? If so, you can’t use text delimiters around yes. Just use -1 instead.

[RunTime91]

Yeah, I was hoping that would be the case, unfortunately it is not, it is set to Text.

[ssanfu]

PMFJI,

What is the list box row source? Do you have a PK field for Tbl_Associates (hopefully an Autonumber type)?

Is there a chance that there could be more than 1 record in Tbl_Associates where "[Agent Name] = .Column(0) And Tbl_Associates.Updated = .Column(1)"?


Really should edit the dB objects and remove any spaces in names....

[June7]

@andy49 - why DateDiff()?

Maybe:

And [updated] = #" & .Column(1, i) & "#"

[andy49]

The debug.print line produces the mysql line in the intermediate window. As I’ve already said, posting that here would make it easier for us to help

June: I wondered if one (or both) of the fields might store time as well so wouldn’t be equal as such.