Barcodes

2024-12-022017-10-31 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

Nifty Access Barcode Pagex

Nifty Access Barcodes

Barcodes

Bar-codes along with a bar-code scanner can be a very useful feature to add to your database.

You can quickly scan in serial numbers from mass deliveries of items / components. Bar codes are also very handy for managing the repair process of an item returned for repair. For example, by recording a repaired item’s serial number, you can retrieve useful information like how many times the item has been returned.

BARCODE’s are very easy to add to your MS Access Database. You can add a barcode font, See “Access All In One” Video 2 (5:41) Below, and wherever the bar-code number appears, it will automatically be replaced with a barcode.

The Barcode scanners themselves plug directly into your USB port, no setup, away you go! They work just like a keyboard. Here’s a Wireless Example.

Barcode Scan – Demo

Video 1 (01:19)

In this video I’m testing barcode input for a till system (point of sale system). Notice that if you scan the product barcode a number of times, it updates the number of products the customer has purchased.

I went further with the development and added the facility for removing a product in cases where a product was accidentally scanned in.

Play Video

Barcodes – MS Access – (Northwind)

Video 2 (5:41)

This is an excellent video here from “Access All In One” – Hacking Northwind 01 – Using Barcodes In Access.

See my video VBA Beginner – Setup the Northwind dB – Nifty Access, which provides instructions on how to install the “Northwind Traders” sample database from Microsoft.

Barcode Links

Normalization Tool

2023-06-222017-10-25 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

Normalization Tool

Introducing the “Transpose Tool” or “Database Normalization Tool,” your go-to solution for seamlessly converting spreadsheet-structured data into MS Access-structured data. This transformation not only equips you to construct exceptionally powerful MS Access databases, but is also a critical process exclusive to power users—those with an adept understanding of MS Access, often regarded as problem-solvers within an organization. If that’s you, you’re likely familiar with this technique. This tool aims to streamline and enhance the process, offering you a smoother experience!

If you happened on this page perchance and are wondering what it’s all about, then I suggest you have a look at these links.

Normalisation tool in Action

Excel in Access - Transpose YEARS

Video 1 (4:38)

I created this video in answer to the question in this thread. Query for years of service — Basically the table resembles an Excel Spreadsheet. This columnar Data is not ideal for MS Access.The solution is to transpose the Excel like table into a new table, in a format suitable for MS Access. You can download the tool “Transpose Tool” from — Nifty Access” – press the download button below.

Video 1 (4:38)

… …

Build Query Heads 1

Video 2 (4:24)

In this first video, I demonstrate how to normalise some data imported from Excel. This consists of breaking it up into 3 separate tables. I then demonstrate how this normalised data can be drawn back together again into a useful and usable table. I then demonstrate how to use a cross-tab query to produce a particular output required.

Video 2 (4:24)

In answer to an OP question on AWF Here:-

… …

Build Query Heads 2

Video 3 (10:05)

The form “frmTranspose” (shown in the video) is available from my website CLICK HERE with further information on issues. It is very handy for turning denormalized data into normalised data for use in MS Access.

A precursor to this video is above, See Video 1. It can also be found on YouTube here:- Build Query Heads – Nifty Access where I do a presentation showing how to convert the data imported from a spreadsheet into data suitable for MS Access.

Video 3 (10:05)

In answer to an OP question on AWF Here:-

… …

Transpose Excel to Access

Video 4 (11:11)

OP rasras123 ask a question on Access World Forums (AWF) which indicated that the table structures being used were not optimised for MS Access. In answer to the question I created a table in the unoptimised format as indicated by the OP, and used my Transposition Tool to convert the the unoptimized table into 3 tables, suitable for taking advantage of the advanced features of MS Access. As indicated by MajP, there is an alternative the OP could use which is also shown in the thread.

Video 4 (11:11)

Original Question on Access World Forums (AWF)

… …

Filter Fleet City to Return Rate

Video 5 (5:41)

Our tool provides an insightful example, showcasing how to utilize the optimized database. This tutorial demonstrates how to answer a specific question, as illustrated in the third video: “Filter Fleet City to Return Rate.” This video answers the original poster’s question, leveraging the improved structure (see YouTube: Nifty Access – Transpose Excel to Access).

By separating elements like City and Fleet, these items can be looked up in combo boxes and the resulting data can be used to perform the required search. At first glance, transforming a simple Excel table into a more complex three-table system might seem counterintuitive, and perhaps like a great deal of work for minimal return. However, the benefits are immediately apparent. Opting for an unoptimized, un-normalized route is simply not the most efficient path.

The advantages and future simplicity your database offers far outweigh the initial extra effort. If you bypass this restructuring process when transitioning from Excel to MS Access, you might find tasks becoming increasingly challenging, eventually to the point where obtaining the desired results with a de-normalized structure becomes impossible.

The bottom line is, if you’re building anything beyond the simplest of databases, this step is non-negotiable. If you’re managing only a very simple database, MS Access may not be necessary—you might consider sticking with Excel. But for more complex tasks, our tool is here to simplify and improve your MS Access experience.

Video 5 (5:41)

Original Question on Access World Forums (AWF)

… …

Using the Tool - In Pictures

ALL Controls are Disabled ......

Image 1

When you first open the Form all of the controls are disabled, except for the first Combobox.

Image 1

Select the Table

Image 2

Select the table you want to transpose data from. Note:- the combobox only shows tables with names prefixed “Xtbl”

Image 2

Select the Unique "ID" Field

Image 3

Select the field which represents the “Records” unique identity. This integer will enable you to link all of your tables back together again, and display the data in the original format. You might be wondering why you have to go through this awkward process, however as you can see in the above video, the benefits in the simplicity of creating your queries, reports and everything else you want to use/do with the data far out way this small Inconvenience, particularly if you catch it early enough in your database development.

Image 3

Select the Fields from the Listbox

Image 4

Selecting the unique “ID” triggers the list box to fill with names of the fields from the table. Select the fields that you want to be transposed from the old table into the new table

Image 4

Select the Table to Transpose to

Image 5

The names of tables prefixed with “Ytbl” will appear in this combobox select the table you want the data to be transposed into from this combobox.

Image 5

Select the Field to store the ID

Image 6

Now select the field that stores the record ID that matches the new table to the original Table

Image 6

Choose a two, or more column Table

Image 7

You can copy the data to a two column table or 3 columns, in a table with more than two columns. A two column table would be for storing Data directly related to the field name. Where the field name identified the data, for instance a “true or false” situation like “Order Completed”

Image 7

Two or Three Column Solution

Image 8

In this case you have selected the three columns solution. This means you have a column for the ID; a column for the field name and column for the Data. The field name “conveys information” like in the example video above, the field name conveys the information “the year” and then whether the person was an active volunteer in that year.. “The Data”

Image 8

Select the Field for the the Data

Image 9

Again you are using the three columns solution. So now you need to select the field where you want the data to be placed. In this case, see again the example video above; the Data is whether the person was a volunteer for that particular year or they abstained.

Image 9

Press "ONLY ONCE!"

Image 10

All of the necessary selections have been made. You just need to press the button to complete the process of filling the table. Don’t press the button more than once otherwise you will double or triplicate the data placed in it.

Image 10

This Tool helps convert a flat file MS Excel spreadsheet into a Relational set of Tables more suitable for use in MS Access. Looking back through my notes I realised that this was the original thread that got me started on creating the Transpose Tool – Split one table into 2 (relational) ones

If you have any problems using this form, of if you have a data set that you think should transpose but you’re not sure how to do it, give me a call…..

Cheers Tony…

Date …………… Views
2010_08_23 … 516
2010_08_24 … 551
2010_08_25 … 576
2011_02_19 … 824
2011_10_19 … 1476

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Option Group Label Color Change

2022-06-302017-10-25 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

Option Group Label Color Change

The technique employed in this sample is well worth learning and applying in your own MS Access database. There's always a gotcha when you are developing new code, and I hit one when I was trying to build this.

I wrote the code to change the label colours, but it didn't work? It turned out "IT WAS WORKING" but the background colour of the label was set to "TRANSPARENT" Therefore I couldn't see the colours change.

This is why you have to do coding yourself, this is why you have to push yourself and try new things, because YOU WILL come up against brick walls, BUT once you've pushed through the brick wall you know something new, you improve, and you become a better programmer.

Option Group Label - Video & Download

Option Group Label Color Change

Video 1 (7:17)

This video demonstrates a code module which allows you to change the colour of the selected option group label, and optionally collect the caption of the selected label.

The code module is generic, it will work for any option group containing any number of labels (within MS access limits) and will change the colour of the selected option group label, you could also modify the code to change the other features of the label, like the border colour, border style, text style, font size, any attribute which is modifiable in VBA code.

You can obtain the “Option Group Label Color Change” Here:-

Video 1 (7:17)

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Open one Form from Another and transfer Data

2022-06-302017-10-23 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

Passing Form Values

A common requirement is to pass values from one form to another. In these videos I demonstrate some problems you might face, and demonstrate a good and robust way of doing it.

Open One Form From Another

Video 1 (3:15)

I have used the technique shown here, for many years. I find it is very flexible, also, quick and easy to copy from one Form to another.

In the sample database I use this code:-

				
					Private Sub btnOpenForm2_Click()
Dim strFrmName As String
strFrmName = "Form2"
    
    DoCmd.OpenForm strFrmName
        With Forms(strFrmName)
           '.btnOnForm2.Enabled = True
           '.fSetUp ("Sample Parameter")
           '.Caption = "I CAN CHANGE THE CAPTION"
        End With

End Sub

… to open the form and directly and access the properties of the command button.

I have deliberately left items (the commented out lines) in the code so you can get an idea of what’s possible by using this method.

For example this line here ‘.fSetUp calls the function “fSetUp” which is a public function within the form you are opening. If you un comment that line it will calls a function and you can see how it works.

This is a very powerful way of controlling what happens when you open another form….

You can also provide function parameters which provide one method of Passing values through to the form you are opening…

Video 1 (3:15)

Form Load, Activate, Open, Issue

Video 2 (3:58)

… …

Form Values passed to Controls

Video 3 (1:22)

… …

MORE - On the Form Load Issue

The Form Load Issue

Video 4 (2:00) Approx!

This video, from my YouTube Playlist:-

Object Oriented – Nifty Access

Explores creating a Class Module and how to use it with Allen Browne’s Calendar Form… However Allen Browne’s Calendar Form can be set up and used as is, you don’t need the Class Module, unless you want access to the advanced features the Class Module provides.

Follow the instructions on Allen Browne’s website – Popup Calendar HERE:-

http://allenbrowne.com/ser-51.html

I have created my own Calendar Control/Form, The:-

Nifty Date Picker

The NIFTY DATE PICKER has the distinct advantage that it’s very customisable. More Here:-

Nifty Date Picker for ALL Versions of Access – Nifty Access

Colin’s Date Picker which in my opinion is very slick looking, (I’m jealous!) is HERE:-

A better Date Picker

Video 4 (2:00) Approx!

… …

PopUp Form - Pass Data Back

Video 4 (7:57)

In this video I demonstrate calling a pop-up form, extracting a row number from a subform in datasheet view on the form. I demonstrate how to extract text from the same subform. I suggest t’s a good idea to start using the “Value” of property. I explained how to pass the value by making a copy of the textbox. I passed that to the Pop-Up form. Then, add a custom property and pass the data into the custom property. I use the “With” – “End With” Block to bracket the code that operates on the form.

Video 4 (7:57)

… …

Call Called Class

Video 5 (9:44)

This was one of my early videos on “Call Calling” Class. As with most projects your code starts out with the goal of solving one particular problem. As you progress, you realise your idea can be applied more generally. I recently updated this Class Module . It was originally based on the Microsoft active X calendar control.

See YouTube playlist here:- Object Oriented – Nifty Access

Video 5 (9:44)

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Setup the Northwind dB

2022-06-302017-10-22 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

Northwind Database

I'm not sure when the Northwind database became available, but it seems to me like it's been around right from the beginning. I have blogged about the Northwind database several times on the Nifty Access Site…

See below for info on How to set Northwind up

Setup the Northwind Sample dB

Northwind Sample dB

Video 1 (1:20)

In this video I demonstrate how to install and setup the “Northwind Sample Database” from Microsoft.

If you are interested in learning how to program MS Access with VBA then my “VBA Beginner – Nifty Access” playlist on YouTube might be a good start for you!

Find more Northwind Blogs in the Menu Below:-

Video 1 (1:20)

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

TempVars Value Error

2022-06-302017-10-20 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

You cannot Always omit “Value”

I often see people are advised they do not need to refer to a Control’s Value implicitly like this:-

				
					Dim strValue As String
strValue = cboCurrent.Value

This advice is given because “Value” is the default, therefore it is unnecessary to append it, So you could write the above like this:-

				
					Dim strValue As String
strValue = cboCurrent

However this is not entirely true in Every case!

I noticed this “Unexpected behaviour” a few years back, when I tried to convert one of the Northwind database’s macro’s into a VBA routine. Some of the Northwind macros save information into TempVars. The conversion routine used the combobox name only, omitting the Value property and resulted in an Error.

I would guess it’s not widely known that by omitting the “Value” property when saving to TempVars causes the TempVar to assume you are trying to save an object to it, as most VBA programmers avoid useTempVars, and would seldom experience this problem.

You can reproduce this behaviour yourself:-

Try and add the value of a combobox to a TempVar with this Code:-

				
					TempVars.Add "CurrentUserID", cboCurrent

You will trigger the following error:-

Error 32538 — TempVars can only store data. They cannot store Objects.

This is because the TempVar thinks you are trying to assign an object to it (the combobox) and not the value of the object (the combobox Value).

What’s interesting is, if you assign the combobox value to a string variable, (you can do this without appending the control name with the “Value” property) then you assign the string variable to the TempVar and it works fine:-

				
					Private Sub Command0_Click()
Dim strValue As String

strValue = cboCurrent

MsgBox " >>> " & strValue

TempVars.Add "CurrentUserID", strValue
End Sub

TempVars Value Error

Video 1 (2:31)

I discovered a minor issue when converting a Macro to VBA code. You may have to include the “Value” Property for your text box or combo box (whichever control the the macro is associated with) – this seems a bit odd to a seasoned Microsoft Access programmer because the value property is normally a default and you don’t have to add it (Append it). However it causes a problem because if the macro code assigns a variable to one of the newfangled “TempVars” then for some reason these temporary variables can interpret the assignment as being an object and not text. This confuses the the newly created code, the code created by the Microsoft utility for converting macros into VBA. Running the newly created VBA code causes the following runtime error:- Runtime error 32538 – TempVars can only store data they cannot store objects.

Video 1 (2:31)

New Blog about "VALUE HERE:-

… …

Macro Conversion to VBA Causes Error

Video 2 (5:15)

In the section of this video:- “Is this Control a Hyperlink – Nifty Access” starting at time index 2 minutes, (the video should automatically start at that time index due to the Marvellous features of Elementor!) I demonstrate the conversion of a Microsoft Access Macro into Microsoft Access VBA. The video utilises the free Northwind sample database provided by Microsoft. This is an excellent demonstration you can perform yourself once you have downloaded the Northwind sample database.

Video 2 (5:15)

… …

Access 2007 Login Form

Video 3 (5:53)

The Northwind sample database provided by MS Access 2007 has been upgraded to demonstrate many of the new features available in MS Access 2007. In this video we take a look at the log-in form and the new MS Access 2007 tempVar…..

Video 3 (5:53)

… …

MS Access 2007 Login & TempVars 1

MS Access 2007 Login & TempVars

Video 4 (6:46)

In this video we take a look again at the log-in form and the new MS Access 2007 tempVar in more detail. The conversion from a macro into VBA wasn’t as good as I hoped, and left code with a requirement for some slight modifications which are shown in this video.

Video 4 (6:46)

… …

MS Access 2007 Login & TempVars 2

MS Access 2007 Login & TempVars

Video 5 (6:53)

I cant fathom out what the code in the after update event of the combo box is for. It appears to save the name of the combo box into the temporary variable, the temporary variable which contains the users record number from the user table, and then this number is used in later forms to identify the person taking the orders and other search admin functions.

Video 5 (6:53)

… …

Tidy up the Login Form

Video 6 (2:22)

It’s always a good idea to tidy up your code and your forms as you go, and in this particular case, some code which is possibly redundant is commented out using a custom command bar and a buttons.

Video 6 (2:22)

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Import CSV

2022-06-302017-10-20 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

CSV Import

I’ve done quite a bit of research into importing Comma Separated value Files into MS Access. In particular, I’m working on importing CSV files with more than 255 columns. I have had a reasonable amount of success, but it was thwart with complications that were difficult to resolve. Not ready to release my results publicly, but if you want to contact me privately, I will happy to discuss it with you.

Import CSV into Staging Tables

Import CSV

Video 1 (2:15)

Import the Comma Separated Value file (CSV )into a staging table. Then run two queries against the staging table. One query to extract “H” into a new table, the other to extract “R” into a new table.

Video 1 (2:15)

More Info on Import CSV:-

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

List Box Default Selection

2022-06-302017-10-20 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

List Box Default

Please be aware that while upgrading the Nifty Access Website I discovered this post. I tried to link it back to the original post on Access World Forums, however I couldn’t I find the post. I found one that looks similar, but I don’t think it is the correct one! I’m going to leave this post like it is for now and get back to it at a later date. Nothings wrong with the post other than the links back to the forum might not be correct… cheers Tony.

List Box Highlight a Default Selection

Video 1 (2:0)

This video is In answer to a specific question asked by Frank.

Frank Asked:- I need to select (highlight) the first item in a llstbox in order to ensure that a piece of code operates, but I cannot remember how to do this in code on the opening of the form obtaining the listbox. The form is called frmListRecd and the listbox is called lstRecd. Any help would be greatly appreciated.

Set the listbox controls default value so that the list highlights the same row …

Video 1 (2:0)

Access World Forums Post HERE:-

Highlight a Default Selection - Bullet Points

00:27 go to the list box properties
00:45 put something in the default value
00:47 a “2”
01:12 and you can see that item “2” is selected
01:26 the list box needs to be linked to a field in the underlying table
01:35 it won’t work if it’s not linked to a table because the value is not recorded (although I’m not sure that’s right!)

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Control Wizard (Access 2007)

2022-06-302017-10-19 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

Control Wizard

The Control Wizard is a very handy feature of MS Access. It provides built-in wizards, which simplify many of the processes you have to partake in to build your MS Access database.

Button Wizard (Access 2007)

Video 1 (2:09)

Demonstrate how to use the MS Access (2007) command button wizard to open another form and filter it to display the same record on the second form as you were looking at in the first form.

Video 1 (2:09)

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Enable or Disable a Text box

2022-06-302017-10-19 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

Enable or Disable by Command

In this video I demonstrate how to make a control appear greyed out. In other words, you can’t do anything with it. I demonstrate the process by using two separate command buttons, however it is possible to do it with a single command button. I have a video demonstrating this, however I couldn’t find it! Will post it here when I find it! If you need it, contact me and I will definitely find it for you!

Enable or Disable a Text box

Grey out a Text box in MS Access

Video 1 (1:57)

I’ve done a quick and dirty video here showing how to enable or disable a text box just using a couple of command buttons.

Video 1 (1:57)

…

Barcodes

Barcode Scan – Demo

Video 1 (01:19)

Barcodes – MS Access – (Northwind)

Video 2 (5:41)

Barcode Links

Normalization Tool

Normalisation tool in Action

Excel in Access - Transpose YEARS

Video 1 (4:38)

Video 1 (4:38)

Build Query Heads 1

Build Query Heads 1

Video 2 (4:24)

Video 2 (4:24)

In answer to an OP question on AWF Here:-

Build Query Heads 2

Build Query Heads 2

Video 3 (10:05)

Video 3 (10:05)

In answer to an OP question on AWF Here:-

Transpose Excel to Access

Transpose Excel to Access

Video 4 (11:11)

Video 4 (11:11)

Original Question on Access World Forums (AWF)

Filter Fleet City to Return Rate

Filter Fleet City to Return Rate

Video 5 (5:41)

Video 5 (5:41)

Original Question on Access World Forums (AWF)

Using the Tool - In Pictures

ALL Controls are Disabled ......

Image 1

Image 1

Select the Table

Image 2

Image 2

Select the Unique "ID" Field

Image 3

Image 3

Select the Fields from the Listbox

Image 4

Image 4

Select the Table to Transpose to

Image 5

Image 5

Select the Field to store the ID

Image 6

Image 6

Choose a two, or more column Table

Image 7

Image 7

Two or Three Column Solution

Image 8

Image 8

Select the Field for the the Data

Image 9

Image 9

Press "ONLY ONCE!"

Image 10

Image 10

More Useful Stuff HERE:-

Option Group Label Color Change

Option Group Label - Video & Download

Option Group Label Color Change

Video 1 (7:17)

Video 1 (7:17)

More Useful Stuff HERE:-

Passing Form Values

Open One Form From Another

Open One Form From Another

Video 1 (3:15)

Video 1 (3:15)

Related Links:-

Form Load, Activate, Open, Issue

Form Load, Activate, Open, Issue

Video 2 (3:58)

Video 2 (3:58)

Form Values passed to Controls