Limiting Rows By Given Number In Continuous Form

[vdanelia]

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

 

[vbaInet]

N <= Quantity

OR

Number of rows = Quantity

Which one is it?

 

[vdanelia]

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

 

[vbaInet]

That was a question for you to answer, not a solution.

 

[vbaInet]

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]

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]

Alright, it makes sense. So on the Current event of your subform do this:

Me.AllowAdditions = (Me.Recordset.RecordCount < 5)

 

[vdanelia]

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]

Oops... the following code should go in the Current event of the main form:

With Me.[COLOR=Red]SubformControlName[/COLOR].Form
    .AllowAdditions = (.Recordset.RecordCount < Me.[COLOR=Red]Quantity[/COLOR])
End With

Amend the red bits.

 

[vdanelia]

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]

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]

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:

    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]

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]

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]

So let me ask you, what event (in the subform) do you think the code should go in?

 

[vdanelia]

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]

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]

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]

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?

 

[vdanelia]

Buttons On Click Event