Open form to specific record after status update

[camerontaylor]

I have a main form, and on that form I have a combobox with employee names, and a button which when pressed, will open a new form with the project that the employee selected has worked on or is working on. On this new form, there are 8 fields, of which all are disabled except for a project status field (combobox for different selections). Each project is a record in a table called ProjectList, and I have the primary key set as an incrementing autonumber, instead of the sequentially number project number (EPyymm-##).

I want to write some VBA for the After-Update() event of the status combobox, which says that when the status = "Completed", open a new form for that specific record which has been updated, so that the user can change the "CompletionDate" field of the record. I am struggling to think of a way to open the form to the specific record, unless there is a way to create a variable with the project number of the updated record which I can reference for opening the updateCompletionDate form.

Any help or advice for a different way to do this is greatly appreciated.

 

[Gasman]

You could open the form with the criteria or use openargs?
Look at the openform parameters.

 

[camerontaylor]

use openargs

I'm pretty unfamiliar with how openargs works, could you possibly give a very general example of how I might do this?

 

[Gasman]

I would use the WHERE option in the openform myself.
https://docs.microsoft.com/en-us/office/vba/api/access.docmd.openform

However OpenArgs
https://docs.microsoft.com/en-us/office/vba/api/access.form.openargs

 

[camerontaylor]

I thought about doing something like that, but I'm not sure how to set the Where argument up so that the new form has the record which has had its status updated to "Completed"? Because I can't just open a form which has all records which are completed, as it wouldn't open to the correct completed record. I just need to be able to set a variable = the project number of the record which has had its status changed. I thought about doing something like projectNum = Me.ProjectNumber but that didn't seem to work

 

[Gasman]

Say you have a field call ProjectID and the control on the initial form is txtProjectID, then it would be

"ProjectID =" & Me.txtProjectID

Look at the links for the syntax.