If already existing 1

[Xenocide]

Hi

I'm trying to do a If that would check the database to see if the record that the user wanna create already do so I can tell him

Here's what I have

If
(DLookup("IdEngagementA", "Table850", "IdEngagementA = '" & txt1.Value & "'")) Then

txt1.Value is what the user is gonna enter.
The problem is that either it skip over the if or it tell me that there is type incompatibility

I tried that too

numero = Format(txt1.Value, "00-000000-00")

then doing the same if but with numero instead of txt1.Value

Why the 00-000000-00 cauz in my database it's the inputmask for that particular data

 

[jbpez]

What data type is the IdEngagementA field in your table ?

John Borges

 

[Xenocide]

it's a text date with this input mask 00-000000-AA

(sorry I forgot I changed to AA when I wrote just before, but I changed to AA in my code so it'S not the problem)

 

[jbpez]

I tried to simulate what you did and it work fine for me.

Could you show us your code ?

John Borges

 

[jbpez]

What is the format of your txt1 textbox ?

John Borges

 

[Xenocide]

ok that,s the complete code where I do the if :

Private Sub cmd1_Click()
On Error GoTo Err_cmd1_Click

Dim strSQL1, strSQL2, numero As String
Dim Total1, Total2, Total3 As Long

numero = Format(txt1.Value, "00-000000-00")

If (DLookup("IdEngagementA", "Table850", "IdEngagementA = '" & numero & "'")) Then
MsgBox "Ce numéro d'engagement existe déjà"
Else
'Executer la requête CreerPlanifI
DoCmd.OpenQuery "CreerPlanifI", acNormal, acEdit

Total1 = DLookup("MontantActuel", "Table850", "IdEngagementA='" & "01-000000-BU" & "'")
Total1 = Total1 + txt3.Value

strSQL1 = "UPDATE Table850 SET Table850.MontantActuel = '" & Total1 & "' " & _
WHERE Table850.IdEngagementA='" & "01-000000-BU" & "' ;"
DoCmd.RunSQL strSQL1

Total2 = DLookup("Disponibilité", "Table850", "IdEngagementA='" & "01-000000-BU" & "'")
Total2 = Total2 + txt3.Value

strSQL2 = "UPDATE Table850 SET Table850.Disponibilité = '" & Total2 & "' " & _
"WHERE Table850.IdEngagementA='" & "01-000000-BU" & "' ;"
DoCmd.RunSQL strSQL2

txt1.Value = Null
txt2.Value = Null
txt3.Value = Null
End If

Exit_cmd1_Click:
Exit Sub

Err_cmd1_Click:
MsgBox Err.Description
Resume Exit_cmd1_Click
End Sub

that's it

txt1 is a plain normal table where the user enter the data
IdEngagementA is a text field in the database with an inputmask being 00-000000-AA

 

[Xenocide]

txt1 as no format selected.. it's simply text

 

[PHV]

What is displayed if in the debug window (Ctrl-G when in VBE) you type this:
? DLookup("IdEngagementA", "Table850")

Anyway you may try this:
If DCount("IdEngagementA", "Table850", "IdEngagementA = '" & numero & "'") > 0 Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Xenocide]

it gave me 01-000000-BU (one of the data under IdEngagementA)

and yur other thing worked perfectly thank you