find duplicate record before posting 1

[benniesanders]

Hello!

I have a form that has three drop downs (combos):
Start Time
End Time
Task
On_click it posts the info to a table
It's being called from another form and picks up two fields that also get posted:
Job ID
Operator
I would like to be able to validate whether the following combination exists before it's posted to the table:
Job ID
Operator
Start Time
End Time
Task

I tried creating a new recordset to check it with before it posts with a "select" statement of the five above (select * from tblTimeSheet where JobID='"&strJob&"', etc. and doing an "If not oRsDup.eof then" and a msgbox "Duplicate Record" but it's not working. Any ideas of another way to check to make sure the whole record doesn't already exist? Thanks in advance.

Bennie

 

[thornmastr]

Asuming all your variables are on the current form, how about using the forms before update event to do something along the following lines

Dim db as dao.database
Dim rs as dao.recorset
Dim strSQL as string

Strsql = “SELECT * FROM TBLTIMESHEET WHERE JOBID =’” _
& ME!JOBID & “’ AND OPERATOR = ‘” & ME!OPERATOR & “’AND START_TIME” _
& “= ‘”& ME!STARTTIME &”’ AND ENDTIME + ‘” & ME!ENDTIME & “’” _
& “ TASK = ‘” & ME!TASK & “’”
set rs = db.openrecordset (strsql, dbopensnapshot)
if rs.recordcount = 1 ‘ duplicate record
msgbox “Duplicate record”, vbinformation
cancel = true
endif

WATCH THE WORDWRAP IN THE ABOVE Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[benniesanders]

Thanks a million, Robert. I'll give it a try!

 

[benniesanders]

Forgive me for not replying sooner. That's what I needed. Thanks for the tip!

 

[LMRollins]

Thornmastr, I was wondering if you could help me with a similar situation. I've tried using the code you have above but I keep getting debug problems. I'm not very familiar with code but I'm learning. I think I've left something out. This is what I have so far:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "select * from tblphysicalenter where Page_No = '" & Me.Page_No & "' and Line_no = '" & Me.Line_No & "'"
Set rs = db.OpenRecordset("strSQL", dbOpenSnapshot)
If rs.RecordCount = 1 Then
msgbox ("Duplicate record.&quot
Cancel = True
End If
End Sub

This is for our inventory sheets. There can be duplicate page numbers but only 26 lines per page. There can be no more than 600 pages. I have the validate option set for the maximum pages and lines but would rather have a custom message pop up for this also. What the above code should tell me is as an example is whether page 4 line 2 has been entered. If it has then they should get a message window telling them that it is a duplicate entry.

 

[thornmastr]

I think you are very close, although I’m not sure I agree with the way you are setting up your tEsting concept; but what is important here is the learning process… there is plenty of time to debate philosophies. Change the following line to the line below it and give it a whirl. If you need further help, post back..


strSQL = "select * from tblphysicalenter where Page_No = '" & Me.Page_No & "' and Line_no = '" & Me.Line_No & "

strsql = “SELECT * FROM tblphysicalenter where page_no = “ _
& me.page_no.value & “ and line_no = “ _
& me.line_no.value

HTH.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[LMRollins]

This was the closest thread that I could find to my problem. If you have any suggestions, I am all ears. I don't know enough about code to do it right, only make it work. I changed the line as you said but I still get the following error for this line:

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

error message - object variable or with block variable not set

 

[rsch]

While my posting to LMRollins is quite outdated, I wanted to give my solution in case anyone is interested.

I want to thank thornmastr for his excellent post!

I solved the error message by adding Current to the line, so it reads:
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot).

Hope this helps.

 

[19thJIS]

Greetings all,

This thread was a time saver for me. It helped solve a problem that I was having. Thanks for the great info.

I am still having just a small problem once the msgbox reports a duplicate entry, how do I regain focus so I can enter a different name?

Currently, the user has to click the save button that starts the save process. Since I have the check for duplicates under the Before Update on the form, I get an error asking me to debug the VB which is pointing to the save record.

Thanks again. I am still learning this part of Access.

Chuck

 

[RoyVidar]

Hi!

Welcome to Tek-Tips!

Setting focus to controls:

[tt]Me!txtNameOfControl.SetFocus[/tt]

- name of your text control not matching the validation

Or, if the control alredy has the focus, you'd need to setfocus to another control first.

[tt]Me!txtNameOfSomeOtherControl.SetFocus
Me!txtNameOfControl.SetFocus[/tt]

About the save error, there's not enough information. Consider starting a new thread if you can't make it work.

Here's a faq on "guidelines" to get the most out of TT membership, faq181-2886, enjoy!

Roy-Vidar

 

[19thJIS]

RoyVidar,

Thanks for the help. The set focus works fine. As for the save, I moved the code to the Private Sub save area and expanded the If_Then_Else and was able to eliminate the error message.

Chuck