Discovery

July 11th, 2010

I enjoy the discovery phase of a project; learning a new business, seeing how other people arrange and manage their business. No two businesses are the same. There lies the shortfall of software packages. They are not able to be tailored to suit each unique situation. Instead you have to adapt your organization to fit the Software. That is not a way to purchase software. Every company needs software tailored to how they do their business. It always is easier to adapt your custom software than it is to change the way people do their jobs.

Posted in Uncategorized | No Comments »

What my customers enjoy

June 10th, 2010

I find that when developing a proposal, if I give the client a graphic display of their database as it would look to the user, that they are better equiped to help me design the database the way they want it the first time and not after many revisions.

Posted in Uncategorized | No Comments »

Closing all open forms except certain forms

June 7th, 2010

I had a tough time accomplishing this one and my final solution isn’t elegant at all!

I wanted to close all currently open forms except the main menu.  And if the main menu wasn’t open, I wanted to open it at the end of the routine.

 I used the Forms collection to inspect all of the currently opened forms.  I compared the name of the form to two hard coded names that I didn’t want to close (one of which was the form calling this routine).

 But the challenge I encountered was that once a form was closed, the overall collection seems to have changed so that the next time the loop is iterated, a previous form in the collection was being skipped!

 My crappy solution was to simply run the entire routine 5 times which should capture all forms eventually.

I know there must be a better solution, but I simply don’t have the time for elegance on this project and my solution runs so darn quickly, the user will never know.

 Private Sub cmdContinue_Click()

     DoCmd.RunCommand acCmdSaveRecord

      Dim frm As Form

    Dim i As Integer

       For Each frm In Forms

        Debug.Print frm.Name

    Next

       Debug.Print

    Debug.Print “—————————————————————————————————————————————————”

    Debug.Print

   

    For i = 1 To 5

        Debug.Print “LOOP: ” & i

        Debug.Print

        For Each frm In Forms

            Debug.Print “   ” & frm.Name

            If frm.Name = “frm_MainMenu” Or frm.Name = “frmadm95Choose Case” Then  ‘don’t close the main menu

                Debug.Print “    – form not closed: ” & frm.Name

            Else

                Debug.Print “    – trying to close form: ” & frm.Name

                DoCmd.Close acForm, frm.Name

            End If

          Next

       Next

       If nz(Me!cboCase) = 0 Then

        MsgBox “You must choose a case.”

        Me!cboCase.SetFocus

    Else

           DoCmd.Close acForm, Me.Name

        DoCmd.OpenForm “frm_MainMenu”

    End If

End Sub

Posted in Uncategorized | No Comments »

Data Comparison/Change made easy?

August 25th, 2009

I do a weekly download from a .csv file (for my client).

They want to visually compare the orig data (in a form) to the new data.  Then they may or may not want to accept any data changes.

So I’m building them a data comparison form (pop up) to lie on top of the current (old) data.  I’m changing colors on the popup form to visually indicate which data has changed.  And I’m adding a function to the double click event of the controls so they may easily accept the changes.  Why a function?  so I won’t clutter the coded window and so I can handle the various currentdb.execute update statements all in one place.

But here’s what I’d like to know:  From the properties window of the popup form, how do I pass in the name of the control in the function in the double click event?  That sure would have saved me a lot of time!

 

Arrgghhhhhh!!!!!!!!!

 

data form comparison

Posted in Uncategorized | No Comments »

Subreport Hell

January 13th, 2009

So, a new item found today (actually found a week or so ago):  Problems linking reports/subreports.

I went to link a subreport to a main report just like I’d done many, many times before:  I dragged a pre-created subreport onto a main report.  Both reports already had the linking field in their record source.  Both linking fields were the same data type (actually, the same field name as well).  So, everything was set up perfectly.

Surprise, surprise:  When I took a look at the ‘Link Master Field’/'Link Child Field’ properties of the subreport, they were blank (previous versions of Access would have filled this in for me).  So, I patiently clicked the ‘…’ button on the ‘Link Master Field’ property to bring up the linking window.  Much to my disappointment, again the link was not prepopulated like the good old days.  But what was worse, it wouldn’t recognize my desired choice.  I’ve screen printed the popup window after I fixed ’cause I couldn’t get it to recreate exactly.

I then proceeded to play with this issue for at least 3 hours.  Yes, I searched google for explanations but to no avail (hence this blog entry).  Please see the attached .xls file that explains the problem and how it was solved.  I think pictures are much more helpful, don’t you?

Eventually, I went back to the main report’s record source, added the TransactionID (Master link field) a SECOND time and then used this new field (default name:  exp1000) to perform the link.  That worked!   Why???!!!!  I don’t know.  Ask the genius at Microsoft that threw together MS Access 2007 without testing it and apparently without asking any 15+ year veterans what they thought of it!

Sorry to be so angry.  But who’s going to pay for my 3 hours of wasted time?  I am, that’s who.

Posted in Access 2007 problems/solutions | No Comments »

Another Access 2007 Issue – Default grouping of objects on report and how the heck do you ungroup?

November 18th, 2008

Created a default report (I hate the lack of control you have on default reports!  Where are my old wizards that I’ve loved over the past 10 years?  Arrrggghhhh!!!!!).  Then started to modify the controls that were pre-populated on the screen.  Started by deleting undesirable controls.  All the controls to the right slid to the right!  Why the heck did this happen?  Not sure I like that.  What if I were reserving the deleted space for something else (which I was)?  Frustrating but let’s move on…

So I copied / pasted a label and text box (choosing both like a good little Access developer) and WhamO!  New controls just pasted are under the old controls!  Header & Detail section has bee expanded to accomodate.  Now I’ve got to move the new label individually, then the new control individually, then resize the header then resize the detail.  What a pain!  I realize the old way was flawed (pasting directly on top of the copied controls), but why this new solution of copying below?  How about still on top but slightly to the right to indicated the new controls are ‘slightly’ on top of the old?  How much more convenient would that be!!!!!!  But wait, it gets worse…

Now I can’t even move my new controls!  There some sort of grouping issue going on so I need to ungroup.  Oh Lord!  I feel a headache coming on ’cause I’m sure they’ve moved the grouping/ungrouping process.  Sure enough, 5 minutes of searching reveals nothing (Another 5 minutes of my life gone done the tubes due to Access 2007.  Arrrggghhhh!!!!!!  What idiot at Microsoft has done this to us!)

So I get one of my other developers to come over and help.  Apparently she’s encountered this frustration previously and knows how to solve.  She right clicks the grouped controls scrolls down to Layout and chooses ‘Remove’.  That’s not intuitive, now is it?  How about giving us a choice to ‘Remove Grouping’?  would that be so difficult?  Idiocy!!!!!!!

Posted in Access 2007 problems/solutions | No Comments »

Another Access 2007 Issue – Back Color values not working in code…

November 18th, 2008
I continue to be disappointed with Access 2007 from a Developer’s point of view.  Here is my most recent issue: 

I’m attempting to set the backcolor property of a text box control using the ‘On Format’ event property of a report’s detail section.  I’ve been doing this sort of stuff for 10 years.

I want the backcolor to be green.  When setting this property from the ‘…’ choice in properties, the resulting value is ‘#22B14C’.  Note that I’m totally surprised by such a bizarre value as I’ve only gotten Long Integer values in the past.

Well, when I go to code to set the backcolor value:  txtConditional.backcolor = #22B14C, I get an error indicating that value can’t begin with a ‘#’ sign.  So I go ahead and wrap the value in double quotes: “#22B14C”.  I don’t get an error in code.

I choose the ‘View’ choice in the upper left hand corner of the report’s design view and it previews successfully.  Some of my rows are green, and some are red.  However, when I go to ‘print preview’ (btw, what the heck is the ‘View’ choice for, then?) I get an error telling me that the “#22B14C” value is incorrect.

Well that’s just great!  So now, I’ve got to go Google and figure out how to properly set this value.

 Conclusion:  Microsoft changed everything around like item above, and didn’t test, and didn’t take into account the thousands of experienced Access Developers.  More to come!

Jack Jaeger

President Data Control, LLC and

15 year Access Veteran

Posted in Access 2007 problems/solutions | No Comments »

Working for UVA soon?

November 18th, 2008
Hey, just had my first meeting with a University of Virginia manager than needs an Access database built.  She’s got the process flow mapped out, the data structure mapped out, and now just needs the database built. 

I asked her how she ‘found’ us, and she said ‘Craigslist’.  Another CL win!

Posted in Business Opportunities | 1 Comment »

Newer Entries »