Dlookup & MSGBOX with record details

[unclefink]

I've got a form which holds data for employees, fname, lname, ..... and an entered date which defaults to now().

On the before update event, i have the following.

     If DCount("*", "trainingdata", "[Empid]=" & Me!EmpID & _
                                    " And [subjectid]=" & Me!SubjectID & _
                                    " And [trainingdate]=#" & Me!TrainingDate & "#") > 0 Then
         MsgBox ("This record already exists")
         Cancel = True
         Me.Undo
         Me.SubjectID.SetFocus
     End If
End Sub

With the above vba code, a msgbox pops up if the record combination already exists. What I would like to do is in the same msgbox have the "entered" date display and make the msgbox say something like.

This record already exists and was entered on 6/18/2015, [entered]

Any help is appreciated.

Respectfully,

David

 

[marlan]

Hi,
Then you would need to select the date from the table. Use Dlookuphttp://www.techonthenet.com/access/functions/domain/dlookup.php)

EnterDate = Dlookup("yourDateEnterFieldNameHere", "trainingdata", "[Empid]=" & Me!EmpID & _
" And [subjectid]=" & Me!SubjectID & _
" And [trainingdate]=#" & Me!TrainingDate & "#")

The concatenate it to your message:

"This record already exists" & _
", and was entered at: " & CStr(EnterDate)

ATB

 

[unclefink]

I'm afraid i'm having difficulty understanding this application.

In my previous post, I explained the wrong table while addressing the question but still looking for the same result.

I'm now attempting this on a form for my tblemployee table.

The table holds the following entered by the user.
Fname
Lname
DLNumber
and
"Entered"=now() set for the date/time the record was created.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblEmployee", "[FName]=" & Chr(34) & Me!Fname & Chr(34) & _
" And [LName]=" & Chr(34) & Me!Lname & Chr(34)) > 0 Then
MsgBox entered = DLookup("entered", "tblemployee", "[fname]=" & Me!Fname & _
" And [lname]=" & Me!Lname) & "Person already exists" & _
", and was entered on: " & CStr(entered)
Cancel = True
Me.Undo
End If
End Sub

I get this error when I test it against a record that already exists. See the attached titled "capture" and then when I click on debug, I get the screenshot titled "capture2"

Being the fact I'm getting the error, i'm obviously applying your steps incorrectly. How should I be applying this?

Thank you in advance.

David

 

[marlan]

Hi,
See Documentation on Dlooup: http://www.techonthenet.com/access/functions/domain/dlookup.php
Seems to me Me!Fname and Me!Lname fields should be in quotes:

DLookup("entered", "tblemployee", "[fname]='" & Me!Fname & _
"' And [lname]='" & Me!Lname) & "' Person already exists" & _
", and was entered on: " & CStr(entered)

BTW, your 'entered' is sent to the message box string before receiving value from Dlookup. I expect problems there too...

ATB

 

[spikepl]

Using Now()also stores the time, which makes it hard to look for a given record (made at a specified date AND time). Use Date()

Seems to me Me!Fname and Me!Lname fields should be in quotes:

No. Chr(34) is better here, because it allows for apostrophes in names like O'Malley

 

[marlan]

Using Now()also stores the time, which makes it hard to look for a given record (made at a specified date AND time). Use Date()


No. Chr(34) is better here, because it allows for apostrophes in names like O'Malley

Yup, you need the quotes, Chr(34) is better here:

"[fname]=" & Chr(34) & Me!Fname & Chr(34) & _
" And..."