Goto Record with parameters

[csdrex87]

Ok so my database contains a duplicate record check. My boss wants me to have the prompt box that comes up if there is, take the user to the duplicate record to modify instead of what it currently does which is asks if they want to save a new record. Can someone help me with this general line of code since I am not familiar with searching for and bringing up previous records?

 

[wazz]

is there any code to get the duplication warning? can you post it?

 

[csdrex87]

Here is the code... where i put the astrix is where i want the other code to go to open up the duplicated record instead...

Private Sub Command11_Click()

Dim iEmpId As Long

If IsNull(Me.Emp_ID) Then
If (MsgBox("Not Enough Data!" & vbCrLf & _
vbCrLf & "Please enter a different ID.", _
vbOKOnly, "Duplicate Employee") = vbOKOnly) Then
Cancel = True
End If
End If

If IsNull(Me.DailyDate) Then
If (MsgBox("Not Enough Data!" & vbCrLf & _
vbCrLf & "Please enter a different date.", _
vbOKOnly, "Duplicate Employee") = vbOKOnly) Then
Cancel = True
End If

Else:
iEmpId = DCount("Emp_ID", "tblEmployeeDaily", "Emp_ID = " & Me.Emp_ID & " And DailyDate=#" & Me.DailyDate & "#")

If iEmpId <> 0 Then
If (MsgBox("An entry for this date already exists. " & vbCrLf & _
vbCrLf & "Would you like to edit the record?", _
vbOKCancel, "Duplicate Employee") = vbOK) Then
****************Put record Code Here*********************
End If

Else:
DoCmd.Save acForm, "EmployeeDaily"
End If

End If
End Sub

 

[wazz]

if you are already on the form that holds the information, try:
Me.Recordset.FindFirst "EmpId = " & iEmpId

if you have to go to another form:

dim strCrit as string
strCrit = "EmpId = " & iEmpId
 
docmd.openform "frmName",,,strCrit

oh, and change DCount to DLookup.
and you might have to change this:
If iEmpId <> 0 Then
to
If Not IsNull(iEmpId) Then

and, i would also change iEmpId to lngEmpId. iEmpId looks like it's an integer, but it's typed as Long.

 

[csdrex87]

if you are already on the form that holds the information, try:
Me.Recordset.FindFirst "EmpId = " & iEmpId

I tried this with changing the empid to be the field in my table and the iemp id to be the field in the form but im not sure if it works that way. Either way it gets the information from the table tblEmployeeDaily. But i get the error message type mismatch with this code:


Me.Recordset.FindFirst "Emp_Id = " & Me.Emp_ID And "Dailydate = #" & Me.DailyDate & "#"

(thanks for your help btw)

 

[wazz]

how about just:
Me.Recordset.FindFirst "Emp_Id = " & iEmpId

looks like you need a date but to start off, does that work?

 

[csdrex87]

Ok it appears that they both work seperately but when i try to throw them together its giving me an error that there is a type mismatch.
So

Me.Recordset.FindFirst ("Emp_ID = " & Me.Emp_ID And "DailyDate = #" & Me.DailyDate & "#") [with or without parenthesis]

Doesnt work

Me.Recordset.FindFirst "Emp_ID = " & Me.Emp_ID
works as well as
Me.Recordset.FindFirst "DailyDate = #" & Me.DailyDate & "#"

 

[wazz]

Me.Recordset.FindFirst ("Emp_ID = " & Me.Emp_ID & " And DailyDate = #" & Me.DailyDate & "#")

 

[csdrex87]

Still not working... now its giving me a syntax error... I've tried
Me.Recordset.FindFirst "Emp_ID = " & Me.Emp_ID & " And "DailyDate = #" & Me.DailyDate & "#"

and

Me.Recordset.FindFirst ("Emp_ID = " & Me.Emp_ID & " And "DailyDate = #" & Me.DailyDate & "#")

 

[wazz]

you still have the quote before DailyDate - remove it.

 

[csdrex87]

Oh haha. Thanks works perfectly now . I really appreciate you stickin with me on this one!

 

[wazz]

you're welcome.