I need help writing code to tell me if a record exists

[gcrutch70]

I have an access application with forms, tables, etc. I have a form that the user enters data. When the user enters a new record i need it to tell the user if that record already exist or not. if so, give message. if not, add it.

 

[dhookom]

What determines if a "record already exist or not"? Do all the fields need to match for a record to already exist?

Do you have a unique index defined on the field(s) that should not be duplicated?

Duane
Hook'D on Access
MS Access MVP

 

[gcrutch70]

i have a field Tag_ID which is the TagNumber from another table. So i wasn't sure if i need to use Tag_ID or TagNumber.

 

[dhookom]

Your reply didn't help me much. Why did you bring another table into the discussion?

Please ask again with table and field names as well as what you really want to determine.

Duane
Hook'D on Access
MS Access MVP

 

[gcrutch70]

ok I'm sorry. I have a table called "Calls" this is where all the data is written once the user enters the info in the form. In this call table is a field called "Tag_ID" which is an auto number field and primary key in another table called "Tag". in this "Tag" table there is a field named "TagNumber" where the actual tag number is stored.

btw, the TagNumber is the field that will determine if the record exist or not.

I have a user that takes calls from people when they see someone liters on the street or wherever. (I work for the city). The user takes the information and enters it into a form. info such as date, time, tag number, state, type of debris, location, vehicle info, etc. once the information is entered there is a command button called "Send Letter" and at this point the suspect will be sent a warning letter. So this is where the test comes in....for repeaters they will be sent a letter that says "Second Warning".
So i need the user to be able to know if that tag number has already had a warning letter so they will get a message and it will print a message that says "second warning" and also print second warning on the letter instead of just warning. here is the code that prints the letters.

I have the code but can't figure out how to attach it on here.....i've been sent an email warning me not to include code in the message part.

 

[dhookom]

I expect you would write some code to count the number of records with the same tag number. This might look something like:

   Dim intCountTag as Integer
   intCountTag = DCount("[TagNumber]","Tag","[TagNumber]='" & Me.txtTagNumber & "'")
   If intCountTag > 0 Then
     'some code here
     Else
     'more code here
   End If

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

If you simply look for the tag number in the table, how do you know if the second warning has all ready been issued?
I'd add a field to indicate how many letters have been sent. Or, although some would consider this bad practice,
a couple of yes/no fields - one for each letter/warning.

Randy

 

[gcrutch70]

ok that works but after it runs and print the letter it gives an error: Object variable or with block variable not set. What is that about?

 

[gcrutch70]

Thank you so much for your help. I think i've got it working now.

 

[gcrutch70]

well nope....thought I did. still getting the object variable error. Help

intCountTag = DCount("[TagNumber]", "Tag", "[TagNumber]='" & Me.TagNumber & "'")
If intCountTag > 0 Then
MsgBox ("that Tag Number already exists, this will be a second warning! Go back and check Second Warning box."), vbOKOnly

Me.SecondWarning.SetFocus

Else
PrintWarningOne

End If
rs.Close
db.Close
Exit Sub

 

[dhookom]

Why are you closing an rs and a db when these objects haven't been set?

Duane
Hook'D on Access
MS Access MVP