I have an application that I'm trying to finish in VBA, and as I've never written in it, it's a bit challenging to me. I've written some in VB but the syntax isn't the same between the two for some things. Essentially, here's the logic (or lack of) that I'm trying to accomplish with this task. This is a payroll program that was written by someone who is no longer with our organization. I am trying to finalize this, and realized that when reviewing it for finalization, that it was missing some components. Currently there is no way to create an audit trail for this program (which is why the timestamp is now coming into play.) Also, there is a value (the option group) that wasn't in the original version of this app. As far as the other two buttons on the first form, those perform calculations that end with a result when a user presses button #3 on the main form. What I'd like that 3rd button to also do is to open the second form, post the data from the results on the first form, show the option group with a submit button, when the user presses the submit button on form2, it appends a timestamp and the options group selection to the same table that the results of form1 were posted to.
Step by step ... this is how the program should work:
1. User presses buttons 1, 2 and 3 on main form and gets a "sum" for the payroll for a specific time frame.
2. When the user presses the last button on main form, the form closes, the sum of the data is posted to a SQL database and a second form is opened.
3. The second form is a option group with 3 options, 1, 2, or 3 and a submit button.
4. The user chooses an option and presses the submit button on the second form, which then posts the option group result AND the timestamp to the SQL table. (the same SQL table where the information from the main form is posted)
5. Form 2 closes.
here is my code thus far: (main form code)
and form2 code
The main problem that I'm having at this point is writing the sql connection string. I've never written one in vba. Also passing the data from the access table to a record set and then to the sql is the other issue.
Can someone please assist?
Thank you
Doug
Step by step ... this is how the program should work:
1. User presses buttons 1, 2 and 3 on main form and gets a "sum" for the payroll for a specific time frame.
2. When the user presses the last button on main form, the form closes, the sum of the data is posted to a SQL database and a second form is opened.
3. The second form is a option group with 3 options, 1, 2, or 3 and a submit button.
4. The user chooses an option and presses the submit button on the second form, which then posts the option group result AND the timestamp to the SQL table. (the same SQL table where the information from the main form is posted)
5. Form 2 closes.
here is my code thus far: (main form code)
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()
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
DoCmd.OpenForm "Form2", acNormal
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
Code:
Private Sub Command2_Click()
Option Compare Database
Dim batchid As String
sConn = "Provider='SQLOLEDB';Data Source='xxxxx';" & _
"Initial Catalog='xxxxx';"
Set sConn = New adodb.Connection
sConn.Open
On Error GoTo DateStampError 'Error reporting on DateStamp code
sqlStmt = "Insert into payroll (timestamp, batchid) values (date(), batchid)"
Set rs = CurrentDb().OpenRecordset(sqlStmt)
With rs
If .RecordCount = 1 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
SelectCase Me.Frame7.Value
Case 1
batchid = "='1'"
Case 2
batchid = "='2'"
Case 3
batchid = "='3'"
End Select
Command2_Click_Exit
DateStampError:
MsgBox "DateStamp code failed. " & Error$
Resume Command2_Click_Exit
DoCmd.Me.Close
End Sub
The main problem that I'm having at this point is writing the sql connection string. I've never written one in vba. Also passing the data from the access table to a record set and then to the sql is the other issue.
Can someone please assist?
Thank you
Doug