Prevent (semi-) duplicate entry to table by form (1 Viewer)

[Scaniafan]

Hello all,

I'm not sure if this should be posted in the VBA section, but I'll start off here.

I've got the following parts in my DB:

- Master table with all possible reason codes
- Table with shipment number / reason code / comment
- Form to enter new data in to the second table

However, what I am aiming for is the following

- A shipment number can occur multiple times, if the first two digits of the reason code is not identical. For example:

1234567 | LD****
1234567 | LC****
1234567 | CM****

should be allowed, however:

1234567 | LDCF**
1234567 | LDFM**
1234567 | CM****

should not be allowed. Background to this is that each stage of a shipment (indicated by the first two digits) can only have one reason code connected to it.

Most optimal solution would be a message box when a duplicate is entered, which notifies the user that a reason code is being entered which already exists, and if the existing record should be overwritten yes/no

"yes" would lead to a deletion of the existing record and saving the new entry, "no" would cancel the entry.

 

[bob fitz]

Not sure that I understand completely but it sound to me as though you just need a little bit of validation code in the BeforeUpdate event of the control in which the reason code is entered. I would use Left() to get the first two letters and DCount() with Shipment Number as criteria to test if any records already exist.

 

[Scaniafan]

Ok, I have created something that works when I run it in a select query, however transferring it to the BeforeUpdate event results in the following run-time error:

Run-time error '2465':

Microsoft Access can't find the field '|1' reffered to in your expression.

The test code I've build is:

If DCount("Shipment Number", "TBL_Master_Reason_Code_Per_Shipment", [TBL_Master_Reason_Code_Per_Shipment]![Shipment Number] = Me.ShipmentNumber And Left([TBL_Master_Reason_Code_Per_Shipment]![Reasoncode], 2) = Left(Me.Reasoncode, 2)) > 0 Then

MsgBox "Existing"

Else

MsgBox "Not existing"

End If

I can't find the reason why this error should occur... Maybe someone here?

 

[bob fitz]

Try:

If DCount("Shipment Number", "TBL_Master_Reason_Code_Per_Shipment", "[TBL_Master_Reason_Code_Per_Shipment]![Shipment Number] = Me.ShipmentNumber And & "'Left([TBL_Master_Reason_Code_Per_Shipment]![Reasoncode], 2) = Left(Me.Reasoncode, 2)'" & ")" > 0 Then

 

[Minty]

Try

If DCount("Shipment Number", "TBL_Master_Reason_Code_Per_Shipment", "[Shipment Number] = " & Me.ShipmentNumber & " And Left([Reasoncode], 2) = Left('" & Me.Reasoncode & "', 2)) > 0 Then

 

[Scaniafan]

Try:

If DCount("Shipment Number", "TBL_Master_Reason_Code_Per_Shipment", "[TBL_Master_Reason_Code_Per_Shipment]![Shipment Number] = Me.ShipmentNumber And & "'Left([TBL_Master_Reason_Code_Per_Shipment]![Reasoncode], 2) = Left(Me.Reasoncode, 2)'" & ")" > 0 Then

Based on above, I still get the same error message.

Based on the code of Minty I keep getting an "Expected: list seperator or)" error. I count 9 x " in the code, so it looks to me that a part is not closed, however I can't seem to find where...:banghead:

If DCount("Shipment Number", "TBL_Master_Reason_Code_Per_Shipment", "[Shipment Number] = " & Me.ShipmentNumber & " And Left([Reasoncode], 2) = Left('" & Me.Reasoncode & "', 2)) > 0 Then

If I retype the code, the yellow bar that pops up when you are entering an expression keeps disappearing when I close the DCount expression, until I put the " after Me.ShipmentNumber &

If I put these in place, the yellow bar stays in place with the criteria part of the expression bold.

 

[bob fitz]

If [Shipment Number] is a text type field you may need something like:

If DCount("Shipment Number", "TBL_Master_Reason_Code_Per_Shipment", "[Shipment Number] = [COLOR="Red"][B]'[/B][/COLOR]" & Me.ShipmentNumber & "[COLOR="red"][B]'[/B][/COLOR] And Left([Reasoncode], 2) = Left('" & Me.Reasoncode & "', 2)) > 0 Then

 

[Scaniafan]

The shipment number field is indeed text. Based on your suggestion I ended up with below code which at least doesn't generate the "Expected: list seperator or)" error, and I get the message which I was expecting.

If DCount("Shipment Number", "TBL_Master_Reason_Code_Per_Shipment", "[Shipment Number] = '" & Me.ShipmentNumber & "'" And Left([Reasoncode], 2) = Left("'" & Me.Reasoncode & "'", 2)) > 0 Then

The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Access from saving the data in the field

What's left is a data type mismatch to solve. Will keep you guys posted.