Solved Edit and update some field via vba error (1 Viewer)

Akai90

Member
Local time
Tomorrow, 07:35
Joined
Feb 8, 2022
Messages
65
hi.

i want to edit and update some field via vba . i write some code but it as some error

Code:
Private Sub listsemuaguru_DblClick(Cancel As Integer)
Me![listgurufilterips].Requery
Dim tguru As Integer, RESPON As String
Dim MyDB As DAO.Database, MyTable As DAO.Recordset
Dim JWBCONFIRM As String

    Set MyDB = DBEngine.Workspaces(0).Databases(0)
    Set MyTable = MyDB.OpenRecordset("TGuruIPS", DB_OPEN_TABLE)
   MyTable.Index = "NamaGuru"
   MyTable.Seek "=", Forms![FkemaskiniPG]![listsemuaguru]
   With MyTable
   .Edit
   !KodInstitusi = [kodips]
   !JenisInstitusi = [jenisips]
   !Institusi = [namaips]
   !Alamat = [alamatpenuh]
   .Update
   End With
   DoCmd.OpenQuery "qListGuru"
  Me![listsemuaguru].Requery
    DoCmd.Close
    DoCmd.OpenQuery "QListFilterGuru"
Me![listgurufilterips].Requery
DoCmd.Close
End Sub

but got this error

1654361386611.png
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:35
Joined
Feb 19, 2002
Messages
42,981
Bound forms or Action queries are always better than VBA loops.

I'm guessing that the error means you didn't find a record with the Seek. You need to check to see if you found a record before you attempt to update it.
 

Akai90

Member
Local time
Tomorrow, 07:35
Joined
Feb 8, 2022
Messages
65
1654369048263.png

Code:
Private Sub listsemuaguru_DblClick(Cancel As Integer)
Dim clnpusatini As Integer, RESPON As String
Dim MyDB As Database, MyTable As Recordset
Dim JWBCONFIRM As String

    Set MyDB = DBEngine.Workspaces(0).Databases(0)
    Set MyTable = MyDB.OpenRecordset("TGuruIPS", DB_OPEN_TABLE)
   MyTable.Index = "NamaGuru"
   MyTable.Seek "=", Forms![FkemaskiniPG]![listsemuaguru]
   With MyTable
   .Edit
   !KodInstitusi = [kodips]
   !JenisInstitusi = [jenisips]
   !Institusi = [TbutirIPS.namaips]
   !Alamat = [alamatpenuh]
   .Update
   End With
   DoCmd.OpenQuery "qListGuru"
  Me![listsemuaguru].Requery
    DoCmd.Close
    DoCmd.OpenQuery "QListFilterGuru"
Me![listgurufilterips].Requery
DoCmd.Close
End Sub

got when try to update the code...
can point me what the problem..

error point here : Dim MyDB As Database, MyTable As Recordset
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:35
Joined
Feb 19, 2002
Messages
42,981
Which line of code is highlighted? Hit cntl-break when the error pops up to view the code.
 

June7

AWF VIP
Local time
Today, 15:35
Joined
Mar 9, 2014
Messages
5,423
Eliminate Seek, open filtered recordset.

Dim MyDB AsDAO.Database, MyTable As DAO.Recordset
Set MyDB = CurrentDb
Set MyTable = MyDB.OpenRecordset("SELECT * FROM TGuruIPS WHERE NamaGuru='" & Forms![FkemaskiniPG]![listsemuaguru] & "'")
MyTable.MoveLast
MyTable.MoveFirst
If Not MyTable.EOF Then

Is listsemuaguru a single-select listbox?
Why do you need to open qListGuru and QListFilterGuru?
Why not a bound form for data entry? Are [kodips], etc. UNBOUND controls?
Why save institution code and other institution info - why not just save the institution code?
 
Last edited:

Akai90

Member
Local time
Tomorrow, 07:35
Joined
Feb 8, 2022
Messages
65
I HAVE TWO LIST

1 is all teacher
2 is filter by school name.

so i want to transfer 1 teacher to another school
when i double click on all list teacher. it will update the school name
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:35
Joined
Sep 21, 2011
Messages
14,048
Qualify those names with DAO or ADO :(
Is your project called Database? :(
 

Akai90

Member
Local time
Tomorrow, 07:35
Joined
Feb 8, 2022
Messages
65
this is my project and what i want to do..
i want to edit and update few fields
just double click the listbox

Untitled.png
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:35
Joined
Sep 21, 2011
Messages
14,048
Need to show us this screen, or upload your DB
1654441915585.png
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:35
Joined
Sep 21, 2011
Messages
14,048
You have completely omitted the part I was hoping to check? :(
Post a pick from THE TOP of the treeview

You STILL have not qualified those statements.?
Why do it in some places and not others? Be consistent.
 

Akai90

Member
Local time
Tomorrow, 07:35
Joined
Feb 8, 2022
Messages
65
1654444905437.png


sorry i am new in access
and my english is not good enough
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 23:35
Joined
Sep 21, 2011
Messages
14,048
OK, your project is called Database. :(
Name it something a little more meaningful.

Reagrdless, qualify the database and recordset as you have done in other places using DAO.
 

Akai90

Member
Local time
Tomorrow, 07:35
Joined
Feb 8, 2022
Messages
65
1654446566663.png


i rename my database
i got error
Run-time Error 3021, No Current Record

on yellow .edit
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:35
Joined
Sep 21, 2011
Messages
14,048
View attachment 101004

i rename my database
i got error
Run-time Error 3021, No Current Record

on yellow .edit
Back to post #1 then. :)

Look at the debugging link in my signature.
Walk through your code. I would hazard a guess that you are not finding what you are seeking?
You need to check you have before trying anything else.

You are using commands I have never used, so others will need to help you there, but I suspect you will need to test .NoMatch after your seek.
Learn to walk through your code anyway.
 

June7

AWF VIP
Local time
Today, 15:35
Joined
Mar 9, 2014
Messages
5,423
Is post #6 and attempt to answer post #5? It doesn't. Did you try code suggested in post #5?
 

Akai90

Member
Local time
Tomorrow, 07:35
Joined
Feb 8, 2022
Messages
65
Eliminate Seek, open filtered recordset.

Dim MyDB AsDAO.Database, MyTable As DAO.Recordset
Set MyDB = CurrentDb
Set MyTable = MyDB.OpenRecordset("SELECT * FROM TGuruIPS WHERE NamaGuru='" & Forms![FkemaskiniPG]![listsemuaguru] & "'")
MyTable.MoveLast
MyTable.MoveFirst
If Not MyTable.EOF Then

Is listsemuaguru a single-select listbox?
Why do you need to open qListGuru and QListFilterGuru?
Why not a bound form for data entry? Are [kodips], etc. UNBOUND controls?
Why save institution code and other institution info - why not just save the institution code?
yes post #6 is answer for post #5

i dont know using the DAO
Code:
Dim MyDB As DAO.Database, MyTable As DAO.Recordset
Set MyDB = CurrentDb
Set MyTable = MyDB.OpenRecordset("SELECT * FROM TGuruIPS WHERE NamaGuru='" & Forms![FkemaskiniPG]![listsemuaguru] & "'")
MyTable.MoveLast
MyTable.MoveFirst
If Not MyTable.EOF Then
MyTable.Edit
MyTable!KodInstitusi = [kodips]
MyTable!JenisInstitusi = [jenisips]
MyTable!Institusi = [TbutirIPS.namaips]
MyTable!Alamat = [alamatpenuh]
MyTable.Update
End If

the code something like this ?
 

June7

AWF VIP
Local time
Today, 15:35
Joined
Mar 9, 2014
Messages
5,423
Yes, code like that.

Still didn't answer my questions.
 

Users who are viewing this thread

Top Bottom