Results 1 to 9 of 9
  1. #1
    PPat is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    17

    Opening form to Appended Record

    Hi everyone,

    I have a question concerned forms. On my form, I have a button that appends, a single, record to a new table (this was done for a special case, not normalized but organized). The button runs a macro that append a record to a new table, and deletes the record from the old table. Then, the macro opens the new form (associated with the new table) and closes the old form.



    I would like to set this up so that the user can append a record, and have the form pop up to the record that was just appended. This is so that the user can add in new information that wasn't in the fields of a previous table. Any idea what SQL or VBA code I could use to make it do that? Any advise or information is greatly appreciated.

    Thank you,
    PPat

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Do you know unique ID or combination of data to uniquely identify this new record? Open form filtered by that info.

    Otherwise, if you know the record will be the last one in the dataset, code can move to the end of the records when form opens or have the form's RecordSet in descending order.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    PPat is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    17
    Yes, each record has a unique PK of their "student ID". It most likely won't be the last one in the data set ever, seeing as there are so many students/unique records. How would I write the criteria so that it opens the new form on last record appended?

    Just as a note: The student ID is in a textbox called "StudentIDTextBox", can I make it open to the last ID added? If so, how?

    And thank you for your time June7, you helped me last time and I greatly appreciate it.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If you have the PK when opening the form can use the WHERE CONDITION of OpenForm to filter form to the one record, like:

    DoCmd.OpenForm "form name", , , "StudentID=" & Me.StudentIDTextBox
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    PPat is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    17
    So I created the module, and now I want to build it into my button. Here is what was part of the macro I had converted into a module. What should I do from here?

    Function Move_to_Old_Problems()
    On Error GoTo Move_to_Old_Problems_Err
    DoCmd.OpenQuery "Move to Old Problems", acViewNormal, acEdit
    DoCmd.OpenForm "Old Problems Form", acNormal, "", "", "StudentID=" & Me.StudentIDTextBox, acNormal
    DoCmd.OpenQuery "Delete from New Problems", acViewNormal, acEdit
    DoCmd.Close acForm, "New Problems Form"

    Move_to_Old_Problems_Exit:
    Exit Function

    Move_to_Old_Problems_Err:
    MsgBox Error$
    Resume Move_to_Old_Problems_Exit

    End Function
    Also I attached a screenshot, if that helps. Click image for larger version. 

Name:	Capture.PNG 
Views:	11 
Size:	48.4 KB 
ID:	11903

  6. #6
    PPat is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    17
    Anyone know anything? I need to have this finished soon.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    What you have is a function procedure. If you want code behind a button on a form:

    1. form in design view

    2. create button on form

    3. select button, open Properties dialog, select Events tab

    4. in button Click event select [Event Procedure]

    5. click the ellipses (...) next to the Click event, this will open the VBA editor in the Click event procedure

    6. type or copy/paste code
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    PPat is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    17
    Ok, I figured out how to do it in Access. Now I just need to know how to open a form to an appended record, using VBA?? (And thank you again for your response).

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Doesn't matter how the record is created (appended or key entry), open it the same way. If you know unique identifying info for the record then use that as criteria. If you don't know the unique info then give more info. I don't know enough about your situation to be more specific. If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 9
    Last Post: 02-04-2013, 03:22 PM
  2. Help with opening form to specific record
    By manic in forum Programming
    Replies: 7
    Last Post: 09-18-2012, 08:44 PM
  3. Opening a form for a particular record.
    By 86rainey in forum Forms
    Replies: 6
    Last Post: 06-24-2012, 10:14 AM
  4. opening a form with last one record.
    By cap.zadi in forum Forms
    Replies: 1
    Last Post: 01-17-2012, 12:45 AM
  5. Replies: 9
    Last Post: 09-16-2011, 03:52 PM

Tags for this Thread

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