How to have GUI move user to newly created record in form?

stuckp1
stuckp1 used Ask the Experts™
on
Motive:

    The User wants to eliminate re-typing repetitive data whenever a new record is created.

Approach/ Background:

   
The idea is to have the User go to an existing record.
   
The User then clicks on a radio button in the "Footer" region to indicate what parts of the existing record are to be used to pre-populate the new record.
 
The User clicks on a button that triggers the VBA that
   a) Creates the record.
    b) Copies over the desired fields indicated by the User's radio-button choice.
    c) Moves the User to that newly created record.

What works:

 
 I am able to create the new record.
  I am able  to pre-populate it.

Problem:

   
The GUI/Form doesn't show the changes until I manually Refresh All.
   
The User is not brought to the newly created record.
Illustration of GUI/Form and VBA code
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bill RossProgrammer

Commented:
Hi,

Please post you code for the button.

Thanks,

Bill

Author

Commented:
Here is the code:
Private Sub Create_New_Record_Click()
    Dim dbStorageAuto As DAO.Database
    Dim rstStorageAuto As DAO.Recordset
    Dim Architect_Pane As Boolean
    Dim Windows_Pane As Boolean
    Dim Storage_Pane As Boolean
    
    Set dbStorageAuto = CurrentDb

    Set rstStorageAuto = dbStorageAuto.OpenRecordset("dbo_LUN_Requirements_Report", dbOpenDynaset, dbSeeChanges)
    Architect_Pane = Form_Storage_Automation_Main_Form.Architect_Pane_Option
    Windows_Pane = Form_Storage_Automation_Main_Form.Windows_Pane_Option
    Storage_Pane = Form_Storage_Automation_Main_Form.Storage_Pane_Option
    
    If (Architect_Pane) Then
    
        rstStorageAuto.AddNew
        rstStorageAuto("Customer_Name").Value = Form_Storage_Automation_Main_Form.Customer_Name
        rstStorageAuto.Update
   End If
   Form_Storage_Automation_Main_Form.Refresh
End Sub

Open in new window

Well there is nothing in the code that attempts to take the user to the new record.
To get to the new record you need some ID value that can be found in code.

BUT the basic problem is that a .Refresh doesn't bring in new records.. You must use .Requery to do that.

So you need code that does...

saves the new record
identifies the key for the new record and stores the value
requery the form's recordset
find the record with the saved key and move to that record

Author

Commented:
Hi Peter,
I've gotten some pointers and tried out this, but, being a VBA "newbie" I'm still a bit at a loss.
Thus the code does some of what you ask, but generates.

"Run time error '3159': Not a valid bookmark."

I apologize for asking you these, probably basic, questions:
How would I save a record? Is that the .Update?
Is the "key" you mention the "bookmark"? Why do I generate the above error?
"Requery" would be the Requery() function?
"find the record ...  move to that record" How would you do that?
Where would you best place that code?
Private Sub Create_New_Record_Click()
    Dim dbStorageAuto As DAO.Database
    Dim rstStorageAuto As DAO.Recordset
    Dim Architect_Pane As Boolean
    Dim Windows_Pane As Boolean
    Dim Storage_Pane As Boolean
    Dim myBookmark As Variant
    
    
    Set dbStorageAuto = CurrentDb

    Set rstStorageAuto = dbStorageAuto.OpenRecordset("dbo_LUN_Requirements_Report", dbOpenDynaset, dbSeeChanges)
    Architect_Pane = Form_Storage_Automation_Main_Form.Architect_Pane_Option
    Windows_Pane = Form_Storage_Automation_Main_Form.Windows_Pane_Option
    Storage_Pane = Form_Storage_Automation_Main_Form.Storage_Pane_Option
    
    If (Architect_Pane) Then
    
        rstStorageAuto.AddNew
        rstStorageAuto("Customer_Name").Value = Form_Storage_Automation_Main_Form.Customer_Name
        rstStorageAuto.Update
        myBookmark = rstStorageAuto.Bookmark
        
    End If
    Form_Storage_Automation_Main_Form.Refresh
    Form_Storage_Automation_Main_Form.Bookmark = myBookmark
End Sub

Open in new window

MIS Liason
Most Valuable Expert 2012
Commented:
There is a "Duplicate record" option (under "Record Operations") when using the wizards to create buttons.
Here is the code:

    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdCopy
    DoCmd.RunCommand acCmdRecordsGoToNew
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdPaste

...This will copy the entire current record into a new record.

So then you will probably want to clear out some of these duplicated fields,..
...Then use code like this:
me.SomeField1=""
me.SomeField2=""


Finally if you really need to save the record, you can use code like this:
DoCmd.RunCommand acCmdSaveRecord


HTH

JeffCoachman

Author

Commented:
Thanks Jeff,
I'll give your code a try and let you know how it goes.
It is indeed ingenious of you to copy the whole thing and then trim out what you don't want.
Clever! :-)
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
;-)
Just another option for you to explore....

Author

Commented:
A clever way of turning a problem on its head by removing unwanted field contents.
Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial