Rank with VBA

2024-09-092018-07-09 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

Rank with VBA

With the Google presentation and a video below, I demonstrate how to change the rank of an item in a table. This is particularly useful if you have a lookup table and you want to offer your user the ability to move an item up or down the list. To add this feature you need to add a new field which stores a value you can change. Sort the list ascending so that the preferred option selected by the user comes to the top. See the presentation and video together for further information.

I developed this code for the Nifty Switchboard Builder. The page data is controlled by an index which prevents duplicate values. This causes a problem when you want to move one of the switchboard page rows up or down. Let’s say you want to move record number 5 to record number 4, you can’t because you can’t change 5 to 4 because this will trigger the duplication prevention provided by the unique index. The solution is to move it to a row number not within the set of row numbers. You could invent an arbitrary figure saying 1000, however you must be sure that this 1000 will not be used by some other process.

Rank with VBA

Video 1 (7:06)

This 1st video follows along from the presentation below. The presentation goes over the basic idea, then the video goes into details about how to construct the queries required to effect the ranking. In this video I show the next step, which is to create the queries which move people up and down in a list with VBA… In the second video of (not published yet) I will show how to convert the queries into SQL string and run them with VBA.

Video 1 (7:06)

Presentation as PDF Here:-

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Recordset Loops How To

2022-06-302017-11-29 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

Recordset Loops

I recently answered a question on my Facebook site… Link:- Full Text of my Answer:- basically it would be best to use a record set loop to solve the OP’s problem.

I agree with Sherry, you need to use a Recordset Loop… I did a set of video instructions on how to create a Checklist in an MS Access database.

One part of the video covers the record set loop. If you have a look at this video:- 1 – Adding a Checklist to MS Access at this time index:- https://youtu.be/r1y_Zl3uLkM?t=1m12s

The three videos below are actually three separate time indexes of the same video, time indexes to each place in the video relevant to the question.

The Two Functions

Video 1 (Time Index 72 to 95)

This Video clip gives you an overview. The video discusses the function on the left, which is something similar to What You Want. Move on through the video to this time index:- https://youtu.be/r1y_Zl3uLkM?t=1m49s

Video 1 (Time Index 72 to 95)

… …

The Function "fCopyListToData"

Video 2 (Time Index 109 to 150)

In this video clip, I discuss the operation of that function which is generally termed a “Recordset Loop” If you go to the video at this time index https://youtu.be/r1y_Zl3uLkM?t=3m20s

Video 2 (Time Index 109 to 150)

… …

Extracting the Row Information

Video 3 (Time Index 200 to 250)

That’s the bit you need to change, listSets and listID refer to fields in the record set passed into the record set loop. You need to provide your own SQL statement based on your own tables and then change listSets and listID to the particular fields in the tables from your project you have passed in then check the contents of those variables which represent the fields past in. With regard to deleting a row, off the cuff so to speak without attempting to do it myself, I think I would add an extra field to your table, a boolean field, and write the code to flag the field(s) you want to delete.

Video 3 (Time Index 200 to 250)

Complete set of videos along with a download of the sample database are available on my website here:- Add a Check List to your MS Access Database

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Create Many Records

2023-11-012017-11-28 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

Recordset Loop - Create Records

Becoming an MS Access Power User 💪
Once you start converting queries into SQL statements – in other words, a string version of the query – you can take that string and manipulate it, extract things from it, and do all sorts of things with it! One of the best stepping stones towards becoming an MS Access Power User should be to start writing simple VBA to leverage your ability to construct SQL statements in the query builder grid. The video and the accompanying ZIP file should give you a good idea of what can be accomplished simply, quickly, and easily by following a few simple rules. 📚🎓

Learning about Loops in Programming 🔄
The ‘for-next’ loop is one of the basic programming constructs, and there’s much information about it on the internet. The ‘recordset loop’ is a bit more specific to MS Access, although it can be used throughout the VBA environment, and other languages use something very similar, if not the same. 🌐

Create Many Records

Video 1 (5:16)

At last an opportunity to demonstrate Record Set Loops!!! I can see that this question “Generate in between numbers” (See below) is a good candidate for a demonstration, it has two simple tables. It’s very easy to understand what the OP wants to happen. It fits very well with my knowledge of Record-set Loops and For Next Loops. Question Originally Posted HERE:- Generate in between numbers

In this recent post “Generate in between numbers” the OP wanted to generate a varying number of Records depending on entries in a table. One table the Control Table, defines the first and last record of the set of Records required for each row in the control table. There is also additional information (UserName) which needs adding to the table.

This is the actual question:-

Generate in between numbers

I have ms-access table (A) as below, and I want a macro/VBA code that allow me to generate the in between serial number as shown in table (B).

Regards

Table (A)

ID	Name	Size	From	To
1	Sarah Umer	3	1	3
2	Tallia Noor	2	4	5

Table (B)

ID	Name	Size
1	Sarah Umer	1
1	Sarah Umer	2
1	Sarah Umer	3
2	Tallia Noor	4
2	Tallia Noor	5

Table (A) is the control table and provides from/to record numbers these could be serial numbers for example. It also provides a name to add to the new table “Table (B)” and for some reason it states the size, which I guess is the number of records that will be generated.

If you look at the structure of Table (A) in the accompanying MS Access sample database I created to answer this question, you will see that I use a calculated field to generate this size number automatically. Download the File:- Create Many Records – Nifty Access

Video 1 (5:16)

Leave List as per Date Range

A similar question was posted on Access World Forums — Leave List as per Date Range recently and Jack came up with a SQL method of manipulating the data. I thought it was very interesting and might provide some insight into doing it with this or a similar example at least! Hence I have copied the code and posted it here!

				
					SELECT Leave.LeaveAppliedOn
, Leave.LeaveAppliedTill
, Leave.EmployeeName
FROM Leave
ORDER BY Leave.LeaveAppliedOn;

More Info from the Access World Forums THREAD — Leave List as per Date Range Below is the SQL from June’s post, she indicates that the SQL method might be slower than the VBA method. I refer you to the AWF thread for further information.

				
					PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
SELECT DISTINCT Format([StartDate] - 1+ 100*Abs([Hundreds].[id] Mod 10) + 10*Abs([Tens].[id] Mod 10)+Abs([Ones].[id] Mod 10)+1, "mm/dd/yyyy") AS MDY, Format([StartDate] - 1+ 100*Abs([Hundreds].[id] Mod 10) + 10*Abs([Tens].[id] Mod 10)+Abs([Ones].[id] Mod 10)+1, "yyyy q") AS YearQtr, 1 AS Data
FROM MSysObjects AS Ones, MSysObjects AS Tens, MSysObjects AS Hundreds
WHERE [StartDate] - 1+ 100*Abs([Hundreds].[id] Mod 10) + 10*Abs([Tens].[id] Mod 10)+Abs([Ones].[id] Mod 10)+1 Between [StartDate]-1 And [EndDate];

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Combo Box

2022-06-302017-11-27 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

Combo Boxes

Combo Boxes are the most fascinating and useful “Controls” you can have on your Form. They have Many uses. Combo Boxes can be manipulated in all sorts of ways without using VBA. Once you’ve mastered the combo-box, and once you have mastered Queries, the building of queries in the query builder grid, a Query you have designed to provide your Combo-Box with a unique set of Records, then it’s a short step to becoming a VBA programmer. One of the most powerful things you can do with VBA is control what your Combo Box does. You can add fantastic and clever features to your Form with minimal VBA, and minimal effort! It is a small stepping stone, on the way to becoming a VBA programmer. The first step, learn the inside outs of Combo Boxes. Learn how to make queries in the query builder grid, and then you will be ready! You will be chomping at the bit to take that Next step into VBA!

Combo Boxes - In the Beginning

The Most Basic Combo Box

Video 1 (4:45)

In this video I show the creation of a very simple combobox. The combobox is unbound and stores it’s values within itself in a “Value List”. I demonstrate creating a combo box both with and without the control wizard. I demonstrate enabling the ability for the user to add new values to the Combobox list. I demonstrate how to stop the user changing values. I demonstrate how you can allow the user to type “Free Text” – anything they like in the combobox.

Video 1 (4:45)

The Following links take you to key places in the video:-

00:30 select a control from the designer tab

00:33 add a combobox with wizard support

00:45 switch the wizard off

00:57 add a combo box without wizard support

01:15 using the wizard; type in the values you want

01:55 the combobox is “unbound”

02:25 allow free typing entries

02:45 set “Limit to List” to Yes

03:00 ask user if they want typed value saved to the list

03:17 shows the item added to the list

03:37 property sheet displaying the values in the “Value List”

03:47 set “Allow Value List Edits” to “NO”

04:12 the “Agile Programming Method”

… …

Get Info From a Combo-box

Video 2 (5:50)

This video shows how to display information from a Combo Box in a text box. Consider a combo box in which you select the customer’s name, then the “After Update Event” causes the address details for that customer to be displayed on the form in text boxes…

Video 2 (5:50)

… …

Form with Combo Box Lookup

Video 3 (4:56)

Combo boxes are one of the most difficult things to understand. In this video I hope I make them a bit clearer.

Video 3 (4:56)

… …

Cascading Combo Box

Video 4 (10:19)

This video shows you how to change the Row Source of the “cascade” combo, with an SQL Statement and the after update event of the Selection combobox

Video 4 (10:19)

… …

Combobox - Change Query Criteria

Change Query Criteria with a Combobox

Video 5 (12:26) - 4-Min Excerpt

Demonstration of how to set up a Combo-box and a subform. This demonstration uses the older version of the Northwind Database. The Combo is Shown restricting the records displayed in the subform. This method utilises the Query that the subform is based on. The Selection in the Combo Box provides the Criteria for the Query. This method requires the Subform/Subreport Control be refreshed, the method employed is demonstrated in the video.. Nifty Access is Here to help Establish you as the “Go To” person in your organisation for database improvements! Nifty Access – will quickly elevate you to “Power User Level!”

Video 5 (12:26) - 4-Min Excerpt

More Info on th Northwind Sample dB:-

… …

Combobox - Displaying a Number?

Video 6 (7:17) - 5-Min Excerpt

Your combobox is displaying a number instead of text? In this video I demonstrate one way you can fix this problem…

I recently answered a question on Access World Forums HERE:- Report only shows Combo box primary key instead of text — I referred the OP to this web page, and Video No# 6. I also so wrote a text explanation on AWF, and I have copied it here because I think it will prove useful to anyone with similar problem. In Video No# 6, I demonstrate this technique on a combo box on a form. However, the Access World Forums (AWF) Question was the same problem, but on a report. The solution is the same, the Report ComboBox needs to reference the information in the Customer table by changing the Combo’s properties slightly… The following is my text explanation to accompany the video explanation:-

Video 6 (7:17) - 5-Min Excerpt

Combobox - Displaying a Number?

Good practice is to store information in the smallest possible format. This is normally achieved by storing "repeating information" in a table. Each row in the table is identified by a unique identity an "ID field"… For example you could have a table which stores customer information, it might store:- salutation; surname; first name; address; telephone number. Each row of "information" would have a unique ID.

When you want to use this information somewhere else, let's say you want to provide the customer details for an orders table, in the orders table you only need one field to identify the customer. You store the ID from the customer table in this field. But that's not a very user friendly method because the user is never going to know which customer any particular number refers to. This is easily solved by using a combo-box as effectively a up look-up agent.

When you create your "Orders Form" based on your orders table, it will very likely create a text-box for the customer field. As suggested this text box will just display the number relating to the customer. However if you change this text box into a combo box, you can do this by right clicking on it, then selecting the third option in the list "Change To" now you have a combo box. It's always a good idea to follow a naming convention so immediately find the combo box name in the property sheet under the tab "Other" and prefix it with "cbo".

Also the Combo Box default name might not mean much, and may contain spaces. Remove the spaces and rename it something meaningful to your programming processes. Now whilst you've got the property sheet open, select the data tab, select the ellipsis (…) at the end of the "Row Source" property and you will be taken to the query building tool. Select a Table, in this case you want the Customers Table. Select the customer ID field and the customer name field so they appear next to each other in the query builder grid. You need a minimum of two Fields, the ID field and the customer name field. You may want other Fields, for instance you may have a Boolean field to flag a discontinued customer. Select this field and set the criteria option to "False". Now discontinued customers will not show in your results. You don't want to see the "Boolean field" in the combo-box so make sure the "Show" checkbox is unchecked.

Close the query builder Grid and re-open the property sheet for the combo-box as we've got just a couple more things to check/set-up. On the data tab make sure that the "bound column" property is set to 1 and "limit to list" is set to true (Yes)… Select the format tab and set the column count property to 2, and the column widths property add the Text "0, 2" …

The "bound column" is the column where the data from the combo box will be taken and fed back into your orders table, and you want the customer ID which will be bound column 1, the first, the left most part of the SQL/Query, in this case the ID. The column width property of the Combo Box is a way of specifying what actually appears in the actual combo-box, is Visible, is what the user sees. In this case we have have query which returns two columns, the ID and the customer name. But we don't want to see the ID, we just want to see the customer name. To do this set the "column widths" to 0; 2 — This shows Nothing of the ID, but 2cm of the customer name. Some versions of MS Access will not show centimetres but inches so the figures might be slightly different for your version of MS Access.

There are several other properties you can change for a combobox, however these property's I have explain here are the basics and you will seldom find yourself changing anything else….

… …

Advanced Combo Box Techniques - by Mike Wolfe

Video 7 (54:30)

The Microsoft Access combo box control can display a list of options, enable/disable AutoComplete to reduce keystrokes that are necessary to choose options and enter data quickly, and a way to select hidden data. It’s optimized for key board and mouse, and promotes discovery and efficiency; the techniques demonstrated boost these 2 key objectives. Combo BoxApalooza Advanced Combo Box Techniques by Mike Wolfe ( www.NoLongetSet.com ) 18 June 2021, Friday

Video 7 (54:30)

From AccessUserGroups.org 3.65K subscribers

More excellent stuff from Mike - Combo Links Below

… …

Combobox - Show Country & Region

Combobox - Country & Region

This DOWNLOAD example is in answer to a particular Question on Access World Forums (AWF) – The OP wanted to enter the name of a country into a text-box on an MS Access Form and then have a combo-box to display the continent/region? related to that selection. I suggested an alternative method, using a combo box to select only the Country and have the continent/region extracted from the ComboBox and displayed in a text box. I used a data-set I downloaded (Excel Format) from the internet to create the entries in the table for this MS Access demonstration from this URL here:- Statvision The information for the Microsoft Excel file was created from information provided by this website here:- gsociology.icaap.org You can download the MS Access example from the Nifty Access Digital Downloads Website here:- Country / Region Combo Box Example.

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Advanced Message Box

2022-06-302017-11-27 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

“Advanced” Message Box

Message boxes can be modified quite extensively to give you your user a much better experience. So before you set about creating a Form to use as a Messsage Box, consider using the more advanced features of a “Built In” Message Box.

There’s a really nicely done example database on message boxes from Colin on Access World Forums here:- An Attention Seeking Database

Extract:-

Features include various ways of creating messages to get users’ attention including:
1. Formatted message boxes
2. Customised message boxes with HTML formatting & countdown timer
3. Flashing, scrolling and balloon tooltip text
4. Dim / blur / remove background
5. Adding warning sounds (use speakers if possible)

“Advanced” Message Box - Walk Through

Video 1 (8:06)

Message boxes have some advanced features which you may or may not be aware of. In this video I demonstrate some of these advanced features. In the video I mention an old programming technique. I believe I happened across the term for it today, and the term is “Bit-wise Comparison” You can see my non-explanation of it here at time index:- https://youtu.be/f1tDWoKOkek?t=4m31s

Video 1 (8:06)

More Info on “Advanced” Message Box:-

… …

“Advanced” Message Box 2

Video 2 (3:43)

In this follow-up video from my first Advanced Message Box Video, I demonstrate the code working in The “Nifty Switchboard Builder”. I go through what’s happening demonstrating the code actually controlling events, and explain how it’s doing it. I have also provided a code example of the Message Box Code working in a sample database. Download the code by clicking on the link below.

Video 2 (3:43)

… …

“Advanced” Message Box 3

Video 3 (8:06)

I wanted to delete a Menu Page in my “Nifty Switchboard Builder”. The Switchboard Builder helps you build a menu system for your MS Access database. It’s based on the original Microsoft switchboard builder, but I have added some extra features, and made it a lot easier to use! I needed to check that the user was sure that they were ready to delete a menu page and understood the implications. I also didn’t want them to be able to delete the “Main Menu” the “Master Menu Page” because this would cause all sorts of complications. I realise that the “Advanced Message Box Code” would just drop in and allow me to quickly and easily create a pop-up message box and help the user in making the correct decision. I also had two, different user cases to solve, one was calling the code from a button on the form and the other one was calling the code on a pop-up form, both common MS Access User Case scenarios. I recorded the generation of the code and my thoughts about my decisions in creating the code in the YouTube videos ABOVE which should help anyone who wants to undertake the same task in their own database. The code is also available for free in text format at the bottom of this web-page.

Video 3 (8:06)

… …

Example of the MsgBox Code

I have recently updated the sample message box code (SEE BELOW) to reflect an actual real world example. It’s not quite the same as the code shown in Video 1 ABOVE. There are now THREE Videos Demoing the Advanced Message Box. I have also packaged the code shown in that video in a Sample Database which you can download by clicking on the download links above. It contains TWO working examples of the “Advanced Message Box Code” in use. The Code is based on the “Nifty Switchboard Builder”. The Switchboard Builder allows you to create a new Switchboard Page, the Switchboard Page will have a default name, however this Message Box offers the user the option to change the default name to something else, or to leave it as the default name and escape from the process without making any changes.

“Advanced” Message Box -Code Example

				
					Private Sub btnGo_Click()
If txtNewPageName = "" Or IsNull(Me.txtNewPageName) Then
    Me.txtNewPageName = "Default New Page Name"
End If

    If txtNewPageName = "Default New Page Name" Then

            Dim strMsg1 As String
            Dim strYes As String
            Dim strNo As String
            Dim strCancel As String
            Dim strTitle As String

            strMsg1 = "Do You Want Change Default Page Name, to Something Meaningful?"
            strYes = "  Yes:          Return to the Textbox. I will Create my own Name"
            strNo = "  No:          Just Use the Default Please"
            strCancel = "  Cancel:    It's Bloody Confusing, I'll Just Play it Safe for Now"
            strTitle = conAppName & " --- Change Default Page Name?"

                Select Case MsgBox(strMsg1 & vbCrLf & vbLf & strYes & vbCrLf & strNo & vbCrLf & strCancel & vbCrLf, vbYesNoCancel + vbQuestion, strTitle)
                    Case vbYes:
                        Me.txtNewPageName = ""
                        Me.txtNewPageName.SetFocus
                    Case vbNo:
                        Call fAddNewPage
                        DoCmd.Close acForm, Me.Name
                    Case vbCancel:
                        DoCmd.Close acForm, Me.Name
                    Case Else: 'Default case to trap any errors
                        'Do nothing
                        MsgBox "Error From --- Select Case in btnGo_Click"
                End Select
    Else
        Call fAddNewPage
        DoCmd.Close acForm, Me.Name

    End If
End Sub      'btnGo_Click

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Searching for Answers

2022-06-302017-11-21 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

Searching For Answers

The upgrade to the Access World Forums (AWF) has also provided a much better search facility. So this blog is practically obsolete! However, I still find that the Google Search offers a much greater control over what you are searching for. If you’re trying to find something obscure, give it a try, compare the two searches the forum Search and the Google Search; let me know what you find!

Searching For Answers

Video 1 (1:27)

Did you know that you can use Google to search Access World Forums and get a more refined result?

I find that the search facility provided by vBulletin forum software is excellent at searching for single words. However it does not provide the sophistication of tools like Google for searching for exact phrases.

To get this sophistication Click on the GREEN BUTTON BELOW:-

What to do when you are stuck! Occasionally you can get stuck in your MS Access Project and don’t know how to proceed.. We all get stuck from time to time and it’s a big problem; but when you’re starting out it’s an even bigger problem, because it’s so difficult to find what you want… Just searching the Internet willy-nilly can return you frustrating and confusing results. Therefore I suggest you use the advanced features of Google Search (See the video on this page or click on the green button!) and target that search at a specific knowledge Site, one that has a far higher chance of answering the question you are searching for an answer to.. It’s actually quite easy when you know how.

However, it’s not always as simple as searching, especially when you are new to MS Access. You don’t always know what to search for, you have an idea, but your search is not returning any sensible results. This is not unusual, this happens because of the way you are asking the question. It’s unavoidable, because if you don’t know the correct term to search for then you’re going to get incorrect search results… What can you do? Join the forum!!! Sign up!!! A very simple process. Then ask, just say:- I’ve got this problem, I’ve searched for this, that and the other, but I’m not getting anything back which is useful can anybody advise me…..

Video 1 (1:27)

Searching For Answers

… …

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

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

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)

…

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Display Field Data Horizontally

2022-06-302017-10-09 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

Transform, Concatenate Examples

A common requirement in MS Access is to extract Columnar data into horizontal data, for instance to make reports look better, to provide a list of emails suitable to send to with Outlook. In the YouTube videos below I explain how this can be done.

Display Field Data Horizontally

Video 1 (4:53)

Good database design requires you to store your data Vertically. However it’s difficult to display this Vertical, columnar data in forms and reports. Also, in the case of an email list, you may need it convert it from the “Vertical” to “Horizontal” for use in VBA code to send multiple emails.

In this video I demonstrate how to extract this columnar data and concatenate it into a string suitable for these type of uses…There is enough information in the video for you to be able to reconstruct the demonstration yourself.

If you want to save yourself a bit of time, then by all means you can download the sample database for a small fee..

Video 1 (4:53)

VBA Code - Concatenate Email Addresses - Sample - From Video 1

				
					Public Function fConcatList()
Dim DB As DAO.Database
Dim RS As DAO.Recordset
    
Dim strSQL As String
Dim strText As String

strSQL = "SELECT tblContact.MailToHere FROM tblContact" 'This is just a query like you would build
                                                        'in the query builder, but it is in text format
Set DB = CurrentDb

    'Open a Recordset and loop through it to fill the text box txtTest
    
    Set RS = DB.OpenRecordset(strSQL, dbOpenForwardOnly)
    Do Until RS.EOF
            If strText = "" Then 'This If statment prevents a  single comma at the begining of the text","
                strText = RS!MailToHere
                Else
                strText = strText & ", " & RS!MailToHere
            End If
        RS.MoveNext
    Loop
    
    RS.Close
    Set RS = Nothing
    Set DB = Nothing

    fConcatList = strText

End Function      'fConcatList

… …

Complex Data Horizontally

Video 2 (6:32)

In the above example “Display Field Data Horizontally” I demonstrate how to compile a list of email addresses which are displayed in a text box. The results could just as easily be passed to another function designed to email the list of clients stored in a table. It would also be possible to provide a query (as an SQL string) with a where clause, making it possible for you to email a subset of clients based on some sort of criteria.

It is always desirable to have the SQL Statement build itself as it were. In this Video “Complex Data Horizontally” I demonstrate how to build the SQL Statement with a WHERE clause that limits the records extracted using the batch number and serial number of the item. The item is a very sophisticated and expensive device somewhat similar to a mobile phone. You just could not manually create the necessary queries effectively.

In this YouTube I’m going to show you one way to go about this.

Video 2 (6:32)

VBA Code - Concatenate Repair Description - Sample - From Video 2

				
					Option Compare Database
Option Explicit

Const strSQLWhere As String = "SELECT tblUnitRepair.UnitRepairBatchNo, " & _
"tblUnitRepair.UnitRepairSerialNo, tlkpRepair.RepairDesc " & _
"FROM tlkpRepair INNER JOIN (tblUnitRepair INNER JOIN tblRepairLog " & _
"ON (tblUnitRepair.UnitRepairSerialNo = tblRepairLog.RepLogSerialNo) " & _
"AND (tblUnitRepair.UnitRepairBatchNo = tblRepairLog.RepLogBatchNo)) " & _
"ON tlkpRepair.RepairID = tblRepairLog.RepLogRepair " & _
"WHERE (((tblUnitRepair.UnitRepairBatchNo)="

Const strSQLAnd As String = "AND ((tblUnitRepair.UnitRepairSerialNo)="
Const strSQLOrder As String = "ORDER BY tlkpRepair.RepairDesc"
Const strSQLParenRHS1 As String = ") "
Const strSQLParenRHS2 As String = ")) "

Public Function fConList(intBatchNo As Integer, strSerialNo As String)
Dim DB As DAO.Database
Dim RS As DAO.Recordset
    
Dim strSQL As String
Dim strText As String

strSQL = strSQLWhere & intBatchNo & strSQLParenRHS1 & _
strSQLAnd & "'" & strSerialNo & "'" & strSQLParenRHS2 & strSQLOrder
Set DB = CurrentDb

    'Open a Recordset and loop through it to fill the text box txtTest with
    'the faults Repaired >>>reprorted by the customer<<<<
    
    Set RS = DB.OpenRecordset(strSQL, dbOpenForwardOnly)
    Do Until RS.EOF
            If strText = "" Then 'This If statment prevents a  single comma at the begining of the text","
                strText = RS!RepairDesc
                Else
                strText = strText & ", " & RS!RepairDesc
            End If
        RS.MoveNext
    Loop
    
    RS.Close
    Set RS = Nothing
    Set DB = Nothing

Let fConList = strText
End Function

… …

Posts Related to this Blog:-

The link(s) below relate to various discussion(s) related to this Blog.

Visit these and you may find someone else has already solved the problem you are having. You could also pose a new question to clarify the process of using this technique for concatenating vertical lists into a horizontal display of information.

… …