Results 1 to 4 of 4
  1. #1
    usmcgrunt's Avatar
    usmcgrunt is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Location
    Just outside the gates of freedom
    Posts
    71

    MsgBox for duplicate entry

    I am trying to develop a custom message when a user enters a duplicate lesson number(not primary key). However, in this case, duplicate entries are ok as long as the user first verifies the information between the two records.

    I have the following code that works perfectly but I want to be able to display additional information for the user to make the yes or no decision to add the duplicate lesson number.



    What I would like to add to the message box is more information about the lesson that is already in the database. Namely the lesson title. So I would like the message box to say something like:

    The Lesson Number 'E(E)1234' is already assigned to 'Lesson Title'. Do you still want to use it?

    If 'Lesson Title' is inactive, click yes to proceed otherwise click no to cancel and enter another lesson number.

    Code:
     
    Private Sub LNumber_BeforeUpdate (Cancel As Integer)
     
         strMsg = " Lesson Number already in use!!! " & _
                      vbCrLf & vbCrLf & " Lesson Number " & Me.LNumber & " is already in use.  Do You still want to use it?" & _
                      vbCrLf & vbCrLf & " If the Lesson currently assigned the Lesson Number " & Me.LNumber & " is inactive  " & _
                      vbCrLf & " click 'Yes' to proceed otherwise click 'No' to cancel and enter another Lesson Number.        "
     
         If Not IsNull(Me.LNumber) Then
              If DLookup("LNumber", "tblLCData", "[LNumber] = '" & Me.LNumber & "'") > 0 Then
                   If MsgBox(strMsg, vbQuestion + vbYesNo, "Duplicate Lesson Number!!!") = vbNo Then
                        Cancel = True
                   Else
                        Cancel = False
                   End If
              End If
         End If
    End Sub
    Thanks in advance for the assistance.

    Sean

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Try changing DLookup to DCount and see what happens.

  3. #3
    usmcgrunt's Avatar
    usmcgrunt is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2010
    Location
    Just outside the gates of freedom
    Posts
    71
    Quote Originally Posted by RuralGuy View Post
    Try changing DLookup to DCount and see what happens.
    RG, thanks for the reply. Changing to DCount does not appear to do anything differently than DLookup. When I open the form and purposely type in a lesson number that already exists, I get the same message whether I use DLookup or DCount.

    What I am trying to do is get the lesson number and the lesson title of the lesson that is already in the database to appear in the msgbox. If I type Me.LTitle as the reference in the msgbox string I get blanks because nothing is the LTitle field yet. How do I pull Ltitle from tblLCData that matches the lesson number in the LNumber field and then put that in my message box?

    Thanks, Sean

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Try using the .Text property here and see what happens. Me.LNumber.Text
    DLookup() returns Null when it does not locate a record so DCount() makes more sense in this context.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Customer Entry/Find Duplicate/Similar names
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 08-27-2010, 09:20 PM
  2. Custom MsgBox
    By roccoIT in forum Programming
    Replies: 3
    Last Post: 07-06-2010, 10:43 AM
  3. Msgbox in a query with parameters
    By seb in forum Forms
    Replies: 7
    Last Post: 07-06-2010, 05:07 AM
  4. Replies: 3
    Last Post: 06-04-2010, 12:47 PM
  5. Yes No Cancel MsgBox
    By Rick West in forum Forms
    Replies: 5
    Last Post: 04-14-2010, 08:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums