looping through specific records and updating

I have a table that lists many box numbers. Each box number has a
Pallet Number (indicating which pallet the box is in). When the
Pallets are loaded into a shipping Container I need to update the
table to indicate which pallets and boxes are in the container.

In my code below, in the table named "Products", I find the first
Pallet Number that matches the Pallet Number typed in the box on my
form (PalletNumberContainerFormComboBox). Then for that Pallet number
I update the Container field with the container number from the
GETContainerNumber box. BUT if stops after updating the first Pallet
number. There are many records in the table that have the same Pallet
number and I need to find each one and set the Container Number. I
have tried every kind of For-Each, While Wend, etc. loop but I can't
get it to work. Thanks in advance for any help with this.

Private Sub PalletNumberContainerFormComboBox_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[PalletNumberProductsTable] = " &
Str(Nz(Me![PalletNumberContainerFormComboBox], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
[ContainerNumberProductsTable] = Me.GETContainerNumber.Value
End Sub

re: looping through specific records and updating

Hmm... any reason you're not using a parameterized update query?
Seems a lot easier than this method...

re: looping through specific records and updating

I don't know how to write an update query in code. The Container
number is set via a bar code scanner attached to a laptop. The boxes
and pallets are scanned as they go into the container. The cursor is
in a combobox on a form and when the pallet number is scanned it's the
same as typing the number in the combo box and pressing enter. If I
can use a different method that would be fine. I don't need code,
just point me in the right direction. If you don't think the update
query will fit this then should I use the code I have and put in a
MoveNext to make it cycle? Or.... Do If pallet number equals the
variable then container number equals the container number loop?
Thanks for your help.

pietlinden@hotmail.com (Pieter Linden) wrote in message news:<bf31e41b.0408211524.189046bb@posting.google. com>...[color=blue]
> Hmm... any reason you're not using a parameterized update query?
> Seems a lot easier than this method...[/color]

re: looping through specific records and updating

I don't know SQL, I usually write everything in VBA, but, I could make
an update query in Access design view and then switch to SQL view to
see the SQL code. Then, do I just pop that SQL language into the VBA
editor to replace part or all of the code that I posted with this
message?

pietlinden@hotmail.com (Pieter Linden) wrote in message news:<bf31e41b.0408211524.189046bb@posting.google. com>...[color=blue]
> Hmm... any reason you're not using a parameterized update query?
> Seems a lot easier than this method...[/color]

re: looping through specific records and updating

Sounds like you are comfortable with basic VBA skills... Yes???

If so, then you are correct about copying the SQL code from a
manually-created query into a Click event for a command button. Stuff
the code into a string variable, then edit the string to replace the
"hardwired" parts from the manual query with variables taken from your
form's various controls.

Then execute the query: Currentdb.exe sSQL

Using the Access query grid to construct the proper SQL syntax is a
great time-saver and a great way to learn SQL. You just need to learn
what parts of the query to modify in code for the current conditions.
As mentioned, using update queries is vastly superior to looping thru
records.



On 22 Aug 2004 05:59:21 -0700, rcmail14872@yahoo.com (RC) wrote:
[color=blue]
>I don't know SQL, I usually write everything in VBA, but, I could make
>an update query in Access design view and then switch to SQL view to
>see the SQL code. Then, do I just pop that SQL language into the VBA
>editor to replace part or all of the code that I posted with this
>message?
>
>pietlinden@hotmail.com (Pieter Linden) wrote in message news:<bf31e41b.0408211524.189046bb@posting.google. com>...[color=green]
>> Hmm... any reason you're not using a parameterized update query?
>> Seems a lot easier than this method...[/color][/color]


**********************
jackmacMACdonald@telusTELUS.net
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

re: looping through specific records and updating

Here is a sample of my looping code that does not work.

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[PalletNumberProductsTable] = " &
Str(Nz(Me![PalletNumberContainerFormComboBox], 0))
While Not rs.EOF
rs.FindNext "[PalletNumberProductsTable] = " &
Str(Nz(Me![PalletNumberContainerFormComboBox], 0))

rs.Edit
rs.Update
[ContainerNumberProductsTable] = Me.GETContainerNumber.Value
rs.MoveNext
Wend

pietlinden@hotmail.com (Pieter Linden) wrote in message news:<bf31e41b.0408211524.189046bb@posting.google. com>...[color=blue]
> Hmm... any reason you're not using a parameterized update query?
> Seems a lot easier than this method...[/color]

re: looping through specific records and updating

I tried to make some SQL code using both if and iif and the code below
gives the error message "you have entered and operand without an
operator" and the word "then" is highlighted.

iif ([Products]![PalletNumberProductsTable] =
Forms![ContainerAssociationForm]![PalletNumberContainerFormComboBox].Aft
erUpdate then [Products]![ContainerNumberProductsTable] =
Forms![ContainerAssociationForm]![GETContainerNumber])


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

re: looping through specific records and updating

rcmail14872@yahoo.com (RC) wrote in message news:<3c71b23b.0408220643.6a6d49a8@posting.google. com>...[color=blue]
> Here is a sample of my looping code that does not work.
>
> Dim rs As Object
> Set rs = Me.Recordset.Clone
> rs.FindFirst "[PalletNumberProductsTable] = " &
> Str(Nz(Me![PalletNumberContainerFormComboBox], 0))
> While Not rs.EOF
> rs.FindNext "[PalletNumberProductsTable] = " &
> Str(Nz(Me![PalletNumberContainerFormComboBox], 0))
>
> rs.Edit
> rs.Update
> [ContainerNumberProductsTable] = Me.GETContainerNumber.Value
> rs.MoveNext
> Wend
>[/color]
Well, the most obvious problem here is that you don't update any
fields in your recordset after going into Edit mode.

Say you've already found the records you want, so then it's:

rs.Edit '----allow editing of the record
rs.Fields("PalletNumber")=Me.Controls("cboPalletNo ")
rs.Update '----update the record with the new value.

rs.close
set rs=nothing
.....

re: looping through specific records and updating

There are at least two problems with your syntax

1) IIF() is the "Immediate If" function, and is not used at all like
you have indicated. It is used like this:
X=IIF(A=1,2,3)

where x will be assigned the value "2" if A=1, otherwise it will be
assigned the value "3"

2) .AfterUpdate is an event handler -- it is not a variable that you
can use in a comparison.

I am guessing that you wanted something like:

IF ([Products]![PalletNumberProductsTable] = _
Forms![ContainerAssociationForm]![PalletNumberContainerFormComboBox]

Then

[Products]![ContainerNumberProductsTable] = _
Forms![ContainerAssociationForm]![GETContainerNumber])

EndIf


Note that neither of these two items pertain to my original suggestion
of using an update query. Judging by your code in another message, I
would probably use something like:

sSQL = "UPDATE Products SET [ContainerNumberProductsTable] = " & _
Forms![ContainerAssociationForm]![GETContainerNumber]) & _
" WHERE [PalletNumberProductsTable] =" & _
Me![PalletNumberContainerFormComboBox]

CurrentDB.Execute sSQL



On 22 Aug 2004 15:13:20 GMT, R C <rcmail14872@yahoo.com> wrote:
[color=blue]
>I tried to make some SQL code using both if and iif and the code below
>gives the error message "you have entered and operand without an
>operator" and the word "then" is highlighted.
>
>iif ([Products]![PalletNumberProductsTable] =
>Forms![ContainerAssociationForm]![PalletNumberContainerFormComboBox].Aft
>erUpdate then [Products]![ContainerNumberProductsTable] =
>Forms![ContainerAssociationForm]![GETContainerNumber])
>
>
>*** Sent via Developersdex http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it![/color]


**********************
jackmacMACdonald@telusTELUS.net
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security