Increasing the number of indexed fields with no duplicates

I have a large table that checks lots of fields to make sure duplicate
entries are not present but Access seems to limit the number of these. Is
there a way to increase the number of fields? Without the no duplicates
indexes the problem doesn't get solved well.

The maximum number of indexes on a table is 32 and you cannot change this.

One index gets used up for every relationship you set on the table.

You choices may be
-- to redesign your table structure
-- use code to check the values when you enter data via a form
-- live with the limitation

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

McLaren wrote:
I have a large table that checks lots of fields to make sure duplicate
entries are not present but Access seems to limit the number of these. Is
there a way to increase the number of fields? Without the no duplicates
indexes the problem doesn't get solved well.

To expand a bit on what John wrote, the form's After Update event is
generally the place to perform data validation.

Here is something I copied from a posting by Albert Kallal. I didn't note
the date or newsgroup of the posting. I have used this code to good effect.
Note that you can customize the error message (the second item within the
quotes) to whatever extent you want. You can even have the full message
there, and leave out "is required" in the message box, if you like.

***************

I use the following "general" code routine to give custom messages for
fields that are not filled out.

The code below is a great way to verify fields that you want to be requited.

Another nice feature is that after the given message, the cursor (focus)
moves to the field in question.

The code is used as follows:

in the forms before update event..you go:

Cancel = MyVerify.

And, then the two following routines need be put into the forms module. You
can see how in the first example, you just put in the list of controls that
you want requited, and also the text "error" message to display. Note
carefully how the full string is enclosed in quotes.

This routine is called in the forms Load event:

Private Function MyVerify() As Boolean

Dim colFields As New Collection

MyVerify = False

colFields.Add "TourDate,Tour date"
colFields.Add "Description,Description"
colFields.Add "City,City"
colFields.Add "cboProvince,Province"
colFields.Add "StartDate,Start date"
colFields.Add "EndDate,end date"

MyVerify = vfields(colFields)


End Function

Private Function vfields(colFields As Collection) As Boolean

Dim strErrorText As String
Dim strControl As String
Dim i As Integer

vfields = False

For i = 1 To colFields.Count
strControl = Split(colFields(i), ",")(0)
strErrorText = Split(colFields(i), ",")(1)
If IsNull(Me(strControl)) = True Then

MsgBox strErrorText & " is required", vbExclamation, AppName
Me(strControl).SetFocus
vfields = True
Exit Function
End If
Next i


End Function


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada



***************************



Here is another one. I don't recall if I have tried this one, but I don't
think so:



*****************************

Here is a function I use routinely to check for required fields:

Public Function fm_CheckRequiredFields(f As Form, FieldList As Variant, _
Optional NormalColour As Long = vbWhite, _
Optional HighlightColour As Long = &H60FFFF) As Integer
Dim iFirstTab As Integer, sFirstTab As String
Dim c As Control, i As Integer, iBadFields As Integer
For i = LBound(FieldList) To UBound(FieldList)
Set c = f.Controls(FieldList(i))
If IsNull(c) Then
If iBadFields = 0 Or c.TabIndex iFirstTab Then
iFirstTab = c.TabIndex
sFirstTab = c.Name
End If
iBadFields = iBadFields + 1
c.BackColor = HighlightColour
Else
c.BackColor = NormalColour
End If
Next
If iBadFields Then
f.Controls(sFirstTab).SetFocus
fm_CheckRequiredFields = iBadFields
End If
End Function

You pass it the current form object (Me) and an array of control names. It
checks each of the controls for null. If a control is OK (not null) it sets
its BackColor to NormalColour (default white), otherwise it sets it to
HighlightColour (default pale yellow). It sets focus to the first (if any)
of the invalid (null) controls according to the tab order of the form, and
then returns the number of invalid controls.

So, you can say something like this:
Select Case Me.Openargs
case 1 ' recommendation
Cancel = fm_CheckRequiredFields( Me, _
Array( "Field1", "Field2", "Field3")
case 2 ' response
Cancel = fm_CheckRequiredFields( Me, _
Array( "Field4", "Field5", "Field6")
... etc
End Select
If Cancel then
If msgbox( "Input is required in the highlighted fields. Click OK
to fix " _
& "this, or Cancel to undo all your changes and close the
form", _
vbOkCancel ) = vbCancel Then
Cancel = false
Me.Undo
DoCmd.Close acForm, Me.name
End If
End If

From Graham Mandeno, microsoft.public.access posting 12-May-2005

***********************

"McLaren" wrote in message
...
I have a large table that checks lots of fields to make sure duplicate
entries are not present but Access seems to limit the number of these. Is
there a way to increase the number of fields? Without the no duplicates
indexes the problem doesn't get solved well.

Hi Bruce

"BruceM" wrote in message
...
To expand a bit on what John wrote, the form's After Update event is
generally the place to perform data validation.

Didn't you mean the form's BeforeUpdate event?
--
Cheers :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

John Spencer wrote:

The maximum number of indexes on a table is 32 and you cannot change this.

One index gets used up for every relationship you set on the table.

Other indexes get auto created if the field ends in ID and a few other
extensions set in options.

However other indexes get created which aren't visible via VBA/DAO
code. I just came across an example yesterday where the client had
only 13 indexes but it said there weren't any available indexes.

Actually only 8 indexes were available on the table design view
indexes screen. An additional five were the relationship auto created
indexes.

So I'm not sure what's all happening in behind the scenes.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

Oops.

"Graham Mandeno" wrote in message
...
Hi Bruce

"BruceM" wrote in message
...
To expand a bit on what John wrote, the form's After Update event is
generally the place to perform data validation.

Didn't you mean the form's BeforeUpdate event?
--
Cheers :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

well if you don't know whats going on behind the scenes- then maybe
you should move to a database platform that allows you to manage your
indexes in a reliable manner.

I don't think that I have used the GUI to write an index in the past
decade-- I do it only with code

create index IX_tblName_FieldName
on tblName (FieldName)




On Dec 15, 7:07*pm, "Tony Toews [MVP]" wrote:
John Spencer wrote:
The maximum number of indexes on a table is 32 and you cannot change this.

One index gets used up for every relationship you set on the table.

Other indexes get auto created if the field ends in ID and a few other
extensions set in options.

However other indexes get created which aren't visible via VBA/DAO
code. *I just came across an example yesterday where the client had
only 13 indexes but it said there weren't any available indexes. *

Actually only 8 indexes were available on the table design view
indexes screen. *An additional five were the relationship auto created
indexes.

So I'm not sure what's all happening in behind the scenes.

Tony
--
Tony Toews, Microsoft Access MVP
* *Please respond only in the newsgroups so that others can
read the entire thread of messages.
* *Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm
* *Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/