Building a new Database (1 Viewer)

Allytech

New member
Local time
Today, 15:46
Joined
Feb 22, 2020
Messages
2
Ok so while I’m not new to Access it’s become pretty clear to me that what I’m trying to achieve seems to be out-with my grasp at this time, and while I’m not looking for anyone to build what I need – because I want to re-learn this stuff – I need to know if what I’m attempting is indeed possible.



I currently supervise a repairs section and we put around 3000 units back into service every month, these are units that would otherwise be scrapped.



So once they’re refurbished we do a thing called a Portable Appliance Test (PAT) on these units, this requires a basic electrical safety test and a test label is populated with a Unique ID (which is formatted as a barcode), Test Date, Tester’s Name, Model Number, Unit Classification Notation and a few other details.



Currently we use a Brother printer which links to an Excel file.



The Excel file currently acts as the record keeping system of what stock we’ve PAT tested. It gives no indication of what has been dispatched and what still lies on the floor awaiting sale.



I know that I can easily solve the issue of current stock and sold stock and sold to whom, that’s not my issue. My issue is that I would like to be able to produce the PAT test labels from within the Access Database.



The labels themselves measure 30mm x 60mm but I cannot for the life of me find out how to set the label wizard up to print one individual label at a time. It seems to want to print all the labels.



The hope I have for my database is that I can add all the data I need into one form and I’m not just talking about the data required to populate the PAT label but data that determines if value is added to an item – for example, say you have two LCD TVs but one has a stand and one has not, which means one has more value than the other. I want to be able to check a box on the data input form to advise us of that difference (I know I can do this) but I also wish to print the PAT test label from that form as I’ve finished inputting the data.



Then, when I’m selling my goods I want to be able to scan the barcode and input the fact that unit has sold and to which customer, and then I can run a query which will provide a list of units sold to that customer that day and (for example) which have stands and which do not – then I can hand that list to my office who can provide an invoice.



I’m pretty sure this is achievable but as I said I’m having issues trying to print one single record as a PAT test label.



Currently when we scan the goods being sold we are having to scan the barcode into an excel sheet and then manually type in the other data that determines it’s value – this is so inefficient I’m pulling my hair out because some days we have 500 units going out meaning I lose two to three guys on loading and data input.



I’d love it if when I scanned that barcode – it would populate a list with all the data previously input at the PAT test stage.



As I mentioned in my “hello” post – it’s been so long since I’ve done access that VBA looks like hieroglyphs to me now – that said, I am currently running a very basic database which keeps track of the stock of some of my more expensive units, so I know how to build and filter my queries to show my stock levels and what’s shipped and to whom – but it’s only practical due to the fact the volumes are low.



Anyway – tips and advice would be most welcome but to clarify my aims are:



  • Create a database that I can print my 30mm x 60mm PAT test labels one record at a time.


  • When I’m inputting my PAT test data I want to include other data that I can use as filters so when I sell my goods, I can scan the barcode on the label and it will draw those records into a list which contains the data I need to produce an invoice.
Thanks.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:46
Joined
Oct 29, 2018
Messages
13,104
Hi. I think what you want is achievable in Access. I would recommend storing all the individual components of the barcode into separate fields. It is much easier to put things together to create your barcode than it is to split them apart to tell the date or tester from the barcode. As for printing single labels, you should be able to add a WhereCondition or use a query with a criteria for your label report. What could be tricky is printing a label on a sheet with already used up labels.
 

Cronk

Registered User.
Local time
Tomorrow, 02:46
Joined
Jul 4, 2013
Messages
2,435
I'd say definitely achievable in Access.

First thing though with development of a new db is design of tables. For me, a db is modelling a subset of all the types of things (which I call entities) in the universe. In this case, I see at least 2 main ones - StockItems and StockTests. (This is on the presumption that a unit could have more than one test entered in the db.)

The next question would be, is a new barcode printed for each test ie the bar code identifies a particular test, OR is the same barcode used for subsequent tests ie the barcode identifies the unit. This determines whether the barcode field is in the table for StockItems or for StockTests.

About only printing one barcode, as alluded to by DBguy, the source for the output report to the printer would be
"select Barcode FROM tblStockItems where StockItem=" & me.txtStockItemID
or modified if the barcode is stored in tblStockTests

As an aside, how are you handling the printing of a single label from a sheet of labels. Or is your Brother printer using a roll of labels for the barcode printing?
 

zeroaccess

Active member
Local time
Today, 09:46
Joined
Jan 30, 2020
Messages
608
I'm not sure what your equipment budget is but you could get yourself a barcode printer.

Also you may have some of this figured out, but just to explain my process: when I print labels, I use the font "ABC Code 39 regular" and use the following as the Control Source for the text box on the report:

=("*" & [Identifier] & "*")

Where Identifier is the part number, in my case.

You would need to set up the report with the appropriate margins for the label stock you are using. It will likely take some trial and error and some wasted labels to get everything right, but it will save a lot of headache with label sheets in an inkjet or laserjet office printer.
 

Users who are viewing this thread

Top Bottom