Numbering a sub form record (1 Viewer)

[Pete490h]

Hi,
I have a main form INSPECTION which has a field COMPONENTQTY
With each Inspection, the ComponentQty is totally variable, 1 off, 2 off, 10 off, 100 off etc
On the same form is a single form subform COMPONENTDATA and this has a field COMPONENTNO along with other fields of data
What I’m looking for is a way of using the ComponentQty to be the ComponentNo on the ComponentData subform without any input by the operator
So for example, if the ComponentQty is 7, the the single form subform ComponentNo starts at 1 for first record, 2 for second, 3 for third etc until 7 for seven
I have an event procedure set up to run after update of the ComponentNo

Hope this makes sense

 

[Pat Hartman]

Are you asking how to generate the sequence number or are you asking how to stop when the quantity is reached? Here's a sample that shows how to generate sequence numbers.

 

[Pete490h]

Are you asking how to generate the sequence number or are you asking how to stop when the quantity is reached? Here's a sample that shows how to generate sequence numbers.

Thanks Pat,
I’ve looked at your sample database but sorry to say I don’t fully understand the coding
Attached is my table in design and a couple of manual sample entries
Dont know if it’s possible for you to show what my coding needs to be thanks

 

[Pat Hartman]

You can do it. You just have to dig in a little

Look at the Detail Items form since that sample is just a number and so is more like what you want.

In the BeforeInsert Event, look at the Dmax() expression. In my sample it finds the max value for ItemNumber (Component is your equivalent) for a given salesID (InspectionID is your equivalent).

The BeforeInsert runs ONCE for each NEW record and is the second event that runs when the form is dirtied. The on Dirty event is first, then the BeforeInsert event.

The Nz() around the DMax() handles the case where this is the first record for a SalesID(InspectionID). So if the DMax() returns null (no record found), it is changed to 0. The final part of the expression is +1 to increment the value returned by the DMax()

 

[Pete490h]

You can do it. You just have to dig in a little

Look at the Detail Items form since that sample is just a number and so is more like what you want.

In the BeforeInsert Event, look at the Dmax() expression. In my sample it finds the max value for ItemNumber (Component is your equivalent) for a given salesID (InspectionID is your equivalent).

The BeforeInsert runs ONCE for each NEW record and is the second event that runs when the form is dirtied. The on Dirty event is first, then the BeforeInsert event.

The Nz() around the DMax() handles the case where this is the first record for a SalesID(InspectionID). So if the DMax() returns null (no record found), it is changed to 0. The final part of the expression is +1 to increment the value returned by the DMax()

Thanks so much Pat,
I have to admit, I didn’t previously look at the Details sample form but I certainly have now and successfully used it on the FEATURE INFORMATION subform, as shown on the attached form and design views of my Inspection Report, which is brilliant
I have also tried it on my initial COMPONENTNO requirement and even though it gives me a number, I need a few additional actions to follow, maybe you can advise the best routine if you’re happy to......

You can see on the main form a field COMPONENT QTY which in this case is 6
I’m looking at running a FOR NEXT routine to run a couple of macros that copy and paste the previously created item numbers from the Feature Information subform to each of the Component Result records
I’ll try to describe my required entry actions on the COMPONENT subform which I‘d like to be fully automatic after the FEATURE INFORMATION is completed

1. I click the green box between Component and Result which the first number, #1 appears
2. The above FOR NEXT routine runs
3. After #1 is completed, #2 runs but without any click of the green box
4. This runs until all the #‘s in the Component Qty is completed

I‘ve previously had this running manually and after entering #1 in the green box, the macros run successfully on an After Update event procedure, however the remaining component numbers have all to be manually entered
We can have a situation that there are 100 components, hence why I’d like it to be automatic

many thanks again

 

[Pat Hartman]

Do this with a query. Create a "tally" table with one numeric field filled with the values from 1 to x depending on what you think is your max quantity. Join to this table on the quantity field. Select the PK from the parent table and map it to the FK on the child table. Select the Numeric field from the tally table. Then switch the query to SQL view and change the join from = to <=.. The query will no longer be viewable in QBE view. You can only open it in SQL view because the QBE cannot represent anything except equi-joins. This <= in the join will now return x record. If the quantity is 8, you will get back 8 rows with the sequence values 1-8 which s just what you want. So you can ignore my sample in this case.

 

[Pete490h]

Wow Pat, now I’m in very new territory
Can’t be exactly sure what the maximum quantity would ever be but suppose if I set it to 1000, I’d probably be on correct side

Where and when would I run the required copy and paste macros I mentioned as they need to be run when the component number changes

Thanks
Peter

 

[Pat Hartman]

Hi, I made a sample, If you find a bug, please let me know. There isn't a lot of error handling.

The form opens to a new record. Enter a quantity and press the button. If you press the button on an existing record, it will ignore duplicates but add missing numbers. It will not delete any existing rows.

Look at the tables and make sure you create the correct unique index to prevent the append query from appending duplicates.

 

[Pete490h]

Hi, I made a sample, If you find a bug, please let me know. There isn't a lot of error handling.

The form opens to a new record. Enter a quantity and press the button. If you press the button on an existing record, it will ignore duplicates but add missing numbers. It will not delete any existing rows.

Look at the tables and make sure you create the correct unique index to prevent the append query from appending duplicates.

Thanks so much Pat, can’t wait to have a look and go tomorrow morning
I’ve been looking and reading up on Tally’s most of the day and still a little puzzled
I’m off for so shut eye as my eyes are worn out but will be up early doors to try out your sample
Thanks again
Peter

 

[Pat Hartman]

I called it a "tally" because others here called it that. I don't know that the technique has an official name. I've used it for creating labels. I had an app for a wholesale florist. They would enter the number of boxes in the order and the app would print out the appropriate number of labels using this type of query. I also used it for an inventory app where each item had to be stored individually so that they could make sure to ship the oldest items first. So, the receipt would show 10 boxes (and print 10 labels) and I would create records 1-10 for that item with todays date. When they were shipping, they would pick the oldest first although the app could have done that for them. But they wanted options so they did it manually. So, it is an extremely useful technique which is why I made the sample.

 

[Pete490h]

I called it a "tally" because others here called it that. I don't know that the technique has an official name. I've used it for creating labels. I had an app for a wholesale florist. They would enter the number of boxes in the order and the app would print out the appropriate number of labels using this type of query. I also used it for an inventory app where each item had to be stored individually so that they could make sure to ship the oldest items first. So, the receipt would show 10 boxes and I would create records 1-10 for that item with todays date. When they were shipping, they would pick the oldest first although the app could have done that for them. But they wanted options so they did it manually. So, it is an extremely useful technique which is why I made the sample.

Hi Pat,
Brilliant, copied your coding with my relevant tables, fields etc and it works a treat thanks
I’ve now moved to hopefully the final stage before this module can start to be used, namely applying the copy and paste macros to each of the component records by using the FOR NEXT coding
I’ve manually run the macros by manually scrolling through the component records, however again I’d like to run it automatically by adding the necessary code to the GENERATE button
Thought it would be pretty simple by a simple goto next record macro but not working
Have you another magic option you could share.....
many thanks
Peter

 

[Pat Hartman]

NEVER use a code loop when a query will do the job. Include whatever data you want in the initial append query. It is generally poor practice to create "empty" records. The only time I would do it is if the user must provide some information for each empty record to complete a process. A survey is a good example. If the survey has 20 questions, you would append 20 rows that link to questions and provide a place for an answer. I don't create future billing records. I create them one month (or billing period) at a time. That provides flexibility should the billing increment change or the service be cancelled.

It is always easier for us to give you the correct help the first time if we know what you are trying to do. Sometimes you need syntax help but your problem is different. You are doing something we don't think you should be doing and unless we know what you are trying to do, we cannot guide you effectively or efficiently.

PS, stay away from macros. Their logic is bizarre. In the long run, VBA will be easier and more flexible. Find a copy of the Access Cookbook ver 2, O'Reilly, Getz, Litwin, and Baron. It is OLD but so is VBA. It is an excellent learn by example tool. Access has been upgraded over the years to "fix" some of the things this book gives you solutions for but it still helps to know how you could have done something. Another useful book is Fixing Access Annoyances, O'Reilly, Phil Mitchell & Evan Callahan.

Both of them are prior to A2007 but if you can get past the screen shots, VBA hasn't actually changed although there are more functions then there used to be.

Once you know how to program, reading a syntax manual is better but books like these help you to learn how to think like a programmer.

 

[Pete490h]

NEVER use a code loop when a query will do the job. Include whatever data you want in the initial append query. It is generally poor practice to create "empty" records. The only time I would do it is if the user must provide some information for each empty record to complete a process. A survey is a good example. If the survey has 20 questions, you would append 20 rows that link to questions and provide a place for an answer. I don't create future billing records. I create them one month (or billing period) at a time. That provides flexibility should the billing increment change or the service be cancelled.

It is always easier for us to give you the correct help the first time if we know what you are trying to do. Sometimes you need syntax help but your problem is different. You are doing something we don't think you should be doing and unless we know what you are trying to do, we cannot guide you effectively or efficiently.

PS, stay away from macros. Their logic is bizarre. In the long run, VBA will be easier and more flexible. Find a copy of the Access Cookbook ver 2, O'Reilly, Getz, Litwin, and Baron. It is OLD but so is VBA. It is an excellent learn by example tool. Access has been upgraded over the years to "fix" some of the things this book gives you solutions for but it still helps to know how you could have done something. Another useful book is Fixing Access Annoyances, O'Reilly, Phil Mitchell & Evan Callahan.

Both of them are prior to A2007 but if you can get past the screen shots, VBA hasn't actually changed although there are more functions then there used to be.

Once you know how to program, reading a syntax manual is better but books like these help you to learn how to think like a programmer.

Append query sorted with macro ditched, so pleased, so easy and so quick, would never have considered this if I hadn’t contacted this group and so, thanks again Pat

Sometimes I find it hard to fully explain and detail what I need, so I appreciate you may find it vague, but my database is quite large and obviously you can’t see what I can

I’m fully self taught in Access, found it so addictive and ended up creating a full business operating system for three companies in an engineering group I worked for, including Sales, Production, Purchasing, QA, Spares and Financial modules
I‘m retired now but still continue to develop and enhance the system for one of the companies, so I’ll certainly be looking if my new learning can help, mind you don’t want to break something that’s working pretty well

I’m not a great reader, but I know that vba knowledge will help so I’ll have a search thanks

 

[Pete490h]

Hi Pat,

wonder if you could help me again on this……

The GENERATECOMP button is working great however I have noticed an issue and although not really affecting the system, it doesn’t look right

I’ll try my best to explain
When we click the button, it creates the correct number of component records as per the Component Qty (see attachment 1)
The problem is that all the previous Inspection Reports have the number of component records duplicated after 5he button is clicked (see attachment 2)
I don’t understand why this is happening
I’ve also attached the GENERATECOMP code and the three query designs

I know I’m asking a lot but can you see anything where the duplicate action occurs

Many thanks
Peter

 

[Pat Hartman]

Several things appear wrong with the design but the query named NISAppltemComp has an invalid relationship. InspectionID to InspectionID is not valid because InpectionID is NOT the PK in either table. This join is likely what is causing the duplication.

I don't understand why I'm looking at multiple records in the subforms but only one is showing a navigation bar and it is saying 1 of 2 when there are more than two items visible.