Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2012
    Posts
    4

    Unanswered: Edit duplicate/existing record within form

    Hey all,

    I've been struggling to find some way to allow my form to detect & notify a user of a duplicate/existing value of the primary key AND then pull up the existing record and edit it within the form.

    What I'm looking for is, in my data entry form, when a duplicate entry of the primary key text box is detected it will load the information into the form and allow the user to edit the contents and re-save/re-add it with the new information.

    Eventually I will likely create a popup form with a command button with "Edit or "Cancel" option.

    To put it in context, this is very simple order recording system where we sometimes input estimates into the db that are later converted into sales orders. When/If it becomes a sale we need to add an invoice number, and change the final total. Estimates and Sales are based on the same primary unique key (the order #), but sales have extra information included with them.

    All the information is held in one table.

    Hopefully someone could point me in the right direction. I have minor experience with VBA and while I can't code from scratch I can usually "understand" and decipher what is written out.

    Thanks for your help.
    Last edited by fixt; 09-06-12 at 13:03.

  2. #2
    Join Date
    Oct 2006
    Posts
    110
    Try researching the Dlookup function. You'll be able to use it to search for a duplicate order number.

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The syntax varies, depending on whether the OrderNumber is defined as a Text Datatype or as a Number Datatype.

    If OrderNumber is Text

    Code:
    Private Sub OrderNumber_BeforeUpdate(Cancel As Integer)
     
     Dim varOrder As String
     Dim rs As Object
     
     If DCount("*", "OrderTable", "[OrderNumber] ='" & Me.OrderNumber & "'") > 0 Then
        
       varOrder = Me.OrderNumber
      
       Me.Undo
     
       Set rs = Me.Recordset.Clone
        
       rs.FindFirst "[OrderNumber] = '" & varOrder & "'"
     
       Me.Bookmark = rs.Bookmark
         
     End If
     
    End Sub


    If OrderNumber is Numeric

    Code:
    Private Sub OrderNumber_BeforeUpdate(Cancel As Integer)
     
     Dim varOrder As Integer
     Dim rs As Object
     
     If DCount("*", "OrderTable", "[OrderNumber] = " & Me.OrderNumber) > 0 Then
        
       varOrder = Me.OrderNumber
      
       Me.Undo
     
       Set rs = Me.Recordset.Clone
        
       rs.FindFirst "[OrderNumber] = " & varOrder
       Me.Bookmark = rs.Bookmark
         
     End If
     
    End Sub


    Linq ;0)>
    Last edited by Missinglinq; 09-06-12 at 22:16.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Sep 2012
    Posts
    4
    Thanks for the replies.

    I am using a dlookup to detect duplicates and display an error message currently, but I wasn't aware I could use this to pull the record and edit it as well. In doing some research I could actually use it to create a new record and pull the old data from the previous record - maybe that will work.

    Unfortunately Missinglinq, I had stumbled across this piece of code before and had been stonewalled at an error 3021 with the "Me.Bookmark = rs.Bookmark" line. I've done extensive google searching and tried dozens of fixes but nothing has worked for me. Oddly enough I haven't found a person who has actually had success with this code.

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Don't know what to tell you; this code has literally been running for years, on a number of apps, without problems, and runs just fine, for me! Generally speaking, any identical code you find online, from multiple sources, will be reliable. The error message "No current record" makes no sense to me either, but Access error messages often don't!

    Only thing I can think of is that possibly you've got a missing Reference.

    If you haven't checked for this kind of thing before, here are Doug Steele's detailed instructions on how to troubleshoot the problem:

    Access Reference Problems

    You might copy and paste/post the exact code you tried, so we can look at it.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In Missinglinq's, procedure, try to define the recordset object as a DAO Recordset:
    Code:
    Dim rs As DAO.Recordset
    Instead of:
    Code:
    Dim rs As Object
    That way, the compiler will immediately warn you if the needed reference is missing.
    Have a nice day!

  7. #7
    Join Date
    Sep 2012
    Posts
    4
    Sorry for the slow response - was spending some time troubleshooting.

    So I recreated a very basic new table to test out this code outside of my modifications, and one by one added in my settings and features of my main database. End result, it doesn't work in Data Entry mode. Which I guess I should have clued in to (funny how problems seem so obvious after we know what they are eh!).

    Now I have the dilemna of reworking the operation of my form or trying to find something that actually queries the table without the recordset bookmark

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by fixt View Post

    ..to find some way to allow my form to detect & notify a user of a duplicate/existing value of the primary key AND then pull up the existing record and edit it within the form.
    Quote Originally Posted by fixt View Post

    ...it doesn't work in Data Entry mode...
    Of course not! If you look at the record count when you open a Form with Data Entry set to Yes, you'll see that it has no records! With Data Entry set to Yes the RecordSet contains no existing Records, only those entered during that session.

    Why do you have Data Entry set to Yes? It's only purpose is to only allow the entry of New Records. If you simply want the Form to open on a New Record, but also be able to access existing Records (so the code above will work) you can use

    Code:
    Private Sub Form_Load()
      DoCmd.GoToRecord , , acNewRec
    End Sub

    Linq ;0)>
    Last edited by Missinglinq; 09-11-12 at 11:27.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    personally I'd use dlookup to finmd out if the rtecord al;ready exists
    if it does then I'd set a filter to the primary of that row
    do the editign
    and remoive the filter when the user
    leaves that row (in the on current event)
    OR
    finshes editing (in the after update event)
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Sep 2012
    Posts
    4
    Quote Originally Posted by Missinglinq View Post
    Of course not! If you look at the record count when you open a Form with Data Entry set to Yes, you'll see that it has no records! With Data Entry set to Yes the RecordSet contains no existing Records, only those entered during that session.
    Would have been a great indicator if I didn't remove that ugly navigation pane from the form, so the fact didn't even click

    Quote Originally Posted by Missinglinq View Post
    Why do you have Data Entry set to Yes? It's only purpose is to only allow the entry of New Records. If you simply want the Form to open on a New Record, but also be able to access existing Records (so the code above will work) you can use

    Code:
    Private Sub Form_Load()
      DoCmd.GoToRecord , , acNewRec
    End Sub

    Linq ;0)>

    Because the form's primary purpose was the entry of new records and I wanted to prevent as much dicking around with the data as possible, given that I am not the only user and other users have questionable abilities. We realized afterwards we needed the ability to edit previous records. We have traditionally processed estimates as new orders, but our new system allows us to reuse old estimate order #s.

    I've set it back and readjusted as you mentioned - to open a blank order on open.

    The only trick now is figuring out how to readjust the date of the record to the day it was updated (locked field).




    Most of my experience is in web programming and pulling information into a form from a database is such a simple task, I forgot to account for "settings" of the program that will override code. I do appreciate your help with this and happy I was able to get it working.
    Last edited by fixt; 09-11-12 at 13:56.

  11. #11
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad we're finally getting somewhere with your problem! I usually remove much of the native Access whistles and bells, too, before sending an app to the users, but leave them in until then, as it can be helpful, in things like this.
    Quote Originally Posted by fixt View Post

    ...The only trick now is figuring out how to readjust the date of the record to the day it was updated (locked field).
    Is this going to be the current date, when you actually are updating the Record? If so, Values can be assigned to Controls, thru code, even when they are Locked.

    You can do this with code in the Form_BeforeUpdate event. This will populate the Control with the date when the Record is first created and thereafter when it is edited:

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
     Me.Date_Of_Update_Textbox = Date  'or Now(), if you want Time as well as Date
    End Sub

    To only populate it when updating the Record:

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
     If Not Me.NewRecord Then 
      Me.Date_Of_Update_Textbox = Date  'or Now(), if you want Time as well as Date
      End If
    End Sub

    If you don't want this to happen automatically, you can do the same thing with the the OnClick event of a Command Button or, if you want to keep away the hoi polloi, and only allow certain users the ability to do this, in the OnDoubleClick event of an independent Label (one not tied to another Control). Most users don't realize that clicking on a Label can be made to do things. I use the OnDoubleClick as a precaution as users will sometime accidentally click on a Label, but will seldom, in my experience, OnDoubleClick on one.

    If you want to be able to manually Unlock the Control, then enter a date, you could, once again, do it with a Command Button, by Unlocking the Control, then Locking it when you move to another Record:

    Code:
    Private Sub UnLockField_Click()
     Me.Date_Of_Update_Textbox.Locked = False
    End Sub
    
    Private Sub Form_Current()
     Me.Date_Of_Update_Textbox.Locked = True
    End Sub

    Once again, for added security, you could place the Command Button code in the DoubleClick event of an independent Label.

    Linq ;0)>
    Last edited by Missinglinq; 09-11-12 at 18:19.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •