Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL syntax (A2000)

    A couple of questions here, I'm a little stumped on.

    1.The following SQL needs an extra criteria adding:
    strSQL = "INSERT INTO tblEstimateDetails (EstimateNo,Supp, Code, Item)
    and also:
    [Forms]![frmEstimateDetails]![Supp]
    Do I precede this with a "And" or a "&"

    strSQL = "INSERT INTO tblEstimateDetails (EstimateNo, Code, Item) SELECT [Forms]![frmEstimateDetails]![EstimateNo], MainCode, AssociatedItem FROM tblAssociatedParts WHERE MainCode=[forms]![frmEstimateDetails]![sbfEstimateDetails]![cbocode];"

    2.I have the following code on an update button:

    Dim RST As Integer
    RST = DCount("*", "tblEstimateDetails", "ESTIMATENO=" & Me![EstimateNo] & " and SUPP=" & Me.Supp & "")
    If RST > 0 Then
    Dim strSQL As String
    strSQL = "INSERT INTO tblEstimateDetails (EstimateNo, Code, Item) SELECT [Forms]![frmEstimateDetails]![EstimateNo], MainCode, AssociatedItem FROM tblAssociatedParts WHERE MainCode=[forms]![frmEstimateDetails]![sbfEstimateDetails]![cbocode];"
    'DoCmd.SetWarnings False
    DoCmd.RunSQL (strSQL)
    'DoCmd.SetWarnings True
    End If

    This checks the record source of the subform.
    What I need it to do is to loop thru the items and update all.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL syntax (A2000)

    1. You write that you need an extra criterion, but you made Supp bold, which seems to indicate that you want to include an extra field in the INSERT INTO. If the latter is the case, the statement would be

    strSQL = "INSERT INTO tblEstimateDetails (EstimateNo, Supp, Code, Item) " & _
    "SELECT [Forms]![frmEstimateDetails]![EstimateNo], [Forms]![frmEstimateDetails]![Supp], " & _
    "MainCode, AssociatedItem FROM tblAssociatedParts " & _
    "WHERE MainCode=[forms]![frmEstimateDetails]![sbfEstimateDetails]![cbocode];"

    If I misunderstood this question, please post back.

    2. Do you want to loop through all records of the subform? If so, is the combo box cboCode bound to a field, or is it unbound? If it is unbound, be aware that it will have the same value for all records in the subform.
    You could use DAO to loop through all records of a clone of the subform's recordset.

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL syntax (A2000)

    Thanks Hans
    The SQL is just fine.

    The cboCode is bound to field [Code] for the loop.
    I was looking up some old code which involved .EOF & .BOF but don't really understand it's concept.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL syntax (A2000)

    Here is a possible way to do it. It's just demo code, you'll have to tweak it for your situation. I have assumed that Code is a text field. If it is numeric, remove the single quotes from the line<pre>"WHERE MainCode='" & rst!Code & "'"</pre>

    so that it reads<pre>"WHERE MainCode= "& rst!Code</pre>

    Sub Test()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String

    On Error GoTo ErrHandler

    Set dbs = CurrentDb

    ' *** USE ONLY ONE OF THE FOLLOWING TWO INSTRUCTIONS ***
    ' If the code is called from the main form
    Set rst = sbfEstimateDetails.Form.RecordsetClone
    ' If the code is called from the subform
    Set rst = Me.RecordsetClone

    ' Loop through the records of the subform
    Do Until rst.EOF
    ' Set up SQL statement
    strSQL = "INSERT INTO tblEstimateDetails (EstimateNo, Code, Item) " & _
    "SELECT " & rst!EstimateNo & ", MainCode, AssociatedItem FROM tblAssociatedParts " & _
    "WHERE MainCode='" & rst!Code & "'"
    ' Execute append query
    dbs.Execute strSQL, dbFailOnError
    ' Move to next record
    rst.MoveNext
    Loop

    ExitHandler:

    ' Clean up
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:

    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL syntax (A2000)

    Hans
    I get "Variable not defined" here:

    Set rst = sbfEstimateDetails.Form.RecordsetClone

    I am calling from the main form.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL syntax (A2000)

    Dave, check the following:
    1. This is DAO code, so you need a reference to the Microsoft DAO 3.6 Object Library.
    2. I had assumed that sbfEstimateDetails is a subform of the form that calls the code. If that is correct, you should use the exact name of the subform control - this is not necessarily the same as the name of the subform in the database window. If it is not a subform of the form calling the code, use

    Set rst = frmEstimateDetails!sbfEstimateDetails.Form.Records etClone

    where frmEstimateDetails is the name of the main form containing the subform.

    The instruction didn't raise an error when I tested it on the database I attached last week to another thread on the Estimates database.

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL syntax (A2000)

    Hans
    I've got it.
    When I checked the properties of the sub within the main, the subform is called "sbsEstimateDetails1

    I'll just add the [supp] now and I should be fine.

    Have a good weekend and thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •