limit number of records in subform base on a field select on MainForm (1 Viewer)

beti

Registered User.
Local time
Today, 06:46
Joined
Sep 20, 2012
Messages
19
I have Main Form name MainForm and I have Sub Form named SubForm
In the MainForm I have a field name Quantity and I would like when I enter Quantiy such as 3 then I just let people to enter only 3 rows in
the SubForm, if I enter 1 then I just let people to enter only 1 rows in SubForm.
How do you write the code for this?
Thank you very much
 

isladogs

MVP / VIP
Local time
Today, 13:46
Joined
Jan 14, 2017
Messages
18,186
I'm sure it can be done and in fact I have a more complex version for validating licenses to ensure that the number of user licenses in use doesn't exceed the number purchased.
For obvious reasons, I'm not going to supply that code!

However is there any point doing this when increasing the value in the Quantity textbox will allow more records to be entered anyway.
And what should happen if you set Quantity = 3, enter 3 records & then reduce Quantity to 2?
 

beti

Registered User.
Local time
Today, 06:46
Joined
Sep 20, 2012
Messages
19
Yes if I reduce Qty to 2 then it should let me enter only 2 records or increase Qty then increase number of record. Can you guide me how to do this? Thanks a lot
 

vba_php

Forum Troll
Local time
Today, 08:46
Joined
Oct 6, 2019
Messages
2,884
beti,

I don't think uve got the correct setup, otherwise something like what you want wouldn't be needed. however, look at this sample to do what you want. I have no idea why i had to include the "+1" in the code, as the code "recordcount" should work fine.

open the "cust" form.
 

Attachments

  • sample.zip
    56.2 KB · Views: 71

isladogs

MVP / VIP
Local time
Today, 13:46
Joined
Jan 14, 2017
Messages
18,186
That's a nice simple solution for the OP
You need the +1 so you can enter data in the final (max) record allowed

I've modified the code supplied to add Option Explicit and error checking.
This includes ensuring the user enters a valid number in the quantity box.

However my original comments to the OP still apply.
There's little point in doing this if the quantity allowed can just be increased.
You could of course lock the quantity box if it contains a value but that would create another issue if you made a mistake.
 

Attachments

  • sample_v2.zip
    33.4 KB · Views: 85

Mark_

Longboard on the internet
Local time
Today, 06:46
Joined
Sep 12, 2017
Messages
2,111
beti,

If the user enters a quantity of 3, then adds 3 records, what do you do IF they change quantity to 2? Do you delete the last record? Do you prevent them from changing quantity?
 

beti

Registered User.
Local time
Today, 06:46
Joined
Sep 20, 2012
Messages
19
beti,

I don't think uve got the correct setup, otherwise something like what you want wouldn't be needed. however, look at this sample to do what you want. I have no idea why i had to include the "+1" in the code, as the code "recordcount" should work fine.

open the "cust" form.

Yeah, you're right if they reduce qty then they have to manually delete record. You sample is what I need. Thank you very much.
 

beti

Registered User.
Local time
Today, 06:46
Joined
Sep 20, 2012
Messages
19
That's a nice simple solution for the OP
You need the +1 so you can enter data in the final (max) record allowed

I've modified the code supplied to add Option Explicit and error checking.
This includes ensuring the user enters a valid number in the quantity box.

However my original comments to the OP still apply.
There's little point in doing this if the quantity allowed can just be increased.
You could of course lock the quantity box if it contains a value but that would create another issue if you made a mistake.

Thank you for modified, it really helped.
 

beti

Registered User.
Local time
Today, 06:46
Joined
Sep 20, 2012
Messages
19
beti,

If the user enters a quantity of 3, then adds 3 records, what do you do IF they change quantity to 2? Do you delete the last record? Do you prevent them from changing quantity?

I think if they reduce qty, they have to manually delete what they don't need. Thank for thinking about the different cases ...
 

beti

Registered User.
Local time
Today, 06:46
Joined
Sep 20, 2012
Messages
19
isladogs/vba_php,

Continue with the 'sample db', if I have another subform name subform1, this one is also use id to link to Mainform and subform purch. In this subform1, I have combo box name 'Purchase' and I would like to select all the data from 'Purch' in Subform for Purchase combo box in subform1. I did use select statement on Row Source as Select Purch from Purch table BUT how do I pass the id/Custid from Subform/Mainform to Subform1?
Thanks a lot
 

vba_php

Forum Troll
Local time
Today, 08:46
Joined
Oct 6, 2019
Messages
2,884
In this subform1, I have combo box name 'Purchase' and I would like to select all the data from 'Purch' in Subform for Purchase combo box in subform1. I did use select statement on Row Source as Select Purch from Purch table BUT how do I pass the id/Custid from Subform/Mainform to Subform1?
i'm not sure if anyone here will follow what you're saying beti! got another sample of what you are *now* trying to do? it might be easier looking at your attempt instead of reading your words. (no offense meant by that)
 

beti

Registered User.
Local time
Today, 06:46
Joined
Sep 20, 2012
Messages
19
In this subform1, I have combo box name 'Purchase' and I would like to select all the data from 'Purch' in Subform for Purchase combo box in subform1. I did use select statement on Row Source as Select Purch from Purch table BUT how do I pass the id/Custid from Subform/Mainform to Subform1?
i'm not sure if anyone here will follow what you're saying beti! got another sample of what you are *now* trying to do? it might be easier looking at your attempt instead of reading your words. (no offense meant by that)
Sorry, I mean "Continue with the 'sample db', if I have another subform name subform1, this one is also use id to link to Mainform and subform purch" .I mean your Sample DB you posted here :=)
But anyway, I did modify your 'sample db' to add another subform ... on this subform, under Purchase combo field, I would like to select data from 'purch' field of subform. How do to do this? I selected and got all data ... how do I pass and match the ID/CustID?
 

Attachments

  • sample_v3.zip
    57.6 KB · Views: 57

vba_php

Forum Troll
Local time
Today, 08:46
Joined
Oct 6, 2019
Messages
2,884
on this subform, under Purchase combo field, I would like to select data from 'purch' field of subform. How do to do this? I selected and got all data ... how do I pass and match the ID/CustID?
beti,


what you're doing is really unusual. when you look up data to populate a combo box, u should be looking it up from a table or query, not another form control or subform control. I'm not even sure you can do that anyway. without writing odd pieces of code. isn't it possible for you to upload the DB you're actually working with? if you have sensitive data in, just replace it with fake data by running an update query or two.
 

Mark_

Longboard on the internet
Local time
Today, 06:46
Joined
Sep 12, 2017
Messages
2,111
How you normally set up the data would be Customer - Purchase Order - Line item.

Customer is the parent. Purchase Orders are on a subform for the parent. Line items are on a subform under the Purchase Orders.

You normally don't look at the hundreds of line items for purchases directly on the customer form. You MAY show only those line items that have not shipped or otherwise have an issue, but most users wouldn't want to try scrolling through all of that.

I'd suggest reworking how you relate your data. Otherwise you'd need to change the source for your Purchase to have both CustomerID and the Purchase number, then limit based on the parent's CustomerID.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:46
Joined
Feb 19, 2002
Messages
42,970
I think if they reduce qty, they have to manually delete what they don't need. Thank for thinking about the different cases ...
YOU are responsible for data integrity. YOUR code should PREVENT the user from changing the value from 3 to 2 if there are more than 2 records already in place. You can't just passively say they need to do the right thing. You are imposing this restriction based on some business rule so you need to ensure that at no point is the rule EVER violated.
 

Users who are viewing this thread

Top Bottom