Full Version: Dlookup to see if a value exists in a table
UtterAccess Forums > Microsoft® Access > Access Forms
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.

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
If IsNull(DLookup("TitleField", "Movies", "TitleField='" & "'")) Then
hould be...
If IsNull(DLookup("TitleField", "Movies", "TitleField='" & Me.TitleField & " ' ")) Then
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.
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.
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:
A VBOk dialogue box comes up that says:
X-Men 2
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"
End If
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.
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.
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
Do Until rst.EOF
If strMovies = "" Then
strMovies = rst![Title]
strMovies = strMovies & ", " & rst![Title]
End If
End With
Msgbox strMovies
End if
Haha...Scott your idea is much better, and much easier!
At the risk, and probability, of sound retarded... What kind of code would I need to put in the NotInList Event?
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.