RE: Passing over Requery

Hi, Jenny.

You're welcome. Glad you got it working.

> The other stuff you said went over my head.
> No formal training, so I have problems with
> the lingo etc. I appreciate your time.

The best developers are often those who are self-taught, not the ones who
took a class to learn Access. However, you may want to post questions in the
"New Users" newsgroup, since answers posted in the "FormsCoding" newsgroup
are generally geared for people with intermediate or advanced skills. You
may find the "New Users" newsgroup on the following Web page:

http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.access.gettingstarted&lang=en&cr=US

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are Ripley@xxxxxxxxxxxxxxx and scott@xxxxxxxxxxxxxxxxxx

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


"Jenny at APOC" wrote:

> '69 Camaro,
> Thanks. I ended up with this
> SQLStmt = "UPDATE FileCartonTable SET FileCartonTable.[Carton Status] = No
> " & _
> "WHERE FileCartonNbr = '" & stFCarton & "'"
> And it works. The other stuff you said went over my head. No formal
> training, so I have problems with the lingo etc. I appreciate your time.
>
> "'69 Camaro" wrote:
>
> > Hi, Jenny.
> >
> > > The commented out code is the code that works. Why would
> > > something not requery?
> >
> > It _is_ requerying the list box. What it's _not_ doing is updating the
> > records you'd like to update. Is FileCartonNbr a text data type? If so,
> > then change the following lines:
> >
> > SQLStmt = "UPDATE FileCartonTable SET Carton Status = FASLE " & _
> > "WHERE (FileCartonNbr = " + CStr(stFCarton) + ")"
> >
> > to:
> >
> > SQLStmt = "UPDATE FileCartonTable SET [Carton Status] = FALSE " & _
> > "WHERE (FileCartonNbr = '" & stFCarton & "')"
> >
> > Is stFCarton a string data type? If so, you don't need the CStr( ) method
> > if you delineate the string with single quotes or pairs of double quotes, as
> > shown above. And while the "+" operator will work, it's usually better to
> > concatenate strings with the ampersand to avoid unexpected operations on
> > values.
> >
> > Also, instead of using the RunSQL( ) method, you may want to use the
> > Execute( ) method of the Database object to set options on the action query.
> > For example:
> >
> > db.Execute SQLStmt, dbFailOnError
> >
> > ... where db is a Database object. In this example if the query fails for
> > any reason, then the transaction will be rolled back. To get the best
> > performance in a multiuser environment, this statement should be embedded
> > within an explicit transaction.
> >
> > HTH.
> >
> > Gunny
> >
> > See http://www.QBuilt.com for all your database needs.
> > See http://www.Access.QBuilt.com for Microsoft Access tips.
> >
> > (Please remove ZERO_SPAM from my reply E-mail address, so that a message
> > will be forwarded to me.)
> >
> > - - -
> > When you see correct answers to your question posted in Microsoft's Online
> > Community, please sign in to the Community and mark these posts as "Answers,"
> > so that all may benefit by filtering on "Answered questions" and quickly
> > finding the right answers to similar questions. Remember that the first and
> > best answers are often given to those who have a history of rewarding the
> > contributors who have taken the time to answer questions correctly.
> >
> >
> > "Jenny at APOC" wrote:
> >
> > > I have a form, with a list box that has a query for the rowsource. The User
> > > selects a command button that is supposed to take the selected value away
> > > from the list box by updating a table then requerying the listbox.. I have
> > > this working on a different form so I cannot understand what I did so
> > > differently. The commented out code is the code that works. Why would
> > > something not requery?
> > >
> > > Private Sub CloseaCarton_Click()
> > > Dim stFCarton As String
> > > On Error GoTo Err_CloseaFCarton_Click
> > >
> > > 'capture the carton number from the list
> > > stFCarton = AvailableFCartonList.Value
> > >
> > > 'Now construct insert statment
> > > SQLStmt = "UPDATE FileCartonTable SET Carton Status = FASLE " & _
> > > "WHERE (FileCartonNbr = " + CStr(stFCarton) + ")"
> > >
> > > 'Now execute SQL and insert
> > > DoCmd.RunSQL (SQLStmt)
> > >
> > > 'Now refresh list box
> > > Me.AvailableFCartonList.Requery
> > >
> > > Err_CloseaFCarton_Click:
> > > If IsNull(AvailableFCartonList.Value) Then
> > > MsgBox "You must select an FC to Close", vbOKOnly, "Closing FCs"
> > > End If
> > > '
> > > ' 'capture the box number from the list
> > > ' stBoxNbr = AvailableBoxList.Value
> > > '
> > > ''Now construct insert statment
> > > ' SQLStmt = "UPDATE BoxTable SET BoxStatus = FALSE"
> > > ' SQLStmt = SQLStmt + " WHERE (BoxNbr = " + CStr(stBoxNbr) + ")"
> > > '
> > > '
> > > ' 'Now Execute SQL and insert
> > > ' DoCmd.RunSQL (SQLStmt)
> > > '
> > > ' 'Now refresh list box
> > > ' AvailableBoxList.Requery
> > > '
> > > ' 'If available box list is null
> > > 'Err_Closeabox_Click:
> > > 'If IsNull(AvailableBoxList.Value) Then
> > > ' MsgBox "You must select a box to close", vbOKOnly, "Closing Boxes"
> > > ' End If
> > > '
> > >
> > > End Sub
.