Results 1 to 13 of 13
  1. #1
    liam_898 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Posts
    11

    Moving to new record from a different form

    Hey guys,



    I've got two forms. Form A which displays 'Customer purchases' and form B that allows the user to add customer details quickly (a sub form)

    The issue I have is that I need requery form A upon closing from B to show the data added. This results in form A moving to record 1 rather than a new record that was previously selected.

    Any suggestions guys?

    Only thing I can think of is setting the onload of form A to:

    Code:
    DoCmd.GoToRecord , , acNewRec
    But from the users perspective I don't particularly want that

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Maybe this link will be helpful: http://www.baldyweb.com/Requery.htm

  3. #3
    liam_898 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Posts
    11
    Quote Originally Posted by RuralGuy View Post
    Maybe this link will be helpful: http://www.baldyweb.com/Requery.htm
    Hmm, could work, but a new record would show as null.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I thought you said a New Record was previously selected?

  5. #5
    liam_898 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Posts
    11
    Quote Originally Posted by RuralGuy View Post
    I thought you said a New Record was previously selected?
    Well yes, add new record is open but with the above example it can't reference a new record as it has no ID :/

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    So you need to tell FormA what the NewRecord ID is, right?

  7. #7
    liam_898 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Posts
    11
    Quote Originally Posted by RuralGuy View Post
    So you need to tell FormA what the NewRecord ID is, right?
    Well it has no ID. From A and B are separate tables.

    Suppose I could save the ID when opening form B, which I think is what you're getting at.

    Cheers

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I guess I do not understand your db structure. What tables are involved in FormA and what in FormB. What record do you have selected in FormA before opening FormB.

  9. #9
    liam_898 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Posts
    11
    But again that wouldn't be ideal as there are required fields that would need to be changed

  10. #10
    liam_898 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Posts
    11
    Okay, here are some print screens. its tbl_Customers, tbl_Customer_purchases and tbl_Products that are being used.


    Table structure



    This is the form in question (Form A).. Customer field references customer ID, product field references product ID.


    This is form B..


    However when I click save and close it goes back to record one, which is not what I want :P

    Code that is used to refresh is:
    Code:
    Public Sub Command17_Click()
        
        Call frm_Customer_sub_SaveRec
        Forms!frm_Customer_purchase.Requery
        Call frm_Customer_sub_CloseSubfrm
        
    End Sub

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Are you aware of the fact that the NotInList event of a ComboBox will allow you to add a New Customer and select that customer after you have created them?

  12. #12
    liam_898 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Posts
    11
    Quote Originally Posted by RuralGuy View Post
    Are you aware of the fact that the NotInList event of a ComboBox will allow you to add a New Customer and select that customer after you have created them?
    Yes, but that combo box is displaying the customers first and last name. So to do it that way I'd need to add the ID and the first and last name aswell from one combo box.. and I'm pretty sure that cannot be done.

    It's not what I wanted, but I've come up with a quick work around. I'll just reopen the form A when from B is closed and ensure From A opens on a new record with the 'acAdd' command.

    I just thought there would be a quick way to make one form go to a new record from a different from event. (I'm relatively new to VBA so just thought I'd ask).

    Appreciate your time ruralguy

  13. #13
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Glad to hear you got it sorted. I marked this thread as Solved for you.

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

Similar Threads

  1. Replies: 1
    Last Post: 12-12-2011, 01:58 PM
  2. Moving From Form to Report
    By herbc0704 in forum Forms
    Replies: 3
    Last Post: 12-05-2011, 02:20 PM
  3. Moving a record.
    By Cutter596 in forum Queries
    Replies: 1
    Last Post: 08-02-2011, 02:49 PM
  4. Things moving around on my form
    By jlm722 in forum Forms
    Replies: 0
    Last Post: 09-30-2009, 03:34 PM
  5. Moving Information from One Form to Another
    By bsharpbyoung in forum Forms
    Replies: 2
    Last Post: 03-22-2009, 09:37 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