Make sure record is not added when the MoneyOwed value in another table is not 0

[whoisit]

i am currently working on a video rental database
How do i make sure that no member who owes money makes a loan
Below are the fields in my 2 tables. A loan is made through the form "frmAddNewLoan". The two table are related via the field MemberId. The field MoneyOwed is a currency field, not a true/false field. Default value is 0

Any suggestions would be appreciated
THANK YOU

 

[trucktime]

Disable

I assume you have a subform for the loans on your main form and that the
field MoneyOwed is on your main form.

You could try something like this.
Put the following code in the AfterUpdate Event of the field MoneyOwed

If MoneyOwed > 0 Then
Forms!frmAddNewLoan!yoursubform.Enabled = False
Else
Forms!frmAddNewLoan!yoursubform.Enabled = True
End If

So, if the value in the field MoneyOwed is greater than 0, the user cannot ad a record in the subform.

 

[whoisit]

Trucktime
the code you gave me does not work, when i type in the name of my subform "frmAddNewLoanSub", it says that it cannot find the field "frmAddNewLoanSub".
Any suggestions
THANK YOU

 

[whoisit]

Any ideas
Please Help
THANK YOU

 

[trucktime]

Forms

I'm not sure what you're trying to do.
Why would you type in the name of a form?
And please check if you have replaced the part that was printed red in my post has been replaced with the correct name of your subform.

 

[whoisit]

Thanks trucktime
I did replace code with the name of my subform
What i forgot to mention was that the field MoneyOwed is on the subform and not main form. When i enter the memberId on the main form, the subform then displays the money owed. May be that why the code did not work.
If the money owed is greater than 0 then, when i click ok, form prompts me saying do i want to save this record, even if i click yes, i do not want the record to be saved and a message appearing saying cannot save record
THANK YOU

 

[trucktime]

Save record

You can try this.
Put the following code in the Before Update Event of the Subform:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_BeforeUpdate_Click
If Me.MoneyOwed > 0 Then
MsgBox "Cannot save record at this time", vbOKOnly, ""
DoCmd.CancelEvent
Else
End If

Exit_BeforeUpdate_Click:
Exit Sub

Err_BeforeUpdate_Click:
Resume Exit_BeforeUpdate_Click
End Sub

This will prevent saveing a record when MoneyOwed has a value greater than 0. You can easily change the message that will pop up.

 

[whoisit]

Trucktime, sorry but the code is not working
Thanks for the help
below are screenshots of my form and subform and if you could give it another try it would be really appreciated

What happens is you type in the memberId and the moneyOwed automatically appears which is done by the code "!Forms!formname!fieldname" in a query

Any suggestions please
THANK YOU

 

[trucktime]

Record

I see your save button is on the main form, I thought it would be on the subform. Try putting the code I posted earlier in the BeforeUpdate Event of the main form and refer to the MoneyOwed field in the code accordingly.

 

[whoisit]

sorry trucktime but i have another problem
As shown in my attachment everytime i open the form this message is shown

Please Help, really appreciate all the previous help
THANK YOU

 

[trucktime]

Form

The form can find the Member ID. If the form is based on a query make sure the field actually shows up in the query. and check the reference to that field.

 

[whoisit]

trucktime
Thanks a lot
after all these attempt i have finally got it to work
Thanks for the all the help and the number of replies you made
THANK YOU

 

[trucktime]

Great

You're very welcome, glad you got it to work.

I'm sure we'll meet again.