A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Add a record in VBA



 
 
Thread Tools Display Modes
  #1  
Old April 15th, 2008, 02:36 PM posted to microsoft.public.access.forms
szag via AccessMonster.com
external usenet poster
 
Posts: 99
Default 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  
Old April 15th, 2008, 02:54 PM posted to microsoft.public.access.forms
NuBie via AccessMonster.com
external usenet poster
 
Posts: 67
Default 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  
Old April 15th, 2008, 03:23 PM posted to microsoft.public.access.forms
szag via AccessMonster.com
external usenet poster
 
Posts: 99
Default 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  
Old April 15th, 2008, 03:32 PM posted to microsoft.public.access.forms
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default 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  
Old April 15th, 2008, 03:40 PM posted to microsoft.public.access.forms
NuBie via AccessMonster.com
external usenet poster
 
Posts: 67
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:53 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.