Category Archives: Computer Programming

Access Database Programming, Computer Programming, Digital, Internet

Access VB – create ODBC connection to download data – part 1

I am transitioning a program from manual data entry at a registration table to online registration and item entry done by the sellers.

This is from an application that I wrote for a non-profit/hobby group that holds a monthly auction – similar to a swap meet. People come and bring their stuff to sell and the buy other peoples stuff.

Currently we have an online form that sellers enter their items and prior to the auction start we do a mass download that fills the onsite program with all items. Then item labels and lists are printed along with bidder cards so that the sellers can stop at the check in desk and pick up their packet when they get to the auction.

The challenge is that we close item entry at 7am and the auction does not start until 11am – we have to drive 2 hours to get to the auction site and up until now, there has not been an internet access at the location. So the download had to happen before we left the house.  An internet access has been made available and so I wrote this code so that we can download at the time of check in – allowing people much more time to enter the items they were bringing into the online form.

The programming problem is that when an access database with a linked table (non local) is opened the ODBC connection needs to be “refreshed” at that time, however it will close when there is no activity (?). AND the kicker is that the database/program will need to be closed to reactivate it. This is a well-known annoyance and according to all the forums I have read lately they are supposed to be working on it but nothing has really been seen to have changed as of yet. So you have to do a couple of work arounds.

Here is my take on the subject:

*** This will not run if you are running this application using Access Runtime – you must have a full version of MS Access to get this to run – at least that is what 20+ hours of recoding, testing and research has proven to me so far.

***the ODBC connection will time out – not necessarily when you plan it to – Connection Timeout=6000 – doesn’t necessarily mean what it should. According to all the documents I have found you SHOULD have to close the program and load it again for this to work. What I did find out is by having the timeout=6000, yes the connection will close. And if you try it again once it closes or feels that it has been quiet for that period of time, you will be a error/message that the ODBC connection is closed. However, if you continue working with the database program still open, after a period of time – more than the amount you have set in the timeout option – and you click the button it will refresh/reset the connection and start working again.  I am testing this but it is difficult to get exact measurements because each time you ‘test’ it, the timer seems to get reset so you start counting all over again. But I have gotten it to work pretty regularly/irregularly.

From:

Form is a seller information form.
Find the seller database and there is a button to “Download Items”

Code:

Dim myDB As Database
Dim tdf As TableDef
Dim strCN As String
Dim imsg As String
Dim pRecCount, aRecCount As Integer

‘get count of items for this seller before download
pRecCount = DLookup(“CountofIID”, “qrySellerStats”, “sid = ” & Me.SID)

Set myDB = CurrentDb()
Set tdf = myDB.TableDefs(“tblItems-web”)
‘Debug.Print tdf.Connect

strCN = “Driver={MySQL ODBC 5.1 Driver};” & _
“Server=1.2.3.4;” & _
“Port=1111;” & _
“Database=database on server;” & _
“Uid=username;” & _
“Pwd=password;” & _
“Connection Timeout=6000;”

tdf.Connect = strCN
tdf.RefreshLink

DoCmd.SetWarnings False

‘access query to download the data and append to the database for selected user

DoCmd.OpenQuery “qryAppendFromWeb_sid”, acViewNormal

Set tdf = Nothing

‘refresh form to show new items downloaded
Me.subfrmInputItems.Requery

‘find how many records are in the database for this seller
aRecCount = DLookup(“countofiid”, “qrySellerStats”, “sid = ” & Me.SID)

If aRecCount > pRecCount Then
      Me.type.Value = “M”   ‘sets a field on the form that items were entered online
End If

imsg = aRecCount – pRecCount & ” records imported”

Me.Refresh

DoCmd.SetWarnings True

MsgBox (“Import Process complete – ” & imsg)

~~~~~~~~~~~

So to get around the problem of the connection time out issue I rewrote this to use a secondary database just for downloading the data. 

Access Database Programming, Computer Programming, Digital, Internet

Access VB – create ODBC connection to download data – part 2

Part 1 of this topic.

Read the Notes and comments about this process/code – it might save you some time.

So to deal with the disconnection issue and not knowing exactly when it resets I took another approach.

Form:

everything on the form is the same – there is a button do download the items. However, this time I have that button open a new database file so that the connection will always be available.

Option 1.a:

download button code:

Dim pRecCount, aRecCount As Integer

pRecCount = DLookup(“CountofIID”, “qrySellerStats”, “sid = ” & Me.SID)

Application.FollowHyperlink “\\localdrive\folder\itemdownload.accdb”, , True

‘opens message box have this program stop and wait for manual answer as to when the import is done so that it can refresh the item list for this seller
MsgBox (“download applications starting” & vbCrLf & “click ok when complete”)

aRecCount = DLookup(“countofiid”, “qrySellerStats”, “sid = ” & Me.SID)

If aRecCount > pRecCount Then
  ‘simply sets a field on the form that items were input on the web if items were downloaded
   Me.type.Value = “M”
End If

Me.subfrmInputItems.Requery

~~~~~

When the other application opens up, there is a spot where the user has to enter (Select from a dropdown) the Sellers ID number and then it runs the code that was originally written.

****This will not work if you are running using Access Runtime – it requires a full version of MS Access to do this.

Option 2

This was actually the first way that I wrote this but after testing it I found that many parts of it would not work in the runtime version that I use on the computers. 

This option also uses a secondary database to do the actual download – getting around the problem of the odbc connection.

Download Button code:

Dim objAcc As Object
Dim accFrm As Object
Dim strBookmark, strDBName As String
Dim pRecCount, aRecCount As Integer

pRecCount = DLookup(“CountofIID”, “qrySellerStats”, “sid = ” & Me.SID)

other database program that does the actual download
strDBName = “W:\auctiondownload.accdb”

Set objAcc = CreateObject(“access.application”)
objAcc.UserControl = True
‘opens the new database
objAcc.OpenCurrentDatabase (strDBName)
‘in the new application that is running, takes the SID from this form and populates a field (selsid) on the new screen
objAcc.Forms(“frmMainMenu”).selsid = Me.SID
‘updates the information box on that form to show the user that is being downloaded
objAcc.Forms(“frmMainMenu”).sellerinfo.Requery

objAcc.Forms(“frmMainMenu”).SetFocus

‘there is code out that to run the actual download program but I could not get it to run from here
‘so what I did is on this secondary access program (auctiondownload.accdb) the main menu opens
‘and the main menu is on a timer – after 5 seconds
‘if there is a seller id in the selsid field on the form it performs the download process and closes the application

‘this message box is just so that once the download program is done you confirm that and it refreshes the item list
MsgBox (“download applications starting” & vbCrLf & “click ok when complete”)

aRecCount = DLookup(“countofiid”, “qrySellerStats”, “sid = ” & Me.SID)

If aRecCount > pRecCount Then
  Me.type.Value = “M”
End If

Me.subfrmInputItems.Requery

~~~~

AuctionDownload.accdb
MainMenu – loaded automatically

The database program puts the seller ID into this form

The form is setup with a timer interval and what to do when that interval is reached.

So 5 seconds after the form is loaded it runs the code in Timer()

‘you should see the other program open up and run the process and a msg box should show up that it’s done.

Private Sub Form_Timer()
     cmdDownload
     DoCmd.Quit
End Sub

I know this really seems like a cheat – but it works and it works really well.

Note:

  • the download process will not run use the Access runtime. It just errors out.
  • If you are directly connected to the server (locally) that holds the data, then the ODBC disconnect problem should not be an issue for you. Meaning – if you can click on those tables and see the data in there without having to do any type of connection (ODBC screen does not pop up asking for username/password) you do not need to use any of this -you can just run the append queries without a problem.
  • There are probably 20 or 200 other options to do this, THIS is what worked in my environments (I have used this in multiple situations now with only minor issues) and maybe it will work for you too.
  • THIS WILL NOT WORK IF YOU USE MS ACCESS RUNTIME – i put this here because no one else has and I spent a lot of time trying to get it to work and got very frustrated.
  • Also, be aware that different versions of Windows sometimes has an affect on how the secondary database will open – I tested it on one computer that is running Windows 8 and the secondary application window would never come up to the front – that is why I went with the timer option on the form. So this is not a full proof answer – just my answer to the situation.

Excel programming

A tasty Excel tidbit today

This is one of those things that I wish (Upon a star) that would just be automatic.

There are so many things in Excel that are automatic and a nuisance at the same time, I think we take for granted the automatic things in Excel that don’t annoy us.  How’s that for your riddle of the day!

I was recently filling out an expense form and I thought – Hey, I got a minute, let’s fancy this puppy up a bit (I say this to myself with a southern drawl accent…when I say these things to myself – it spices up my day a bit).

So on the expense report I added formulas to total the row and then total the column. But when you add the formula it places a 0 or $0 in the column – it’s the default (yuck).

From this… …to this.

There are a couple ways to deal with this:

  • change the formula to something like this: if(sum(e19:m19)=0,””,sum(e19.m19))     
    If you do this, however, you will could get an error if you were adding the column up

  • Use Conditional Formatting: 
    The catch here is that sometimes when you set the font to white in conditional formatting, when you print the text will show up. You won’t see it on the screen, but when creating a PDF and/or printing it will show there.

Select “Use a formula…”

Choose white for the text color

It will show you what you have defined but in this case it is to display white text, so it won’t show here.
  • cell format: Number -> Custom ->   $0.00;-0;;@,
    seems straight forward and to the point, but not unless you understand the format keys. But this is my preferred way to do this.

I know this is pretty rudimentary, but lately I’ve noticed that those are the things being overlooked these days.

 

Computer Programming, Digital, Internet, Javascript

Javascript – check required fields in form – return to form

PROBLEM: check required fields (more than 2) before adding items to a database – this will help curb bad data entries in your database.

I have a form that uses .ajax to write to the database and then relist all the items added. Like a shopping cart…in a way.  So I split out the validation part – it was simpler this way.

<input type=”button” class=”addtocart_button” value=”Save” title=”Save” onClick=”ckItem()” />

SOLUTION:

When the “Save” button is clicked it runs: ckItem()  –

function ckItem(){
//javascript function loaded in header
   
   $formID = "itemEntry";
   //pulls information from form that was submitted
   $category = document.forms[$formID]['category'].value;
   $genius = document.forms[$formID]['desc1'].value;

   //checks required fields for data  
   if (!$category || !$genius) {
       //if required field(s) are blank, do not add record
      alert("Category & Description are required fields");
      if (!$category) {
         document.forms[$formID]['category'].focus();  //returns cursor to this field
         return;  //stops the function
      } else if (!$genius) {
         document.forms[$formID]['desc1'].focus();  //returns cursor to this field
      }
   } else {
      //if both fields have data, it will now write the data to the database
      saveItem();
   };
}

 

Computer Programming, Digital, Internet

PHP Multidimensional Array NOT using Index

Okay – so I run into this every once in awhile and I usually code around it, but I decided on this cold day in January (-18° below at 7am) that I was going to get this working the way I want it to be!!! 

When I google this issue it just loops around and around in a big circle – very frustrating – so when I got it working I decided to put it here – mostly so I could find it!!! 🙄 

SITUATION:

I have a form where the information is then put into an sql table.
On this form is a dropdown selection box – being a good data steward, I am only recording 1 character for each choice. However, when displaying the completed list of items that have been entered, I would rather display the actual description rather than the “value” of the dropdown. 
Seems like a simple thing – right?!

Here is the dropdown  Here is the code behind it 
<select name="category" id="category">
   <option selected value="">Select Category</option>
   <option value="F">Food</option>
   <option value="L">Live</option>
   <option value="E">Equipment</option>
   <option value="P">Plants</option>
   <option value="M">Media</option>
   <option value="T">Tank</option>
   <option value="R">Large Item</option>
   <option value="O">Other</option>
 </select>
So when the items are listed it looks like this:

So, what I wanted to do is when I am looking thru the recordset of the database, I want to have an array that has a cross-reference to the Value that is stored in the Category field in the database.

Seems simple enough – but every reference I could find to arrays in PHP you use the index number to find and display the item in the array. I went thru 6 pages on google to finally find one that references it using the item not the index number.  Kudos to this place.

MY SOLUTION:

In my listing file, I created this array (this is the information used in the form – shown above):

$catList = array(
             "F"=>"Food",
             "L"=>"Live",
             "E"=>"Equipment",
             "P"=>"Plants",
             "M"=>"Media",
             "T"=>"Tank",
             "R"=>"Large Item",
             "O"=>"Other",
 );

Then, to do the lookup in the array:

           $catList[$row[‘category’]]

$row is the array of information that is being pulled from the database, category is the the field from the database that holds the info from the submitted form.

Well, now the list looks a whole lot better:

BEFORE AFTER

 

Access Database Programming

Access (VBA) – reference control dynamically

So I will write my code as I need to but I always like being able to save key strokes.

I recently wanted to be able to copy text in a field to the clipboard to use it in another program (one that I could not link to the main program).  So I wrote this:

 

Private Sub SReqOrganizationName_DblClick(Cancel As Integer)
      Dim strCTL As String
      strCTL = Screen.ActiveControl.Name
      With Me.Controls(strCTL)
        If Len(.Value) Then
           .SelStart = 0
           .SelLength = Len(.Value)
           DoCmd.RunCommand acCmdCopy
        End If
      End With
End Sub

By using the with statement, I didn’t have to customize this for each field that I wanted to do this on.

The better thing would be to write this as a public command rather than put this is each fields DoubleClick command. But this works and works well too.

 

Computer Programming, Excel programming

New Favorite thing in Excel – Frequency

I love it when I can learn a new thing in software program.

I have had to do this function many times before, however, I have never run across this option:  “Frequency”.

A B C D
1 Purchased   Colors
2 Green   Green
3 Yellow    
4 Green    
5 Red    

 

=frequency(B2:B10,D2) Results = 2

Counts how many times in B2 thru B10 that match what is in D2 – Awesome.

so, let’s add a little bit to this (I love things that do multiple things at one time (multi-tasking!) )

IF… you wanted to know how many unique items are in a column:

=SUM( — (FREQUENCY(B2:B5,B2:B5)>0))

—   =  Double-unary: A single unary operator (-) coerces true/false values into -1/0. By using the double unary operaor, we coerce the values again to 1/0.

This can be used in place of the countif statement for large sets of data.

Access Database Programming, Computer Programming

Access/VB do not print report with no data

Just a little bit of programming..after all it pays the bills.

I had setup the option to print different reports, depending on if something is manufactured in house or outsourced.  So I came up with this:

The code behind the button on the form:

Private Sub cmdPrintReport_click()

    On Error GoTo CatchErrNo

     
    If Me.JobNo > “” Then DoCmd.OpenReport “rptWorkOrder”, acViewPreview, , “JobNo = ” & Me.JobNo

 

CatchErrNo:

    If Err.Number > 0 Then
        ‘report will not open if there is no data, but throws an error. This will catch that error and print the workorder instead
        ‘jk – 4/18/2017
        If Err.Number = 2501 Then
            If Me.JobNo > “” Then DoCmd.OpenReport “rptPickList”, acViewPreview, , “JobNo = ” & Me.JobNo
        Else
            MsgBox Err.Number & ” – ” & Err.Description
        End If
    End If

End Sub

In the report design mode, you have to tell it what to do with no data (I never knew this was here)

Report – Event – On No Data

Private Sub Report_NoData(Cancel As Integer)
   Cancel = True
End Sub

Are there other ways to do this, yes, probably 100, but this is what works in the environment that I’m working in.

Access Database Programming, Computer Programming, General

Access / VB Programming – Using a MultiSelect field to send selected info via email

I have never used a multi-select list before, but now is the time.

Below is my adaption of Allen Browne‘s code.

My requirements are this:

  • Allow user to select from open orders
  • send list of selected orders to production – formatted simply – via an email

The database I am working with has a SQL datafile attached so saving a queryDef with the selected records was not an option. So I came up with this work around:

  • Collect the selected items and create the strWhere variable
  • Open the report using the condition – but open hidden (WindowMode:=acHidden) – it does not display to the user, but you can manipulate anything it it = assign the where condition
  • Send the report as an Excel file to specified email address with a subject of “Open Orders” and “Attached is the latest Open Orders list” in the body of the email – because the report is open and the filter is applied that is what gets sent out in the email – kinda slick. 🙄 

 

My changes to his code are bolded:

Private Sub cmdSendList_Click()
 On Error GoTo Err_Handler
 'Purpose:  Open the report filtered to the items selected in the list box.
 'Author:   Allen J Browne, 2004.   http://allenbrowne.com
 'implemented into database - 3/8/2016 - jk
 Dim varItem As Variant      'Selected items
 Dim strWhere As String      'String to use as WhereCondition
 Dim strDescrip As String    'Description of WhereCondition
 Dim lngLen As Long          'Length of string
 Dim strDelim As String      'Delimiter for this field type.
 Dim strDoc As String        'Name of report to open.
 'strDelim = """"            'Delimiter appropriate to field type. See note 1. -
 strDoc = "rptOrderList"
'Loop through the ItemsSelected in the list box.
 With Me.lstJobs
   For Each varItem In .ItemsSelected
     If Not IsNull(varItem) Then
       'Build up the filter from the bound column (hidden).
       strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
       'Build up the description from the text in the visible column. See note 2.
       strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
     End If
   Next
 End With
'Remove trailing comma. Add field name, IN operator, and brackets.
 lngLen = Len(strWhere) - 1
 If lngLen > 0 Then
    strWhere = "[jobID] IN (" & Left$(strWhere, lngLen) & ")"
    lngLen = Len(strDescrip) - 2
    If lngLen > 0 Then
       strDescrip = "Categories: " & Left$(strDescrip, lngLen)
    End If
 End If
'Report will not filter if open, so close it. For Access 97, see note 3.
 If CurrentProject.AllReports(strDoc).IsLoaded Then
     DoCmd.Close acReport, strDoc
 End If
'Omit the last argument for Access 2000 and earlier. See note 4.
 DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, WindowMode:=acHidden, OpenArgs:=strDescrip
 DoCmd.SendObject acSendReport, strDoc, "Excel", "OpenOrders@company.com", , , "Open Orders", "Attached is the latest Open Orders list" 

 

If CurrentProject.AllReports(strDoc).IsLoaded Then
 'closes the report that I had opened with "WindowMode:=acHidden"
 DoCmd.Close acReport, strDoc
End If
Exit_Handler:
 Exit Sub
Err_Handler:
 If Err.Number <> 2501 Then  'Ignore "Report cancelled" error.
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
 End If
Resume Exit_Handler
End Sub