How to control Number of Entries

[isladogs]

Have a look at the short video (attached) showing how my example file works ... and it DOES work!

I haven't looked at your file and may not have time to do so today

[deepakg27]

Hi Ridders52,

Thanks for your reply and Sorry for saying that your access file was not working. Re-downloaded and this time it was working perfect. Thanks.

Me.AllowAdditions = DCount("*", "tblSaleDetails", "SaleID = " & Me.SaleID) >5

Now could you please explain as per your sample SaleID is used in both tblSale & tblSaleDetails, which SaleID is being referred in the code above, please specify so that I can make changes accordingly in my database and try. (In my database in tblSale field is named SaleID_PK & in tblSaleDetail field is named SaleID_FK)

Thanks and Regards
Deepak Gupta

[isladogs]

Hi Ridders52,

Thanks for your reply and Sorry for saying that your access file was not working. Re-downloaded and this time it was working perfect. Thanks.

Me.AllowAdditions = DCount("*", "tblSaleDetails", "SaleID = " & Me.SaleID) >5

Now could you please explain as per your sample SaleID is used in both tblSale & tblSaleDetails, which SaleID is being referred in the code above, please specify so that I can make changes accordingly in my database and try. (In my database in tblSale field is named SaleID_PK & in tblSaleDetail field is named SaleID_FK)

Thanks and Regards
Deepak Gupta

Glad its now working for you ....

The code is in the subform which uses tblSaleDetail as its record source. So you need the field SaleID_FK

However, you've copied the code incorrectly.
You need < in that line & NOT >
It works like this
If DCount("*", "tblSaleDetail","SaleID_FK = " & Me.SaleID_FK) < 5, that condition is true so Me.AllowAdditions=True & records can be added
If DCount("*", "tblSaleDetail", "SaleID_FK = " & Me.SaleID_FK) = 5, the condition is false so Me.AllowAdditions=False so no records can be added
The count can't exceed 5 unless records added in the table, BUT if you did that the form would remain LOCKED

If your record limit is 5, it should be

Me.AllowAdditions = DCount("*", "tblSaleDetail", "SaleID_FK = " & Me.SaleID_FK) < 5

As you originally said the limit was 10, it needs to be:

Me.AllowAdditions = DCount("*", "tblSaleDetail", "SaleID_FK = " & Me.SaleID_FK) < 10

I've successfully tested this in your your db - updated version attached.
NOTE:
I've changed the relationship between the 2 tables to an outer join
This is needed so you can enter the first record in the subform!

[deepakg27]

Dear Ridders52,

Thanks, it is working perfectly. I was making some mistake GOD knows, what did everything from start and it worked.

Regards
Deepak Gupta

[isladogs]

You're welcome
BTW I've also got a use for this feature in one of my own apps
In my case to restrict the number of users to the licenses purchased.

So thanks for asking the question ....as it made me work out the answer

[deepakg27]

Dear Ridders 52,

I just tried your uploaded version and also changed the join to outer in my data base, still it is giving problem in creation of the new record.

Please kindly recheck and help.
Thanks and Regards
Deepak Gupta

[Bob Fitz]

Try attached file while waiting for ridders52

[isladogs]

Hi

I can add records to the subform in the version I uploaded including when there are no existing records.
However, error 3075 occurs adding records to the main form as there are no corresponding entries in tblSaleDetail

One way to fix that would be to change the subform Form_Current code to:

Private Sub Form_Current()

On Error GoTo Err_Handler

    If Me.Dirty Then Me.Dirty = False
    Me.AllowAdditions = DCount("*", "tblSaleDetail", "SaleID_FK = " & Me.SaleID_FK) < 10
    
Exit_Handler:
    Exit Sub
    
Err_Handler:
    If Err = 3075 Then Resume Next
    MsgBox "Error " & Err.Number & " in Form_Current procedure: " & Err.Description
End Sub

There are ways of getting the same result

I also tried Bob's slight variation on the same idea.
No error adding records to the main form.
However, there were 2 issues:
1. If there are no records in the subform, you can't add any (it has an INNER join between the tables)
2. Bob set the limit to 9 rather than 10 for some reason

[deepakg27]

Dear Bob,

tried your example also still no result.

The code works differently in two different situations.
First scenario:
Earlier SubForm has less than 9 (9 being the limit set in the code) records while making entries and when we move to making entry for new records, we can make entries into subform (as it is not greyed out), but the cursor in the subform is set on the last field of IGST, which is very irritating as the cursor has to be moved through mouse to Product field.

Second Scenario:
Subform has 9 entries (reached it's maximum limit) in the earlier bill & when we move to making new bill entry the subform is greyed out no entries are possible.

Awaiting for the reply.
Thanks and Regards
Deepak Gupta

[deepakg27]

Dear ridders52,

I am able to add records but the data base is not working as it should.

I am not getting any error message 3075, in your code also i am facing same problem as you mentioned, that we are unable to add any records in the subform if in last bill the limit was reached. As i pointed out in my earlier reply to BOB also.

I feel the subform addition property is getting locked whenever the limit is reached and hence in new bill also it remains locked, so if some how we can give a code in the main form that when ever new bill starts subform addition property should be in allow status, may be it can do the trick. I am just a novice and the thought just struck to my mind. hence shared it.

Awaiting Reply.
Thanks and Regards
Deepak Gupta

[isladogs]

Sorry but I can't replicate what you are saying or perhaps I'm not understanding you correctly

With the change I described in my last post, I can add new records to the main form without error.
I can also add records to the subform up to the limit of 10 records.
It doesn't make any difference how many subform records the previous main record has.
They are independent!

One thing:
Open the subform in design view. Go to the Property sheet & click the Data tab
Check the Allow Additions property = Yes & Data Entry = No

P.S. I very much doubt that the Access version used is an issue

[deepakg27]

Hi Ridders52,

First of all thanks for the error handling code, it takes care of the problem when new record is being added. Thanks.

I am so sorry that I was unable to explain you my point clearly. Let me try again. I am using your version of the file, as uploaded by you.

Now as I add records in the subform for BIll#1010 and complete 10 records


As you can notice in Subform I have added 10 records and further records will not be possible due to code as required. Till here database works fine.

Now as I move to the next bill entry Bill#1011 and start adding data in Main form and finally reach Subform (I am unable to add any records in subform, as it is greyed out, pic attached)


You can notice in the Record selector (Main form is 11 of 11 & Subform is greyed out I cant add any records). This happens only when in last bill limit (which is 10) has been reached. If i close the main form and reopen it I am able to add records for bill#1011.

Looking forward to your help. Hope I have been able to explain myself.
Thanks and Regards
Deepak Gupta

[isladogs]

You explained it perfectly Deepak

You'll be pleased to know I can now replicate it & AFAIK have now fixed the issue

I believe the issue was due to ProductID_FK being a required field in the subform.
This meant that it couldn't add a record to tblSaleDetail as that field was blank ... so the subform had no records and was locked

I've got it to work by changing this field so its no longer a required field
I added this code to the main form:

Private Sub BuyerName_AfterUpdate()

'CR 15/01/2018 - this adds a new blank record to the subform if none exist
    If Me.Dirty Then Me.Dirty = False

    If DCount("*", "tblSaleDetail", "SaleID_FK = " & Me.SaleID_PK) = 0 Then
        CurrentDb.Execute "INSERT INTO tblSaleDetail ( SaleID_FK )" & _
            " SELECT DISTINCT " & [Forms]![frmSale].[SaleID_PK] & " AS Expr1" & _
            " FROM tblSaleDetail;"
        Me.frmSaleDetailSubform.Requery
   End If

End Sub

Updated version attached
I've made 2 hidden fields visible in your main form (in RED) so I could check what was going on
The screenshots show the sequence of events

NewRecord1 - clicked New on main form navigation button but no record committed yet
NewRecord2 - populated required field BillNumber - record added to tblSale but not yet to tblSaleDetail
NewRecord3 - populated required field BuyerID_FK (using Buyer Name combo) - blank record added to tblSaleDetail & shown in subform

The process could undoubtedly be improved further but hopefully it now works as intended

I suggest you modify the bill number control so it automatically shows the next available bill number
e.g. using DMax(BillNumber) +1

[deepakg27]

hi ridders52,

Many many thanks for your help. The code is working perfectly now.

Thanks and Regards
Deepak Gupta

[isladogs]

Hooray!