MS Access automatically assign the box number on the form

[alex.rez]

Hello,
Can someone help, please...
I need in MS Access form automatically assign the box number for specific count of specific group name and increase the number when QTY reach the max. For packaging purpose.
For example just need pack 5 items of specific group A in box # A1 and when it reach qty-5 start packing other 5 in box # A2. at same time Group B pack in box #B1 and when it reach qty-5 start packing in box #B2 Example:
Group: A, B
Count per box: 5 for each group
If I have:
ID1 Gr:A Box #A1
ID2 Gr:A Box #A1
ID3 Gr:B Box #B1
ID4 Gr:A Box #A1
ID5 Gr:A Box #A1
ID6 Gr:A Box #A1
ID7 Gr:B Box #B1
ID8 Gr:A Box #A2
ID9 Gr:B Box #B1

I would like to use VBA code but do now know how to start it in this case. Please help. Do not see anywhere examples :banghead:

 

[The_Doc_Man]

Before we dive into this, a question or two might be in order.

First, is the packing box ALWAYS a 5-item box? The reason I ask is because if not, it radically changes the nature of the problem.

Second, if you run out of items, is it the nature of this beast that you simply close all boxes that have at least one item? And do you start with new empty boxes the next time you run this or do you hold over the unfilled box from a previous run?

Third, I understand you want to use VBA code, but it is possible that if your answers to the first two questions go the right way, this could be done with an SQL query. So how firm is that desire to use VBA?

Answer these questions and we might be able to do something for you pretty quickly.

 

[alex.rez]

Thank you for quick replay.

1st: Yes, always 5. I may have other Group with qty-10 per box (example: A always -5, C always-10) but for same group always will be the same count.
for starting point lets pretend all group always keep 5 per box.

2nd: We never run out of items and do not have to close the incomplete box. so we can hold over the unfilled box from a previous run and keep add until reach 5.

3rd: I am ok with SQL query. As longs it works.
Just need enter data on the form and function assign a box number so when I click create record it will print label that showing box number (I know how to do this part with label) Thanks again.

 

[Uncle Gizmo]

I need in MS Access form automatically assign the box number for specific count of specific group name and increase the number when QTY reach the max. For packaging purpose.

First of All, this isn't an answer to your very specific question. It's a general solution which may provide you, or anyone who happens to find themselves in a similar situation with some useful information.

See this YouTube;-
Serial Number Collector - Nifty Access

More Info on Nifty Access HERE:- Check List Extras

 

[The_Doc_Man]

If the ID numbers you showed in the 1st post are ALWAYS unique and ALWAYS increasing then you can do a counting SQL operation for the "Always 5 units in the box" case.

The method here uses a SUBQUERY (the SELECT statement within a SELECT statement) to assign a counting number to each row and then compute the box number based on integer division. I may have been a little pedantic with the parentheses but that was just to assure proper statement grouping.

SELECT 
    ID, 
    GR, 
    ( 1 + ( SELECT 
            INT( COUNT(ID) / 5 ) 
           FROM ITEMS J 
           WHERE J.ID < I.ID AND I.GR = J.GR ) ) AS GRBOXNUMBER
FROM ITEMS I 
GROUP BY GR, ORDER BY ID ;

It might look something like that except that when you enter the SQL, it would all go on one long line probably. I broke it up for readability.

I'm sure if I got this wrong, my colleagues will rag me about it but that's OK as long as you get a decent answer when it is over.

 

[Cronk]

"Rag" you Doc? Too much respect but a small correction given grouping

SELECT 
    ID, 
    GR, 
    ( 1 + ( SELECT 
            INT( COUNT(ID) / 5 ) 
           FROM ITEMS J 
           WHERE J.ID < I.ID AND I.GR = J.GR ) ) AS GRBOXNUMBER
FROM ITEMS I 
GROUP BY GR, [COLOR=Red][B]ID[/B][/COLOR] ORDER BY ID ;

 

[The_Doc_Man]

Whoopsie! Correction noted. I was, after all, shooting from the hip.

 

[alex.rez]

Thank you very much!!! now I see how things starts clearing out and it is really possible to accomplish this project.

before I try still have couple questions:
I am not too familiar with SQL on forms (I use SQL query for reports only and not many)

How can I make it run with form when I create new record?

Also on the SQL above has "ITEMS J", "J" and "I" what it stands for?

Thanks again!!!

 

[The_Doc_Man]

Regarding items I and J in the post:

You will note that in the SQL clauses, the FROM keyword is followed by a data source name, either a table or a query. IF you then follow the data source name by a space and a second name, that second name becomes a shorthand method for naming the source. In particular, when you refer to the same source TWICE in two different contexts (which happens in the main query and subquery in this case), having different shorthand names allows you to specify which context to use. In these cases, that second name in that sequence is always arbitrary but the first name always refers to something that is actually part of the DB.

How can I make it run with form when I create new record?

Kind of depends on the direction you were going with this. If you create a new record, it will probably have a new and unique ID, right?

If the corrected query (with thanks to my colleague Cronk) is a named query, then there is a simple solution. You are going to want the box number, right? If you have the ID field and the group field as part of the query that drives the form (I.e. whatever is listed in the form's .Recordsource), and if they appear on the form as bound entities, there is a chance that you could put code behind an event to perform a DLookup on that named query. The question is what you do with the box number. It is always possible to store the box number in a table, though if the previously discussed rules hold true, you don't need to store the number because it is recomputable through the query.

Let's say that the query as shown by Cronk's correction is stored as named query qBoxNum. (An arbitrary name, to be sure.) If ON THE FORM you know the item number (lngID) and the group (txtGR) then the code for the lookup is:

lBoxNum = DLookup( "[GRBOXNUMBER]", "qBoxNum", "[ID]=" & CStr(lngID) & " AND [GR]='" & txtGR & "'" )

Note that I am mixing double-quote (") and apostrophe (') marks in the above string. This works because a Domain Lookup can use either a table OR A QUERY as its data source. I'm using a query. Access doesn't care because it can do the lookup for either case. All you have to do then is to provide the information it needs to know WHICH value you were seeking ("[GRBOXNUMBER]") and what conditions apply (the part where you supple ID and GR).

Note also that I am presuming that the fields on the form DO NOT have the same names as the fields in the underlying recordset. Oh, it is often possible to do that, but it can also often lead to confusion regarding which one you meant when two different objects have the same name. In this case, the two objects would be the recordset field and the form textbox holding that field. They actually have different locations even though they are related to each other.

Note that I didn't tell you where that code goes. I can't because I don't know how you are designing the form.

 

[alex.rez]

Thank you very much for clarifications!
Looks like I still need help.

I created SQL query and it runs fine but looks like it just pack all different groups in the same boxes. It just count in order and when it reach 5 on the list (even those are different Groups it start new box#)
Please see my SQL code and result below
any idea what i did wrong?

SELECT I.ID, I.Group, (1+(SELECT INT( COUNT(ID) / 5 ) FROM HP_VMI_MB AS J WHERE J.ID < I.ID AND I.Group)) AS GRBOXNUMBER
FROM HP_VMI_MB AS I
GROUP BY I.ID, I.Group
ORDER BY I.ID;

Result:

ID Group GRBOXNUMBER
1 WISTRON 1
2 QUANTA 1
3 Scrap 1
5 WISTRON 1
6 Scrap 1
8 WISTRON 2
10 WISTRON 2
11 QUANTA 2
12 QUANTA 2
13 QUANTA 2
14 QUANTA 3
15 WISTRON 3
16 WISTRON 3
17 WISTRON 3
18 WISTRON 3
19 QUANTA 4

 

[The_Doc_Man]

Yes. Remove the two RED "AS" keywords where they are noted in your query. They change the meaning of the alias.

SELECT I.ID, I.Group, (1+(SELECT INT( COUNT(ID) / 5 ) FROM HP_VMI_MB [COLOR="Red"]AS[/COLOR] J WHERE J.ID < I.ID AND I.Group)) AS GRBOXNUMBER
FROM HP_VMI_MB [COLOR="red"]AS[/COLOR] I
GROUP BY I.ID, I.Group
ORDER BY I.ID;

 

[isladogs]

Yes. Remove the two RED "AS" keywords where they are noted in your query. They change the meaning of the alias

How?
As I understand it, using AS before the table alias in this context is optional and has no effect on the query meaning or results.
I always use AS because I find it easier to understand the query but that's just my personal preference

 

[alex.rez]

I removed red "AS" but still same issue.

Actually I did not enter "AS" in the code some how it fixed by it self and when I saw it I left in as I know in other coding languages it works both ways for this function.

 

[Cronk]

Change the sort to

ORDER by I.GR

If you do want the results shown by the original ID order, create another query based on the existing query and sort on ID

 

[alex.rez]

No changes just ordered by group (see below) but I need put first 5 Quanta in box #1 other Quanta in the box #2, then first 5 Wistron in box #1 other Wistron in box #2. and do same with each different group.

I think now to create separate SQL query for each group.


ID Group GRBOXNUMBER
2 QUANTA 1
11 QUANTA 2
12 QUANTA 2
13 QUANTA 2
14 QUANTA 3
19 QUANTA 4
3 Scrap 1
6 Scrap 1
1 WISTRON 1
5 WISTRON 1
8 WISTRON 2
10 WISTRON 2
15 WISTRON 3
16 WISTRON 3
17 WISTRON 3
18 WISTRON 3

 

[alex.rez]

Hi All!!! Thanks for help!!! I did it.

I made it works by creating SQL query for each group. It is more complicated but i like it better it let me manipulate what box number I can start for each Group.
On the form I use IF statement with DLookup (provided above) to look up box number and shows on the label when I print.
it works and I like it.

Thank you All again!!!