Solved DoCmd.GoToRecord doesn't take me to the requested record

[Valentino54]

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]

You go to a particular record before you edit it?

 

[Valentino54]

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]

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]

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]

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:

rstEsclusione .movelast
rstEsclusione .movefirst

=> you can use:

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]

You could modify the OpenRecordset to identify a specific record such as

Set rstEsclusione = dbsGestione.OpenRecordset  _
           ("select * From tblGIOCATORI where ID_Giocatore =" & intIDGiocatore )

 

[cheekybuddha]

Hello,

Why bother with a recordset at all?

  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

 

[vba_php]

d

always a pleasure listening to you, d. whatever your name is. I think you told me on UA though, didn't u?

 

[arnelgp]

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]

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

 

[cheekybuddha]

I suppose that depends on whether the form is using tblGIOCATORI as its RecordSource.

 

[cheekybuddha]

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]

it may not be evident on a Single user.
possible even Lock the whole Table on multi-user when
issuing SQL Update.

 

[Valentino54]

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.