Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2009
    Location
    Antwerp (Belgium)
    Posts
    33

    Unanswered: deleting a record in a continous form

    I have a btnDelete in the main form.

    I would like to delete the line from a continous subform

    Code:
    cKey = Me.ctlKlantSubform2.Form.Recordset("Artikel")
    If lEmpty(cKey) Then Exit Sub
    
    cSQL = "delete from " & mogld.cTablename & " where Artikel = '" & cKey & "'"
    CurrentDb.Execute cSQL
    Me.ctlKlantSubform2.Form.Requery
    Me.ctlKlantSubform2.Form.Refresh
    where mgold.cTablename is the table to which the subform is bound. This should delete the record and refresh the subform no?

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Not sure your cSQL string is correct (but I'm an ADO coder, not DAO).

    cSQL = "delete from " & mogld.cTablename & " where Artikel = '" & cKey & "'"

    perhaps

    cSQL = "delete * from myTableName where Artikel = '" & cKey & "'" (for string matching - note the *)
    or
    cSQL = "delete * from myTableName where Artikel = " & cKey & "" (for integer matching)

    To Requery...
    me.MySubformName.requery

    or

    Forms!MyMainFormName!MySubformName.requery

    This...
    Me.ctlKlantSubform2.Form.Refresh
    should most likely produce an error for you.

    Add some msgbox to your code to see what's going on...
    ie.

    msgbox cSQL

    You can also easily design a new DELETE Record button (using the wizard!!) and see the code in that which usually works (and you just add a me.requery command after the delete code.)

    Otherwise, what I do (in ADO) is something like this (which always works)...
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from MyTableName where RecordID = " & myRecIDVariable & ""
    rs.open strSQL, currentproject.connection, adopendynamic, adlockoptimistic
    rs.delete
    rs.close
    set rs = nothing

    Again, I use ADO and to each his own.
    Last edited by pkstormy; 01-23-10 at 20:02.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Dec 2009
    Location
    Antwerp (Belgium)
    Posts
    33
    Thanks.

    In a "if you can't beat them join them " kind of spirit, I changed the code to :

    Code:
    Me.ctlKlantSubform2.Form.Recordset.Delete
    It works now. No need to requery, not need to refresh. I could have sworn it worked the way I had coded it, before .... Go figure.

    BTW I'm curious, is the DAO - ADO controversy really just a question of "each his own" or are there more objective criteria to decide "what is best for you"? Would be interested in reading something about this.

    Thanks again and take care.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes. DAO or ADO is pretty much a personal preference. I started coding using ADO many years ago since I found the syntax to be much easier to work with. Some will say executing DAO is a bit faster than ADO but the difference is like nano-seconds. Microsoft (I believe) has changed newer versions of MSAccess to default to ADO and you add new references to utilize DAO but some of the hard-core DAO coders can probably expand on that.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •