If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Add a record in VBA
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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200804/1 |
#2
|
|||
|
|||
Add a record in VBA
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 szag wrote: 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. -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
Add a record in VBA
It worked - thanks!
NuBie wrote: 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. [quoted text clipped - 11 lines] Thanks for the help. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200804/1 |
#4
|
|||
|
|||
Add a record in VBA
"NuBie via AccessMonster.com" u42912@uwe wrote in message
news:82b7a78409f09@uwe... 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 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 Set dbs = Nothing 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 -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#5
|
|||
|
|||
Add a record in VBA
Glad to help
szag wrote: It worked - thanks! something like this will work [quoted text clipped - 22 lines] Thanks for the help. -- spread the WORD Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200804/1 |
Thread Tools | |
Display Modes | |
|
|