DLookup and record pointers

[liddlem]

Hi There
When a user adds a new record (or edits an existing one) I generate a checksum value (based upon a number of fields) and use it in the 'Before Update' event to check that a duplicate record is not being created.

If the Checksum already exists, then I want to undo the addition/update and take the user to the existing record.

<code>
If MyCheck <> "" Then
MyRecNo = DLookup("ID_CMTY_Address", "QRY_ADDRESS_Maintenance", "AddrChecksum = '" & MyCheck & "'") ' I get an error here

If MyRecNo > 0 Then 'Checksum already exists, therefore this is a duplicate record.
MyMsg = MsgBox("It seems that this address already exists." & vbCr _
& "the changes that you have made will be undone." & vbCr _
& "Do you wish to go to the existing record?", vbYesNo, "ADDRESS EXISTS")
Me.Undo
Cancel = True
If MyMsg = 7 Then
DoCmd.GoToRecord acDataForm, "Frm_Admin_Address", acGoTo, MyRecNo
End If
End If
</code>

I'm getting an error on the DLOOKUP line - I suspect that its because the checksum contains +,-,/ and = signs in it, but I am encasing this in single quotes.
The form is bound to "QRY_ADDRESS_Maintenance"
Can you see that I have missed something?

The next part of the problem related to the "DoCmd.GoToRecord"
1. Surely I want to move to the record pointer, not necessarily the record id (ID_CMTY_Address)?
So how do I DLookup the record pointer and what DoCmd do I need to use?
2. The user is able to filter the form - How will this affect the above?

 

[The_Doc_Man]

What error is being reported?

The checksum could contain those characters inside quotes and might still work OK. Can it ever be other characters? Like apostrophe or double-quote? Can it ever be an ampersand? Or worse - two ampersands in a row?

I could write this out but I have a quicker and dirtier way to get you started. Build a combo box with wizards enabled. One of the options is to build a record-finder. So... build a record-finder. NOW customize the code that the wizard just built for you so that you do your GoToRecord based on your goals. And your surmise in question #1 is correct, you want to move to the record pointer, though you actually call it a recordset.Bookmark in this context.

As to how a filtered form affects this? A form can see its own filter, and that filter resembles a WHERE clause without the word WHERE. You could COPY that filter and use it constructively in the record-finder code I just told you how to build.

 

[Minty]

How are you obtaining the value for MyCheck ? What error are you getting.

Having read the other post about the reasoning for this, have you checked how many hits you will get with this method. I suspect it will be minimal, as you are probably comparing things in a far too granular method.

 

[static]

Database tables use indexes. Indexes can span multiple columns and ensure the combined values are unique.

You can either run SQL like

ALTER TABLE MyTable ADD CONSTRAINT MyIndex UNIQUE(field1, field2,. . . fieldn)

Or, open the table in design view, select Indexes
Type the name of your index, ie, UniqueAddress and select the first unique field.
With that line still selected you should see 3 options at the bottom of the window. Set Unique to Yes.
Add your other fields under the first.

If the same values are entered for those fields an error will be raised that you can catch in your code.

 

[missinglinq]

Not familiar with using Checksums, at least in this manner, but is AddrChecksum a Numeric value?

"AddrChecksum = '" & MyCheck & "'"

is only valid syntax if AddrChecksum is defined as Text.

For a Numeric value it would simply be

"AddrChecksum = " & MyCheck

Linq ;0)>

 

[liddlem]

Thanks Doc_Man

What error is being reported?

It seems that I just needed to close and re-open the db to refresh it. It was doing strange things all around.

The checksum could contain those characters inside quotes and might still work OK. Can it ever be other characters? Like apostrophe or double-quote? Can it ever be an ampersand? Or worse - two ampersands in a row?

Good point - I hadn't thought about this. there are no apostrophe's or double ampersands, all end in a double equals sign. I will strip that out of the checksum at runtime.

I could write this out but I have a quicker and dirtier way to get you started. Build a combo box with wizards enabled. One of the options is to build a record-finder. So... build a record-finder. NOW customize the code that the wizard just built for you so that you do your GoToRecord based on your goals. And your surmise in question #1 is correct, you want to move to the record pointer, though you actually call it a recordset.Bookmark in this context.

I haven't had a chance to check this yet - Its still early Monday morning for me.

As to how a filtered form affects this? A form can see its own filter, and that filter resembles a WHERE clause without the word WHERE. You could COPY that filter and use it constructively in the record-finder code I just told you how to build.

Ah - nice to know.

Thanks for the asssitance.