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.

 

[Pat Hartman]

I'm confused. If you are on the record you want to update, why are you searching for it. Unless some specific record in the subform has the focus, the FIRST record is the current record.

Step through the code and make sure that you are really finding the record you think you found. You are not using the record's PK for the search so perhaps multiple records are satisfying the criteria and you are updating the first one which might actually not be the visible one.

A better technique would be to add the update code to the double click event in one of the subform controls. No additional recordset is required.

 

[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

 

[Pat Hartman]

You had multiple suggestions, Specifying which you used will help people who find this thread with a search. We can't tell if you are thanking arnel or someone else.