[Solved] Print single ID barcode label

Creating and using forms
Post Reply
ns309
Posts: 21
Joined: Tue Oct 29, 2013 3:30 pm

[Solved] Print single ID barcode label

Post by ns309 »

I have a base form, in which I would like to print the person_id on a bar code printer when the print ID button is pressed.

I have tried looking for a macro that would do that. I am not too good at writing macros myself.

I have also tried to create a report that would list all the person_id's but still cannot find a way to print just one person_id.

Please can anybody help me with this. Any help is greatly appreciated.

I attach the database for your reference.

Thank you

N
Attachments
ns_q4_03112017.odb
(37.64 KiB) Downloaded 258 times
Last edited by Hagar Delest on Mon Nov 27, 2017 2:56 pm, edited 1 time in total.
Reason: tagged solved
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Print single ID barcode label

Post by Villeroy »

I cant open your document because of error "The connection to the data source "ns_q4_03112017" could not be established."

Anyway, macros are not the solution (almost never), particularly if you can't program.

Create a parameter query:

Code: Select all

SELECT * 
FROM "some table" 
WHERE "person_id" = :Enter_Person_ID
and create your label document, report or whatever based on that query.
It will prompt you to enter a person id.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
ns309
Posts: 21
Joined: Tue Oct 29, 2013 3:30 pm

Re: Print single ID barcode label

Post by ns309 »

Thank you so much for your suggestion.

I created the query as per your suggestion and attached it to the report. When I open the report it asks for an ID and after entering the ID the report opens as an .odt file.

All good so far. However, the report opens as an A4 document. Is it possible to make the report open as a label (2.5cm x 2.5cm).

Many thanks for your help.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Print single ID barcode label

Post by Villeroy »

All database objects can be opened for editing.
Select the item and choose Edit from the Edit menu or from the context menu.
Open your report for editing and set the page format that is required for your printer.

Alternatively, create a Writer document with the right page/margin settings and insert database fields from your parameter query. Use the dialog method via Insert>Fields>Other... Database... or drag column headers from the data source window into the document. When printing the document, you will be prompted if you are going to print a form letter. The you will be prompted for the ID. The next dialog can be confirmed with [OK] because there will be only one record to choose from. Finally you get the normal print dialog.
 Edit: Oh, you can also click the datasource window's refresh button, answer the ID prompt, select the resulting row (click the grey row handle left of the data row) and then the "Data to Fields" button which will replace the database fields with the values of the selected record 
Alternatively you can drag the parameter query into a Calc sheet, add a defined print range with references to the field values you actually want to print.
menu:Data>Refresh will refresh the import range prompting for the ID. When you print the document, only the print range will be printed
 Edit: and since you posted in the "Forms" forum -- yes, it's possible to mis-use an input form for output. Simply define a page size and arrange the form controls. If the source of the form is a parameter query you get only one record when you refresh the form and enter another ID value. 
 Edit: and finally, you could even use a Draw with form controls 
Last edited by Villeroy on Sat Nov 25, 2017 5:26 pm, edited 3 times in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Print single ID barcode label

Post by Villeroy »

The attached spreadsheet example is based on the Bibliography database. You replace the import data by refreshing the import range at cell B7. Then you print the defined print area of the document.

The parameter query is

Code: Select all

SELECT "Identifier", "Type", "Author", "Pages", "Publisher", "Title", "Year", "ISBN" FROM "biblio"
WHERE "Identifier"= :Enter_Identifier
The Draw document has the same page size as the spreadsheet (75 x 60 mm).
It has an input form linked to the same parameter query as the spreadsheet.
The form has a non-printing refresh button and 4 multi-line text boxes with thin grey borders. The borders should be removed (line style: None).
Attachments
Biblio_Label_Form.odg
Single lable drawing with a Bibliography form and param query.
(8.34 KiB) Downloaded 254 times
Biblio_LabelPrint.ods
Single label print area linked to a parameter query on Bibliography.biblio
(27.26 KiB) Downloaded 243 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
ns309
Posts: 21
Joined: Tue Oct 29, 2013 3:30 pm

Re: Print single ID barcode label

Post by ns309 »

Thank you very much for your suggestions and help.

I have managed to get the report working. Your input is very much appreciated.
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Print single ID barcode label

Post by UnklDonald418 »

Looking at the database you uploaded in your first post I have some additional recommendations.

Your table PERSON_ID has both columns “web_id” and “person_id” set to be a Primary Key. Since there can only be one Primary Key for each table, those two columns are combined into a compound Primary Key which is a violation of the rules of normalization for database tables. That results in the possibility for each “web_id” to have multiple “person_id” values and also each “person_id” can have multiple “web_id” values without violating the unique constraint on the compound Primary Key.

Both your tables use text columns for the Primary Key but that often doesn't turn out well. The HSQLDB database engine that Base uses for the Embedded databases uses integer values to index the rows of data in all the tables, so when you use a text column for the Primary Key HSQLDB must create an internal index. Additionally, having the user type in a Primary Key value can result in difficult to correct errors.

It is more efficient to use an automatically generated integer for the Primary Key. Then add a unique constraint to each of the columns “person_id” and “web_id”.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Post Reply