Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-16-2019, 12:49 AM   #1
beti
Newly Registered User
 
Join Date: Sep 2012
Posts: 19
Thanks: 6
Thanked 0 Times in 0 Posts
beti is on a distinguished road
limit number of records in subform base on a field select on MainForm

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

beti is offline   Reply With Quote
Old 11-16-2019, 01:22 AM   #2
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,345
Thanks: 115
Thanked 3,107 Times in 2,825 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: limit number of records in subform base on a field select on MainForm

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?
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 11-16-2019, 10:25 AM   #3
beti
Newly Registered User
 
Join Date: Sep 2012
Posts: 19
Thanks: 6
Thanked 0 Times in 0 Posts
beti is on a distinguished road
Re: limit number of records in subform base on a field select on MainForm

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

beti is offline   Reply With Quote
Old 11-16-2019, 11:08 AM   #4
vba_php
Software Engineer
 
vba_php's Avatar
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 644
Thanks: 79
Thanked 75 Times in 75 Posts
vba_php is on a distinguished road
Re: limit number of records in subform base on a field select on MainForm

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.
Attached Files
File Type: zip sample.zip (56.2 KB, 9 views)
vba_php is offline   Reply With Quote
The Following User Says Thank You to vba_php For This Useful Post:
beti (11-17-2019)
Old 11-17-2019, 01:00 AM   #5
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,345
Thanks: 115
Thanked 3,107 Times in 2,825 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: limit number of records in subform base on a field select on MainForm

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.
Attached Files
File Type: zip sample_v2.zip (33.4 KB, 4 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
beti (11-17-2019)
Old 11-17-2019, 05:57 AM   #6
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,112
Thanks: 20
Thanked 400 Times in 393 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: limit number of records in subform base on a field select on MainForm

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?
Mark_ is offline   Reply With Quote
Old 11-17-2019, 09:47 PM   #7
beti
Newly Registered User
 
Join Date: Sep 2012
Posts: 19
Thanks: 6
Thanked 0 Times in 0 Posts
beti is on a distinguished road
Re: limit number of records in subform base on a field select on MainForm

Quote:
Originally Posted by vba_php View Post
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 is offline   Reply With Quote
Old 11-17-2019, 09:50 PM   #8
beti
Newly Registered User
 
Join Date: Sep 2012
Posts: 19
Thanks: 6
Thanked 0 Times in 0 Posts
beti is on a distinguished road
Re: limit number of records in subform base on a field select on MainForm

Quote:
Originally Posted by isladogs View Post
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 is offline   Reply With Quote
Old 11-17-2019, 09:54 PM   #9
beti
Newly Registered User
 
Join Date: Sep 2012
Posts: 19
Thanks: 6
Thanked 0 Times in 0 Posts
beti is on a distinguished road
Re: limit number of records in subform base on a field select on MainForm

Quote:
Originally Posted by Mark_ View Post
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 is offline   Reply With Quote
Old 11-17-2019, 10:11 PM   #10
beti
Newly Registered User
 
Join Date: Sep 2012
Posts: 19
Thanks: 6
Thanked 0 Times in 0 Posts
beti is on a distinguished road
Re: limit number of records in subform base on a field select on MainForm

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
beti is offline   Reply With Quote
Old 11-17-2019, 10:32 PM   #11
vba_php
Software Engineer
 
vba_php's Avatar
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 644
Thanks: 79
Thanked 75 Times in 75 Posts
vba_php is on a distinguished road
Re: limit number of records in subform base on a field select on MainForm

[QUOTE=beti;1651848]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?
[QUOTE]
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)
vba_php is offline   Reply With Quote
Old 11-17-2019, 11:11 PM   #12
beti
Newly Registered User
 
Join Date: Sep 2012
Posts: 19
Thanks: 6
Thanked 0 Times in 0 Posts
beti is on a distinguished road
Re: limit number of records in subform base on a field select on MainForm

[QUOTE=vba_php;1651851][QUOTE=beti;1651848]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?
Quote:
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?
Attached Files
File Type: zip sample_v3.zip (57.6 KB, 5 views)
beti is offline   Reply With Quote
Old 11-17-2019, 11:41 PM   #13
vba_php
Software Engineer
 
vba_php's Avatar
 
Join Date: Oct 2019
Location: Iowa City, IA, USA
Posts: 644
Thanks: 79
Thanked 75 Times in 75 Posts
vba_php is on a distinguished road
Re: limit number of records in subform base on a field select on MainForm

Quote:
Originally Posted by beti View Post
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.
vba_php is offline   Reply With Quote
Old 11-18-2019, 04:35 PM   #14
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,112
Thanks: 20
Thanked 400 Times in 393 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: limit number of records in subform base on a field select on MainForm

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.
Mark_ is offline   Reply With Quote
Old 11-20-2019, 04:57 PM   #15
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,316
Thanks: 15
Thanked 1,601 Times in 1,520 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: limit number of records in subform base on a field select on MainForm

Quote:
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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Set limit on number of records that can have a particular field value romeluc Tables 22 08-19-2014 01:20 AM
Using subform records to select mainform record sal Forms 1 09-10-2010 12:17 AM
Limit Records in SubForm when opening MainForm DubaiDave Forms 2 09-15-2009 02:08 AM
Limit number of records in subform fergler Forms 7 06-15-2007 05:30 AM
count(*) in subform footer returning #error in mainform when no records in subform Happy YN Modules & VBA 4 05-06-2003 03:43 AM




All times are GMT -8. The time now is 01:17 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World