Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Apr 2016
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Records with similar information do not save as a new record.

    Hi,

    I am totally new to Access and I have a problem saving records on a form.

    I have about 20 fields on the form which is basically client information like first name, age, address, phone number, etc.
    Whenever I use the combo box to find previous records, they populate the fields as expected.

    The problem arises when I only edit a few of the fields (like name and phone number) but want to leave the rest the same (like address and client company profile).
    When I click the "add new record" button, access does not save it as a new record, but edits the same record and saves it.

    Can I know what should I do to make sure access understands this is a new record and save it appropriately?

    Thank you so much!

  2. #2
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,411
    Thanks
    447
    Thanked 405 Times in 377 Posts
    At least one of the fields you are not editing are serving as "reference" fields -- that is, that is how Access determines if the record is the same, or if it is actually a new record. These "reference" fields need to be unique. An example of a field which will always be unique is the social security number. In other words, if you enter a person's ssn, you KNOW that you will get the correct customer record, because no two people have the same ssn. If the ssn is not one of your fields, then you'll need to have a field which will always be unique to the particular customer, such as an account number. I have often created a "hidden" reference number for each customer (hidden to normal view) which is unique to each customer; and that reference number is how I always refer to the customer, because I never have to worry about duplicate numbers, nor about a change in the format of that number. (It is theoretically possible for an account number to have a change in format at some point in the future.)

    To test this, edit the fields that you have not been editing, to see if Access will save it as a new record.

    If I am correct about this, you will need to decide which field you want to serve as the reference field for customer records.

    Another question: I'm not very familiar with Access, but something concerns me about the way you are entering the data: Are you hitting the "add new record" button AFTER you enter the data on the record? If so, then Access may be saving the data and then opening up a new record. In other words, you may need to hit that button at the beginning of the process of adding a record, rather than at the end of the process of adding a record.

  3. #3
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,726
    Thanks
    147
    Thanked 156 Times in 149 Posts
    A couple of questions. Which version of Access? Where is the "Add new record" button you're clicking? I have 2016 and can't see a button which would do what you're describing. The only add button I can see is the one at the bottom (in the line where it says record x of y) and that adds a new blank record.

    Indeed, it's normal that if you change data, it will save that data in a new record.
    Talk is cheap because supply exceeds demand

  4. #4
    New Lounger
    Join Date
    Apr 2016
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    mrjimphelps,

    you helped me made a lot more sense of what is going on. Because I used the combo box to bring up the previous record for editing, it brings up the ID as well which is obviously a reference field. So when I go and edit data in the fields and click save it just changes the data for that record. Which brings me to my next question, after I bring up a record and edit some of its information, Can I tell access to generate a new ID and save it as a new record?

    I understand what you mean about the "add new record" Button and yes I understood that button wrongly. I thought it was meant to tell access to add the data I just entered as a new record at the end. I will change the setup of the form slightly.

    access-mdb

    I am using 2013 access. I find the button from the button wizard when I am in design view of the form. It is under "record operations" when the wizard for the button features appear.

  5. #5
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,411
    Thanks
    447
    Thanked 405 Times in 377 Posts
    Quote Originally Posted by shin View Post
    mrjimphelps,

    you helped me made a lot more sense of what is going on. Because I used the combo box to bring up the previous record for editing, it brings up the ID as well which is obviously a reference field. So when I go and edit data in the fields and click save it just changes the data for that record. Which brings me to my next question, after I bring up a record and edit some of its information, Can I tell access to generate a new ID and save it as a new record?

    I understand what you mean about the "add new record" Button and yes I understood that button wrongly. I thought it was meant to tell access to add the data I just entered as a new record at the end. I will change the setup of the form slightly.
    Whenever you want to update a record, you should make doubly sure that you are actually updating an existing record rather than creating a new record, and vice versa.

    There are probably two ways to do either of those tasks: (1) Follow a manual process in Access, or (2) Follow whatever process has been set up by whoever set the database system up.

    I am right now in Access 2010, and I am doing a contact list from one of the templates which were listed. If I click the (New) link, a contact page appears on the screen, in which I can fill in the information. I then can click Save and New or Close. Both of these will put the information on the contact list, but the difference is that Save and New lets me enter several contacts before going back to the contact list.

    This action simply populates the info onto the contact list. I still have to save the contact list, or all of my data entries will be lost.

    If I would like to edit or update the info on an existing customer record, I can click the link in the ID column; this will bring up the contact page for that listing.

    Something you need to understand here is that saving the info is a two part process: (1) Entering each contact onto the contact list, and (2) saving the contact list.

    If you or someone else has set up, via Visual Basic programming, an additional way to enter the data, then you could also follow that method; but I wouldn't have any idea about what else might have been set up.
    Last edited by mrjimphelps; 2016-04-06 at 13:29.

  6. #6
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,726
    Thanks
    147
    Thanked 156 Times in 149 Posts
    Quote Originally Posted by shin View Post
    mrjimphelps,

    you helped me made a lot more sense of what is going on. Because I used the combo box to bring up the previous record for editing, it brings up the ID as well which is obviously a reference field. So when I go and edit data in the fields and click save it just changes the data for that record. Which brings me to my next question, after I bring up a record and edit some of its information, Can I tell access to generate a new ID and save it as a new record?

    I understand what you mean about the "add new record" Button and yes I understood that button wrongly. I thought it was meant to tell access to add the data I just entered as a new record at the end. I will change the setup of the form slightly.

    access-mdb

    I am using 2013 access. I find the button from the button wizard when I am in design view of the form. It is under "record operations" when the wizard for the button features appear.
    shin, it seems you created your form and use it to edit your data. Assuming that no one has used VBA to do anything and you have created your form, then to add a new record with some details from an existing record, you have to duplicate the existing record before editing it. The add new record button (wherever it's found) indeed adds a new blank record after saving your changes to the original record - not what you want. Though I've never tried it, it may be possible to use VBA to do what you want - but what I've just suggested is (as far as I know) the only way with a plain vanilla database.

    To duplicate a record, just select it (the whole record, not just a field), copy it, ensure you're on the home tab, then select the drop down at the bottom of the paste icon at top left, and select paste append. You won't be able to save the new record if you haven't changed any duplicates of the index, primary key or one or two other things - the error message is quite clear.

    Hope this helps

    Jim, I think we're on the same wavelength here!
    Talk is cheap because supply exceeds demand

  7. #7
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,411
    Thanks
    447
    Thanked 405 Times in 377 Posts
    Quote Originally Posted by access-mdb View Post
    Jim, I think we're on the same wavelength here!
    It's been a very long time since I did any database programming. This brings back a lot of fond memories!

    I used FoxPro for DOS and FoxPro for Windows!

  8. #8
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,411
    Thanks
    447
    Thanked 405 Times in 377 Posts
    Quote Originally Posted by shin View Post
    mrjimphelps,

    you helped me made a lot more sense of what is going on. Because I used the combo box to bring up the previous record for editing, it brings up the ID as well which is obviously a reference field. So when I go and edit data in the fields and click save it just changes the data for that record. Which brings me to my next question, after I bring up a record and edit some of its information, Can I tell access to generate a new ID and save it as a new record?

    I understand what you mean about the "add new record" Button and yes I understood that button wrongly. I thought it was meant to tell access to add the data I just entered as a new record at the end. I will change the setup of the form slightly.
    If you haven't entered much data, you may want to consider starting over from scratch, so as to get a nice, clean system.

    First: Decide which data files you need to have, in order to keep all of your information. You need to keep the kinds of data separate, that is, in separate data files. For example, keep the customer information in one file, product information in another, and records of purchases in another.

    The customer information file and the product information file will each contain very complete information about customers and products. However, the records of purchases file will not; it will contain minimal information (item number, date of purchase). What it will contain is reference fields which will link the purchase records back to the products file, and other reference fields which will link the purchasers back to the customer information file.

    The reason you keep minimal, rather than full, info in the records of purchases file is because you don't want to have duplicates of the same data scattered around in different files; also, if you update some product info, you go only one place to do it; the records of purchases file stays updated, because it simply looks to the products file and customer info file for its info.

    This process of segmenting the data into the minimum number of places to store it is called "normalizing the database".

    Step 2: Decide what functionality you need. The first thing that comes to mind is the ability to generate invoices. Invoices are generated from the records of purchases file; but the reference fields allow the invoices function to look to the customer and product files for more complete information than is contained in the records of purchases file. Therefore, you don't have to store all of the info in the records of purchases file.

    Step 3: Do regular backups of your database system!

    Generally, functions such as invoices are set up by writing the commands, etc., in Visual Basic; but some functions are already available as "canned" functions in Access.
    Last edited by mrjimphelps; 2016-04-07 at 12:44.

  9. #9
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,411
    Thanks
    447
    Thanked 405 Times in 377 Posts
    Just curious: Why did you choose Access? Excel is a lot simpler, and it may be all you need. You can set up several different spreadsheets in Excel and manually keep them up-to-date. It could match whatever paper system you have been using.

    The benefit of Excel is that it is a lot simpler, because you keep track of everything much more manually than you do with Access.

    With Access, you are able to relate a lot of different databases to each other, to get an entire relational database system. With Excel, you store your data in several spreadsheets, and you manually keep it all together. So you don't get as much automated functionality with Excel, but it is a lot simpler to keep track of everything.

    In order to do Access right, you need someone who is skilled at relational databases, and who has some proficiency at programming in Visual Basic. With Excel, however, you could quickly come up to speed yourself and handle it all yourself.

    You may want to start over, this time using Excel instead of Access.

  10. #10
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,726
    Thanks
    147
    Thanked 156 Times in 149 Posts
    Jim is right if all you're wanting is a simple list of clients. But you haven't said what you want it for, and indeed, who has asked you to do it. There is something called specification creep - which means you asked to do something simple, but the requirement begins to get more and more complex ("This is good, can we do X with it? What about Y"). I've written a billing database which was a quick and dirty one to get us going, but was still in use for some years after I left that section.

    If this is a possibility, then Access might be better (I've never thought Excel is particularly good at being a database, but that's based on Excel 203 - it may be much better now).

    It's really up to you to choose your path, but be assured, there's plenty of people on here who will give you advice; all you have to do is ask!
    Talk is cheap because supply exceeds demand

  11. #11
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,411
    Thanks
    447
    Thanked 405 Times in 377 Posts
    I'm concerned that if he doesn't understand the concepts of a relational database, he will get lost in the complexity of doing it that way. As you say, specification creep can happen. When that happens, if you don't know what you're doing, you can quickly have a real mess on your hands.

    On the other hand, if he does it in Excel, and specification creep occurs, he can much more easily keep up with it, even redoing or scrapping entire spreadsheets if required, without nearly the potential for confusion as would be in a full-blown Access environment.

    With Excel, he would always manage the logic manually; with Access, the programmer manages the logic by writing code.

    Based on his initial statement "I am totally new to Access and I have a problem saving records on a form.", I believe he'd be much better off with Excel than with Access.

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    It would help if you could provide the database with any sensitive data taken out.

    What I would do in VBA behind a button is to:
    a. use the INSERT INTO command to insert a record based upon the ID from the combo box
    b. using the new records ID change the OnFilter field to read the new record onto the form
    c. make your changes and you should be ok.
    Attached Files Attached Files
    Last edited by patt; 2016-04-10 at 06:52. Reason: added an example database

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    I have made a change as follows:
    Code:
        DoCmd.SearchForRecord , "", acFirst, "[id_MainTable] = " & rs!id_MainTable
    '    Me.Filter = "[id_MainTable] = " & rs!id_MainTable
    '    Me.FilterOn = True
    The previous using the filter commands only shows the new record, after your changes you then have to disable the filter to see all the records again, the SearchForRecord command points to the record just added.
    Last edited by patt; 2016-04-10 at 08:48.

Posting Permissions

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