RE: Passing over Requery

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

'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
.

Quantcast