By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,454 Members | 3,191 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,454 IT Pros & Developers. It's quick & easy.

looping through records and updating specific records

P: n/a
RC
My code below will loop through all the records in the table, and when
the if statement is true it goes to the
***Me.ContainerNumberProductsTable = GETContainerNumber.Value*** bit
like should but it does not make the change/update in the table. Any
ideas?
Dim rst As Object
Set rst = Me.Recordset.Clone
If Not rst.EOF Then Me.Bookmark = rst.Bookmark
varKeepGoing = [PalletNumberContainerFormComboBox]
Dim Found As Boolean
Do While Not rst.EOF
If rst("PalletNumberProductsTable") = varKeepGoing Then
Me.ContainerNumberProductsTable = GETContainerNumber.Value
End If
rst.MoveNext
varKeepGoing = [PalletNumberContainerFormComboBox]
Loop
If Not Found Then
msgbox "No Match Found"
End If
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
RC wrote:
My code below will loop through all the records in the table, and when
the if statement is true it goes to the
***Me.ContainerNumberProductsTable = GETContainerNumber.Value*** bit
like should but it does not make the change/update in the table. Any
ideas?
Dim rst As Object
Set rst = Me.Recordset.Clone
If Not rst.EOF Then Me.Bookmark = rst.Bookmark
varKeepGoing = [PalletNumberContainerFormComboBox]
Dim Found As Boolean
Do While Not rst.EOF
If rst("PalletNumberProductsTable") = varKeepGoing Then
Me.ContainerNumberProductsTable = GETContainerNumber.Value
End If
rst.MoveNext
varKeepGoing = [PalletNumberContainerFormComboBox]
Loop
If Not Found Then
msgbox "No Match Found"
End If


Not sure what you're trying to do here but if you're trying to update
the recordset then the following points may help.

1) you should assign a value to one of the recordset's fields, e.g.
rst!FieldName = blahblah
2) If it's a DAO recordset you need rst.Edit to start the update (not in
ADO)
3) In both DAO and ADO you need a rst.Update to finish updating the
recordset.
--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

Nov 13 '05 #2

P: n/a
Trevor Best <nospam@localhost> wrote:
2) If it's a DAO recordset you need rst.Edit to start the update (not in
ADO)


The whole DAO/ADO thing is a bit over my head but AIUI A97 uses DAO but
A2003 uses ADO - that right? That being the case, if I convert an app from
A97 to A2003 are my rs.Edit instructions no longer required and, more
importantly, will leaving them there do any harm?

Or have I completely misunderstood the whole concept? ;o)
Nov 13 '05 #3

P: n/a
"Keith Wilby" <ke*********@AwayWithYerCrap.com> wrote in message
news:Xn************************@10.15.188.42...
Trevor Best <nospam@localhost> wrote:
2) If it's a DAO recordset you need rst.Edit to start the update (not in ADO)
The whole DAO/ADO thing is a bit over my head but AIUI A97 uses DAO but
A2003 uses ADO - that right? That being the case, if I convert an app

from A97 to A2003 are my rs.Edit instructions no longer required and, more
importantly, will leaving them there do any harm?

Or have I completely misunderstood the whole concept? ;o)\


You've misunderstood "some" of the concept. The differences relate to what
libraries are referenced *by default in a new project*.

If you convert an A97 file that uses DAO to a newer version it will still
reference and use DAO in the newer version and none of your syntax need
change. It is when you create *brand new* files that DAO will not be
referenced by default in A2000 and A2002. A2003 actually references BOTH
ADO and DAO by default so you have to remove one of them or make sure you
use unambiguous syntax for objects that exist in both ADO and DAO.

DAO.Recordset instead of just Recordset (for example)
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


Nov 13 '05 #4

P: n/a
RC
I figured out a way to loop through the records in VBA code and update
specific records but it was extremely slow. The solution: I added the
single line of SQL code below to the AfterUpdate event for the
GetPalletNumberBox combo box on Form1. It works perfectly and lightning
fast. It takes the number in Text3 and puts that number in the
ContainerNumber column in Table1 for each record where the PalletNumber
column matches the number in the GetPalletNumberBox on Form1.

DoCmd.RunSQL ("UPDATE Table1 SET Table1.ContainerNumber =
[Forms]![Form1]![Text3] WHERE
(((Table1.PalletNumber)=[Forms]![Form1]![GetPalletNumberBox]));")

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #5

P: n/a
"Rick Brandt" <ri*********@hotmail.com> wrote:
Or have I completely misunderstood the whole concept? ;o)\
You've misunderstood "some" of the concept. The differences relate to
what libraries are referenced *by default in a new project*.


Only some? ;-)

If you convert an A97 file that uses DAO to a newer version it will
still reference and use DAO in the newer version and none of your
syntax need change. It is when you create *brand new* files that DAO
will not be referenced by default in A2000 and A2002. A2003 actually
references BOTH ADO and DAO by default so you have to remove one of
them or make sure you use unambiguous syntax for objects that exist in
both ADO and DAO.
Understood.

DAO.Recordset instead of just Recordset (for example)


Now that's very useful to know, I'll need to apply that by the looks of it.
Thanks again.
Nov 13 '05 #6

P: n/a
Keith Wilby wrote:
DAO.Recordset instead of just Recordset (for example)

Now that's very useful to know, I'll need to apply that by the looks of it.
Thanks again.


And "DAO.Recordset" notation is really useful when posting code to
usenet to save some poor guru babbling on about one way when the poster
is doing the other :-)

--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.