VBA code to load a form with specific record

[|PiP|]

How do I write VBA code for a command button that loads a form, and
displays a given record? I have built a form that shows all my
uncompleted orders, and when the user presses "Order is Complete" it
sets the order to complete, and shows the form that lets you browse
through the completed orders. However, I want it to show the record of
the order that I just completed. How can I do that?

 

[Ron Hinds]

How do I write VBA code for a command button that loads a form, and
displays a given record? I have built a form that shows all my
uncompleted orders, and when the user presses "Order is Complete" it
sets the order to complete, and shows the form that lets you browse
through the completed orders. However, I want it to show the record of
the order that I just completed. How can I do that?

Are you using DoCmd.OpenForm? If so, the last argument to that method,
OpenArgs, is a string. You could set it to the primary key of the record you
want, probably the OrderID. Then, in the Open Event of the completed orders
form, you look for the Form's OpenArgs property, and move to that record.
Here's some sample code (obviously incomplete, with no error checking):

In the click event of "Order Is Complete", open the new form with the
OrderID as the OpenArgs argument:

DoCmd.OpenForm "frmCompletedOrders", , , , , [OrderID]

In the Open Event of frmCompletedOrders:

Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone

rs.FindFirst "OrderID=" & OrderID

Me.Bookmark = rs.Bookmark

 

[fredg]

How do I write VBA code for a command button that loads a form, and
displays a given record? I have built a form that shows all my
uncompleted orders, and when the user presses "Order is Complete" it
sets the order to complete, and shows the form that lets you browse
through the completed orders. However, I want it to show the record of
the order that I just completed. How can I do that?

Code the command button's click event:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm "FormName", , , "[RecordID] = " & Me![RecordID]

The above assumes the Unique [RecordID] field is a Number datatype.
Change [RecordID] to whatever the actual unique prime key field is.

While the just completed record is the form's current record, click
the command button. The new form will open and display the same record

 

[|PiP|]

Anyone know a good site to learn VBA coding, specifically for MS Access?

How do I write VBA code for a command button that loads a form, and
displays a given record? I have built a form that shows all my
uncompleted orders, and when the user presses "Order is Complete" it
sets the order to complete, and shows the form that lets you browse
through the completed orders. However, I want it to show the record of
the order that I just completed. How can I do that?

Code the command button's click event:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm "FormName", , , "[RecordID] = " & Me![RecordID]

The above assumes the Unique [RecordID] field is a Number datatype.
Change [RecordID] to whatever the actual unique prime key field is.

While the just completed record is the form's current record, click
the command button. The new form will open and display the same record

 

[Ron Hinds]

Oops - this line

rs.FindFirst "OrderID=" & OrderID

should have been

rs.FindFirst "OrderID=" & Me.OpenArgs

How do I write VBA code for a command button that loads a form, and
displays a given record? I have built a form that shows all my
uncompleted orders, and when the user presses "Order is Complete" it
sets the order to complete, and shows the form that lets you browse
through the completed orders. However, I want it to show the record of
the order that I just completed. How can I do that?

Are you using DoCmd.OpenForm? If so, the last argument to that method,
OpenArgs, is a string. You could set it to the primary key of the record you
want, probably the OrderID. Then, in the Open Event of the completed orders
form, you look for the Form's OpenArgs property, and move to that record.
Here's some sample code (obviously incomplete, with no error checking):

In the click event of "Order Is Complete", open the new form with the
OrderID as the OpenArgs argument:

DoCmd.OpenForm "frmCompletedOrders", , , , , [OrderID]

In the Open Event of frmCompletedOrders:

Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone

rs.FindFirst "OrderID=" & OrderID

Me.Bookmark = rs.Bookmark

 

[Ron Hinds]

Start here:

http://www.mvps.org/access

Anyone know a good site to learn VBA coding, specifically for MS Access?

How do I write VBA code for a command button that loads a form, and
displays a given record? I have built a form that shows all my
uncompleted orders, and when the user presses "Order is Complete" it
sets the order to complete, and shows the form that lets you browse
through the completed orders. However, I want it to show the record of
the order that I just completed. How can I do that?

Code the command button's click event:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm "FormName", , , "[RecordID] = " & Me![RecordID]

The above assumes the Unique [RecordID] field is a Number datatype.
Change [RecordID] to whatever the actual unique prime key field is.

While the just completed record is the form's current record, click
the command button. The new form will open and display the same record