SELECT CASE Statement

Select Case Statement

The Select Case statement can only be used in VBA, and is very similar to an if-then-else statement in other languages. It is great for use with an option box where a user can select one of many options and then click a button to perform an action, open a form, etc. It is also very useful in other instances. The Select Case statement holds the value or expression to be evaluated. The lines of Case " " statements are conditions that are compared to the select case value. If one of these conditions is true, then the code below will execute.

This example uses a button that is clicked by a user when they want to view a completed purchase order. The Select Case is looking at a listbox that is located on a form. If the 8th column of the listbox (listbox columns always start at 0), has a order status equal to any of the below conditions (open, requested, issued, hold, cancelled....) the associated code will execute.

For example if that order status of the item selected in the listbox is Issued, then 
frmReceiving_View will open.


Private Sub btnReceiving_Click()

Select Case Forms!frmMain!lstbx_Lookup.Column(7)

Case "Open"
MsgBox "Receiving detail not available until PO is issued."
Exit Sub

Case "Requested"
MsgBox "Receiving detail not available until PO is issued."
Exit Sub

Case "Issued"
DoCmd.OpenForm "frmReceiving_View"

Case "Partial"
DoCmd.OpenForm "frmReceiving_View"

Case "Complete"
DoCmd.OpenForm "frmReceiving_View"

Case "RFQ"
MsgBox "Receiving detail not available until PO is issued."
Exit Sub

Case "HOLD"
DoCmd.OpenForm "frmReceiving_View"

Case "Cancelled"
MsgBox "Receiving detail not available when PO is cancelled."
Exit Sub

End Select

End Sub


Is, To, Comma Delimited, and CASE Else

This statement sets value of a string variable based on a form on a field called "age". If [AGE] is equal to any of the case statements then the strAgeGroup variable will be set. Otherwise strAgeGroup will be set to "Other". You can see that the select case statement uses the Is keyword, To Keyword, and commas to delimited values associated with case.

SELECT CASE  [age]

Case Is < 3
     strAgeGroup = "Toddler"

Case 3 to 9
     strAgeGroup = "Child"

Case 10,11,12,13
     strAgeGroup = "Tween"
Case 14 to 17
     strAgeGroup = "Teen"

Case 18 to 25
     strAgeGroup = "Youg Adult"

Case 26 to 64
     strAgeGroup = "Adult"

Case 65 to 110
     strAgeGroup = "Senior"

Case Else
     strOther = "Other"

END SELECT 

Create a report generated by combo box and user input - Access and VBA

So...results are in for another race event and you want to create a report/certificate that any runner can print that will list their name and completed race time. The user should be able to print this report/certificate by typing their name into a textbox and selecting the race/event name from a combobox. This is just an example! Any type of report can be created easily in Access as long as you have the data available and a litte know-how.

1) You need to have at least one table of data imported or created within Access. This tutorial uses MS Access 2010 contains three tables of data. The first is a table called tblBridgeToBrews8k - these are confirmed runners for a race. The second table is tblBridgeToBrews8k_results which contains the results for the runners that participated. The unique identifier for these tables is a field called Race_No. If you know anything about relational databases, you understand that realationships can be created from one table to another using a unique identifier or Primary/Foreign Key. The third table is called tblRaces, and it holds event/race name and date. A field called EVENT_NAME cooresponds with the name of the event and is a primary key of this table. The same field (EVENT_NAME) is in tblBridgeToBrews8k_results.

2)  Create a form. To do this click on the Create tab. In the Forms group, Click on Blank Form- there are other options, but this seems to work the best! A blank form appears on your screen. We will create a form that will contain two combo boxes, and a button. These will allow the user to choose report criteria, and then click to open report.

3) Right click on the Title bar of the form, and click Design View. Your form appears in design mode with a grid like interface.

4) Drag and drop a text box and insert a Title and/or Image onto your form. The textbox and combo box will add next are referred to as Controls. In case this is new for you, controls can be found on the Design tab within the Controls group. Place the textbox as pictured below and use the textbox label to enter Caption: Enter Name. Arrows can be used on the textbox abd textbox label to reposition the control on the form. Click on Property Sheet button on the Design tab in the Tools group to format any control on the form. Click on Title in the Header/Footer group on the Design Tab, nearby is a button to add an image to the from.

5) Next add a combo box control from the Design tab. When you select and place the control on your form a wizard will walk you through populating the combo box. Choose "I want combo box to get values from another table...." Then choose the table or query you will pull data from, what fields you would like to show in the combox box, and if you would like to order the items in the field ascending, etc. Resize or reposition the combo box. The data for this tutorial combo box will be from tblRaces using the field Event Name sorted ascending.



6) Add a label control and give your user directions on how to use the form.

7) Finally drag and drop a button command onto the form. Click Cancel when the macro wizard appears. Use the Property Sheet, All Tab to add a caption to the button and rename the control. I used btnCreate as my button name, and set the caption to "Create Certificate".

8) Final formatting. The Format tab has text and other formatting options.

In Access 2010, the Design tab has a group called Themes. This can help you customize the look and feel of the form, font, colors, etc.

9) Form Properties.

Click to select your form. Go to the Design tab and click on Property Sheet from the Tools group. When the Property Sheet opens make sure Form is selected in the combo box. Change the property values on the listed tab to the following:

FORMAT TAB

Record Selectors - No

Navigation Buttons - No

Scroll Bars - Neither

DATA TAB

Data Entry - Yes

OTHER TAB

Modal - Yes

10) Set the form to open when the Access file opens:

Click on File, Options, and set Display Form to the form in your access database file that you are working on. For this tutorial I chose frmCreateReport.

Here is my completed form:



11) Create a query that will have all the data necessary to populate report certificate.

Click on the Create tab and click on Query Design. Select data tables and create a query. I will select two tables, and create the relationship link between the EVENT_NAME fields.See Below. This lets me bring in matching data from tblRaces. We will set parameters in this query after the report is created. Save the query. mine is called qryReport.

12) Design the Report

Click on the Create tab, and click the Blank Report button. Right click on the title bar and select Design view. Click on the Property Sheet from the Design tab or Report Properties can be accessed on the Design Tab or by right clicking and selecting Report Properties from the menu. Click on the data tab, and find Record Source. Select the qry created in the previous step. This will be your record source for the report.

On the Design Tab, Click on Add Existing Fields - this will open a window with all the fields from the query. They can be dragged and dropped onto the report. Arrange the fields as you wish.

The report can be formatted for color, size, text, images, etc.

Save the report. Mine is called rptCertificate.

13) Set parameters within query

Open the query in Design View. I set parameters on the Name and Event fields. This way whatever the user enters on the form dictates what report/certificate is generated.

See picture. Code Parameters are:

Name: [Forms]![frmCreateReport]![txtName]

Event: [Forms]![frmCreateReport]![cmbEvent]

14) Put Code on form and open Report. -- The final stage!!

Open form in Design view. Right Click on Button, Select Build Event from the pop-up menu, then click Code Builder from the window that opens.

Here is the VBA for the button click event:

Option Compare Database
Option Explicit

Private Sub btnCreate_Click()
DoCmd.OpenReport "rptCertificate", acViewPreview
End Sub

Time to try it out!!

Import/Export Data from Excel 2010 into Access 2010

Importing data from Excel into an Access database is a simple and helpful task.

I ran an 8k a week or two ago, and was sent an e-mail with all the contestents name, city, age, sex, shirt size, etc. Later on results were posted. I will be using this data to do some Access/Sql processes.

1) Open a new or saved database in Microsoft Access.
2) Click on the External Data tab
3) Click on the Excel button within the Import & Link Group (see image). Get External Data window opens.

4) Choose the location of the Excel data file to import

5) Two options appear: Import Source data or Link to Data source. Choose option to Import source data. (See Image). Click Ok

6)  The Import Spreadsheet Wizard should open and will show all/any worksheets in the Excel spreadsheet. Select the worksheet you want to open by selecting it, and clicking Next. (See image) If the first row of data contains data headings, click First Row Contains Column Headings, then Next.

7) The next screen allows you to format columns of data that will be added into an Access table. This is a good time to index column data, which will turn into Access fields -- if necessary. An index is like a map for Access. If you choose to index a column of data, Access has the ability to jump right to the specific data for a query, report, etc. This is a great way to speed up reports which are generated from a query or qry def. Usually I do nothing at this screen though, and just push Next.

8) At the next screen in the wizard, you have an option to add a primary key. This simply numbers each row of data. I usually selection the option No Primary Key, and  use just the data that is being imported.

9) The last screen of the Import Spreadsheet Wizard allows you to name table. After entering text or if you are ok with the name Access has given your table, push Finish.

10) A screen will pop-up asking if you want to Save Import Steps. If this is an Excel worksheet you will import frequently go ahead and save, otherwise click Close.

11) Look in the Navigation Pane and you will see the newly imported table. Double click on the table name and your data appears!





Getting going

Blog is in progress...  I'd like to add some simple tutorials and code to do easy but very useful things in  Access.  I recently completed two databases for users -- both were very different but fun and challenging to work. The first generates reports based on combo box selections on a form, and the other collects information from medical reviews. The later which was a survey type database required the use of list boxes .. these are such great options as they allow a user to select from many options in a list and then perform a function, or also add and delete items themselves to report information, etc.... I found the VBA code for what I wanted the listbox to do to be almost obsolete .. usually I can use some of my own knowledge and google for code i'm unsure of!! So, the tutorials I have in mind to create and post are:

1) Generating a report from several combo box choices on a form with VBA

2) Simple functions

3) Listbox on a form

When I've got a good blog going i'd like to look for more opportunities to create databases. Also working on SQL Server 08 stored procedures! SQL Server seems much more likable then Access but still small business application in Access can be invaluable. If you read this and have a tutorial idea.. i'd love to take a shot at it?!

New simple iphone app!

First time using Image View tool for iphone app..

1) Added two lines of code in header file (.h) to create outlet to a label and action to a button
2) Put .png image file with 320 x 480 resolution into Resources file - iphone only works with .png saved images
3)Added line of code to the implementation file (.m files)
4) Opened the .xib file (pronouced "Nib") file. Anytime this file is opened the Interface Builder opens. The interface builder is part of the apple sdk. Here I was able to open the library and drag image view and set its attribute to the .png file of Porter! Also added a label with no text and a button with label "Press Me"
5) Pressed Command +Return and ran the program .. as pictured!

So exciting.. although this program is very simplistic! It was fun to see Porter (my dog) on the iphone interface!

Access 2007 - form with option frame and combo boxes (using Query Def)

A common application created in Access for an end user or an everyday process will require a form, queries, and VBA/ macros, etc.

My task was to create a report which could be printed from a form based on whatever criteria known by the user. The user may want to create reports for all patients for a specific doctor, all doctor's patients for a specific office, or by specific member or patient Id. This can work for any form with combo boxes and/or option frame and option boxes. Here is what I found to work best!

1. Created a report -- This report has a main record source, and 4 additional sub- reports. I found sub-reports are created most easily by dragging and dropping created select queries onto my report in design view.
2.  Created a form - the record source is a query based off basic patient information in this case. Name, DOB, etc. This is the same record source as my report.
3. On the form I dragged an option box. I used the option box wizard to create 3 option boxes. The user will select just 1, based on the type of search they want to make.
4. I added 2 combo boxes, and one text box, and a report preview button. The combo boxes are each filled from a query, the list box is blank, with no default.
5. Then I added the following code to the report preview button_click event. 

Option Compare Database

Option Explicit


Private Sub btnPreview_Click()


'Declare variables
Dim strSQL As String
Dim qdfNew As DAO.QueryDef
Dim db As DAO.Database


Set db = CurrentDb


Select Case Me.FrameOption.Value


Case 1 'Search by doctor
'If combobox Null - msgBox
If IsNull(Me.cmbDoctor) = True Then
MsgBox "Select Doctor for reporting", vbExclamation + vbOKOnly, "Choose Doctor."


Else


'set variable
strSQL = "SELECT Name,"
strSQL = strSQL & " DOB,"
strSQL = strSQL & " NameID,"
strSQL = strSQL & " DrName,"
strSQL = strSQL & " DrPractice"
strSQL = strSQL & " FROM tblInformation"
strSQL = strSQL & " GROUP BY Name, DOB, NameID, DrName, DrPractice "
strSQL = strSQL & " HAVING (((tblInformation.DrName=[Forms]![frmName]![cmbDoctor])) "


End If


db.QueryDefs.Delete ("qryInformation")


Set qdfNew = db.CreateQueryDef("qryInformation", strSQL)
'open report
DoCmd.OpenReport "rptName", acViewPreview




Case 2 'Search by Practice


'If combobox Null - msgBox
If IsNull(Me.cmbPractice) = True Then
MsgBox "Select Practice for reporting", vbExclamation + vbOKOnly, "Choose Practice"
Else
'set variable

strSQL = "SELECT Name,"

strSQL = strSQL & " DOB,"

strSQL = strSQL & " NameID,"

strSQL = strSQL & " DrName,"

strSQL = strSQL & " DrPractice"

strSQL = strSQL & " FROM tblInformation""
strSQL = strSQL & " GROUP BY Name, DOB, NameID, DrName, DrPractice "strSQL = strSQL & " HAVING (((tblInformation.DrPractice=[Forms]![frmName]![cmbPractice])) "


db.QueryDefs.Delete ("qryInformation")
Set qdfNew = db.CreateQueryDef("qryInformation", strSQL)
End If


'open report
DoCmd.OpenReport "rptName", acViewPreview


Case 3 'Search by MemberID
'If textbox Null - msgBox
If IsNull(Me.NameId) = True Then
MsgBox "Enter NameID for reporting", vbExclamation + vbOKOnly, "Enter NameID"


Else


'set variable

strSQL = "SELECT Name,"

strSQL = strSQL & " DOB,"

strSQL = strSQL & " NameID,"

strSQL = strSQL & " DrName,"

strSQL = strSQL & " DrPractice"

strSQL = strSQL & " FROM tblInformation"

strSQL = strSQL & " GROUP BY Name, DOB, NameID, DrName, DrPractice "
strSQL = strSQL & " HAVING (((tblInformation.NameID)=[Forms]![frmName]![txtNameID])) "


End If
db.QueryDefs.Delete ("qryInformation")
Set qdfNew = db.CreateQueryDef("qryInformation", strSQL)


'open report
DoCmd.OpenReport "rptName", acViewPreview

End Select
'removes combobox and textbox values on lose focus

Me.Form.Requery

Me.cmbPractice.DefaultValue = ""

Me.cmbDoctor.DefaultValue = ""

Me.txtNameId.DefaultValue = ""

End Sub

6. I have an option frame with 3 options. I wanted either a combo box or textbox for search criteria available based on the users prefered method to create report. This is the VBA I added to the form Code.

Private Sub OptPractice_GotFocus()
Me.cmbDr.DefaultValue = ""
Me.cmbDr.Visible = False
Me.txtNameId.Visible = False
Me.txtNameId = ""
Me.cmbPractice.Visible = True
Me.lblForm.Caption = "Select Practice Name for Reporting"
End Sub



Private Sub OptDr_GotFocus()
Me.cmbPractice.DefaultValue = ""
Me.cmbPractice.Visible = False
Me.txtNameId.Visible = False
Me.txtNameId = ""
Me.cmbDoctor.Visible = True
Me.lblForm.Caption = "Select Doctor for Reporting"
End Sub




Private Sub OptNameID_GotFocus()
Me.cmbPractice.Visible = False
Me.cmbPractice.DefaultValue = ""
Me.cmbDr.Visible = False
Me.cmbDr.DefaultValue = ""
Me.txtMemberId.Visible = True
Me.lblForm.Caption = "Enter patient name for Reporting"
End Sub

 -- This is a method to create a report based on form criteria using a query def. If you had strictly text boxes -- say where the user was entering report start or end dates -- a simple forms!frmName![TxtbxValue] could be entered into the form recordsource query criteria. Because this form has many options used to create the appropriate report. I chose to use the query def method.. which is amazing!!

One of those kind of days!

I don't know.. somedays are just the days to learn and not necessarily the day to accomplish? Wrote a module and a couple subs for Access form today for a report i'm working on.. i'm not sure if it was the syntax or record source.. shouldn't be either! but i'll be back at it tomorrow! I'm thinking a good blog .. instructions to create a form with combo box and text box fields to open a report is in in order here!

Now, working on a iphone app.. 320X 460 is the resolution.. cool! I made a cool background for an app idea I have and saved it as a .png.. of course it won't save in my resources file.. ahhgh! .. todays is not my day.. I give up! :) hopefully tomorrow is brighter?!

The good thing is i'm not afraid to play around.. but frustrating! ideas? links?