Solved Continuous Form command button always refers to first record (1 Viewer)

Kayleigh

Member
Local time
Today, 08:12
Joined
Sep 24, 2020
Messages
706
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 ?
Code:
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

AWF VIP
Local time
Today, 01:12
Joined
Apr 13, 2010
Messages
1,401
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?
Code:
Me.Collected=True
Me.DateCollected=Now
 

Kayleigh

Member
Local time
Today, 08:12
Joined
Sep 24, 2020
Messages
706
Sorry that didn't work.
Current recordset is not updateable so can't just update fields.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:12
Joined
Feb 19, 2002
Messages
42,981
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

AWF VIP
Local time
Today, 01:12
Joined
Apr 13, 2010
Messages
1,401
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

AWF VIP
Local time
Today, 00:12
Joined
Mar 9, 2014
Messages
5,424
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

I’m here to help
Staff member
Local time
Today, 01:12
Joined
Oct 29, 2018
Messages
21,358
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

..forever waiting... waiting for jellybean!
Local time
Today, 16:12
Joined
May 7, 2009
Messages
19,169
Code:
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") & "#"
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:12
Joined
Feb 19, 2002
Messages
42,981
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.
 

Users who are viewing this thread

Top Bottom