Results 1 to 12 of 12
  1. #1
    rockape is offline Novice
    Windows XP Access 97
    Join Date
    Sep 2009
    Posts
    10

    Help with vba code

    Hi all,



    I'm new to programming and I would like some help:

    I have a database with a few fields, where one in particular has a unique number (which cannot be auto generated).

    I now have a form where I want include a new record. However I want the form to prevent further entry if that unique number is about to be entered.

    cheers to all

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    I think the answer to your question is to use a Domain function in the BeforeUpdate event of the control where the value has been entered. Here's a link to the proper syntax. Cancel the update event and post a message to the user if it is a duplicate.

  3. #3
    rockape is offline Novice
    Windows XP Access 97
    Join Date
    Sep 2009
    Posts
    10
    Hi ruralguy,

    thanks for your reply. I dont understand. I want the routine to look up the entered value, check the database of records and prevent further entry if it finds the same value.

    The way I have used using Dlookup only checks and returns a value!

    Grateful or further help

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    If the DLookup() returns other than a Null or DCount() is > 0 then Cancel = True and show the problem to the user. This will cancel the update and hold the focus in the current control.

  5. #5
    rockape is offline Novice
    Windows XP Access 97
    Join Date
    Sep 2009
    Posts
    10
    Thanks rg again,

    I need a starting help with the code.

    I have a field name called "reference" and these references are stored in the table called tbl_personnel.

    I have a form called "frm_input" and a unbound box named "ref". It is through this box that i enter the data for "reference" in the tbl_personnel.

    I need the code to do as i ask/mention in my previous threads.

    cheers to all

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Why is the ref control unbound?

  7. #7
    rockape is offline Novice
    Windows XP Access 97
    Join Date
    Sep 2009
    Posts
    10
    Hi,

    Ooops.....not strictly unbound, my mistake.

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Try something like:
    Code:
    Private Sub ref_BeforeUpdate(Cancel As Integer)
       Me.RecordsetClone.FindFirst "[reference] = '" & Me.Refresh & "'"
       If Not Me.RecordsetClone.NoMatch Then
          MsgBox "Duplicate value...retry the value!", vbInformation + vbOKOnly
          Cancel = True
       End If
    End Sub
    Warning...this is AIR CODE.

  9. #9
    rockape is offline Novice
    Windows XP Access 97
    Join Date
    Sep 2009
    Posts
    10
    Thanks rg

    will try the code and let you know.

    Regards

  10. #10
    rockape is offline Novice
    Windows XP Access 97
    Join Date
    Sep 2009
    Posts
    10
    hi,

    did not work. perhaps it would be better if I gave the entire prob!

    regards

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    What did the code do? Did it give an error? Post what you actually have.

  12. #12
    rockape is offline Novice
    Windows XP Access 97
    Join Date
    Sep 2009
    Posts
    10
    Hi,

    can't copy it now! will have to try some other method or simplify the solution.

    thanks a million

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

Similar Threads

  1. Filter Code
    By botts121 in forum Programming
    Replies: 0
    Last Post: 09-03-2009, 01:59 PM
  2. Code Trouble?
    By briancb2004 in forum Access
    Replies: 0
    Last Post: 10-08-2008, 04:47 PM
  3. Need help with code
    By hoenheim in forum Programming
    Replies: 9
    Last Post: 09-11-2008, 04:19 PM
  4. Need Help Getting to Code
    By Snuffles in forum Programming
    Replies: 8
    Last Post: 04-22-2008, 05:25 PM
  5. Navigation Code
    By henryclay in forum Forms
    Replies: 2
    Last Post: 11-15-2006, 09:48 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