Solved Continuous Form command button always refers to first record

[Kayleigh]

Hi,
I am having trouble with getting a command button on continuous form to work. It was definitely working in the past...
It should lookup the ID and Date of current record and create a edit the record to add today's date etc however now it will only find the top record in the recordset which has already been edited...
I don't have any current events in this form so no requery events should be firing. Also checked my DB isn't corrupted.
What am I missing ?

Dim rs As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb
Set rs = db.OpenRecordset("jtblClientReward")

rs.FindFirst "[fldExistingClientID] =" & Me.fldExistingClientID & " AND [fldNewClientID] =" & Me.fldNewClientID & " AND [fldCDate] = " & Me.fldCDate
If Not rs.NoMatch Then
    rs.Edit
        rs!fldCCollected = True
        rs!fldCDateCollect = Now
    rs.update
End If
rs.Close

Me.Requery

Set rs = Nothing

 

[bastanu]

You're missing the date delimiter (#):
rs.FindFirst "[fldExistingClientID] =" & Me.fldExistingClientID & " AND [fldNewClientID] =" & Me.fldNewClientID & " AND [fldCDate] = #" & Me.fldCDate & "#"

But why use the recordset and not just simply update the fields?

Me.Collected=True
Me.DateCollected=Now

 

[Kayleigh]

Sorry that didn't work.
Current recordset is not updateable so can't just update fields.

 

[bastanu]

Are the two ID fields both numeric? If text you need to wrap them in single quotes. I also noted that later you issue a Me.Requery which will reposition the focus to the first record; to stay on the record being update try Me.Recordset.Requery.

Cheers,

 

[June7]

Instead of opening recordset to all records, apply filter criteria to recordset object then FindFirst will not be necessary.

"SELECT * FROM jtblClientReward WHERE ..."

Or use UPDATE sql action instead of opening recordset.

Why does table have fldExistingClientID and fldNewClientID?

Why is form recordset not updatable?

 

[theDBguy]

Hi. Pardon me for jumping in. This may not apply to you; but in some cases where I've seen this happen, it was because the user wasn't clicking on a button. Instead, they were clicking on an image that represents a button.

 

[arnelgp]

CurrentDb.Execute "update jtblClientReward " _
        & "set fldCCollected = True, fldCDateCollect = Now " _
        & "where " _
        & "[fldExistingClientID] =" & Me.fldExistingClientID _
        & " AND [fldNewClientID] =" & Me.fldNewClientID _
        & " AND [fldCDate] = #" & Format$(Me.fldCDate, "mm/dd/yyyy") & "#"

 

[Kayleigh]

Yes that is what I did in the end. Thanks