Method question - Labels (1 Viewer)

C

Ceebee86

Registered User.
Local time
Today, 01:52
Joined
Sep 9, 2019
Messages
25
Hi Access world!

I'm an access novice, and I have an excel program which creates many labels based on the users selection on one page. I want to put this function into my access database. I know how to print labels from a Report with GS1 barcodes, I just need to feed it the data to print to my zebra printer.

Scenario:

A form pops up ideally, with the user being able to select a company from a drop down, select a product, select a qty of labels.

They should be able to press Print, and this would create an amount of records equal to the qty of labels.

So, what direction would you guys take to create multiple records from one form, each record having the same barcode1 value, and each record having its unique barcode2 value, taking the same details for company?
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 09:52
Joined
Feb 19, 2013
Messages
16,607
same barcode1 value, and each record having its unique barcode2 value,
Click to expand...
unique barcode2 based on what? a number incrementing from 1?
 
C

Ceebee86

Registered User.
Local time
Today, 01:52
Joined
Sep 9, 2019
Messages
25
CJ_London said:
unique barcode2 based on what? a number incrementing from 1?
Click to expand...


Barcode1 looks like '[tblcompanyCOMPANYCODE][tblproductPRODUCTCODE]00001'

00001 is in its own field and is calculated at moment expression: 00000+[RecordID].

barcode2 looks like 461000000000001 and I have used expression 461000000000000+[RecordID]

So they both increment.
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 09:52
Joined
Feb 19, 2013
Messages
16,607
do the following - change names if you prefer

1. create a table called admCounter with one numeric field called ctr
2. populate this table with 0..9 (a total of 10 records)
3. create a new query, and copy this sql as is into the sql window
Code: 
SELECT CLng([singles].[ctr]+([tens].[ctr]*10)+[hundreds].[ctr]*100)+([thousands].[ctr]*1000) AS [Counter]
FROM admCounter AS tens, admCounter AS singles, admCounter AS hundreds, admCounter AS thousands
note: no need to sort at this stage
4.save this query as admCount. It's quite useful for other things as well
5. how you do the next bit depends on your form, but assuming it is unbound you will need a query something like

Code: 
SELECT Counter, [Forms]![myForm].CompanyName AS Company, [Forms]![myForm].ProductName AS Product, [Forms]![myForm].CompanyCode & [Forms]![myForm].ProductCode & format(counter,"000000") AS BCode1, "461000000" & format(counter,"000000") as BCode2
FROM admCount
WHERE Counter<=[Forms]![myForm].Quantity
ORDER BY Counter;
 
C

Ceebee86

Registered User.
Local time
Today, 01:52
Joined
Sep 9, 2019
Messages
25
CJ_London said:
do the following - change names if you prefer

1. create a table called admCounter with one numeric field called ctr
2. populate this table with 0..9 (a total of 10 records)
3. create a new query, and copy this sql as is into the sql window
Code: 
SELECT CLng([singles].[ctr]+([tens].[ctr]*10)+[hundreds].[ctr]*100)+([thousands].[ctr]*1000) AS [Counter]
FROM admCounter AS tens, admCounter AS singles, admCounter AS hundreds, admCounter AS thousands
note: no need to sort at this stage
4.save this query as admCount. It's quite useful for other things as well
5. how you do the next bit depends on your form, but assuming it is unbound you will need a query something like

Code: 
SELECT Counter, [Forms]![myForm].CompanyName AS Company, [Forms]![myForm].ProductName AS Product, [Forms]![myForm].CompanyCode & [Forms]![myForm].ProductCode & format(counter,"000000") AS BCode1, "461000000" & format(counter,"000000") as BCode2
FROM admCount
WHERE Counter<=[Forms]![myForm].Quantity
ORDER BY Counter;
Click to expand...

I will let you know how I get on, this is all new and exciting, many thanks for taking the time to write some code, I didn't expect that! :)
 
A

AccessBlaster

Registered User.
Local time
Today, 01:52
Joined
May 22, 2010
Messages
5,917
Ideally you would filter the data in a sub form and print off only the labels (records) captured in the filter. The records could be related or not depending on the filter.

Print:
Provided by Allen Browne, September 2004. Updated April 2010.

Code: 
Private Sub cmdPrint_Click()
    Dim strWhere As String

    If Me.Dirty Then    'Save any edits.
        Me.Dirty = False
    End If

    If Me.NewRecord Then 'Check there is a record to print
        MsgBox "Select a record to print"
    Else
        strWhere = "[ID] = " & Me.[ID]
        DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
    End If
End Sub
 
C

Ceebee86

Registered User.
Local time
Today, 01:52
Joined
Sep 9, 2019
Messages
25
isladogs said:
In case its any use to you, have a look at this app in the example databases area Zebra Label Printer for Access
I've never used it so can't tell you anything about it.
Click to expand...

Thank you for the help.

I've looked at this previously, and have it saved somewhere, if everything else fails, I'll go back to it, as it was quite complex for me to understand, and didn't work as it was trying to access the creators file server I think...
 
C

Ceebee86

Registered User.
Local time
Today, 01:52
Joined
Sep 9, 2019
Messages
25
Ceebee86 said:
I will let you know how I get on, this is all new and exciting, many thanks for taking the time to write some code, I didn't expect that! :)
Click to expand...

Ok...so...I have got this to half work I think. Very cool code.

I made the unbound form, and referenced the fields by name in the SQL you provided, and it came out with the below (pictures attached)

Put in 20, it gave me 21. Edit: added +1 to the code. :)

I need barcode1 to stay as MZOUKBTY000001 and the other 20 records to stay as they are....and then for these numbers to never appear again after their generated in the query....is that possible?
 

Attachments

  • query criteria.PNG
    query criteria.PNG
    4.2 KB · Views: 99
  • Query result.PNG
    Query result.PNG
    27.3 KB · Views: 91
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:52
Joined
May 7, 2009
Messages
19,232
you need to somehow save the last record of your query result to a table.
then you will query this table for the last barcode1/barcode2 for MZOUK/BTY.
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 09:52
Joined
Feb 19, 2013
Messages
16,607
is that possible?
Click to expand...
you've moved the goalposts and now I am not clear exactly what you require. It may be Arnel understands, but I'm not sure.

Provide an example of some label runs perhaps 5 labels in each run. something like

run 1
MZOUKBTY000001..416001
MZOUKBTY000001..416002
MZOUKBTY000001..416003
MZOUKBTY000001..416004
MZOUKBTY000001..416005

run 2 - same customer different product
MZOUKBTX000002..416001
MZOUKBTX000002..416002
MZOUKBTX000002..416003
MZOUKBTX000002..416004
MZOUKBTX000002..416005

run 3 - same product
MZOUKBTY000001..416001
MZOUKBTY000001..416002
MZOUKBTY000001..416003
MZOUKBTY000001..416004
MZOUKBTY000001..416005

etc

what about combination of different customer same product, different customer, different product?
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 09:52
Joined
Feb 19, 2013
Messages
16,607
re your output. I presume company is shipper, who's name is probably in column 2 of your combo so change this

[Forms]![myForm].CompanyName AS Company

to

[Forms]![myForm].CompanyName.column(2) AS Company

probably same for products
 
C

Ceebee86

Registered User.
Local time
Today, 01:52
Joined
Sep 9, 2019
Messages
25
CJ_London said:
you've moved the goalposts and now I am not clear exactly what you require. It may be Arnel understands, but I'm not sure.

Provide an example of some label runs perhaps 5 labels in each run. something like

run 1
MZOUKBTY000001..416001
MZOUKBTY000001..416002
MZOUKBTY000001..416003
MZOUKBTY000001..416004
MZOUKBTY000001..416005

run 2 - same customer different product
MZOUKBTX000002..416001
MZOUKBTX000002..416002
MZOUKBTX000002..416003
MZOUKBTX000002..416004
MZOUKBTX000002..416005

run 3 - same product
MZOUKBTY000001..416001
MZOUKBTY000001..416002
MZOUKBTY000001..416003
MZOUKBTY000001..416004
MZOUKBTY000001..416005

etc

what about combination of different customer same product, different customer, different product?
Click to expand...

Hi CJ,

Sorry, I thought I was being clear, and having entered the access world, I have found its hard to describe things...perfectly. :(

run 1
MZOUKBTY000001..416001
MZOUKBTY000001..416002
MZOUKBTY000001..416003
MZOUKBTY000001..416004
MZOUKBTY000001..416005

run 2 - same customer different product
MZOUKBTX000002..416006
MZOUKBTX000002..416007
MZOUKBTX000002..416008
MZOUKBTX000002..416009
MZOUKBTX000002..416010

run 3 - same product
MZOUKBTY000003..416011
MZOUKBTY000003..416012
MZOUKBTY000003..416013
MZOUKBTY000003..416014
MZOUKBTY000003..416015


000001 indicates the delivery it arrived on.
416001 is a pallet.

One delivery can have many pallets, and can also have more than 1 product.



After generating these labels, and printing them in barcode form, I will be scanning them into the database, alongside the customer/supplier barcodes. I've worked out this last bit with a report that can run off a query, or table, and it prints ok.
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 09:52
Joined
Feb 19, 2013
Messages
16,607
in that case - look at arnel's solution. You will need to pick up two numbers, the one associated with customer/product and the other with 416. Strongly recommend store the number as number and concatenate the text when required.

modify your query to use dmax("custProdnum",myTable")+1 (google to find out more) to increment the customer/product value and use the admcounter to increment the 416 number with a criteria of

BETWEEN dmax("416num","myTable")+1 and dmax("416num","myTable")+1+forms!myform.quantity
 
C

Ceebee86

Registered User.
Local time
Today, 01:52
Joined
Sep 9, 2019
Messages
25
Hey guys,

Using CJ's and Arnel's suggestions. Have two tables for the barcodes, tblAGLGRN & tblAGLSSCC (populated with admCount values)

Thanks for the idea's, I think I have got to a place I'm happy with now. :)

So... route I found myself on was...

Main form from a table for Barcode1 ( 00001) which records company. After drop down selections, is inputted as a record to tblAGLGRN.

Click a Button, macro saves record, makes the Unbound form pop up.

Select details from drop downs. (going to work out how to auto populate these boxes from main form.)

Input qty, click save, click run query.

CJ's query below, slightly modified.

SELECT ssccAGL, [Forms]![frmBARCODE4].companyIDform AS Company, [Forms]![frmBARCODE4].ProductComboform AS Product, [Forms]![frmBARCODE4].companyCODEComboform & [Forms]![frmBARCODE4].ProductComboform & format([Forms]![frmBARCODE4].AGLGRNsub,"000000") AS BARAGLGRN, "461" & format(ssccAGL,"000000000000000000") AS BARAGLSSCC, format([Forms]![frmBARCODE4].AGLGRNsub,"000000") AS AGLGRN
FROM tblAGLSSCC
WHERE ssccAGL<=([Forms]![frmBARCODE4].QtyTextform-1)+(SELECT COUNT(ssccTAKEN="TAKEN") FROM tblAGLSSCC) AND ssccTAKEN Is Null
ORDER BY ssccAGL;
Click to expand...

After this has run, macro closes it, Opens report in Print preview mode, and then macro runs an update query against CJ's query to match the barcodes to tblAGLSSCC and updates field ssccTAKEN with 'TAKEN'.

Click print!

Out comes the labels!

Go back to main form, make new record, click button, and get new numbers! no duplicates! :)

Now I'm going to try and make it do multiple products at same time...
 
You must log in or register to reply here.

Similar threads

P
Barcode Printing on Self Adhesive Stickers
Replies
6
Views
491
Pac-Man
P
D
Can you remove groups from PrintPreview ribbon?
Replies
10
Views
531
David Anderson
D
S
Report.Print Method
Replies
4
Views
493
strive4peace
strive4peace
T
Open Page setup for report
Replies
5
Views
560
theinviter
T
M
AutoSizing Text Fields on a pop-up form ...
Replies
9
Views
569
Marshall Brooks
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom