Access MVP (2010-2015)

oOo.oOo.oOo.oOo.oOo.oOo

Importing into a *clean* Database

1. Create a new database naming it differently then the original.  (No worries you can rename it later.)

2. Uncheck Track name AutoCorrect info under File > Options > Current Database, as well as, any other options you don’t want.

3. Go to the External Data tab and select Access under Import.

4. In the Get External Data – Access Database window select Browse and navigate to the old file.

5. Then on the Table tab select only the tables that do not start with MSys*, if showing.  (If not showing on the right click Select All.)  Then select the balance of the tabs and where you find objects in the left pane click the Select All button on the right.

6. Then select OK and wait until done.

You now have your new database.  You will need to set up your Start Up Options again, if you have any because those do no come over to the new file.  Confirm that your new database works as expected and then you can save the old one as a backup or delete it.  Finally, rename your file.

28 total views, no views today

Left-Handed Combo Boxes

I sometimes use Combo Boxes as *labels*.  This comes in especially handy in the communications section of the Contacts Form where any given Contact can have more than one eMail or more than one phone.

Left-handed Combo BoxTo move the drop down to the left… while the Form is in Design Mode click on the Combo Box.  (If not already open bring up the Property Sheet.)  Then on the Format tab of the Property Sheet scroll down to the bottom till you see Scroll Bar Align and change to Left… all done.

30 total views, 1 views today

Changing a Label Caption on a Report

If you do Quotes and Invoicing (or Purchase Orders and Invoicing) they generally share the same layout, the only difference being the Label identifying them.  Rather than creating to reports you can change the Label Caption depending on which Form you are opening it from.

Step 1Caption

Create the Report and for the Label Caption put the name of the one that is used more often.  So, for my Report I put Invoice because I print a lot more invoices than I do quotes.  Make sure the Label is long enough to accommodate the character count of the longest word, i.e. Purchase Order.

Step 2
In the Reports On_Open event place…

 
  If Not IsNull(Me.OpenArgs) Then
      Me.Controls("lblInvoice").Caption = Me.OpenArgs
  End If

…save and close the Report.

Step 3
From the Form that you use to generate your Invoice there is nothing to do, so, go to the one that you use for Quotes.  In Design Mode go to the Command Button(s) you are using for Preview (and Print, if you have one).  Between the Private Sub… and End Sub enter…

    Dim strCaption As String
    strCaption = "Quotation"

Then depending on whether you have a WHERE clause…
No WHERE clause

    DoCmd.OpenReport "rptInvoice", acViewPreview, , , , strCaption

With a WHERE clause (This sample only includes where the WHERE would go.  🙂 )

    DoCmd.OpenReport "rptInvoice", acViewPreview, , strWhere, , strCaption

(Don’t forget some Error Code!)  Save, close and test! All done!

32 total views, no views today

Data Models

Probably the hardest part of designing a database is constructing normalized tables.  Get them wrong and everything else is down hill… you can’t get the Forms to do what you want and the Reports just don’t work.  Let’s see if any of these can make your job easier…

Clubs (or Associations)

Complaints

DVD’s

Grocery List Maker

Hotel Bookings

Inspections

Jobs (or Works Orders)

Parking Lot

Products (Shoes or Clothing)

Property Management

Publications

Purchase Orders with Inventory

Real Estate

Recipes

Recipes with Grocery List Maker

Restaurant Reservations

Tasks

Training with Attendance

28 total views, no views today

Procedure View vs. Full Module View

While developing you may find yourself in the VB Editor adjusting the Function for a Control or the Form itself.  When it opens you have to hunt down where the cursor is flashing, even though you have clicked the After_Update event for a specific control.  There’s an easier way…

  1. When you click the Build button on the event line for which you want to modify…
Step 1

Step 1

 

2.  The VB Editor opens and you see the entire VB Editor.  Rather than hunt down where the cursor is flashing…

Step 2

Step 2

3.  …select the Procedure View button located in the lower left hand corner of the VB Editor (see image below, Step 3).

Step 3

Step 3

Now you’re only going to see the Procedure for the Control you selected!  Pretty cool!

 

37 total views, no views today

Access 2016 Runtime now available

“The Microsoft Access 2016 Runtime enables you to distribute Access 2016 applications to Users who do not have the full version of Access 2016 installed on their computers.”

To download click here

58 total views, no views today

SQL to VBA

Recently, I saw a post regarding moving a Query (SQL) to VBA.  I remember I saw something somewhere that automated this task but could not remember where.  Well, after a little surfing of my favorite sites I found it… Copy SQL statement from query to VBA by Allen Browne.  And, because I couldn’t leave well enough alone, I created the database following the steps listed and, for fun, added a *Copy to Clipboard* button just so you can now click the button and paste, with Ctrl+V, right into the VB Editor…

SQLtoVBA

…and you can download it here.

11.13.2015  Amended so you can now also convert INSERT, DELETE and UPDATE queries as well.  Enjoy!

96 total views, no views today

Count characters

Normally, it’s not an issue if an End User tries to type in more characters than the Control will allow… they just can’t type anymore. BUT, if you are using an UNBOUND Form for data entry this is a big problem, they will not be able to Save and with a not so nice message.  Let’s fix that…

Step 1

Add a TEXT BOX under the Control on your Form named txtCharactersAllowed. In the Properties window for this Control set Enabled to False and Locked to True.  This will make it so the TEXT BOX will not react when the User mouses over it and will display a *### characters left* message.

Step 2

In the On_Change Event Procedure for the Control on your Form place…

     Me.txtCharactersAllowed = 150 - Len(Me.txtYourControlName.Text) & " characters left"
        
        If Len(Me.txtYourControlName.Text) = 150 Then
           MsgBox "No more characters allowed - Entry will not save!", vbCritical + vbOKOnly, "Character Limit"
        End If

Note, in this example the maximum length of the field is 150, be sure to change to reflect the maximum characters allowed for your field.

Step 3

Just in case they ignore that message, I add this to the Save Command Button right at the top.

     Me.txtYourControlName.SetFocus
        
        If Len(Me.txtYourControlName.Text) = 150 Then
           MsgBox "You have exceeded the character limit allowed, adjust and try again!", vbCritical + vbOKOnly, "Save"
        Exit Sub
        End If

Stops the Save until they adjust and no ugly message and, oops, all their hard work just gone.

50 total views, no views today

Microsoft opens a User Voice site for Access

Yep, you read that right… Microsoft has opened up a UserVoice site where YOU can suggest features or improvements you’d like to see in Microsoft Access Desktop and Web Apps, as well as, vote on suggestions made by others. This is a great opportunity for YOU to pass on your ideas directly to the Product Team.  Have a look…  Welcome to Access Suggestion Box!

21 total views, no views today

What’s new with Access 2016?

Here you go… click here

23 total views, no views today