CODEWITHSUNDEEP
excelvbaappend
Xavier
Xavier

Reputation: 7

VBA add new record to table in Access

I have a (linked) table [Traffic] with one field named [Log]
I have a variable IO which can be "I" or "O".

The only purpose of this function is to add a new record/row to table [Traffic] in column [Log] that is containing one string: a date stamp combined with an "I" or an "O" every time a form is loaded/unloaded.

I try to create a Function in Ms Access 2010 without success (Error: "Object Required").

Any help is highly appreciated.

Public Function AppendTxt(IO As String)
Dim sText As String
Dim sTableName As String
Dim col As Integer
Dim lLastRow As Long
Dim iHeader As Integer

sTableName = "Traffic"
sText = Format$(Now, "yyyy\-mm\-dd hhnn") & IO
col = 0

 With ActiveSheet.ListObjects(sTableName)
    'find the last row of the list
    lLastRow = ActiveSheet.ListObjects(sTableName).ListRows.Count
    'shift from an extra row if list has header
    If .Sort.Header = xlYes Then
        iHeader = 1
    Else
        iHeader = 0
    End If
End With
'add the data a row after the end of the list
ActiveSheet.Cells(lLastRow + 1 + iHeader, col).Value = sText

End Function

Upvotes: 0

Views: 6122

Answers (1)

Jens
Jens

Reputation: 879

Public Function Appendtxt(IO As String)
Dim sql As String
sql = "INSERT INTO tbl ( [timestamp], var ) " & _
"SELECT #" & Time() & "#, """ & IO & """ AS Expr2"

DoCmd.RunSQL sql

End Function

Assuming you were making a mistake when posting a piece of excel code here, this should do the trick.

EDIT: to get rid of any warning message, call the following function at the startup of the database.

Function fncSetOptions()
    Application.SetOption "Confirm Action Queries", False
    Application.SetOption "Confirm Document Deletions", False
    Application.SetOption "Confirm Record Changes", False
    DoCmd.SetWarnings False
End Function

Upvotes: 1

Related Questions