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:
as you can see the only thing that's done here is to open my other form (bold text.)
Here's my code for form 2:
This is what I'm not sure of. I just started using VBA two weeks ago and would like to be able to do those 3 things mentioned at the start of this post. Can anyone offer assistance?
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()
[B]DoCmd.OpenForm "Form_Form1", acNormal, acEdit[/B]
On Error GoTo Command2_Click_Err
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
Command2_Click_Exit:
Exit Sub
Command2_Click_Err:
MsgBox "Open Query code failed. " & Error$
Resume Command2_Click_Exit
End Sub
Here's my code for form 2:
Code:
Option Compare Database
Dim batchid As String
Private Sub Submit_Click()
Dim cnt As ADODB.Connection
Dim rs As DAO.Recordset
Dim sqlStmt As String
Const stADO As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;" & _
"Initial Catalog=Payroll;" & _
"Data Source=10.2.1.41"
'where Payroll is SQL Database & 10.2.1.41 is SQL Server
On Error GoTo DateStampError 'Error reporting on DateStamp code
sqlStmt = "SELECT fldDate FROM [tblDateStamp]"
Set rs = CurrentDb().OpenRecordset(sqlStmt)
Set cnt = New ADODB.Connection
With cnt
.Open stADO
.CommandTimeout = 0
Set rst = .Execute(stSQL)
End With
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
DateStampError:
MsgBox "DateStamp code failed. " & Error$
Resume Submit_Click_Exit
End Sub
Private Sub Frame7_AfterUpdate()
SelectCase Me.Frame7.Value
Case 1
batchid = "='1'"
Case 2
batchid = "='2'"
Case 3
batchid = "='3'"
End Select
End Sub
This is what I'm not sure of. I just started using VBA two weeks ago and would like to be able to do those 3 things mentioned at the start of this post. Can anyone offer assistance?
Thank you
Doug