Add a record in VBA

  • Thread starter szag via AccessMonster.com
  • Start date
S

szag via AccessMonster.com

I have a form that has a combination of several unbound fields : Staff Name,
Date, Category, Hours.

When the "Hours" field gets updated I want code to run that will take the
current data in the staff name, date, category and hours fields and put it in
a transaction table with equivalent fields in it.

I realize the best way would be to send the data directly to the table via
the form but for several different reasons the client is asking for some
complexities that do not allow for this.

I am looking for help in writing an add record code to put the unbound data
in the transaction table.

Thanks for the help.
 
N

NuBie via AccessMonster.com

something like this will work

Set dbs = CurrentDb
Set rs = New ADODB.Recordset
Set cn = CurrentProject.Connection

sqlString = "select * from TransactionTable"
rs.Open sqlString, cn, adOpenDynamic, adLockOptimistic
rs.AddNew

rs![StaffName] = Me.TxtStaffName
rs![SDate] = Me.TxtDate
rs![SCategory] = Me.TxtCategory
rs![SHours] = Me.TxtHours
rs.Update
rs.Close

Set cn = Nothing
Set rs = Nothing
Set dbs = Nothing
 
S

szag via AccessMonster.com

It worked - thanks!
something like this will work

Set dbs = CurrentDb
Set rs = New ADODB.Recordset
Set cn = CurrentProject.Connection

sqlString = "select * from TransactionTable"
rs.Open sqlString, cn, adOpenDynamic, adLockOptimistic
rs.AddNew

rs![StaffName] = Me.TxtStaffName
rs![SDate] = Me.TxtDate
rs![SCategory] = Me.TxtCategory
rs![SHours] = Me.TxtHours
rs.Update
rs.Close

Set cn = Nothing
Set rs = Nothing
Set dbs = Nothing
I have a form that has a combination of several unbound fields : Staff Name,
Date, Category, Hours.
Click to expand...
[quoted text clipped - 11 lines]
Thanks for the help.
Click to expand...
Click to expand...
 
D

Dirk Goldgar

NuBie via AccessMonster.com said:
something like this will work

Set dbs = CurrentDb
Set rs = New ADODB.Recordset
Set cn = CurrentProject.Connection

sqlString = "select * from TransactionTable"
rs.Open sqlString, cn, adOpenDynamic, adLockOptimistic
rs.AddNew

rs![StaffName] = Me.TxtStaffName
rs![SDate] = Me.TxtDate
rs![SCategory] = Me.TxtCategory
rs![SHours] = Me.TxtHours
rs.Update
rs.Close

Set cn = Nothing
Set rs = Nothing
Set dbs = Nothing
Click to expand...


Since you're using ADO to write the record, you are not using the (DAO) dbs
object and can delete the lines relating to it:
Set dbs = CurrentDb
Click to expand...
Set dbs = Nothing
Click to expand...

I'd recommend not opening a recordset on the whole table, though, since all
you want to do is add a record. Either apply criteria in the SELECT
statement to return no records:

sqlString = "select * from TransactionTable WHERE 1=0"

Or just build and execute an append query, replacing all the above code
with:

sqlString = _
"INSERT INTO TransactionTable " & _
"(StaffName, SDate, SCategory, SHours)" & _
"VALUES(" & _
"""" & Me.txtStaffName & """," & _
"#" & Format(Me.txtDate, "m/d/yyyy") & "#," & _
"""" & Me.txtCategory & """," & _
Me.txtHours & _
")"

CurrentProject.Connection.Execute sqlString

Note: if the original poster wants to use DAO instead of ADO, then the code
for the recordset method becomes:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset( _
"select * from TransactionTable WHERE 1=0")

With rs
.AddNew
![StaffName] = Me.TxtStaffName
![SDate] = Me.TxtDate
![SCategory] = Me.TxtCategory
![SHours] = Me.TxtHours
.Update
.Close
End With

Set rs = Nothing

And the append-query approach becomes:

sqlString = _
"INSERT INTO TransactionTable " & _
"(StaffName, SDate, SCategory, SHours)" & _
"VALUES(" & _
"""" & Me.txtStaffName & """," & _
"#" & Format(Me.txtDate, "m/d/yyyy") & "#," & _
"""" & Me.txtCategory & """," & _
Me.txtHours & _
")"

CurrentDb.Execute sqlString, dbFailOnError
 
Top