check if new item is allready in a table

[bubani]

hi there here is another question. I have made a form to add records to a table. But i want to disable this possibility in 2 situations;

1. if the textfield is empty
2. if the string in the textfield allready excist as a record in the table.

First i will give you my code

Private Sub cmdLokatie_Click()
Dim doos As Byte
 
If IsNull(Me!lokatieToevoegen) Then
doos = MsgBox("mag geen nul zijn", vbCritical + vbOKOnly, "fout")
Exit Sub
End If
 
 
 
 
Dim rst As New ADODB.Recordset
 
Dim strSQL As String
 
strSQL = "Select * From Lokatie"
 
 
 
rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
 
 
With rst
 
    .AddNew
 
    !Lokatie = Me!lokatieToevoegen
       
   .Update
 
    .Close
 
End With
 
 
End Sub

As you can see, i have solved my first problem, but how can i check if the new Item to be added by the user, isn't allready a record in the table, so i can end the procedure with exit sub, en give a msgbox why the new item isnt added?


I hope you can help me

[Ecniv]

Just need to see if an exactly matching record exists already.
(or you could use wildcards to a close match.. but be careful with them).

Private Sub cmdLokatie_Click()
Dim doos As Byte
 
If IsNull(Me!lokatieToevoegen) Then
doos = MsgBox("mag geen nul zijn", vbCritical + vbOKOnly, "fout")
Exit Sub
End If
 
 
 
 
Dim rst As New ADODB.Recordset
 
Dim strSQL As String
 
'---- single quotes around a string
'---- don't need me... its there anyhow...
'---- edit - added the where bit I forgot ;) duuuh - sorry!!
strSQL = "Select * From Lokatie [b]Where Lokatie = '" & lokatieToevoegen & "'"[/b]
 
 rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
 
  With rst
 
    if .eof then
      .AddNew
      !Lokatie = Me!lokatieToevoegen
      .Update
      .Close
    end if
 
 End With
End Sub

Vince

[bubani]

hmm, thanx but now an error appears:

The Component FROM contains a syntaxerror?

and the next line is colored yellow:

rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Can you do something with that?

But till now, thnx for the help

[bubani]

I got it allready, you forgot

where ()

, my code is now like here and it is working fine;

Private Sub cmdLokatie_Click()
Dim doos As Byte
 
If IsNull(Me!lokatieToevoegen) Then
doos = MsgBox("Voer alstublieft eerst een nieuw item toe", vbCritical + vbOKOnly, "Onjuiste invoer")
Exit Sub
End If
 
 
 
 
Dim rst As New ADODB.Recordset
 
Dim strSQL As String
 
strSQL = "Select * From Lokatie Where (Lokatie = '" & lokatieToevoegen & "')"
 
rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
 
  With rst
 
    If .EOF Then
      .AddNew
      !Lokatie = Me!lokatieToevoegen
      .Update
      .Close
    Else
    doos = MsgBox("Dit item bestaat al", vbCritical + vbOKOnly, "Onjuiste invoer")
    End If
 
 End With
End Sub