Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Posts
    107

    Unanswered: .OpenRecordSet for currentdb

    Hi there. I need to just insert a new record into my table. I don't want the system to retrieve all records that currently exist in this table because it's pretty big. But I think that's what my code is doing right now - actually, it was a freebee code snippet that I got from another forum to log all changes made to a form / record in ms access. Can I pass a select statement that doesn't return anything, just to get the recordset set up? How would I do this?
    The code looks like this:

    Public Function basAddHist(Hist As String, frm As Form, MyKeyName As String, MyCtrl As Control, uid as string)

    Dim dbs As DAO.Database
    Dim tblHistTable As DAO.Recordset

    Set dbs = CurrentDb
    Set tblHistTable = dbs.OpenRecordset(Hist, dbOpenDynaset)

    With tblHistTable
    .AddNew
    !mykey = frm.Controls(MyKeyName)
    !MyKeyName = MyKeyName
    !frmName = frm.Name

    !FldName = MyCtrl.ControlSource
    !dtChg = Now()

    !UserId = uid
    !OldVal = MyCtrl.OldValue
    !NewVal = MyCtrl
    .Update
    End With
    End Function

  2. #2
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Hello,

    Why not using a simple SQL-statement to add the record to the table?

    I usually do it like this.

    public sub Test()
    Dim strSQL As String

    strSQL = "INSERT INTO Hist (here you write all tablefields separated by a comma) VALUES ( here you write all the values in the same order as you wrote down the fields also separated by a comma)"

    'Set Warnings to false to avoid the application asking if you are sure to add the record
    DoCmd.SetWarnings (False)

    DoCmd.RunSQL strSQL

    'Set Warning on again
    DoCmd.SetWarnings (True)
    End Sub


    You have to take care that you check the type of the parameters.
    - Put strings between ' '
    - check if value could be Null
    - replace Null in case it is not allowed by using Nz()

    In case something goes wrong I just do a Debug.Print of my strSQL and paste it into the querybuilder. It's qut easy to debug then.

    Hope this will be of any help.

  3. #3
    Join Date
    Feb 2008
    Posts
    7
    Hi

    If you wish to do the insert via DAO, then use a SQL statement on the tble, but set the predicate such that no rows will be returned

    e.g

    strSQL = "Select * from HIST WHERE primary key = -1"

    Set dbs = CurrentDb
    Set tblHistTable = dbs.OpenRecordset(strSQL, dbOpenDynaset)

    no rows are returned, but you have a reference to the table ..

    HTH
    Chris

Posting Permissions

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