Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    PATRICKPBME is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    171

    Form Template Updates Tables But Does Not Save Info on Form?

    I read a recent post which I thought might address my current problem but it seemed that problem was much more complicated than my own. With that...

    I have a database with 6 tables. There are 649 records in each table with different information regarding each client. The tables are all linked, data on each being updated by a form that was designed through a query selecting fields from the respective tables.

    At the moment, when I open the form to add a new client, it does show that there are 649 records currently existing. I go to "New (blank) record" to create a new record and enter in all of the information for the new client (number 650). The form template does indicate that this new form is now "650 of 650." I hit save and exit the form. When I reopen the form, this new record is nowhere to be found. Only the previous 649 are present. At the same time, all of the tables the form is supposed to update do have all of the new information I entered.

    Can't figure out why the form doesn't keep the new data I entered when all of the tables relying on the form do! Thanks.

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    One of the links in the record source is producing this, the new record is not linking due to missing data.

  3. #3
    PATRICKPBME is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    171
    Are you saying that if I leave a blank on the form which links to a table where that information would otherwise be stored the form will not save. In other words, if I have a table for salary but I don't have that salary at the moment I have to put something there for the form to be saved?

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Go to the record source - that is where the problem lies, not with the form. See how many records the query is producing, if it is not producing the correct number of records that means that the links between the tables are all inner joins, i.e. records must exist on each side of the link. One of the joins is not linking the one new record.

  5. #5
    PATRICKPBME is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    171
    Okay, assuming I understood, I ran the query and it only produced 648 of the 649 records showing in every table except one. That "one" table has 648 records. I added to that table the missing data in that field that links all the table together. Running the query again now shows all 649 records. Also, now when I open the form it shows me the record that was otherwise gone. So much for the good news.

    Now, I enter a new record into the form and the record counter then reflects 1 of 650 which would be correct. Then I hit save or refresh and once again the record is gone. Further, now the information I enter for a new record that at least appeared in the tables other than the "one" mentioned now only shows in the table with the primary key.

    Just don't get what I am missing here.

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    It is very difficult to add and/or update data when a record source is based on a query, Access is very picky about which fields it will allow to be updated. Again, the problem is not with the form, get the query working. Open the query in datasheet view and do what you just did on the form - add a new record, enter the data you entered - and see what happens. Trying to figure it out from the form is muddying the waters.

  7. #7
    PATRICKPBME is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    171
    Good call.

    Running the query an then adding the data seems to work. There is only one field in one table that for whatever reason is not "absorbing" the data I am putting in and that is not fatal. I guess going forward I can just enter data this way although with several fields on the final product a form made it a lot easier to add things. I am going to check to see whether once the data is initially entered (via the query) I can do amendments on the form.

    Thank you for your help and insight. I am listed here as a "competent performer" for whatever reason which gives me much more credit than due. Self-teaching Access since November and have a long way to go. Didn't know Access would get picky with me re the form!

    Thanks again.

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The form is displaying what is in the query. Once you understand what is really going on with your record source it should be easy to convert that into a form. I was trying to get you to focus on where the problem lay. You're right, entering data thru a datasheet is a real pita. Now that you understand the data and how it is behaving you can go back to your form.

  9. #9
    PATRICKPBME is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    171
    PITA puts it mildly.

    But what I don't understand is that if the the info/process works with the datasheet, and the forms fields are tied directly to the query and the resulting fields when it is run, why is the form not working? In my world, is adding to the query (datasheet) fills in the form, shouldn't it work in reverse?

  10. #10
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The form should behave the same as the query in datasheet view - presuming that there are no events happening (code or macro) on the form. If you use the wizard to create a form based on a query then the behavior of adding/updating will be the same. Have them open side-by-side and watch what the differences are in order to pinpoint the problem with the form.

  11. #11
    PATRICKPBME is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    171
    I will give that a shot and let you know. Thanks again.

  12. #12
    PATRICKPBME is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    171
    A new day so I am going to try what you suggested. Just to make sure I understand, I should:

    1. Run the query upon which the form is based in datasheet view
    2. Open the form.
    3. Add data to fields (in one or the other) and see if the "other" reflects the change?

  13. #13
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Yes, to see if the form is not doing what you think it should be doing, if it is doing anything differently to the query.

  14. #14
    PATRICKPBME is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    171
    Well, it now seems more bizarre to me than yesterday! I ran the query in database. I then added a new name to that datasheet. I refreshed...and the name disappeared from the datasheet. However, when I went to the table that should have reflected the new name, it was there. Wondering why it won't stay in the datasheet but stays in the table. Also the new name does not appear when I look form. The table went from 649 records to 500. The datasheet and form did not.

  15. #15
    PATRICKPBME is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    171
    I think I may have found something that might help you identify where I messed up and what the problem might be. History to follow

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

Similar Threads

  1. How or can I, save a form as a template?
    By rebfein in forum Forms
    Replies: 8
    Last Post: 01-22-2017, 12:44 AM
  2. Replies: 1
    Last Post: 11-15-2014, 12:43 PM
  3. Replies: 2
    Last Post: 07-15-2014, 10:39 AM
  4. Replies: 7
    Last Post: 12-02-2012, 08:32 PM
  5. How do I save info from a form
    By chibearsfan6 in forum Access
    Replies: 4
    Last Post: 10-24-2012, 03:46 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