Avatar of Peter Fors
Peter Fors
Flag for Sweden asked on

How can a figure like Qty on a MainForm add as many records on a SubForm

This is from a situation where a number of pallets were loaded with goods. Each loaded pallet must receive a unique "pallet number". If the user on MainForm indicates that 7 pallets have been loaded, 7 rows must be created in the SubForm. Each SubForm row should have a pallet number in the "PalletNo" column. My question is, how are these 7 lines created?

* Access 2019VBA

Avatar of undefined
Last Comment
Mark Edwards

8/22/2022 - Mon
Fabrice Lambert

Hmm, it isn't recommended to work like that with relational database as it is just a waste of ressources (rows with no data).

According to your description, the main form should count loaded pallets by packages.
The "loading" process must be done somewhere else.
Peter Fors

Hi Fabrice,
Thanks for helping.
The PalletNo is important since it is an identifier of a specific pallet with a specific load.
There will be more data in the table with the SSCCPalletRows, like qty of items and article numbe

The PalletNo is also used as a part of a customer order.
Important to know, for traceability, which customer received which pallet or pallets.
Fabrice Lambert

Erm ... if PalletNo is an identifier, it can't be used as a quantity.
There is a problem with your database design.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
Peter Fors

PalletNo is an identifier.
Quntity is a figure (in its own column)
Article number is also in its own column.
Peter Fors

Hi I made a simple loop:

    Dim HowManyPallets As Integer
    Dim LCounter As Integer
    HowManyPallets = Forms!Form1.QtyLoaded

    For LCounter = 1 To HowManyPallets
        DoCmd.OpenQuery "Query1"  ' an INSERT qry 
    Next LCounter


Peter Fors

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Mark Edwards

"The PalletNo is important since it is an identifier of a specific pallet with a specific load."
If the PalletNo is a sequential number that starts at "1" every time you click the button, I don't see how it can be used as a unique identifier by itself.  It must be combined with additional data to create a unique identifier.
However, the use of the PalletNo value is not the subject of this post.  Glad to see that Peter's solution does the trick.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.