If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Increasing the number of indexed fields with no duplicates
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. |
#3
|
|||
|
|||
Increasing the number of indexed fields with no duplicates
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. |
#4
|
|||
|
|||
Increasing the number of indexed fields with no duplicates
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 |
#5
|
|||
|
|||
Increasing the number of indexed fields with no duplicates
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/ |
#6
|
|||
|
|||
Increasing the number of indexed fields with no duplicates
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 |
#7
|
|||
|
|||
Increasing the number of indexed fields with no duplicates
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/ |
Thread Tools | |
Display Modes | |
|
|