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

A

Akai90

Member
Local time
Tomorrow, 05:50
Joined
Feb 8, 2022
Messages
67
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:
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.
 
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
 
Which line of code is highlighted? Hit cntl-break when the error pops up to view the code.
 
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:
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
 
Qualify those names with DAO or ADO :(
Is your project called Database? :(
 
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
 
Need to show us this screen, or upload your DB
1654441915585.png
 
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.
 
1654444905437.png


sorry i am new in access
and my english is not good enough
 
Last edited:
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.
 
1654446566663.png


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

on yellow .edit
 
Akai90 said:
View attachment 101004

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

on yellow .edit
Click to expand...
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.
 
Is post #6 and attempt to answer post #5? It doesn't. Did you try code suggested in post #5?
 
June7 said:
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?
Click to expand...
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 ?
 
Yes, code like that.

Still didn't answer my questions.
 
You must log in or register to reply here.

Similar threads

A
Listbox selected double click update value
Replies
3
Views
268
Gasman
Gasman

Users who are viewing this thread

Total: 2 (members: 0, guests: 2)
Back
Top Bottom