Rejecting a Claim based on a certain criteria in the subform (1 Viewer)

[vid]

Hi!

I have a mainform with a subform. The subform has a field called Reason Code. Suppose there are x number of records in the subform for that particular record. What i need to do is that if more than 30% of x have an entry of TBA in the Reason Code field, the claim should be rejected and the user should be notified with a message box.

I know the logic that i want to implement but i'm very knew to access so I'm not good with the VBA syntax at all.. Please help!!

 

[vbaInet]

Something along these lines:

Dim countAll as long, count30 as long

countAll = Controls("NameOfSubform").Form.RecordsetClone.Count
count30 = DCount("*", "SQL, table, or query", "WHERE [Reason Code] = 'TBA'")

If ((count30/countAll) * 100) >= 30 Then
          Msgbox "Max number of claims reached", vbInformation, "Max claims"
Else
         .... Do Something
End If

You could create a query that has the WHERE clause in it already and just simply use that in the DCOUNT() function, ignoring the WHERE part. For any other circumstance you would need the WHERE clause.

NB: You don't need the word WHERE in the function. I put it there so you know which parameter it goes into. So the real syntax is:
count30 = DCount("*", "SQL, table, or query", "[Reason Code] = 'TBA'")

 

[vid]

Hi!

I'm a real beginner at this stuff. Can you please help me a little further.. I don't understand what i should replace

"*", "SQL, table, or query"

with.. and I tried using just the countAll part of the code to see if it works and used a message box to display the number or records that it counts but a window pops-up which says "object doesnt support this property or method!"

Please help...

 

[vbaInet]

Oops... change count to recordcount.

You replace this part:

"SQL, table, or query"

 

[vid]

Thanks a ton!

 

[vbaInet]

You're welome!

 

[vid]

Hey..
The code is working, as in it shows the error and all that but the data still gets saved. How do i make sure that it is rejected?
Please help

 

[vbaInet]

What error? Also, show me this part of your code:

DCount("*", "SQL, table, or query", "WHERE [Reason Code] = 'TBA'")

 

[vid]

Basically I meant that its working fine.. Displays the message box when entries with TBA exceeds 30% but the problem is that it still shows allows the data to be saved. I need to prevent the data from getting saved in that situation.

I have used the following code:

Private Sub SAVE_RECORD_Click()
On Error GoTo Err_SAVE_RECORD_Click
Dim countAll As Long, count30 As Long, siteID As Long
siteID = Me.Warranty_ID.Value
countAll = Controls("Warranty Claim Detail Subform").Form.RecordsetClone.RecordCount
count30 = DCount("*", "[Warranty Claim Details Query]", "[Warranty ID] = " & siteID)
If ((count30 / countAll) * 100) >= 30 Then
MsgBox "Cannot save this Claim! Too many TBA entries", vbInformation, "ERROR"
Cancel = True

Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
End If
Exit_SAVE_RECORD_Click:
Exit Sub

 

[vbaInet]

If your form is bound then it will save when you move to another record. You would need to handle that on the form's Before Update event

 

[vid]

And how do i do that? thanks a ton for all the help...

 

[vbaInet]

You're welcome.

Same (well, similar) code but in a different event, the Before Update event of the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim countAll As Long, count30 As Long, siteID As Long
    
    siteID = Me.Warranty_ID.value
    countAll = Controls("Warranty Claim Detail Subform").Form.RecordsetClone.RecordCount
    count30 = DCount("*", "[Warranty Claim Details Query]", "[Warranty ID] = " & siteID)
    
    If ((count30 / countAll) * 100) >= 30 Then
        MsgBox "Cannot save this Claim! Too many TBA entries", vbInformation, "ERROR"
        Cancel = True
    End If
End Sub

 

[vid]

I tried using it in before update already.
The problem is that it doesnt let me create a new record. In the sense that if i wanna add more records in the subform for an already existing value of warranty ID its fine. But it doesnt let me add records in a new value of warranty ID... I think its because countAll = 0 at that point of time and i think that's creating the problem. But I'm not sure.. Any suggestions?

 

[vbaInet]

I don't get you. If the event is put in the before update event of your main form, it is independent of your subform. Zip, upload and post your db and I'll have a look. You can do this by clicking on GO ADVANCED. Tell me what form I should be looking at and how to replicate the problem.

 

[vid]

alright.. I have attached my database.. Just open the Edit Claims form and try to create a new record in it. Start by entering data in the main form first, then move to the subform. You'll see the problem.
However you will not face his problem when you try to enter data to the already exisiting record...

 

[vbaInet]

I can't seem to open your database as it's showing an unexpected error. What version of Access are you using? Did you use any third party software to "empty" your db?

When you say it's not allowing you add new records, is it coming up with your coded message box?

 

[vid]

That's strange.. I'm attaching it again. Please try once more if you can open it!

And nope haven't used any software to empty it, just deleted them from the table.

yeah, it shows an error : Runtime Error 6 : overflow...

 

[vid]

No.. It isn't coming up with my coded message box. Its showing the runtime error: Overload...

 

[vid]

i think that runtime error was because countAll = 0. So i just put another if condition countAll>=1. But now the code doesn't seem to be working at all! it doesn't even show my coded message box...

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim countAll As Long, count30 As Long, siteID As Long
siteID = Me.Warranty_ID.Value
countAll = Controls("Warranty Claim Detail Subform").Form.RecordsetClone.RecordCount
count30 = DCount("*", "[Warranty Claim Details Query]", "[Warranty ID] = " & siteID)
If countAll >= 1 Then
If ((count30 / countAll) * 100) >= 30 Then
MsgBox "Cannot save this Claim! Too many TBA entries", vbInformation, "ERROR"
Me.Undo
Cancel = True
End If
End If
End Sub

 

[vid]

however it doesnt show the runtime error anymore