option explicit help needed please

[dinkytrouble]

Hi, I am new to the forum so i appologise if this has gont to the wrong section. i have a problem i hope someone will be able to help me with.

my database usually has Option Compare Database. I have re wrote most of my code to compensate to go to option explicit but now i face a problem that it will not allow me to use the case duplicate that has previously worked. here is my code if someone could please tell me why the select duplicate section will no longer work this would be great.
i cannot find this anywhere on the web.


Private Sub Command59_Click()

If (Forms![dry data]!Text62 < 40) And (Eval("[Forms]![dry data]![LOW YIELD REASON] Is Null")) Then
Beep
MsgBox "YOU MUST PUT A REASON FOR THE LOW YIELD", vbOKOnly, ""
[Forms]![dry data]![low yield reason].SetFocus
Exit Sub

End If
Dim LResponse As Integer

LResponse = MsgBox("Do you wish to release this lot to dry inspection?", vbYesNo, "Continue")

If LResponse = vbYes Then

MSG = "Lot Released !!" ' and take appropriate
Dim temp1, temp2, temp3, temp4, temp5, temp6, temp7, temp8, TEMP9, TEMP10 As String


temp2 = [CAVITY]
temp3 = [pldcavity]
temp4 = [POWER]
temp5 = [MAIN PLD]
temp6 = [Balance]
temp8 = [Oven]
TEMP9 = [fit]


Dim MyDB As Database, MyTable As Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyTable = MyDB.OpenRecordset("dry inspection", DB_OPEN_TABLE)
On Error Resume Next ' Set up error handler.
MyTable.AddNew

MyTable("CAVITY") = temp2
MyTable("PLDCAVITY") = temp3
MyTable("target POWER") = temp4
MyTable("main pld") = temp5
MyTable("dry inspection starts") = temp6
MyTable("oven") = temp8
MyTable("FIT") = TEMP9





MyTable.Update
MsgBox "released"

DoCmd.Close


Select Case Err
Case DUPLICATE
MsgBox "This Lot Has Already Been Released To Dry Ispection please consult your team leader !!", , "Status Report.."

Response = DATA_ERRCONTINUE
End Select
Err = 0 '' Clear error.
MyTable.Move 0, MyTable.LastModified
MyTable.Close

Else ' action.
MsgBox "This Lot Has Not Been Released To Dry Inspection !!", , "Status Report.."

End If


End Sub

many thanks
dinky trouble

 

[TonyJollans]

There are several Options that can be used - Option Compare (whatever) and Option Explicit are two of them.

Using Option Explicit is always good practice and most of us can not understand why it isn't a default setting. It forces you to explicitly declare all variables.

Option Compare ... controls the way in which comparisons are done. Outside Access there are two options: Binary and Text. Access offers a third possibility: Database, which says to use what is defined in the database. If you have removed the Option Compare Database statement, you will be defaulting to Option Compare Binary and your comparisons will no longer be producing the same results as before. I don't know what you have in your database, nor what your DUPLICATE error represents, so cannot advise on the specifics.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[HarleyQuinn]

Thought you're going about it in a way that seems a bit odd to me, you'll just need to declare DUPLICATE and assign it the value of the err.number for a duplicate.

Regards

Andy
---------------------------------
Zebracorn: 50% Zebra, 50% Unicorn = 100% Real.

http://lessthandot.com