Dlookup & MSGBOX with record details

unclefink

Registered User.
Local time
Today, 03:24
Joined
May 7, 2012
Messages
184
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.
Code:
     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
 
Last edited by a moderator:
Hi,
Then you would need to select the date from the table. Use Dlookup:(http://www.techonthenet.com/access/functions/domain/dlookup.php)
Code:
EnterDate = Dlookup("yourDateEnterFieldNameHere", "trainingdata", "[Empid]=" & Me!EmpID & _
" And [subjectid]=" & Me!SubjectID & _
" And [trainingdate]=#" & Me!TrainingDate & "#")
The concatenate it to your message:
Code:
"This record already exists" & _
", and was entered at: " & CStr(EnterDate)
ATB
 
Last edited:
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.

Code:
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
 

Attachments

  • Capture.PNG
    Capture.PNG
    11.2 KB · Views: 171
  • Capture2.PNG
    Capture2.PNG
    15.5 KB · Views: 167
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:
Code:
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
 
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
 
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:
Code:
"[fname]=" & Chr(34) & Me!Fname & Chr(34) & _
" And..."
 

Users who are viewing this thread

Back
Top Bottom