Try:
With rst
.MoveFirst
Do Until .EOF
.Edit
If [Ship Qty] > [MaxShipQty] Then
!Status = "Over Shipped"
ElseIf [Ship Qty] < [Order Qty] Then
!Status = "Short"
Else
!Status = "Pass"
End If
rst.Update
rst.MoveNext
Loop
End With
Set rst = Nothing
Set dbs = Nothing
Glenn P. Jones
Comm: 904 823-0653
-----Original Message-----
From: [email protected]
[mailto:[EMAIL PROTECTED] On Behalf Of Toby Bierly
Sent: Monday, 07 November, 2005 7:59 PM
To: [email protected]
Subject: Re: [AccessDevelopers] Re: Database for training
Absolutely, but put the rst.MoveNext AFTER the If statement or else you'll
end up with it looping forever on the first record that doesn't make it
inside the If statement, like it is doing now.
Right now, you are basically saying, "Move to the next record in the
recordset only if condition is true," resulting in an infinite loop.
HTH,
Toby
----- Original Message -----
From: "jamrmask" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Monday, November 07, 2005 4:46 PM
Subject: [AccessDevelopers] Re: Database for training
> Can you within a recordset apply if then statements like below (this
> does not work by the way)? It seems to loop until Access stops
> responding.
>
> Dim dbs As Database
> Dim rst As Recordset
> Dim strSQL As String
>
>
> Set dbs = CurrentDb()
>
> strSQL = "SELECT * FROM [Late Report]"
> strSQL = strSQL & " WHERE [Order#]=" & Me![Order#]
> strSQL = strSQL & " ORDER BY [Order#];"
>
> Set rst = dbs.OpenRecordset(strSQL)
>
> While (Not (rst.EOF))
> rst.Edit
> If [Ship Qty] > [MaxShipQty] Then
> rst.[Status] = "Over Shipped"
> ElseIf [Ship Qty] < [Order Qty] Then
> rst.[Status] = "Short"
> Else: rst.[Status] = "Pass"
>
>
> rst.Update
>
> rst.MoveNext
> End If
>
> Wend
>
>
> --- In [email protected], "Toby Bierly" <[EMAIL PROTECTED]>
> wrote:
> >
> > "I'm lost in how to open the recordset."
> >
> > Here's an example:
> >
> > ********CODE**************
> > Dim rst As Recordset
> >
> > ' <Query, Table, Or SQL> can be the name of a query, the name of a
> table, or
> > the actual text of an SQL query
> > Set rst = CurrentDb.OpenRecordset("<Query, Table, Or SQL>")
> >
> > ' This will loop through and print the first two fields of each
> record in
> > the Debug window (Ctrl-G)
> > With rst
> > Do While Not .EOF
> > ' Refer by index #
> > Debug.Print .Fields(0)
> > Debug.Print .Fields(1)
> > ' Refer by field names
> > Debug.Print !Field1Name
> > Debug.Print !Field2Name
> > .MoveNext
> > Loop
> > End With
> >
> > ' Here's how to refer to the fields outside a With block
> > ' This will print the first records first two fields again
> > rst.MoveFirst Debug.Print rst!Field1Name
> > Debug.Print !Field2Name
> > ********END CODE**************
> >
> > HTH,
> > Toby
> >
> > ----- Original Message -----
> > From: "jamrmask" <[EMAIL PROTECTED]>
> > To: <[email protected]>
> > Sent: Saturday, November 05, 2005 2:26 PM
> > Subject: [AccessDevelopers] Database for training
> >
> >
> > > Does anyone have a access database that has most elements
> including
> > > VBA that a person could use to learn from? I've always had
> trouble
> > > taking from a book (of which I have 8 of them about Access) and
> > > putting it into action. I've always had better luck learning from
> > > "hands on" activities. What I've learned about Access so
> far I
> > > have learned in this fashion either from the databases that come
> > > with Access or snippets from people posting on this group. I'm
> now
> > > in need of learning the deeper things of VBA like my current
> problem
> > > which is I want to be able to use a check box on the main form
> (PO
> > > form) and recieve everything using a filter to open the reciever
> > > form and then have everything equal what was ordered for a quick
> > > recieve. I have a command button on the PO that I can open the
> > > reciever and then recieve line by line however this would be a
> great
> > > time saver when there are 20 or 30 items to recieve. I can get
> the
> > > form to open and for it to recieve the first row/item in the list
> > > and understand I need to loop through all of the filtered records
> > > but that's where I fail. I would assume that I need to open a
> > > recordset and then loop through that applying the same line of
> VBA
> > > to each record and then close the recordset but I'm lost in how
> to
> > > open the recordset. I've looked through Microsoft help and have
> > > downloaded the Help files to my computer but most are to general
> and
> > > do not apply to what I'm trying to do. Is there a site somewhere
> > > that would explain VBA with examples? I don't mind paying for
> access
> > > to a site but want to make sure I do not waste my money. Any
> > > suggestions?
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > Please zip all files prior to uploading to Files section. Yahoo!
> > > Groups Links
> > >
> > >
> > >
> > >
> > >
> > >
> >
>
>
>
>
>
>
>
>
> Please zip all files prior to uploading to Files section. Yahoo!
> Groups Links
>
>
>
>
>
>
>
Please zip all files prior to uploading to Files section.
Yahoo! Groups Links
------------------------ Yahoo! Groups Sponsor --------------------~-->
Fair play? Video games influencing politics. Click and talk back!
http://us.click.yahoo.com/T8sf5C/tzNLAA/TtwFAA/q7folB/TM
--------------------------------------------------------------------~->
Please zip all files prior to uploading to Files section.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AccessDevelopers/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
