Limiting Rows By Given Number In Continuous Form (1 Viewer)

vdanelia

Registered User.
Local time
Today, 16:34
Joined
Jan 29, 2011
Messages
215
Hello Friends, I have a Question about limiting rows by given number in continuous form....
Please look at picture:
I want that the continuous form on my sub form to be limited by a product quantity, that users accidentally not enter some extra data......

Thank you In advanced
 

Attachments

  • Limit.png
    Limit.png
    25.4 KB · Views: 962

vbaInet

AWF VIP
Local time
Today, 23:34
Joined
Jan 22, 2010
Messages
26,374
N <= Quantity

OR

Number of rows = Quantity

Which one is it?
 

vdanelia

Registered User.
Local time
Today, 16:34
Joined
Jan 29, 2011
Messages
215
Hello vbaInet, where to use this?

Number of rows = Quantity Is the best choice i think
Because If I enter in Quantity 4 or 5 on the sub-form list maximum number of rows must be 4 or 5
 
Last edited:

vbaInet

AWF VIP
Local time
Today, 23:34
Joined
Jan 22, 2010
Messages
26,374
That was a question for you to answer, not a solution.
 

vbaInet

AWF VIP
Local time
Today, 23:34
Joined
Jan 22, 2010
Messages
26,374
So the question is, if I use your screenshot as an example, do you want it to return 5 records?

OR

Do you want it to return records where N is less than or equal to 5?
 

vdanelia

Registered User.
Local time
Today, 16:34
Joined
Jan 29, 2011
Messages
215
On the picture there is 5 In quantity box, on the sub-form there is 5 rows which i filled, but i want that if I or someone clicks for the new record... it must not be added on the sub-form rows list
 

vbaInet

AWF VIP
Local time
Today, 23:34
Joined
Jan 22, 2010
Messages
26,374
Alright, it makes sense. So on the Current event of your subform do this:
Code:
Me.AllowAdditions = (Me.Recordset.RecordCount < 5)
 

vdanelia

Registered User.
Local time
Today, 16:34
Joined
Jan 29, 2011
Messages
215
Thanks For the reply vbaInet
OK, But It will work generally it will be limited to 5 for all the records, I needed that it to be limited according to the Quantity
 

vbaInet

AWF VIP
Local time
Today, 23:34
Joined
Jan 22, 2010
Messages
26,374
Oops... the following code should go in the Current event of the main form:
Code:
With Me.[COLOR=Red]SubformControlName[/COLOR].Form
    .AllowAdditions = (.Recordset.RecordCount < Me.[COLOR=Red]Quantity[/COLOR])
End With
Amend the red bits.
 

vdanelia

Registered User.
Local time
Today, 16:34
Joined
Jan 29, 2011
Messages
215
Great Work vbaInet
But it need some modifications
For example when i'm going to a new record it gives me an error (Error, Because when going to new record the quantity off course is empty, I can put 0 as default value for Quantity and it will stop that error).
There are also one thing: When I entered 3 in Quantity, on the sub form I can add many rows, if I refresh the page (Form) then it is Ok.

Generally It works great, does everything as needed....
 

vbaInet

AWF VIP
Local time
Today, 23:34
Joined
Jan 22, 2010
Messages
26,374
For example when i'm going to a new record it gives me an error (Error, Because when going to new record the quantity off course is empty, I can put 0 as default value for Quantity and it will stop that error).
In the code replace Me.Quantity with Nz(Me.Quantity, 0). Don't use Default Value.

There are also one thing: When I entered 3 in Quantity, on the sub form I can add many rows, if I refresh the page (Form) then it is Ok.
Ok, just copy that same code into the After Update event of the main form.
 

vdanelia

Registered User.
Local time
Today, 16:34
Joined
Jan 29, 2011
Messages
215
1. I replaced Me.Quantity with Nz(Me.Quantity, 0). It's Ok
2. I copied The same code into the After Update event of the main form, but does nothing... Is it Because I'm hiding (Minimizing, Maximizing) the form by a command button, here is my Code:
Code:
    If Me!cmdShow.Caption = "Show >>" Then
       Me!cmdShow.Caption = "Hide <<"
       DoCmd.MoveSize , , , 10950
       Me.SForm.Height = 3500
    Else
       Me!cmdShow.Caption = "Show >>"
       DoCmd.MoveSize , , , 6760
       Me.SForm.Height = 0
    End If
 

vbaInet

AWF VIP
Local time
Today, 23:34
Joined
Jan 22, 2010
Messages
26,374
vdanelia,

Don't worry, it has nothing to do with your resize code. The After Update event of the main form will only fire when you save the current record. So if you make a change to the quantity textbox, it won't work until you save the record.
 

vdanelia

Registered User.
Local time
Today, 16:34
Joined
Jan 29, 2011
Messages
215
Thanks vbaInet for your help
I have one Question near this...
On that sub-form i added a command button which is deleting the record, everything is OK, I played with is, but without any goals..
I wanted when the record from the sub-form is deleted, the Quantity must changed automatically (If I have 5 in quantity and if i delete one record from my sub form it must change it to 4).
Thanks In Advanced
 

vbaInet

AWF VIP
Local time
Today, 23:34
Joined
Jan 22, 2010
Messages
26,374
So let me ask you, what event (in the subform) do you think the code should go in?
 

vdanelia

Registered User.
Local time
Today, 16:34
Joined
Jan 29, 2011
Messages
215
vbaInet
What do you mean...
On the Sub-Form I have button which is deleting the record, then it tried to add the function which would change quantity like: Me.Quantity = Me.Quantity - 1 something like that
 

vbaInet

AWF VIP
Local time
Today, 23:34
Joined
Jan 22, 2010
Messages
26,374
Look at all the events of your form. You said you want it to "refresh" after the record is deleted. What event relates to deleting a record?
 

vdanelia

Registered User.
Local time
Today, 16:34
Joined
Jan 29, 2011
Messages
215
It deletes the record if i click on it without any problem! it is working perfectly
I just needed to add such command to that button in "Else" which would change Quantity to that product
 

vbaInet

AWF VIP
Local time
Today, 23:34
Joined
Jan 22, 2010
Messages
26,374
Remember when I said you should put the code in the After Update event of the form? After Update is an event right? So now I'm asking you, what event do you think you should put the code for the Delete to take effect on the code?
 

Users who are viewing this thread

Top Bottom