Help with running an update query on current recordset

[jax1000rr]

Hello,



I know just enough about VBA and queries to get done what I need to, I'm no expert so any help with the below is appreciated.

I need to enter a record into the table, check for a duplicate record before update and if one is located; I need an update query to run on the new record I am entering via the form.

The code checks for the duplicate and displays the warning message fine, my disconnect is with the update query. I can't seem to get it to select the current record set.

VBA and query code as follows:

VBA

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
strMsg = strMsg & "Do you want to add loan to master list?"
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add New Loan") = vbYes Then
   Dim rs As Recordset
   Set rs = CurrentDb.OpenRecordset("MasterList", dbOpenSnapshot)
   rs.FindFirst "LoanNumber = """ & Me![LoanNumber] & """"
   If Not rs.NoMatch Then
      DoCmd.SetWarnings False
      If MsgBox("This loan already exists in the database. Do you want to continue?", vbYesNo, "Duplicate Loan Warning") = vbYes Then
         DoCmd.OpenQuery "Reinstate Query"
      End If
      DoCmd.SetWarnings True
   Else
      DoCmd.RunCommand acCmdUndo
      'For Access 95, use DoMenuItem instead
      'DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
   End If
End If
End Sub

QUERY

UPDATE MasterList SET MasterList.ReinstatedLoanAlert = "[text]", MasterList.LoanReinstated = True
WHERE (((MasterList.LoanNumber)=[Forms]![Add New Loans]![Loan Number]));

[June7]

What do you mean by 'select the current record set'? The query references form for the criteria so it is reading from the record that has focus.

I always give controls name different from the field they are bound to, like tbxLoanNum. Then refer to the control instead of field name.

Instead of using OpenQuery and running an Access query object, could build the sql in the VBA code, like:

CurrentDb.Execute "UPDATE MasterList SET MasterList.ReinstatedLoanAlert = '[text]', MasterList.LoanReinstated = True WHERE MasterList.LoanNumber='" & Me.tbxLoanNum & "'"

If LoanNumber is not text datatype, remove the apostrophe delimiters.

You want field to be updated with the string '[text]' - the word 'text' and brackets?

[jax1000rr]

Thanks much for the info.

I need to add one more criteria to the WHERE in the SQL statement:

DateOpen=[Todays Date]

Can you assist with that?

Thanks again.

[June7]

[Todays Date] is the current real-time date? Try:

... & Me.tbxLoanNum & "' AND DateOpen=#" & Date() & "#"