limit number of records in subform base on a field select on MainForm

[beti]

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]

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]

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]

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.

 

[isladogs]

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.

 

[Mark_]

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]

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]

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]

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]

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]

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]

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?

 

[vba_php]

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_]

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]

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.