Solved DoCmd.GoToRecord doesn't take me to the requested record (1 Viewer)

Valentino54

New member
Local time
Today, 11:48
Joined
Mar 30, 2020
Messages
12
Hi, need a little help. I've got the following code where the DoCmd.GoToRecord stops the flow whit runtime error 2493.
I tried diffferent ways to write it but couldn't sort it out.

I need to open a Table, find a specified record and modify the value of a check box. Is there a syntax error?

intIDGiocatore = Me.ID_Giocatore
Dim rstEsclusione As DAO.Recordset
Set rstEsclusione = dbsGestione.OpenRecordset("tblGIOCATORI")
rstEsclusione.Edit
DoCmd.GoToRecord [rstEsclusione]![ID_Giocatore] = intIDGiocatore
If Me.txtMotivoUscita = "Decesso" Then
rstEsclusione![EsclusoClub] = True
rstEsclusione.Update
End If
If Me.txtMotivoUscita = "Dimissioni dal Club" Then
rstEsclusione![EsclusoClub] = True
rstEsclusione.Update
End If
rstEsclusione.Close
Set rstEsclusione = Nothing
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:48
Joined
Sep 21, 2011
Messages
14,038
You go to a particular record before you edit it?
 

Valentino54

New member
Local time
Today, 11:48
Joined
Mar 30, 2020
Messages
12
No, I believe not.

There is a Form used to add a new record to Table "A". Depending on the value I get from a Option Group I need to set a Checkbox in Table "B"from False to True. Therefore I open a new recordset "rstEsclusione" just to do that. Before that I Close the previous opened recordset .
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:48
Joined
Jan 23, 2006
Messages
15,361
Please post the entire routine. It appears you are trying to assign a value to field ID_Giocatore before you have identified which record to update/edit. Gasman also indicated that as the issue.
Can you tell us exactly which record it is that you want to update?
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:48
Joined
Sep 21, 2011
Messages
14,038
Well this is the syntax for GotRecord, and it appears to need a form?

GoToRecord

Why not use the FindFirst method if the field is unique?

Edit: FWIW I found you can enter edit state and still move records, so it is just your syntax which is incorrect.?
Myself, I prefer to locate, edit,update.
 

vba_php

Forum Troll
Local time
Today, 05:48
Joined
Oct 6, 2019
Messages
2,884
valentino,

this is of course one thing about access that should be fixed but never has been. here's what the issue might be:

=> you need to open the full set before using EDIT:
Code:
rstEsclusione .movelast
rstEsclusione .movefirst
=> you can use:
Code:
rstEsclusione .findfirst
before you EDIT, and I believe it acts just like the bookmark concept.

=> also, this is one of the functions in access where different syntax is acceptable at different times in different versions by the JET or ACE engine. which of course, is why the program isn't the best. have you seen this? https://docs.microsoft.com/en-us/office/vba/api/access.docmd.gotorecord
 

jdraw

Super Moderator
Staff member
Local time
Today, 06:48
Joined
Jan 23, 2006
Messages
15,361
You could modify the OpenRecordset to identify a specific record such as
Code:
Set rstEsclusione = dbsGestione.OpenRecordset  _
           ("select * From tblGIOCATORI where ID_Giocatore =" & intIDGiocatore )
 

cheekybuddha

AWF VIP
Local time
Today, 10:48
Joined
Jul 21, 2014
Messages
2,237
Hello,

Why bother with a recordset at all?
Code:
  Dim strSQL As String

  Select Case Me.txtMotivoUscita
  Case "Decesso", "Dimissioni dal Club"
    strSQL = "UPDATE tblGIOCATORI SET EsclusoClub = True WHERE ID_Giocatore] = " & Me.ID_Giocatore
    CurrentDb.Execute strSQL, dbFailOnError
  End Select

hth,

d
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:48
Joined
May 7, 2009
Messages
19,169
Why bother with a recordset at all?
because you are using a Memory copy of the recordset.
using SQL Update will update the table physically, but your form (if bound to that table/query)
will Complain that your data has changed.

better to use Bound form, no need to use code or Update query.
 

Valentino54

New member
Local time
Today, 11:48
Joined
Mar 30, 2020
Messages
12
Wow!! A lot of suggestions to work on! Many thanks to everybody!!

By the way, here is the code I wrote. I know, a bit confusing, but what matters for a novice... Hopefully it works...

I will post which solution I used later on.

Thanks again
 

Attachments

  • Private Sub boxStatusSenior.zip
    18.3 KB · Views: 62

cheekybuddha

AWF VIP
Local time
Today, 10:48
Joined
Jul 21, 2014
Messages
2,237
I suppose that depends on whether the form is using tblGIOCATORI as its RecordSource.
 

cheekybuddha

AWF VIP
Local time
Today, 10:48
Joined
Jul 21, 2014
Messages
2,237
because you are using a Memory copy of the recordset.
This doesn't really make sense.

What is the advantage of a memory copy of the recordset? If anything, it is a disadvantage since you are loading the whole table into memory just to update a single field in a single record.

If you update the recordset, you update the table anyway in the same way as using the SQL statement.

What am I missing?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:48
Joined
May 7, 2009
Messages
19,169
it may not be evident on a Single user.
possible even Lock the whole Table on multi-user when
issuing SQL Update.
 

Valentino54

New member
Local time
Today, 11:48
Joined
Mar 30, 2020
Messages
12
Well, I opted for CheekyBudda's solution! I think it was the easiest way of doing it.

Many thanks to everybody for suggesions! Well appreciated.
 

Users who are viewing this thread

Top Bottom