0

I am trying to update a field in the Master table by looping through each record and updating it with the corresponding field in my test table. I am to the point where I have pulled the information from the record set to start the loop and I get the following error:

3601 Too few parameters. Expected 1.

Public Function fn_marked() As Integer
    Dim db As Database
    Dim rst As Recordset
    Dim rst_MASTER As Recordset
    Dim strSQL As String
    Dim strSQL_MASTER As String

On Error GoTo Err_Error

    DoCmd.SetWarnings False

    strSQL = "UPDATE tbl_ACTIVE INNER JOIN tbl_FILE_SOURCE ON tbl_ACTIVE.Persno = 
    tbl_FILE_SOURCE.PRNR SET tbl_ACTIVE.marked = True WHERE tbl_FILE_SOURCE.Type='Test'"

    DoCmd.RunSQL strSQL, True

    Set db = CurrentDb
    Set rst_MASTER = db.OpenRecordset("tbl_MASTER")

    If rst_MASTER.RecordCount > 0 Then
        rst_MASTER.MoveFirst
        Do Until rst_MASTER.EOF
        If Not rst_MASTER.EOF then
            strSQL = "SELECT tbl_ACTIVE.marked FROM tbl_ACTIVE WHERE tbl_ACTIVE.Number= rst_MASTER!Number"
            Set rst = db.OpenRecordset(strSQL)

            rst_MASTER.Edit
            Select Case rst!marked
                Case True
                    rst_Master![Test] = "No"
                Case False
                    rst_Master![Test] = "Yes"
            End Select
        End If
            rst_MASTER.Update
        rst_MASTER.MoveNext
        Loop
    End If

    fn_marked = 1

Exit_fn_marked:
    DoCmd.SetWarnings True
    Exit Function

Err_Error:
     MsgBox Err.Number & Err.Description
     'failed to finish
     fn_marked = -1
     Resume Exit_fn_marked

End Function

tbl_Active
column1 Number (Key) 
column2 Name (String)
column3 Marked (Boolean)

tbl_MASTER
column1 Number (Key)
column2 Name (String)
column3 Test (String)

END Result of Table Master
column1 1234
column2 John Smith
column3 Yes (value derived from tbl_ACTIVE Boolean field)
designspeaks
  • 89
  • 6
  • 2
    You need to concatenate the value, not make it part of the string. `"... WHERE Field =" & Value`. – Kostas K. Mar 29 '22 at 16:40
  • Also `RecordCount > 0` will throw an error if the recordset is empty. You need to check the `EOF` property: `If Not rst_MASTER.EOF Then` – Kostas K. Mar 29 '22 at 16:47
  • @KostasK. Thank you! That solved my problem! – designspeaks Mar 29 '22 at 16:47
  • I do not know what you want to update exactly, but you could probably join the two tables and do the update fully in SQL with no vba loops involved. See: [SQL Updating from an inner join](https://stackoverflow.com/questions/12882212/sql-updating-from-an-inner-join). – Olivier Jacot-Descombes Mar 29 '22 at 17:07
  • So now it's giving me a record error, stating no record found. @KostasK. I did add the It not rst_MASTER.EOF Then for the record count, but because the second record does not exist on the tbl_Active table, it's throwing me an error. – designspeaks Mar 29 '22 at 17:30

0 Answers0