determining if record exists 1

[nerd19]

-MS Access 2002-

Im trying to create a form called 'update tool' that will take the values, Employee (number), Serial (text), Size (number), and compare the serial field of the form 'update tool' to a table that holds all of the serials, 'Tools'. So what i have for the 'on click' property of a Save command button is:
Dim strSQL1 As String
Dim strSQL3 As String
Dim stDocName As String
Dim stDocName1 As String
Dim intAnswer As Integer
Dim intAnswer1 As Integer

stDocName = "Tool Update"
stDocName1 = "add tool"
strSQL1 = "SELECT [Tools].Serial FROM [Tools] WHERE ([Tools].Serial = Forms![update tool]![Serial])"
strSQL3 = "INSERT INTO [Tools] (Serial, Size, LastUpdate, Status, Regrinder) VALUES (Forms![update tool]![Serial], Forms![update tool]![Size], Now, 'In', Forms![update tool].[Regrinder]);"
If (Not IsNull(strSQL1) = True) Then
intAnswer = MsgBox("Record not found, is this a new tool?", vbYesNo, "New Tool")
If (intAnswer = 6) Then
DoCmd.OpenQuery stDocName1, acNormal, acAdd
Else
MsgBox ("The tool was not found in the records, and was not added to inventory.")
Forms![update tool]![Regrinder] = Null
Forms![update tool]![Serial] = Null
Forms![update tool]![Size] = Null
End If
End If

So you can see what i'm trying to do, is compare if the serial already exists in the table, if it doesn't then prompt the user that no record was found and that it might be new. if it is new then it will be appened to the table 'tools'. if the record exists then the old record will be updated to the new information. Now i know that the first 'if' loop is wrong since the SQL i am using isn't giving the information i think i need. But what i am asking is how could i compare to see if the field 'serial' from the form already exists in a field 'serial' from a table, 'tools'?

Thanks for the help in advance

 

[nerd19]

oh and the stDocName is an update query while the stDocName1 is an append query

 

[bjb123]

I usually use a dlookup or dcount for stuff like this.


if dcount("*", "Tools", "Tools = " & Forms![update tool]![Serial]) = 0 then


Also, I think it is good idea to set query parameters to variables then you can just make strToolSerial (or whatever you want to call it) = [update tool]![Serial] once then use it in your dcount and select statements.

 

[bjb123]

I forgot something. It should be Tools.Serial

if dcount("*", "Tools", "Tools.Serial = " & Forms![update tool]![Serial]) = 0 then

 

[TheAceMan1]

How are ya nerd19 . . .

Here's a starting point:

[blue]   Dim SQL As String, Criteria, frm As Form
   
   Set frm = Forms("update tool")
   Criteria = "Serial = '" & frm!Serial & "'"
   
   If IsNull(DLookup("[Serial]", "Tools", Criteria)) Then
      MsgBox "The tool already exist and was not added to inventory."
      frm!Regrinder = Null
      frm!Serial = Null
      frm!Size = Null
   ElseIf MsgBox("Tool not found, is this a new tool?", vbYesNo, "New Tool") = vbYes Then
      [green]'Note:Used you append query here as your append SQL has errors and
      'I don't know a few of the data types! Form conrols have to be
      'appended proper for SQL in VBE[/green]
      DoCmd.OpenQuery "add tool", acNormal, acAdd
   End If
   
   Set frm = Nothing[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[nerd19]

sweet, thanks i got it working. although i had to use, If Not IsNull(DLookup....), not sure why but it works. thanks for the help.

 

[TheAceMan1]

[blue] . . . although i had to use, If Not IsNull(DLookup....), [purple]not sure why but it works[/purple].[/blue]

Thats a mistake on my part (forgot Not, according to the program flow).

DLookUp returns [blue]Serial[/blue] if found via criteria and [blue]Null[/blue] otherwise . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884: