Posting a dataset to a SQL table

[dougancil]

I have an application that I'm trying to do the following 3 things on.

When a user presses a button, another form is opened with a Union Group giving the user 3 options (1, 2, or 3) and when the user submits their choice, the form is closed and:

1. A SQL table is appended with the dataset from the application
2. A timestamp is inserted into the table
3. the values of the Union group are added to a field in the table.

I'm new to VBA and don't know how to do this. My code for form1 is as follows:

Option Compare Database

'------------------------------------------------------------
' Command0_Click
'
'------------------------------------------------------------
Private Sub Command0_Click()
On Error GoTo Command0_Click_Err

    DoCmd.OpenQuery "1_LogInScratchPad", acViewNormal, acEdit
    DoCmd.OpenTable "2_ScratchPad", acViewNormal, acEdit


Command0_Click_Exit:
    Exit Sub

Command0_Click_Err:
    MsgBox Error$
    Resume Command0_Click_Exit

End Sub

'------------------------------------------------------------
' Command1_Click
'
'------------------------------------------------------------
Private Sub Command1_Click()
On Error GoTo Command1_Click_Err


    DoCmd.OpenQuery "2_ExceptionsScratchPad", acViewNormal, acEdit
    DoCmd.OpenTable "3_ExcepScratchPad", acViewNormal, acEdit


Command1_Click_Exit:
    Exit Sub

Command1_Click_Err:
    MsgBox Error$
    Resume Command1_Click_Exit

End Sub

'------------------------------------------------------------
' Command2_Click
'
'------------------------------------------------------------
Private Sub Command2_Click()
DoCmd.OpenForm "Form_Form1", acNormal, acEdit
On Error GoTo Command2_Click_Err

Dim rs As DAO.Recordset
Dim sqlStmt As String


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

and form2 code is this

Option Compare Database
Dim batchid As String

Private Sub Frame7_AfterUpdate()
 SelectCase Me.Frame7.Value
 Case 1
   batchid = "='1'"
 Case 2
   batchid = "='2'"
 Case 3
   batchid = "='3'"
   End Select
End Sub

any help would be appreciated.

Thank you

Doug

 

[jdraw]

What happens when you try to run the code? What have you tried? What error message or whatever did you get?

Please help us help you by giving us some additional info.

 

[dougancil]

As it stands currently, I get an error of 2491. The action or method is invalid because the form or report isn't bound to a table or query. This is when I try to run Form1, and when I Debug that it takes me to this line of my code:

DoCmd.OpenForm "Form1", acNormal, acEdit