Full Version: Dlookup to see if a value exists in a table
UtterAccess Forums > Microsoft® Access > Access Forms
proxi
Hello all,
I have a database that I am using to catalog movies. I have a form that is dedicated to inputting movies. I would like to have it so that when I type the title of the movie that it looks into the table and checks it to see if that movie exists. here is what I have... and it is not working... When I change things, either it displays the msg box for everything or for nothing... please help.
CODE

Private Sub Title_BeforeUpdate(Cancel As Integer)
   Dim Msg As String, Style As Integer, Title As String
   Dim DL As String
  
   If IsNull(DLookup("TitleField", "Movies", _
    "TitleField='" & "'")) Then
      MsgBox "That value already exists", vbOKOnly, "Duplicate Value"
   End If
End Sub
kbrewster
If IsNull(DLookup("TitleField", "Movies", "TitleField='" & "'")) Then
hould be...
If IsNull(DLookup("TitleField", "Movies", "TitleField='" & Me.TitleField & " ' ")) Then
proxi
can you take a look at the first attachment and see what you can do with it. I tried the code that you posted and it still didn't work.
kbrewster
You have Title as the primary key in the Movies table...and it is set to No Duplicates. This will catch if a duplicate is added or not...
You really should not have a text field as your primary key, you should use an Autonumber field called MovieID or something.
proxi
alright... Another question then. Is there a way to use coding to show a list of movies that are like what I just typed in if any?
o it would be something like this:
Otype in the box:
"X-Men"
A VBOk dialogue box comes up that says:
X-Men
X-Men 2
kbrewster
If you wanted to remove Title as the Primary key, and check for duplicates in code you would use this code...
If DCount("Title", "Movies", "Title ='" & Me.Title & "'") <> 0 Then
MsgBox "That value already exists", vbOKOnly, "Duplicate Value"
Me.Undo
End If
kbrewster
To do this, I think you will have to open a recordset to display all the records that match what you typed in the Title control.
ScottGem
The way I would handle this is with a "Search" combo. Use the combobox wizard to create a search combo. Make sure its set to AutoExpand. when you start typing in a title, it should find matches. If its not found, use the NotInList event to add the title.
kbrewster
Maybe something like....
im strSQL As String, strMovies as String
strSQL = "SELECT Movies.Title FROM Movies" & _
"WHERE Movies.Title Like '" & Me.Title & "'*"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
If rst.RecordCount <> 0 Then
With rst
rst.MoveFirst
Do Until rst.EOF
If strMovies = "" Then
strMovies = rst![Title]
Else
strMovies = strMovies & ", " & rst![Title]
End If
rst.MoveNext
Loop
End With
Msgbox strMovies
End if
kbrewster
Haha...Scott your idea is much better, and much easier!
proxi
At the risk, and probability, of sound retarded... What kind of code would I need to put in the NotInList Event?
ScottGem
Lookup the NotInList event in Access Help for sample code. Or search UA on NotInList.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.