Update or Select Statement. "Error Too Few Parameters!"

[Constantine AI]

I am trying to code a SAVE button which will automatically change numerous rows of data within a table. I have created the appropriate query and checked it works when the form is open. However the coding doesn't work. I am not very good using the whole rst! syntax. I have attempted to get the code right and checked online for similar solutions however i have come up blank. The is as follows;

    Dim db As Database
    Dim rst As Recordset
 
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT MaterialID, RangeID, ColourID, CarcussColourID, FasciaID, FasciaMaterialID, FasciaFinishID, SuppNo, PONo FROM ordlin WHERE (((ordlin.OrderNo)=[Forms]![frmSOLineDetails]![OrderNo]) AND ((ordlin.SuppNo)=[forms]![frmSOLineDetails]![SuppNo]) AND ((ordlin.ItemNo)=[forms]![frmSOLineDetails]![ItemNo])) OR (((ordlin.OrderNo)=[Forms]![frmSOLineDetails]![OrderNo]) AND ((ordlin.SuppNo)=[forms]![frmSOLineDetails]![SuppNo]) AND ((ordlin.ItemNoSub)=[forms]![frmSOLineDetails]![ItemNo])) OR (((ordlin.OrderNo)=[Forms]![frmSOLineDetails]![OrderNo]) AND ((ordlin.SuppNo)=[forms]![frmSOLineDetails]![SuppNo]) AND ((ordlin.ItemNoCmp)=[forms]![frmSOLineDetails]![ItemNo]))")
 
    DoCmd.SetWarnings False
 
    With rst
        .Fields!MaterialID = cboMaterialDesc
        .Fields!RangeID = cboRangeDesc
        .Fields!ColourID = cboColourDesc
        .Fields!CarcussColourID = cboCarcussColourDesc
        .Fields!FasciaID = cboFasciaDesc
        .Fields!FasciaMaterialID = cboFasciaMatDesc
        .Fields!FasciaFinishID = cboFasciaFinDesc
        .Fields!SuppNo = SuppNo
        .Fields!PONo = PONo
        .Update
    End With
    rst.Close
    Set rst = Nothing
 
    DoCmd.SetWarnings True

Set rst = db.OpenRecordset("SELECT MaterialID, RangeID, ColourID, CarcussColourID, FasciaID, FasciaMaterialID, FasciaFinishID, SuppNo, PONo FROM ordlin WHERE (((ordlin.OrderNo)=[Forms]![frmSOLineDetails]![OrderNo]) AND ((ordlin.SuppNo)=[forms]![frmSOLineDetails]![SuppNo]) AND ((ordlin.ItemNo)=[forms]![frmSOLineDetails]![ItemNo])) OR (((ordlin.OrderNo)=[Forms]![frmSOLineDetails]![OrderNo]) AND ((ordlin.SuppNo)=[forms]![frmSOLineDetails]![SuppNo]) AND ((ordlin.ItemNoSub)=[forms]![frmSOLineDetails]![ItemNo])) OR (((ordlin.OrderNo)=[Forms]![frmSOLineDetails]![OrderNo]) AND ((ordlin.SuppNo)=[forms]![frmSOLineDetails]![SuppNo]) AND ((ordlin.ItemNoCmp)=[forms]![frmSOLineDetails]![ItemNo]))")

This code has been taken from the made up query which filters all the relevant information i require. Basically i would like the button to update all the fields within this query. The form itself is a single form. So the data changed would need changing to all rows of data within the query or rst.

I receive an error stating "Too few parameters Expected 3" however i have made sure all the details that could possibly be changed are on the form. I have still never managed to have a successful attempt at this sort of code so any advice would be much appreciated! Thanks

[Stewart Ross]

Hi Constantine Al. Although the database engine can interpret form control references when run from the query editor such direct form control references are not seen as valid fields when run from SQL in code for OpenRecordset and similar functions, hence the parameter error message.

To resolve it, the norm is to build the SQL string and refer to each control's current value within the string, rather than to the control itself:

Set rst = db.OpenRecordset("SELECT MaterialID, 
...
WHERE (((ordlin.OrderNo)= " & [Forms]![frmSOLineDetails]![OrderNo] & ") AND ((ordlin.SuppNo)= " & [forms]![frmSOLineDetails]![SuppNo] & ") AND ... 

and so on.

In this case, because there are so many of these references to form controls it would probably be better to assign their values to variables and use these as the literals withiin your code:

Dim lngOrderNo as Long, lngSuppNo as Long ...
lngOrderNo = Forms![frmSOLineDetails]![OrderNo]
lngSuppNo = Forms![frmSOLineDetails]![SuppNo] 
...
WHERE (((ordlin.OrderNo)= " & lngOrderNo & ") AND ((ordlin.SuppNo)= " & lngSuppNo & ") AND ... 

If the values concerned are strings rather than numbers you will need to add single quotes as delimiters before and after the literal value to make all this work, like this:

 "... WHERE [somefield] like '" & somestring & "' AND ..."

-Stewart

[Constantine AI]

Hi thanks for the help. I have changed my code to what you told me to, now the only problem i have now is that i receive a write conflict error. I realise the Form is based on the same source as my Set rst so i know why i receive it. Is there a way i can stop this write conflict from happening? Here is my coding;

    Dim db As Database
    Dim rst As Recordset
    Dim lngOrderNo As Long
    Dim strSuppNo As String
    Dim lngItemNo As Long
 
    DoCmd.SetWarnings False
 
    lngOrderNo = Forms![frmSOLineDetails]![OrderNo]
    strSuppNo = Forms![frmSOLineDetails]![SuppNo]
    lngItemNo = Forms![frmSOLineDetails]![ItemNo]
 
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT MaterialID, RangeID, ColourID, CarcussColourID, FasciaID, FasciaMaterialID, FasciaFinishID, SuppNo, PONo FROM ordlin WHERE (((ordlin.OrderNo)= " & lngOrderNo & ") AND ((ordlin.SuppNo)= '" & strSuppNo & "') AND ((ordlin.ItemNo)= " & lngItemNo & ")) OR ((ordlin.OrderNo)= " & lngOrderNo & ") AND (((ordlin.SuppNo)= '" & strSuppNo & "') AND ((ordlin.ItemNoSub)= " & lngItemNo & ")) OR (((ordlin.OrderNo)= " & lngOrderNo & ") AND ((ordlin.SuppNo)= '" & strSuppNo & "') AND ((ordlin.ItemNoCmp)= " & lngItemNo & "))")
 
    Do
        rst.Edit
        rst.Fields!MaterialID = cboMaterialDesc
        rst.Fields!RangeID = cboRangeDesc
        rst.Fields!ColourID = cboColourDesc
        rst.Fields!CarcussColourID = cboCarcussColourDesc
        rst.Fields!FasciaID = cboFasciaDesc
        rst.Fields!FasciaMaterialID = cboFasciaMatDesc
        rst.Fields!FasciaFinishID = cboFasciaFinDesc
        rst.Fields!SuppNo = SuppNo
        rst.Fields!PONo = PONo
        rst.Update
        rst.MoveNext
    Loop Until rst.EOF
    rst.Close
    Set rst = Nothing
    DoCmd.Save
    Me.Requery
 
    DoCmd.SetWarnings True

[Stewart Ross]

Hi. I can only think that the write conflict arises because of a record lock being applied. Record locks usually reflect underlying editing operations, which perhaps may be outstanding at the time.

You can make sure that any outstanding changes to the current record are dealt with by using the form's Dirty property (which is set True if the underlying record has been changed but not saved):

IF me.dirty then me.dirty = false

This resetting of the Dirty property actually saves any changes to the current record. If you use this before running your recordset you can be sure that there are no unsaved edits causing record locks.

If you still receive write-conflict errors it would suggest that either the recordset is not updatable, or again a lock is applied, and it would be worth setting a breakpoint and stepping through your code line by line to see if it is all records that are affected or just one.

-Stewart

[Constantine AI]

Yeah thankyou very much. It worked a treat!