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:
and form2 code is this
any help would be appreciated.
Thank you
Doug
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:
Code:
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
Code:
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