Results 1 to 7 of 7
  1. #1
    brigitteAT is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2021
    Posts
    61

    Question how to safe data from a form in the underlying table before the privat sub starts to work with them

    in the last field (text) of a form (based on Table1), I have created a private sub, which should copy these data simultaneously to a second table (Table2)



    my problem now is, that this doesn't work before I have entered the next line in the form,
    telling me that "the data cannot be inserted because they have to be in relation to the other table"

    (the procedure works well, as soon, as I go to the next line. and then back to the field with the procedure... but its quite uncomfortable for each line to go back and forth , to make the copy-Process work...I have thousands of lines ... book-keeping)

    is there any comand I could insert to the procedure to save the data of the line I'm in first in Table1 and then start the redordset for Table 2???

    when I use "requery" the curser jumps to the beginning - that's no good...
    thus I'm looking for a command that either leaves the curser on the field I am or jumps to the next line...

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the data in the master form MUST be saved before you can copy it or add/copy any data in a SUB form.
    New data must generate the key (autonum), then subform data can be added.

    you don't make 2 recordsets, just run an append query to make a copy.

  3. #3
    brigitteAT is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2021
    Posts
    61
    I don't make 2 recordsets! the data in the form are those from/for the underlying Table1 !!!
    but when I finish a line I would like just to type on in the next line... (and meanwhile the recordset should copy the data to a second Table2.. but not exactly the same data, but based on those of Table1+Form)
    thus I need a command/trick?, which first saves the data in Table1 immediatley before the recordset-procedure works with the data in Table1
    if I were more experienced I could may be start a procedure while I go to the next line?... but so far I only know hot to add procedures to the field-Properties...

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    More importantly, why do you want to duplicate data from Table1 into Table2?
    Data should only ever be stored once so this situation should never arise
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    brigitteAT is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2021
    Posts
    61
    I know the principle, but as I'm not experienced, I need the redundent data to control my Procedures and dependencies...
    and in Table1+Form I decide about on which account the amount of expense or income should show up and in Table2 I split the amount to netto/VAT/Brutto and decide wheter it shows up in the credit or debit side....
    .. and don't worry, I'll be the only one ever to work with this mdb... :-):-):-)
    but still looking for a solution to my problem .....
    is there no command such as requery to save the data immediately?(before I go to the next line)
    or is there a way to place the cursor automatically back in the last field of the previous line after entering the next line?
    or where could I put the procedure so that it starts "when going to the next line"...?

  6. #6
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Perhaps a picture of your forms with the fields highlighted would help.

    It sounds as if you have the Master/Sub forms almost the wrong way around?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    brigitteAT is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2021
    Posts
    61

    how to safe data from a form in the underlying table before the privat sub starts to work with them

    sounds so, but I need it that way:
    as one booking line in table1+form is while booking (via recordset) then split up into 2 and more separate lines in Table2
    and thus I want all these lines in table2 to have the same ID from Table1 so when I delete the line in Table1 ALL lines in Table2 are also deleted
    (or when I want to make changes, i can call up exaktly all these lines in a separate form)

    the last field I fill in in the form(ofTableFL=1) is FL text, so I placed the sub (to create the different lines in Table FJ=2) in the properties of this field
    It would be great if I found a method, to just press enter/Tab (to run the procedure) and work on with the next line
    I don't want to change from keyboard to mouse every line so
    otherwise I would have to make a new field (done/not yet done) and start the process at the end of the page, for all lines above....
    (but I cannot make buttons in this view of the form)

    HAVE TO WORK ON SOMETHING ELSE FOR THE MOMENT - will try to get help later!!!
    Attached Files Attached Files
    Last edited by brigitteAT; 01-21-2021 at 05:43 PM. Reason: maybe later not so important at the moment

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

Similar Threads

  1. Replies: 7
    Last Post: 05-09-2019, 01:59 PM
  2. Replies: 6
    Last Post: 12-06-2015, 08:21 PM
  3. Replies: 12
    Last Post: 11-11-2014, 02:10 PM
  4. Replies: 0
    Last Post: 03-13-2012, 12:57 PM
  5. Form not updating underlying table
    By Kirsti in forum Forms
    Replies: 8
    Last Post: 02-10-2012, 12:23 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