Check for match in table 1 and change status in table 2 if there is a match

[rlsublime]

I am trying to add an on click event to a save event and it should have the following type of statement in it, which compares the loan number entered in the current form to another form and then changes the status in the 2^nd form from Outstanding to Closed if the loan number matches the loan number in the other form. This is the concept of the if statement, but I am not sure of how to combine it with a save event and not sure how to implement within vba. Any help would be appreciated. Thanks



=if(Repurchase![Loan Number]=Investor_Request!Loan_Number And Investor_Request!STATUS="Outstanding",Investor_Req uest!STATUS="Closed")

[John_G]

Hi -

You have the right idea - the statement should be something like this:

if Me![Loan Number] = Forms!Investor_Request!Loan_Number And Forms!Investor_Request!STATUS="Outstanding" then Forms!Investor_Request!STATUS="Closed")

(Me! refers to the current form where this code is running)

This line would go in the same event code as your "Save" (for example, in the On Click event of a Command button to save).

I am assuming that the Investor_Request form is also open - this won't work if it isn't.

HTH

John

[rlsublime]

Thanks. the other form is closed and so the code is not changing the status from outstanding to closed. Could I possibly change Forms! to Tables! Ultimately I would want the table record to be updated with the CLOSED status if the conditions are met. Is there a way to edit this so that i will not have to have the other form open and make the change to the Investor request table?

thanks

[John_G]

Hi -

This is easily done with SQL:

Dim SQL as String

SQL = "Update Investor_Request set Status = 'Closed' where Loan_Number = " & me![Loan Number] & _
" AND status = 'Outstanding'"

currentDB.execute SQL, dbfailonerror

Fix the table and field names if I don't have them right.

If there is no record in Investor_Request that meets the conditions, that is not a problem - the statement just won't update anything.

John

[rlsublime]

I tried using the code but was getting an error. the following was highlighted in yellow "currentDB.execute SQL, dbfailonerror"

Not sure what i was doing wrong. Thanks for the help .

[John_G]

What was the error message you got? Was it a run time or a compile error?

John

[rlsublime]

It was the following error

Run Time error ‘3464’

Data type mismatch in criteria expression

Thanks for the help

[John_G]

OK - two questions -

Is the Loan number in the table and form numeric or text? (Loan "Numbers" are often not really numeric) Can you give an example?
Is the Status in the Investor_Request table actually text, e.g. "Outstanding", or is it a numeric code?

John

[rlsublime]

Thanks a lot. When I changed the table Loan fields from Text to numbers, it generated perfectly. Thanks for your help

[rlsublime]

I have one more question; I was trying to add the button to a save event so I added the following at the end of the
CurrentDb.Execute SQL, dbFailOnError

But it was generating an error 'run time Error 3075' Syntax Error (missing operator) in query expression Loan_Number =AND status 'Outstanding'. The code generates correctly without the following save event added to it. Thanks for the help.

On Error GoTo Err_Save_Record_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_Save_Record_Click:
    Exit Sub
Err_Save_Record_Click:
    MsgBox Err.Description
    Resume Exit_Save_Record_Click

[John_G]

That would seem to indicate that after the record is saved, the form is moving to a new, blank record. Notice that in the error message there is no loan number after Loan_Number = .

Where are you using this code? I don't quite know what you mean by "... add the button to a save event ..." - there is no such thing as a "save event". Do you mean you are doing the save in the on-click event of a command button?

John

[rlsublime]

Yes that’s right, the save is within the on click of a command button. This is the complete code that I am trying to generate. Thanks for your help

Private Sub Save_Record_Click()
 
  Dim SQL As String
 
SQL = "Update Investor_Request set Status = 'Closed' where Loan_Number = " & Me![Loan Number] & _
  " AND status = 'Outstanding'"
 
CurrentDb.Execute SQL, dbFailOnError
 
On Error GoTo Err_Save_Record_Click
 
 
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 
Exit_Save_Record_Click:
    Exit Sub
 
Err_Save_Record_Click:
    MsgBox Err.Description
    Resume Exit_Save_Record_Click
   
End Sub

[rlsublime]

I should also mention that I used this link to set up a required field.

http://www.databasedev.co.uk/data_entry_macro.html

When there is no data entered in the field that is set up as required is the only time that the error generates. When there is data in the required field then the code generates correctly. Thanks

[John_G]

All you need is to check for a Null value in the field before setting up the SQL. Put:

If isnull(me![Loan Number]) then Exit Sub

right after the Dim SQL line. If there is no loan number entered, it will not try to update the table, neither will it try to save the current record - it just exits.

John

[rlsublime]

Thanks, that worked perfectly. is there a better way to add a field as a required field than this link
http://www.databasedev.co.uk/data_entry_macro.html

It does not seem to be working correctly. The message prompting user to enter data in the required field does not have to come on the Onclick or beforeupdate events. i dont mind if it even prompts the user on the save event. Is there something the i could add to the Save command vba code to require a user to enter data in Loan Number, Principal Bal, Last name fields?

thanks