Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-11-2019, 04:29 AM   #1
Ceebee86
Newly Registered User
 
Join Date: Sep 2019
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
Ceebee86 is on a distinguished road
Method question - Labels

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?

Ceebee86 is offline   Reply With Quote
Old 09-11-2019, 05:00 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,263
Thanks: 40
Thanked 3,649 Times in 3,519 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Method question - Labels

Quote:
same barcode1 value, and each record having its unique barcode2 value,
unique barcode2 based on what? a number incrementing from 1?
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 09-11-2019, 05:11 AM   #3
Ceebee86
Newly Registered User
 
Join Date: Sep 2019
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
Ceebee86 is on a distinguished road
Re: Method question - Labels

Quote:
Originally Posted by CJ_London View Post
unique barcode2 based on what? a number incrementing from 1?

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.

Ceebee86 is offline   Reply With Quote
Old 09-11-2019, 05:30 AM   #4
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,263
Thanks: 40
Thanked 3,649 Times in 3,519 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Method question - Labels

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;
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
Ceebee86 (09-11-2019)
Old 09-11-2019, 05:42 AM   #5
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,431
Thanks: 112
Thanked 2,855 Times in 2,601 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Method question - Labels

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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Ceebee86 (09-11-2019)
Old 09-11-2019, 05:48 AM   #6
Ceebee86
Newly Registered User
 
Join Date: Sep 2019
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
Ceebee86 is on a distinguished road
Re: Method question - Labels

Quote:
Originally Posted by CJ_London View Post
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;
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!
Ceebee86 is offline   Reply With Quote
Old 09-11-2019, 05:57 AM   #7
AccessBlaster
.
 
Join Date: May 2010
Posts: 1,152
Thanks: 27
Thanked 270 Times in 257 Posts
AccessBlaster has a spectacular aura about AccessBlaster has a spectacular aura about
Re: Method question - Labels

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

AccessBlaster is offline   Reply With Quote
Old 09-11-2019, 06:06 AM   #8
Ceebee86
Newly Registered User
 
Join Date: Sep 2019
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
Ceebee86 is on a distinguished road
Re: Method question - Labels

Quote:
Originally Posted by isladogs View Post
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.
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...
Ceebee86 is offline   Reply With Quote
Old 09-11-2019, 07:02 AM   #9
Ceebee86
Newly Registered User
 
Join Date: Sep 2019
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
Ceebee86 is on a distinguished road
Re: Method question - Labels

Quote:
Originally Posted by Ceebee86 View Post
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!
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?
Attached Images
File Type: png query criteria.PNG (4.2 KB, 5 views)
File Type: png Query result.PNG (27.3 KB, 4 views)
Ceebee86 is offline   Reply With Quote
Old 09-11-2019, 07:14 AM   #10
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,464
Thanks: 68
Thanked 2,716 Times in 2,601 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Method question - Labels

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.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Ceebee86 (09-12-2019)
Old 09-11-2019, 07:52 AM   #11
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,263
Thanks: 40
Thanked 3,649 Times in 3,519 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Method question - Labels

Quote:
is that possible?
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?
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 09-11-2019, 07:57 AM   #12
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,263
Thanks: 40
Thanked 3,649 Times in 3,519 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Method question - Labels

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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 09-11-2019, 08:01 AM   #13
Ceebee86
Newly Registered User
 
Join Date: Sep 2019
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
Ceebee86 is on a distinguished road
Re: Method question - Labels

Quote:
Originally Posted by CJ_London View Post
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?
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.
Ceebee86 is offline   Reply With Quote
Old 09-11-2019, 08:46 AM   #14
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,263
Thanks: 40
Thanked 3,649 Times in 3,519 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Method question - Labels

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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 09-12-2019, 04:59 AM   #15
Ceebee86
Newly Registered User
 
Join Date: Sep 2019
Posts: 14
Thanks: 3
Thanked 0 Times in 0 Posts
Ceebee86 is on a distinguished road
Re: Method question - Labels

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.

Quote:
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;
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...

Ceebee86 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question about printing labels Papa445 General 2 03-08-2009 09:49 AM
Question Question asking etiquette - using paragraph labels helpful or no? DWayne General 4 03-06-2009 05:49 AM
set method question mech55 Modules & VBA 3 09-16-2005 11:43 AM
Question about labels and the '&' hammerva General 2 01-13-2003 12:07 PM
Printing labels question kbrooks Reports 3 12-30-2002 06:27 AM




All times are GMT -8. The time now is 11:58 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World