looping through records and updating specific records

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

re: looping through records and updating specific records

RC wrote:
[color=blue]
> 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[/color]

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.
\__\

re: looping through records and updating specific records

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

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)

re: looping through records and updating specific records

"Keith Wilby" <keith.wilby@AwayWithYerCrap.com> wrote in message
news:Xns954E9157226AAkeithwilby@10.15.188.42...[color=blue]
> Trevor Best <nospam@localhost> wrote:
>[color=green]
> > 2) If it's a DAO recordset you need rst.Edit to start the update (not[/color][/color]
in[color=blue][color=green]
> > ADO)[/color]
>
> 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[/color]
from[color=blue]
> 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)\[/color]

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

re: looping through records and updating specific records

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!

re: looping through records and updating specific records

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

Only some? ;-)
[color=blue]
>
> 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.[/color]

Understood.
[color=blue]
>
> DAO.Recordset instead of just Recordset (for example)[/color]

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

re: looping through records and updating specific records

Keith Wilby wrote:
[color=blue][color=green]
>>DAO.Recordset instead of just Recordset (for example)[/color]
>
>
> Now that's very useful to know, I'll need to apply that by the looks of it.
> Thanks again.[/color]

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.
\__\