Member Avatar for dougancil

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. I'll go back through my code today and clean it up. I hope that that makes more sense now as to what I'm trying to achieve.

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:

Option Compare Database
 Dim batchid As String
 
 
 Dim sConn As String
 sConn = "Provider='SQLOLEDB';Data Source='xxxxx';" & _
              "Initial Catalog='xxxxx';"
 
 Set sConn = New ADODB.Connection
 .Open
 
 On Error GoTo DateStampError 'Error reporting on DateStamp code
 sqlStmt = "APPEND timestamp FROM [Payroll]"
 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
 
  SelectCase Me.Frame7.Value
  Case 1
    batchid = "='1'"
  Case 2
    batchid = "='2'"
  Case 3
    batchid = "='3'"
    End Select
 
 DateStampError:
  MsgBox "DateStamp code failed. " & Error$
  Resume Command2_Click_Exit
 End Sub

I'm not familiar with how to open a dataconnection string in VBA and also will this code fulfill my other requirements?

Thank you

Doug

  • 2 Contributors
  • 3 Replies
  • 206 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by BitBlt

Dani AI

Generated

is trying to write a two-step save: insert the calculated payroll row from Form1, then collect an option on Form2 and append that option plus a timestamp to the same row. ’s comments about structure and object usage are on point. A small, dependable pattern will fix the issues and keep the code maintainable.

Recommended pattern (high level)

  • Use ADO (ADODB.Connection / ADODB.Command) to talk to SQL Server and add a reference to "Microsoft ActiveX Data Objects x.x" in the VBE.
  • On the main form button: execute a parameterized INSERT into the Payroll table, immediately retrieve the new identity (primary key) using SCOPE_IDENTITY(), then OpenForm and pass the new ID (OpenArgs or TempVars).
  • On the option form: run a parameterized UPDATE that sets the option and the timestamp WHERE PayrollID = @id.
  • Avoid keeping a transaction open across two forms. If atomicity is required, do both steps on the server in one stored procedure or mark the row as "pending" and complete it when the option is submitted.

Example: insert and get new ID (outline)

Dim cn As ADODB.Connection, cmd As ADODB.Command, rs As ADODB.Recordset
Dim newID As Long

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=SQLOLEDB;Data Source=SERVER;Initial Catalog=DB;Integrated Security=SSPI;"
cn.Open

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cn
cmd.CommandText = "INSERT INTO Payroll (Total, BatchID, CreatedAt) VALUES (@tot,@batch,@dt); SELECT SCOPE_IDENTITY() AS NewID;"
cmd.Parameters.Append cmd.CreateParameter("@tot", adDouble, adParamInput, , CDbl(sumValue))
cmd.Parameters.Append cmd.CreateParameter("@batch", adVarChar, adParamInput, 10, batchId)
cmd.Parameters.Append cmd.CreateParameter("@dt", adDate, adParamInput, , Now)

Set rs = cmd.Execute
If Not rs.EOF Then newID = CLng(rs!NewID)
rs.Close
cn.Close

Example: update from Form2 (outline)

' Use OpenArgs or TempVars to get newID on Form2
cmd.CommandText = "UPDATE Payroll SET OptionChosen=@opt, CompletedAt=@dt WHERE PayrollID=@id"
cmd.Parameters.Append cmd.CreateParameter("@opt", adInteger, adParamInput, , CInt(Me.Frame7.Value))
cmd.Parameters.Append cmd.CreateParameter("@dt", adDate, adParamInput, , Now)
cmd.Parameters.Append cmd.CreateParameter("@id", adInteger, adParamInput, , CLng(Me.OpenArgs))
cmd.Execute

Notes and cautions

  • Always close and Set Nothing on ADO objects in an error-handling/finally block.
  • Prefer parameterized commands to avoid quoting bugs and SQL injection.
  • Use Windows authentication (Integrated Security) if possible to avoid storing credentials.
  • If a linked Access table is already used, DAO can work, but direct SQL Server access via ADO is cleaner for this scenario.

Recommended Answers

All 3 Replies

Member Avatar for BitBlt

This code won't do anything. Here's why:
1. On line 5 you declare your variable sConn as a string, then on line 9 you try to assign it as an object. You have to declare your variable as an ADODB.Connection type.
2. On line 10, you have to specify the object to use the Open method on. So it should be "sConn.Open" or something like that.
3. On line 13, the literal is not a valid SQL statement. In order to open it as a recordset, it needs to be a "SELECT" statement.
4. On line 14, CurrentDB() is not an ADODB object. Therefore it has no relationship to sConn (your ADODB connection).
5. On line 42, there is no line label "Command2_Click_Exit".
6. There is no "Exit sub" to bypass executing "DateStampError" code.
7. None of these statements are within an event procedure, a sub or a function, therefore they will not compile or execute.

Shall I go on?

There are more problems with this code than things that are correct. Not to be offensive, but you might consider just passing this assignment to someone that has more experience with programming VBA. Otherwise, you face a steep learning curve (syntax, SQL and program design) if you want to complete your project in a reasonable amount of time.

If it is your desire to learn how to code VBA, then you should probably do more studying and looking at examples before you tackle something as advanced as what you state above.

Edited by BitBlt because: n/a
Member Avatar for dougancil

BitBit,

thank you for your constructive review of my code. I've not had the kind of time to dedicate to this code as I should have and it shows. I've gone back to it and cleaned it up so it should look somewhat better now. Do I have to select a recordset to append that data? I'm not presenting the data again, I'm just posting the recordset to the database. Here is the code from my originating form which passes the recordset to this page:

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
Edited by Dani because: Fixed formatting
Member Avatar for BitBlt

DAO is not the preferred method for data access...it was supplanted years ago by ActiveX Data Objects (ADO). Look in the VB help file for example code.

There's really not enough detail in your post to go on, and this really isn't a tutorial site. It seems like you have a lot of independent study you need to do. If you have specific problems you can't figure out, you can get specific answers here for what has you stumped.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.