Results 1 to 6 of 6
  1. #1
    fluppe is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    48

    How to edit entry in table from VBA ?

    Hi,



    another (stupid) question:
    How can i edit an enty in a table that belongs to a DataBase via VBA ?
    ... without UPDATE queries at best ...
    or is this not even possible ?

    Thanks in advance and greetings,

    ...

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Code:
    dim db as database
    dim rst as recordset
    
    set db = currentdb
    set rst = db.openrecordset("TestTable")
    
    do while rst.eof <> true
        rst.edit
        rst.fields("FieldName").value = NEWVALUE
        rst.update
    
        rst.movenext
    loop
    rst.close
    set db = nothing
    of course if you want to modify a specific record you'll have to define that in the openrecordset using either an updatable query or testing each record to see if it matches your search criteria.

  3. #3
    fluppe is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    48
    Hello,

    thanks for this example. My problem seems to be that i can (for some reason) NOT access the "CurrentDb" ...
    Gonna try further, but it seems not to work.

    I am trying in an autogenerated Callback method for a control inside a Subform. Could that be the problem ?

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    What references do you have enabled?

    I think the defaults are:

    Visual Basic for Applications
    Microsoft Access xx.x Object Library
    OLE Automation
    Microsoft Office xx.x Access database engine objects

    if you have these references enabled in your VBA window you shouldn't have any problem.

  5. #5
    fluppe is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    48
    Hi again,

    here is what doesn't run ....

    [code]

    Code:
    Private Sub cbo_ProjectName_AfterUpdate()
    
    Dim filter_param As String
    Dim ID As Integer
    Dim tmp_qry As QueryDef
    Dim tmp_tbl As Recordset
    Dim db As Database
    
    
        filter_param = "tblProjects.ProjectName = '" & "EinTest" & "'"
        ID = DLookup("ID", "tblProjects", filter_param)
        
        db = CurrentDb
        
    
    End Sub
    [code] ,.... didn't find the "code" - option ... now it worked

    the compiler moans about wrong usage of a field ...
    If i just take out the last line "db = CurrentDb" it runs ... that's strange i think.

  6. #6
    fluppe is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014
    Posts
    48
    ... got it ... added a "Set" ... settled ... however, the example is very useful.
    Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Edit Table from Switchboard
    By smarty84handsome in forum Database Design
    Replies: 12
    Last Post: 01-24-2014, 11:05 PM
  2. Replies: 4
    Last Post: 03-20-2013, 01:03 PM
  3. Edit table only via forms
    By accessnewb in forum Access
    Replies: 1
    Last Post: 08-01-2011, 12:04 PM
  4. Edit record in form with original entry shown
    By erknoebe in forum Access
    Replies: 5
    Last Post: 09-29-2010, 02:33 PM
  5. Replies: 1
    Last Post: 08-13-2010, 10:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums