Prev: Filter Subform
Next: Working days inbetween 2 dates where Friday is the only holiday
From: JohnE on 16 May 2010 23:58
I am populating a form from a sqlserver2005 view using ADO. In the form
there are 5 fields that are allowing the user to enter (or update)
information in. The form is a continuous form. There could be times when
the user will update more then 1 of the records at a time. I would be adding
a SAVE button that would update the 5 fields to the table (not view). It is
a complicated issue (with others) which is why I'm not using the table as a
recordsource so don't ask. I am familiar and can do UPDATE sql statements.
What I am lost (relative noobie) at is how I would go about coding for the
update of 1 or more records from the form to the table in sqlserver2005.
Which is the purpose of this post.
Thanks...John
From: Arvin Meyer [MVP] on 17 May 2010 00:55
Here's some aircode, add error handling:

Sub Form_AfterUpdate()
Dim rst As DAO.Recordset
Dim db as DAO.Database
Dim i As Integer

Set db = CurrentDb
Set rst = db.Openrecordset("tblWhatever")
rst.MoveLast
For i = 1 To rst.Recordcount
With rst
.addnew
!Field1 = Textbox1
!Field2 = Textbox2
!Field3 = Textbox3
!Field4 = Textbox4
!Field5 = Textbox5
.update
End With
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"JohnE" <JohnE(a)discussions.microsoft.com> wrote in message
news:DCD545AD-8CAB-43D8-AB57-B0746A89103C(a)microsoft.com...
>I am populating a form from a sqlserver2005 view using ADO. In the form
> there are 5 fields that are allowing the user to enter (or update)
> information in. The form is a continuous form. There could be times when
> the user will update more then 1 of the records at a time. I would be
> adding
> a SAVE button that would update the 5 fields to the table (not view). It
> is
> a complicated issue (with others) which is why I'm not using the table as
> a
> recordsource so don't ask. I am familiar and can do UPDATE sql
> statements.
> What I am lost (relative noobie) at is how I would go about coding for the
> update of 1 or more records from the form to the table in sqlserver2005.
> Which is the purpose of this post.
> Thanks...John


From: John W. Vinson on 17 May 2010 01:02
On Sun, 16 May 2010 20:58:01 -0700, JohnE <JohnE(a)discussions.microsoft.com>
wrote:

>I am populating a form from a sqlserver2005 view using ADO. In the form
>there are 5 fields that are allowing the user to enter (or update)
>information in. The form is a continuous form. There could be times when
>the user will update more then 1 of the records at a time. I would be adding
>a SAVE button that would update the 5 fields to the table (not view). It is
>a complicated issue (with others) which is why I'm not using the table as a
>recordsource so don't ask. I am familiar and can do UPDATE sql statements.
>What I am lost (relative noobie) at is how I would go about coding for the
>update of 1 or more records from the form to the table in sqlserver2005.
>Which is the purpose of this post.
>Thanks...John

If the form is a continuous form... what is its recordsource? I presume that
it is NOT bound to the SQL table or you wouldn't need code (if it's a
non-updateable view then the user woulnd't be able to edit even one record,
much less two).

Please explain the context, and how the user is updating at all, currently?
--

John W. Vinson [MVP]
From: mie via AccessMonster.com on 17 May 2010 05:07
You use ADO, so the codes..

Dim rs As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim str as String

str="SELECT * FROM Table WHERE " & YourPrimaryKey

cnn.Open YourConnectionString
rs.CursorLocation=adUseClient
rs.Open str,cnn,adOpenDynamic,adLockOptimistic,adCmdText

'--Update Only
If Not rs.EOF Then
With rs
!Field1 = Textbox1
!Field2 = Textbox2
!Field3 = Textbox3
!Field4 = Textbox4
!Field5 = Textbox5
.update
End With
End if

rs.close
Set rs = Nothing
cnn.Close
Set cnn = Nothing

If you want to update multiple records (looping required), then change,
adLockOptimistic to adLockBatchOptimistic
update to updateBatch


JohnE wrote:
>I am populating a form from a sqlserver2005 view using ADO. In the form
>there are 5 fields that are allowing the user to enter (or update)
>information in. The form is a continuous form. There could be times when
>the user will update more then 1 of the records at a time. I would be adding
>a SAVE button that would update the 5 fields to the table (not view). It is
>a complicated issue (with others) which is why I'm not using the table as a
>recordsource so don't ask. I am familiar and can do UPDATE sql statements.
>What I am lost (relative noobie) at is how I would go about coding for the
>update of 1 or more records from the form to the table in sqlserver2005.
>Which is the purpose of this post.
>Thanks...John

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1

From: mie via AccessMonster.com on 17 May 2010 05:38
like John(MVP) said, how do you update record if your view is non-updateable?



JohnE wrote:
>I am populating a form from a sqlserver2005 view using ADO. In the form
>there are 5 fields that are allowing the user to enter (or update)
>information in. The form is a continuous form. There could be times when
>the user will update more then 1 of the records at a time. I would be adding
>a SAVE button that would update the 5 fields to the table (not view). It is
>a complicated issue (with others) which is why I'm not using the table as a
>recordsource so don't ask. I am familiar and can do UPDATE sql statements.
>What I am lost (relative noobie) at is how I would go about coding for the
>update of 1 or more records from the form to the table in sqlserver2005.
>Which is the purpose of this post.
>Thanks...John

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1

 | 
Pages: 1
Prev: Filter Subform
Next: Working days inbetween 2 dates where Friday is the only holiday