oOo.oOo.oOo.oOo.oOo.oOo

Another post on Sequential Numbering…

The most often asked question is about customizing Autonumber.

The answer is, don’t!  If you want to use a sequential numbering system Autonumber is not the Data Type to use.  Even if you want to include Autonumber with another Field to make a composite… don’t, it’s not always sequential.  (More on that later)  Leave Autonumber alone and use it for its intended purpose, a unique Primary Key for your databases internal logic that no one, besides you, sees.  And here’s why…

1. It skips

  • This is a biggie. Once you delete a record or *spend* it (create and then undo) that number is no longer available, it’s gone for good.

MissingNumbers

2. It’s not always sequential

Sometimes Access decides to assign a negative number. Autonumber is only guaranteed to be unique not sequential even if you select Increment under New Values at the Table level.

3. While you can set the initial value (The Access Web – Have Autonumber field start from a value different from 1 or Allen Browne’s Site Set AutoNumbers to start from) you cannot otherwise modify the number. The Autonumber Data Type is not editable and there is no way around that via Access. The only exception to this rule is if you upsized your Table to SQL Server in which case you can modify the number.

4. And, it does not always indicate the order in which records are entered, see Item 2.

OrderByID

So, now what?  Instead, create a separate field for your sequential/custom number/string, herein referred to as the Sequential ID.  A few tips before we get started…

  • Using the Nz() function ensures that the first record is handled correctly, i.e. first record of the year or first record in the table or first record in the group.
  • You will want to Save your Record immediately after the Sequential ID has been assigned. This becomes especially important in multi-user databases, so that the Sequential ID does not get *snatched* away from the User and/or your Users may receive the Error 3022, shown below, if two Users try to save at the same time.

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

You can implement your own Error Handling or Roger Carlson has some simple code to trap the error, see Domain Function Example: Simulate AutoNumber with DMax()

  • Have a back-up plan. When you *commit* your Sequential ID to the Table the Record is saved. However, if the User decides they really did not want to create a new Record you’ll want a way to back out that Sequential ID. You will also want a way to handle *spent* Sequential ID’s, i.e. the Record deleted was not the last record so now there is a gap in the sequence. To find those unused numbers you can use…

SELECT T.lLiftID+1 AS Missing
FROM tblLifts AS T LEFT JOIN tblLifts AS T1 ON T1.lLiftID=T.lLiftID+1
WHERE (((T1.lLiftID) Is Null));

   You will need to decide for yourself what you want to do with this *orphaned* numbers, let them go or allow your Users to use one of them when creating a new Record.

So, let’s create some Sequential ID’s…

A. Sequential ID number, pseudo Autonumber
B. Use a Command Button to assign a Sequential ID
C. Customize your Sequential ID

a. Sequential ID with Year prefixing starting fresh each New Year
b. Sequential ID with YYMMDD with an incremental number suffix
c. Sequential ID strings alpha-numeric or numeric alpha

D. Variations of Sequential ID’s

Side note:  While some of the methods here allow you to use Sequential ID as a Primary Key, however, it would not be my first choice.  That said the method described in A. is at the top of the list for preferred methods should you decide not to use the Autonumber Data Type but implement your own.  Remember, just because you can do so does not mean you should do so.  Evaluate your schema and decide what works best for you (and the Database) your Users are only going to see what you let them see.

Special note:  The sample code provide here does not provide any Error Handling which means you should implement your own.

A. Sequential ID number, pseudo Autonumber

With this example you can use the Sequential ID as a Primary Key by putting it in the Before_Insert of the Form, since the Before_Insert event fires when a User moves to a new Record you can increment and then Save locking your number in place. (Again, just because you can do thing does not mean you should do a thing… so, again, use this one as the one the User sees and maintain Autonumber with another field as the Primary Key Access uses.)

Private Sub Form_BeforeInsert(Cancel As Integer)

If Me.NewRecord Then
Nz(DMax(“YourIDField”,”YourTable”), 0) + 1
DoCmd.RunCommand acCmdSaveRecord
End If

End Sub

Note: You can also use the Before_Update1 event of the Form, and while, the Before_Insert event fires before the Before_Update event the Before_Update event is the only event that *writes to disk* upon firing.

Private Sub Form_BeforeUpdate(Cancel As Integer)

            If Me.NewRecord Then
Nz(DMax(“YourIDField”,”YourTable”), 0) + 1
DoCmd.RunCommand acCmdSaveRecord
End If

            End Sub

 

B. Use a Command Button to assign a Sequential Increment

When using this method you’ll want to *push* the User to a new Record and then create your Sequential ID. After all, we don’t them overwriting an existing Sequential ID. You can either do it automatically…

 

 Private Sub cmdAddNew_Click()

     If Dirty Then Exit Sub
DoCmd.GoToRecord , , acNewRec   ‘Force User to a New Record
Me.txtCoilID = Nz(DMax(“crCoilID”, “tblCoilReceiving”), 0) + 1
Me.cboCoilID = Me.txtCoilID

 End Sub

…or send them a message to go to a New Record if they are not already on one…

Private Sub cmdAddNew_Click()

    If Not IsNull(Me.txtCoilID) Then
‘Tell User to go to a New Record first
MsgBox “You MUST go to NEW record first!”, vbCritical, “Add New”
Exit Sub
End If

     Me.txtCoilID = Nz(DMax(“crCoilID”, “tblCoilReceiving”), 0) + 1
Me.cboCoilID = Me.txtCoilID

 End Sub

C. Customize your Sequential ID

a. Sequential ID with Year prefixing starting fresh each New Year

GetMyID

This example will only increment up to 99999, see GetMyID!  If you plan to use this method you must be careful you will not have more than 99,999 records per year or, if you want to go higher, you can expand on the spaces (placeholders), however, you still need to take note of *how many* you allot when using this example or this might happen…

1400000
1400001
etc…
1499999

…go to add a new Receiving ID and you get…

1400000

…and an Error Message because at the end it will *start again*!  So, let’s avoid code that locks you into a maximum record count unless that is what you mean to and have an end game, like starting a new unique sequence.

Step 1

Copy/paste the below in a Module then Debug > Compile.  Be sure to change the name of the Fields and Table below to match your own.

Function GetMyID() As Long
GetMyID = Format(Date, “yy”) & Format(Nz(DMax(“rSequenceID”, “tblReceiving”, “Year([rReceiveDate]) = ” & Year(Date)), 0) + 1, “00000”)
End Function

 Step 2

Create your Command Button and the copy/paste the lines between Private Sub… and End Sub in the Event Procedure of your Command Button.  There are two procedures listed below, one with validation and one without.  You only need to copy/paste the one you want to use not both.  Again, be sure to change the name of the Fields and Table below to match your own.

With validation…

Private Sub cmdReceive_Click()

 ‘Inform User they are not on a New Record
If Not IsNull(Me.txtReceiveID) Then
MsgBox “You MUST go to NEW record first!”, vbCritical, “Receive”
Exit Sub
End If

 ‘Validate that all other required Controls are filled in prior to creating Sequential ID.
If sfrValidateData Then
Me.rSequenceID = Nz(DMax(“rSequenceID”, “tblReceiving”, “Year([rReceiveDate]) = ” & Year(Date)), 0) + 1
DoEvents
Me. txtReceiveID = GetMyID()

DoCmd.RunCommand acCmdSaveRecord
‘Requery the Combo Box to exclude the newly received item from the Combo Box which shows items not yet received
Me.cboResults.Requery
Else
MsgBox “Oops, no Receive ID created, please retry!”
End If

End Sub

OR if no validation is needed…

Without validation…

Private Sub cmdReceive_Click()

‘Inform User they are not on a New Record
If Not IsNull(Me.txtReceiveID) Then
MsgBox “You MUST go to NEW record first!”, vbCritical, “Receive”
Exit Sub
End If

Me.rSequenceID = Nz(DMax(“rSequenceID”, “tblReceiving”, “Year([rReceiveDate]) = ” & Year(Date)), 0) + 1
DoEvents
Me. txtReceiveID = GetMyID()
DoCmd.RunCommand acCmdSaveRecord
‘Requery the Combo Box to exclude the newly received item
Me.cboResults.Requery

 End Sub

 

b. Sequential ID with YYMMDD with an incremental number suffix SequentialYYMMDD

In this example you can easily tell how many Quotes you got on any particular day and still maintain a unique ID.  This type of Sequential ID can be useful for tracking sales or sales per Associate/Employee.  You can query the table to check how your business or Sales Staff are doing.  One caveat of doing it this way is you will to determine what part is which because you have not formatted two digits for Months and Days.  The second example does this and makes it much easier to determine when the Quote was actually created no matter what create date is entered.

Example One
Step 1

Create a query to count records each day to use in the Function, i.e.

qryCountOfQuotes

SELECT Left([qQuoteID],InStr([qQuoteID],”-“)-1) AS Quotes, Count(Left([qQuoteID],InStr([qQuoteID],”-“)-1)) AS CountOfQuotes FROM tblQuote GROUP BY Left([qQuoteID],InStr([qQuoteID],”-“)-1);

Be sure to change the name of the Fields and Table below to match your own.

Step 2

Create your Command Button and the copy/paste the lines between Private Sub… and End Sub in the Event Procedure of your Command Button.  There are two procedures listed below, one no space holders for double digit Months and Days, Example One and; one with spaces holders for Months and Days, Example Two.  The Year is double digit for both examples.  You only need to copy/paste the one you want to use not both.  Again, be sure to change the name of the Fields and Table below to match your own.

Private Sub cmdAddNew_Click()

            ‘Push User to a New Record
DoCmd.GoToRecord , , acNewRec
Me.txtQuoteID = Format(Date, “yy”) & Format(Date, “m”) & Format(Date, “d”) & “-” & Nz(DLookup(“CountOfQuotes”, “qryCountOfQuotes“, “Quotes = ” & Format(Date, “yy”) & Format(Date, “m”) & Format(Date, “d”)), 0) + 1
Me.cboQuoteID = Me.txtQuoteID

 End Sub

OR

Example TwoqryCountOfQuotes

 Private Sub cmdAddNew_Click()

 Me.txtQuoteID = Format(Date, “yymmdd”) & “-” & Nz(DLookup(“CountOfQuotes”, “qryCountOfQuotes“, “Quotes = ” & Format(Date, “yymmdd”), 0) + 1

End Sub

c. Sequential ID strings alpha-numeric or numeric alpha

Here are two examples using incremental letters; the first will be using an YYDDMM format implementing the letter at the end. The second example I’m using Property Cards which, in this case, only have a letter if there is more than one card. The Property already has a Parcel Number, so no incrementing of the Sequential ID needed.

Example one140725A
 Step 1

Create a query which will be used to create the Sequential ID

 

Me.txtQuoteID = Format(Date,”yymmdd”) & Chr(Nz(DMax(“Asc(Right(qQuoteID,1)),”,”tblQuote”,”Left(qQuoteID,6) = “”” & Format(Date,”yymmdd”) & “”””),64)+1)

 

Step 2

Create your Command Button and the copy/paste the above line between Private Sub… and End Sub in the Event Procedure of your Command Button.  You only need to copy/paste the one you want to use.  Again, be sure to change the name of the Fields and Table below to match your own.

OR

Example two

123456789B

Step 1

Create your Command Button and the copy/paste the lines between Private Sub… and End Sub in the Event Procedure of your Command Button.  You only need to copy/paste the one you want to use.  Again, be sure to change the name of the Fields and Table below to match your own.

Because this is a Property Card and 90% of the information will be duplicated, I am taking what I need from the first card and putting it on the next card.  If this is the first time it’s happening, the only card, I first need to add the *A* and then create the new card.

Public Sub cmdDuplicate_Click()

    Dim intRecCount As Integer
Dim db As DAO.Database
Set db = DBEngine(0)(0)

‘We need to know how many Property Cards there are to determine if we are starting a *A* or some other letter
intRecCount = DCount(“ppParcelNumber”, “tblPropertyProfile”, “[ppParcelNumber] = ‘” & Me![txtParcelNumber] & “‘”) – 1
If Me.Dirty Then
Me.Dirty = False
End If
If Me.NewRecord Then
MsgBox “Select an Property Card to duplicate!”, vbInformation, “Duplicate”
Else

‘If there is only one Property Card assign it an *A*
If intRecCount = 0 Then
Me.cboFormID = “A”
End If

        ‘Duplicate the main record
With Me.RecordsetClone
.AddNew
!ppOwner = Me.txtOwner
!ppAddress = Me.txtAddress
!ppLegal = Me.txtLegal
!ppDTEID = Me.cboDTEID
!ppPropertyClassificationID = Me.cboPropertyClassificationID

‘Start the lettering from *B* on.
!ppFormID = Chr(Asc(“B”) + intRecCount)
!ppParcelNumber = Me.txtParcelNumber
!ppCountyFIPSID = Me.cboCountyFIPSID
.Update
.Bookmark = .LastModified
Me.Bookmark = .LastModified
Me.cboParcelNumber = Me.txtPropertyID
Me.cboParcelNumber.Requery
End With
End If
Set db = Nothing

End Sub

If you just want to increment letters you can use the below.  Note, when using the alphabet you can only go up to *Z* unless you use a custom VBA procedure.  See the link below for one such custom procedure.

To Increment Letters…

    If IsNull(Me.txtRevisionID) Then
Me.txtRevisionID = “A”
Else
Me.txtRevisionID = Chr(Asc(Me.txtRevisionID) + 1)
End If

To increment beyond the single letter click here.

D. Variations

I.
T
his method grabs the first three letters of the Company Name. The Company name used in this example is Access Diva. No worries though, if you have two Companies that have the same first three letters it will increment the numeric side to keep the ID unique. This only becomes a problem if you have more than 9,999 Companies that have the identical first three letters.

ACC0001

Dim lngCountIDs As Long
‘Find how many Companies start with the same three letters and count plus one
lngCountIDs = Nz(DCount(“Right(cCompanyID,3),”, “tblCompanies”, “Left(cCompanyID,3) = ‘” & Left(Me.txtCompanyName, 3) & “‘”), 0) + 1

Me.txtCompanyID = StrConv(Trim(Left(Me.txtCompanyName, 3)), vbUpperCase) & Format(lngCountIDs, “0000”)
DoCmd.RunCommand acCmdSaveRecord

 You can change how many letters you want use as the Prefix by changing all the 3’s to how ever many letters you want as the Prefix.

II.
Always want it to start with specific letter(s)…  This method starts every Sequential ID with the same letters.  Ultimately, you could have a table that determines the Prefix if you want to *tag* which Department, Section or Index Key you assigned the number to.

KB

Me.txtFFESpecification = “KB” & Nz(DMax(“ffeSpecificationID”, “tblFFESpecifications”), 0) + 1

…using a Department, Section or Index Key as a Prefix…

BR

Me.txtFFESpecificationID = Me.cboIndexKeyID.Column(1) & Nz(DMax(“ffeSpecificationID”, “tblFFESpecifications”), 0) + 1

…to add a hyphen… BRwithHyphen

Me.txtFFESpecificationID = Me.cboIndexKeyID.Column(1) & “-“ & Nz(DMax(“ffeSpecificationID”, “tblFFESpecifications”), 0) + 1

To use any of the above create your Command Button and the copy/paste the lines between Private Sub… and End Sub in the Event Procedure of your Command Button.  You only need to copy/paste the one you want to use.  Again, be sure to change the name of the Fields and Table below to match your own.

III.
Only want to go to a specific number…

Private Sub Form_BeforeInsert(Cancel as Integer)

Dim iNext As Integer iNext = Nz(DMax(“[ID]“, “[tablename]“)) + 1 If iNext >= 10000 Then
Cancel = True
MsgBox “All ID’s have been used, shut off the PC and go home”, vbOKOnly
Else    Me![ID] = iNext
End If

End Sub

IV.
Create a Function for use with more than one table…

Function lngSequentialID(strID As String, strTable As String) As Long

   lngSequentialID = Nz(DMax(strID, strTable), 0) + 1

End Function

To use copy/paste the above lines into a Module.  To use…

lngSequentialID(“YourFieldName”, “YourTableName”)

 

So, when do those Form Event fire?

Creating a new record
When you move the focus to a new (blank) record on a form and then create a new record by typing in a control, the following sequence of events occurs:

Current (form) → Enter (control) → GotFocus (control) → BeforeInsert (form) → AfterInsert (form)

The BeforeUpdate and AfterUpdate events for the controls on the form and for the new record occur after the BeforeInsert event and before the AfterInsert event.

From: Order of Events for Database Objects

 

More sites about Sequential Numbering…
Sequential Numbering by Scott Diamond http://scottgem.wordpress.com/2009/11/25/sequential-numbering/
Tech on the Net http://www.techonthenet.com/access/modules/sequential_nbr4.php
FMS http://www.fmsinc.com/microsoftaccess/autonumber%20field/creating.asp

114 total views, no views today

Share Button

Filtering Forms…

Here are three ways to filter records on a Single Form or a Continuous Form. (In these examples no Subforms were used.)

Example 1
To toggle back and forth between (Yes/No Data Type) All and Completed (Tasks, Projects, Orders, etc.)…

  • Step 1

In the Header of you Form place an UNBOUND Check Box and name it chkMyFilter

  • Step 2

In the After_Update event of the UNBOUND Check Box copy/paste everything between the Private Sub and End Sub lines below. After you have copied it change pDone to the name of your Yes/No field in your Table, not the name of the field Control name on the Form.

‘Private Sub chkMyFilter_AfterUpdate()

    If Me.chkMyFilter = True Then
       Me.Filter = “pDone = ” & vbTrue
       Me.FilterOn = True
   Else
      Me.Filter = “”
       Me.FilterOn = False
   End If

‘End Sub

Close and save (or save and then close) and try it out.

 

Example 2
In this example you can use values from a look-up table to filter your records. This allows a lot more flexibility because I can add/edit and/or subtract items from the list via the table. My table is…

tblSources

  • Step 1

In the Header of you Form place an UNBOUND Combo Box and name it cboSSourceID. (The double S is not a mistake, it is because in the Detail section of my Form I have a Combo Box named cboSourceID, so, I put an additional S to indicate this is my cbo*Search*SourceID used to Filter my records.

  • Step 2

Set the Row Source of cboSSourceID the same as the Combo Box in the Detail section of your Form. For this example I used…

SELECT tblSources.sSourceID, tblSources.sSource
FROM tblSources
WHERE (((tblSources.sActive)=True))
ORDER BY tblSources.sSortOrder;

Other settings:

Column Count =2
Bound Column = 1
Column Widths – 0″;1″

  • Step 3

In the After_Update event of the UNBOUND Combo Box copy/paste everything between the Private Sub and End Sub lines below. After you have copied it change pSourceID to the field name in your Table, not the name of the field control name on the Form and in your Table and change the cboSSourceID to the name of your UNBOUND Combo Box in the Header.

‘Private Sub cboSSourceID_AfterUpdate()

   Me.Filter = “[pSourceID] = ” & Me.cboSSourceID
   Me.FilterOn = True

‘End Sub

    • Step 3a

To clear the Filter you can use the Double_Click of cboSSourceID

 Private Sub cboSSourceID_DblClick(Cancel As Integer)

   Me.Filter = “”
   Me.FilterOn = False
   Me.cboSSourceID = “”

End Sub

…or add the above code a Clear Filter Command Button.

Close and save (or save and then close) and try it out.

 

Example 3
This example allows you to create a Query as much or as little criteria as you like for the filter. You can use one Command Button to toggle back and forth between All/Filtered, for this example I am using one Command Button using the Caption property of the Command Button to toggle back and forth.

  • Step 1

Create two Record Sources, one that will show all the Records, such as the Table and another that shows just the filtered Records such as a Query, i.e.:

tblPending for all the Records
qryPending for the filtered Records.

qryPending
SELECT tblPending.pPendingID, tblPending.pActionID, tblPending.pQuantity, tblPending. pReceiveDate, etc…
FROM tblPending
WHERE (((tblPending.pActionID) Is Null) AND ((tblPending.pReceiveDate) Is Null));

  • Step 2

Set the Record Source of your Form using the *filtered* query. In this example that would be qryPending. (If you want to open to showing ALL records you will need to reverse the code!)

  • Step 3

ShowAllFiltered

Place a Command Button in your Forms Header and name it cmdShowAll. If the Wizard pops up, just press Cancel, we are going to put our own code there so we won’t be using any of the Wizards code. Change the Caption property to say “SHOW ALL”. For my Command button I used these settings…

Font Name= Tahoma
Font Size = 7
Font Weight = Bold

Side note:  The buttons will change Caption depending on which Record Source is showing, which is why you only need to add one Command Button.  Make sure you follow Step 4 and Step 5 for the Caption to toggle properly.

  • Step 4

At the top of your Forms Module under…

Option Compare Database
Option Explicit

Side note: If those two lines are not at the top of your Module put them there, particularly, Option Explicit which will *stop* when it finds an undeclared variable(s).

Mine says SHOW ALL or COMPLETED ONLY but yours can say whatever you want. Just remember to make sure the Command Button is wide enough to show the longest Caption.

…place these two lines 

Const ShowAll = “SHOW ALL”
Const ShowFiltered = “COMPLETED ONLY”

  • Step 5

In the On_Click event of the Command Button copy/paste everything between the Private Sub and End Sub lines below.

‘Private Sub cmdShowAll_Click()
On Error Resume Next

   Select Case Me.cmdShowAll.Caption
       Case ShowFiltered
           Me.RecordSource = “qryPending”
           Me.cmdShowAll.Caption = ShowAll
       Case ShowAll
           Me.RecordSource = “tblPending”
           Me.cmdShowAll.Caption = ShowFiltered
   End Select

‘End Sub

Close and save (or save and then close) and try it out.

 

Here are more filtering examples…

Allen Browne’s Search Form
Allen Browne’s Filter a Form on a Field in a Subform

118 total views, 2 views today

Share Button

Excel Tutorials for FREE…

 

EasyExcelA new link has been added to the Microsoft Excel Tips links on the left.  However, after visiting the site…

I have to say, this has to be one of best websites dedicated to Microsoft Excel that I have come across in a long time.  Hmm, maybe I should rephrase that…  This has to be one of best websites dedicated to Microsoft Excel that has come across me by way of Niels Weterings (big *THANK YOU* for dropping me a line).  I am truly impressed with the clear, concise instructions that made even Pivot Tables look easy!

Never mind that it’s FREE, it’s a great site for beginners right on up to advanced Users.  You get sample code, clear definitions and, in some cases, downloadable sample files.  Truly, a great site!

142 total views, no views today

Share Button

Conditional Row Shading on a Continuous Form

Row Shading has come a long way since Access 2003 where you had to use code to get it to happen. Remember this… 

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

   If bluebar Then
       Detail.BackColor = 16777215
   Else
       Detail.BackColor = 13888992
   End If

bluebar = Not (bluebar)

End Sub

Then came Access 2007 and we got Alternate Back Color…

Property Sheet Alternate Back Color

Property Sheet Alternate Back Color

While that was way cool, what was still missing was a way to conditionally shade a row. Here’s a way to do just that with no VBA…

Step 1

Note: In this example the Control names are chkInStock, a Check Box and txtInStock, an unbound Text Box.

Identify a Control (*trigger*) that will let Access know you want to highlight that row. For this example we’re using the Check Box, chkInStock.  Then in your unbound Text Box and type… 

=IIf([chkInStock]=True,”████████████████████████████████████████████████████████████”,””)

Stretch the Control to the full length of the Row, set the Font Size to insure it goes the Height of the Row and select the color of the Font for your Control (this is what determines the color of the highlighting).  In this example I used #F6FBDD.

Row Shading (Design Mode)

Row Shading (Design Mode)

Side note: For Bar (Full *Black* Block) use *Arial U + 2588 Full Block*.  To insert the Full Block…

   a.  Open your Windows Character Map, found under Accessories > System Tools and select the Arial Font from the Font drop down.  

Character Map

Character Map

   b. Once selected click Copy and then navigate back to Access

Character Map (Copy)

Character Map (Copy)

   c. In Access, paste the Full Block within your Statement on the Control Source line… 

Property Sheet Control Source

Property Sheet Control Source

Step 2

You can set your Controls to anything you like, however, I would recommend you give your Controls some sort of Border.  This will not only help ditinguish Columns but also Rows.  In this example all the Controls in the Row are set to… 

Back Style:  Transparent
Back Color:  No Color
Border Width:  Hairline
Border Color:  #C0C0C0
Special Effect:  Etched

Step 3 (Optional)

In this example the Detail section of the Form has been set to alternate colors, you can choose whatever option suits your database.  To set the Rows in the Detail section to alternate colors, in the Properties window for the Detail section set the…

Back Color:  Background 1 (or White)
Alternate Back Color:  #F5F4F1

Step 4

Save and you’re all done.  Now, when you select/deselect the In Stock Check Box the row will toggle according. Enjoy!

Download the sample file here.

Row Shading

Row Shading

 

326 total views, no views today

Share Button

Compile on Demand check or uncheck?

CompileOnDemand

Recently, while trying to Debug some code a colleague of mine suggested I leave Compile on Demand1 unchecked.  Since in all my years of developing I have always left it checked, I asked why and here’s what he said got…

Many years ago, Michael Kaplan mentioned that this setting was known to cause problems, at a Pacific NW Access Developer’s Group meeting.

Here is how having this Option checked can bite you in the patooka’s!  Suppose you have unfinished code, or some other compile error, in code that you are not currently using. You forget to do a final Debug > Compile, to force all code to Compile. You deliver your app. to your customer. They immediately get a Run-Time error, with the same unused code. Why?  Because this setting does not “travel” with your database; it is a global VBA environment setting. With this Option unchecked, ALL code must be compilable before any code can be run. Checking this option (the stupid default setting), allows for partial compliable; in other words, only the Modules that have code that you are running must be compilable. You could still have that experimental new module with code that does not Compile. Personally, I’d rather find these issues myself, instead of having a customer find them.  <smile>

Additional comments here…  Say it aint’s so, Joe.

Now, with that being said, I am still leaving mine checked (and I am not alone), not because I like to skirt danger but because I Compile my code if I just look at it.  (Though nothing to do if you make no change, I check anyway.)  So, the choice is up to up but I would heed the advice if you are not *religious* about Compiling your code!

3,970 total views, 19 views today

Share Button