How to use current record in VBA

[jammin140900]

Hi all,

I'm new to all of this and this is probably straight forward. I have a form in a MS Access database which is basically to be used like a checklist for a set of business processes in processing an application form that is received by the business. (The database is used to record the information from the application form and monitor stages to the application process.)

The 'checklist' is also used as a trigger point. That is, when certain items are flagged as complete, the 'status' changes automatically to let the user know that stage is complete.

I have tried putting a line of code under the "On Change" event of a combo box...

If Me.AppStatusID = 0 Then
Me.AppStatusID = 1

and that works fine for the first record. When I add a new record (next line item) using the combo box, the AppStatusID always goes back and changes the first record entry and not the current applicable record line. Could you please help? Presumably something needs to tell it to use the current record line?

Thank you

 

[jammin140900]

Additional Information

Sorry, I forgot to add that the checklist is currently a subform. Not sure if that makes a difference to the solution?

Thanks,
Mark

 

[tranchemontaigne]

I think you are having a locus of control issue. Try explicitly indicating where AppStatusID is located within your OnCurrent event. You may have to write something like this to get it to work correctly. I'm assuming that your subform is called "frmSubform" in this snippet.

[Forms]![frmMainMenu]![Forms].frmSubform![AppStatusID].value

Open a query and browse to the AppStatusID control with the expression builder to ensure that your syntax is correct, then copy the expression into your VBA code.

Hope this helps. I've used explicitly VBA pathing to control dynamic code based upon control values buried 4 subforms deep. You can make this work. Just try a little more.
________
Buy Herbalaire Vaporizer

 

[Steve R.]

I would like to offer a slightly different approach. You wrote

"The 'checklist' is also used as a trigger point. That is, when certain items are flagged as complete, the 'status' changes automatically to let the user know that stage is complete."

I would suggest using an option group where the "OnClick" event is used to trigger the changes.

In my case, I have a permit tracking system that monitors the progress of a permit from intake to completion. Only one state can exist at any one time, which is why an option group is used. If your project can have several states, then you might want to have a series of y/n check boxes. I don't think you would need to use a subform.

In my database, as you move from state to state by clicking on the option group, the appropriate controls are updated through the "OnClick" event. For example if a permit application is received, the user is provided with a review deadline for determining if the application is "complete" or not. If the permit is filed "complete" the reviewer is given a new deadline for completing the project.

Using the option group also allows projects to be queried by its state.

You used "Me.AppStatusID" with only two values. I would suggest that "Me.AppStatusID" have a variety of values. In my case a value of less than 10 means the project is pending and is either incomplete, complete, public review etc. Values of over 10 mean the project is complete but refers to issues such as "withdrawn"; approved under x,y,z,; "denied" etc. (Based on experience (running out of options), I would have used something like - less than 100 is pending and over 100 is complete.)

 

[jammin140900]

Thanks guys. I tried the first suggestion just now and it works. Thank you for your input. Helps to have someone elses fresh eyes glaze over it.

 

[jammin140900]

Checking data and copying a record upon non existance!

I've come across a temporary road block with this project! I've got a form with a subform (called frmChecklist) whereby if you double click on a line item in the subform, it opens up another form (called frmAuditing) where ChecklistID from both forms match.

Here's the code..

Private Sub Form_DblClick(Cancel As Integer)

DoCmd.OpenForm "frmAuditing", acNormal, , "ChecklistID = " & Me.ChecklistID

End Sub

That's perfect and all good, however when you create a new line item in frmChecklist whereby a new ChecklistID is created, (this is a autonumber) let's call it 15, as 15 is not automatically populated in the auditing table, it finds no matches when you try to open the form and therefore their is no data.

I need a way of copying/adding the ChecklistID from the Checklist table to the Auditing table so that it can match and open at that particular record.

I was trying to fiddle with the code above and try and get it to check the current record in the Checklist table and add the ChecklistID to the auditing table if it doesn't exist.

No idea where to start with the code to do this or if their is a easier way to tackle this (perhaps a query to the form). Any help would be greatly appreciated.

Cheers
Mark