Check if an index value exists

[twgonder]

I've got a validation procedure that runs before update in forms.
It has some basic testing if a record exists, or doesn't exist, for a field that is a foreign key or nonduplicate using DLookup.

Recently, I've got a record save that needs to check for duplicates on a compound index (meaning it's constructed of more than one field).
At present it comes back as RTE 3022, but I would like to catch it in the generalize validation procedure.
Is there a programatic way to read an index for a table and see if an index value is there?

My search didn't turn up anything on this.


Thanks

[Welshgasman]

Why can't you do just a simple Dcount() of the fields in question?

[Bulzie]

Did you write you are already using DLookup on your 2 fields to see if you have a record already?

[twgonder]

Responding to both #2 & #3, I think.

I've got a table for the validations of all forms, so the procedure is generic code.
At the moment it checks just one field using DLookup, and not all the fields are indexed, as it may be against a small helper table.
Since there may be a future application that use 2, 3 or more indexed fields, it would be nice to just index those fields, and then do a test against the index.
So, the idea is to do a test somewhat like the DLookup to test for the existence of an index value (then an error msg about a duplicate), rather than creating n fields of possible validations that have to be anded.

I assume the index is an object that can be accessed, I just haven't seen an example of how to do it.

I don't expect anyone to understand this snippet without seeing all the code and validation table,
but I would be just adding a small test to this code for seeing if the index value exists:

  '* Test relational lookups


  If fValdt(ValCol, 13) <> "" And FldVal <> "" Then  'if lookup doesn't exist then error
    temp1 = "ID="
    If Left(fValdt(ValCol, 2), 2) = "Nu" Or fValdt(ValCol, 2) = "Cu" And IsNumeric(FldVal) Then temp1 = temp1 & Str(FldVal) Else temp1 = temp1 & fQt(FldVal)
    temp2 = DLookup(fValdt(ValCol, 14), fValdt(ValCol, 13), temp1) 'get a field from a foreign record
    temp3 = DLookup("Ps", fValdt(ValCol, 13), temp1) 'get processing status for the related record
    If IsNull(temp2) Or temp3 = 0 Or (fValdt(ValCol, 15) <> "" And fSaLocate(fValdt(ValCol, 15), CStr(Nz(temp2, ""))) = 0) Then
      pSp(2) = fValdt(ValCol, 13)
      sAddStatus Frm, fCntl, fTranltLang(TempVars!loLangID, "3_Ie51", "H", pSp), fCntl(6, 1), , , aValAll
      gError = nProc & "\51": GoTo ExitCd
    ElseIf fValdt(ValCol, 14) = "GrpN" Then
      temp2 = fSaExtract(fValdt(ValCol, 13), 3, "_")
      temp3 = DLookup("LvlN", fValdt(ValCol, 13), temp1)
      If temp2 = "Group" And temp3 <> 1 Then
        sAddStatus Frm, fCntl, fTranltLang(TempVars!loLangID, "3_Ie52", "H", pSp), fCntl(6, 1), , , aValAll
        gError = nProc & "\52": GoTo ExitCd
      End If
    End If
  End If

[CJ_London]

An index is an object stored within the indexes collection of a table. Only way to verify if a specific value is stored within that index is to use dlookup/count or sql

[Bulzie]

Yes not sure I understand why so complicated, do a Dlookup on whatever fields you use for the index that would make it a duplicate record and prompt them in code that there is a duplicate and to change one of the fields or if you can automatically change a value to make it not a duplicate and save the record? Look at BeforeInsert event I believe.

[twgonder]

Yes not sure I understand why so complicated, .

Well, the Access team did it at update time with an index when not allowing a duplicate compound index, and throwing RTE 3022. So I agree, it shouldn't be so complicated/difficult for us developers to catch the error before update.

[CJ_London]

Really not clear what you are suggesting - you can create a unique compound index. To pre check for duplicates in sql server you would use sql same as access.

in management studio you do have an alternative as documented here
https://www.mssqltips.com/sqlservert...nd-index-data/
I’ve never used it so no idea whether it would fulfill your needs but would appear it is still an action you would need to undertake before saving the data -so what would be the benefit?

Perhaps you are talking about the equivalent of using dbfailonerror for recordsets? But you still need to handle the error.

if you are talking about examining the index to see what fields it is based on so you then know how to create your checking method, that can be done. Away from my computer right now but guessing it would be something like

tabledefs(tableName).indexes(indexname).fields(i). name

And one other avenue to perhaps explore - the form error function - that can trap an error after a failed save.

[orange]

If it's a unique composite index it is unique by definition and managed by the the database management system (Access). If you made your composite index unique, then I don't see why further checking is needed. I am not a SQLServer person.

[twgonder]

For #8 & #9

I'll try a simple example:

Existing record
TableT, Record1
Field1 = "A"
Field2 = "X"
A composite index is made using Field1 and Field2, and this is set to No Duplicates.
"A" can be duplicated, as can "X", just not "A,X"

A user, using a form is about to save a new record with a duplicate of Field1 and Field2 (A,X).
A SAVE button triggers a save via Me.Dirty = False
Which then calls the Form_BeforeUpdate procedure, which then calls the validation procedure.
Here I want to test for the existence of the index, to make sure A,X isn't a duplicate, and display an error on a special error form.
The Me.Dirty, back a level in the procedure stack runs after the validation check, which then gets RTE 3022, which I can capture,
The problem is, it's too late to add the error message to the form that displayed validation errors.

There is a way to make the validation multivalued, and then check for the existence of all the indexed fields, but this would be a more complicated bit of code, and complicate the form that creates the validations as well.

What I would like to do is have a simple line of code that could build the index value, and test for an already existing index, in the validation routine.
The validation routine is all parameterized, for dozens of forms/tables/fields, so I can't do custom code for just this one form.

It seems that I have to go with the more complicated solution, of a multivalued parameter to test the fields together, unless I can get straight to testing an index value existence.


A RTE of 3022 happens, as far as I can tell, nothing happens in Form_Error.

[twgonder]

If it's a unique composite index it is unique by definition and managed by the the database management system (Access). If you made your composite index unique, then I don't see why further checking is needed. I am not a SQLServer person.

I've done the composite index. What I avoid in my forms is the cryptic, pop-up Access error messages that are meaningless to the users. I do this by trapping errors in my own generalized validation routine. This way I can also log the errors.

[CJ_London]

A RTE of 3022 happens, as far as I can tell, nothing happens in Form_Error.

Works for me - see this very simple example

[twgonder]

@#12 Different error, created a different way, not using Me.dirty = false and other procedures present.

Here's a list of procedures (below) that ran, if Form_Error had of run it would be in the list.



Also, I tried modifying your table, but I can't seem to get the composite index to index with no duplicates, as my table allows.

[twgonder]

Update on #13

It seems my Access updated today, when I didn't see it.
Some things, like the index properties appear different, hence my confusion.

Try this update to the Database29.accdb.
Run the form, change record three, Field 2 to "aaa", then press the SAVE button. For me Form_Error doesn't run, just a RTE.

[CJ_London]

so use an error handler such as

Private Sub Command9_Click()
  
  On Error GoTo errhandler
  Me.Dirty = False
  
errhandler:
  MsgBox "oops!" & Err.Number
  
  
End Sub

or

Private Sub Command9_Click()

   On Error resume next
   Me.Dirty = False
   if err<>0 then MsgBox "oops!" & Err.Number
  
End Sub