Creating and appending a table

[dougancil]

I am relatively new to VBA (I have dabbled some in VB) and I'm trying to do a few things:

1. When a user presses a button, the dataset that is created is appended to an existing database called "Payroll"
2. When the user presses that same button, they are given a combo box that asks them what BatchID this is (preferably a radio button with either 1, 2 or 3) and then they submit that to the database as it's own field BatchID.
3. Lastly, when the user submits the BatchID to the database, it then assigns a datestamp to the database in its own field "fldDate"

here is the code I have so far:

Private Sub Command2_Click()
On Error GoTo Command2_Click_Err

Dim rs As DAO.Recordset
Dim sqlStmt As String
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection

On Error GoTo Command2_Click_Err    'Error reporting on query code

    DoCmd.OpenQuery "DeleteExecupayTable", acViewNormal, acEdit
    DoCmd.OpenQuery "5_ExcepToExcupay", acViewNormal, acEdit
    DoCmd.OpenQuery "7_SumToExecupay", acViewNormal, acEdit
    DoCmd.OpenTable "6_1_Execupay", acViewNormal, acReadOnly
    
    
On Error GoTo DateStampError       'Error reporting on DateStamp code
sqlStmt = "SELECT fldDate FROM [tblDateStamp]"
Set rs = CurrentDb().OpenRecordset(sqlStmt)

With rs
    If .RecordCount = 0 Then
        .AddNew 'For first time use before a record added
        .Fields("fldDate") = Date
        .Update
    Else
        .MoveFirst
        .Edit
        .Fields("fldDate") = Date
        .Update
    End If
End With
rs.Close
Set rs = Nothing


Command2_Click_Exit:
    Exit Sub

Command2_Click_Err:
 MsgBox "Open Query code failed. " & Error$
 Resume Command2_Click_Exit

DateStampError:
 MsgBox "DateStamp code failed. " & Error$
 Resume Command2_Click_Exit


End Sub

Any help would be greatly appreciated.

Thank you

 

[DJkarl]

I am relatively new to VBA (I have dabbled some in VB) and I'm trying to do a few things:

1. When a user presses a button, the dataset that is created is appended to an existing database called "Payroll"
2. When the user presses that same button, they are given a combo box that asks them what BatchID this is (preferably a radio button with either 1, 2 or 3) and then they submit that to the database as it's own field BatchID.
3. Lastly, when the user submits the BatchID to the database, it then assigns a datestamp to the database in its own field "fldDate"

here is the code I have so far:

Private Sub Command2_Click()
On Error GoTo Command2_Click_Err
 
Dim rs As DAO.Recordset
Dim sqlStmt As String
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
 
On Error GoTo Command2_Click_Err    'Error reporting on query code
 
    DoCmd.OpenQuery "DeleteExecupayTable", acViewNormal, acEdit
    DoCmd.OpenQuery "5_ExcepToExcupay", acViewNormal, acEdit
    DoCmd.OpenQuery "7_SumToExecupay", acViewNormal, acEdit
    DoCmd.OpenTable "6_1_Execupay", acViewNormal, acReadOnly
 
 
On Error GoTo DateStampError       'Error reporting on DateStamp code
sqlStmt = "SELECT fldDate FROM [tblDateStamp]"
Set rs = CurrentDb().OpenRecordset(sqlStmt)
 
With rs
    If .RecordCount = 0 Then
        .AddNew 'For first time use before a record added
        .Fields("fldDate") = Date
        .Update
    Else
        .MoveFirst
        .Edit
        .Fields("fldDate") = Date
        .Update
    End If
End With
rs.Close
Set rs = Nothing
 
 
Command2_Click_Exit:
    Exit Sub
 
Command2_Click_Err:
 MsgBox "Open Query code failed. " & Error$
 Resume Command2_Click_Exit
 
DateStampError:
 MsgBox "DateStamp code failed. " & Error$
 Resume Command2_Click_Exit
 
 
End Sub

Any help would be greatly appreciated.

Thank you

What do you need help with? What part isn't working as intended?

 

[dougancil]

DJ,

First, I'm not sure how to insert my dataset into my table.
Secondly, I've created another form that is going to have the combo box, but I have no idea how to save what is picked in that form as appended data to the database.

That would be the start of this.

Thank you

Doug