Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    lloyddobler is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    19

    if exists UPDATE else INSERT


    Hi all, I've been scouring net and can't find anything that helps ver well. I'm very weak with VB so need all help I can get. I have a form with 2 fields: CONTROLNUMBER and COMMENTS. What I'd like to do is when a CONTROLNUMBER is entered, the COMMENTS fields populates, which works fine with a DLOOKUP. But now I'd like to add a BEFORE UPDATE event that if the CONTROL NUMBER entered already exists, then UPDATE the current record, otherwise, INSERT a new one. I have no idea where to begin and any help would be appreciated. My table has those 2 fields as the only 2 columns as well, as i'd like the CONTROLNUMBER to be unique/primary key.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Have you considered using a ComboBox for the CONTROLNUMBER with LimitToList set to YES and the NotInList event to add a new record?

  3. #3
    lloyddobler is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    19
    No, CONTROLNUMBER is a text field with validation set. There won't always be COMMENTS for a CONTROLNUMBER, so what I need is that when a CONTROL# is entered, if COMMENTS do exist, then append to those existing comments. If the CONTROL# does not exist, insert a new record with whatever COMMENTS are input.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    What are you using the DLookup() for? Don't you have your form bound to a query of the table you have been talking about?

  5. #5
    lloyddobler is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    19
    No, when the form opens it's in Data Entry mode, so when I enter the CONTROL NUMBER, I want the COMMENTS to appear (if they exist for that CONTROL#), otherwise, if they don't exist, I want a new entry to be made.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    It sounds like you are pretty well locked into doing it the way you have it and not open to suggestions.

  7. #7
    lloyddobler is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    19
    I'm open to suggestions, but you've only brought forward one, and I'm not sure how I would implement that suggestion as if I limit my combo box then how would I add a new entry using said combo box?

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Is your form bound to the table involved here? If it was you would not need the DLookup().

  9. #9
    lloyddobler is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    19
    Originally, it wasn't, but then I couldn't insert new records, only modify existing, so I made it bound to my table, now it's reverse, I can add new entries, but not modify existing, as my CONTROL# is my PK (and I want it that way).

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Try adding a ComboBox to your form with the wizard and select option #3.

  11. #11
    lloyddobler is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    19
    Okay, done, so I type in or select my CONTROL #, but COMMENTS for that CONTROL# do not appear, and now I get error 'changes you requested to table were not successful because they'd create duplicated values in index, pk, etc.'

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You get that error when you use the new ComboBox? The cbo should not have a ControlSource. Did you change anything on the new control?

  13. #13
    lloyddobler is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    19
    yes, I get that error using new ComboBox. I've done nothing to the new control, just selected option 3 as you suggested. I now have a form with a control number combo box and a comments text area. Is there not some VB code that would do something like:
    IF (control number exists in table1)
    UPDATE
    ELSE
    INSERT

  14. #14
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Any chance you could post your db? Remove any confidential data as necessary. Something is going on that does not make sense to me.

  15. #15
    lloyddobler is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    19
    Here it is. I have a feeling you want me to have 2 control number fields, one as a combo box, and one as the control source text box, but I'm looking to keep it simple for end user by only having one control# field.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. SQL - find out whether a table exists
    By yurako in forum Programming
    Replies: 2
    Last Post: 01-20-2010, 09:27 AM
  2. How to use if not exists in access?
    By halifaxdalRaymondXie in forum Queries
    Replies: 1
    Last Post: 01-14-2010, 03:18 PM
  3. Replies: 1
    Last Post: 11-30-2009, 05:05 AM
  4. help with insert
    By jamie in forum Access
    Replies: 1
    Last Post: 11-16-2009, 06:02 AM
  5. Create Table If Not Exists
    By jp2access in forum Queries
    Replies: 7
    Last Post: 07-14-2009, 12:49 PM

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