Code to make a button on a form do more

[kyuball]

I was chastised pretty severly the last time I tried this , so here I go again!

I have a form that I want to use to change the status of clients from "open" to "closed" called frmcloseout. The status is being generated from a combobox in the client profile table which has the fields clientid (pk), lastname, firstname, status, opendate, and closeddate. When a client is first entered into the database, he or she goes in as "open" and only the opendate is filled in. From there, the user can see the client's information from a profile form. On the profile form, there is a button called closeout that opens frmcloseout that should change the client's status and thus close out the client's account.

This is the code that I copied from the code page for the closeout button (I just used the button wizard to do it):

Private Sub closeout_Click()
On Error GoTo Err_closeout_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmcloseout"

stLinkCriteria = "[clientid]=" & Me![clientid]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_closeout_Click:
Exit Sub
Err_closeout_Click:
MsgBox Err.Description
Resume Exit_closeout_Click

End Sub


What can I insert in there to 1) Upon opening the document, automatically change the client's status from "open" to "closed" and 2) fill in the empty closeddate field with the default date of Now() while still allowing for a person to edit the close out date in case they are entering the information at a later date?

 

[RossWindows]

I think something like this might work

Private Sub closeout_Click()
On Error GoTo Err_closeout_Click
 
Dim stDocName As String
Dim stLinkCriteria As String
 
     stDocName = "frmcloseout"
     stLinkCriteria = "[clientid]=" & Me![clientid]
 
     DoCmd.OpenForm stDocName, , , stLinkCriteria
 
[COLOR=blue]   Forms!frmcloseout!Status.Value = "closed"[/COLOR]
[COLOR=blue]   Forms!frmcloseout!closeddate.Value = Now()[/COLOR]
 
Exit_closeout_Click:
     Exit Sub
 
Err_closeout_Click:
     MsgBox Err.Description
     Resume Exit_closeout_Click
 
End Sub

This site (this page, specifically) will help you refer to any form/control from any other form/control
http://www.mvps.org/access/forms/frm0031.htm

 

[Galaxiom]

No need to include both an open/closed field and the close date field. The presence of a closed date indicates the same information. Just test for nullls in the closed date field to find the currently open records.

In any case the status really should not be stored as a string (eg "closed") as indicated by RossWindows. If the status has two states it should be a boolean value(Yes/No). If there are more states they should be indicated by a numeric code that represents the state. Working with strings is much slower than using numbers.

Does the Closeout form have any other purpose or would simply changing the status (or entering a CloseOut date) be sufficient? If all you are doing is indicating closeout then run an update command from the button on the Profile form to write the current date directly to the table. This change should appear immediately in any form bound to that table.

 

[kyuball]

Thanks Ross!

Worked like a charm!

Galaxiom: Basically, the form is just used to change client's status and indicate the date that it happened. I wanted to set up a one button command for someone inputting the data (not me) to be able to change status. For the most part, the status doens't change as not too many clients leave the program, but I just thought it would be better that they be able to just change that one field without having to go into an edit information form (which I have in there) that is used primarily to update profile information for those still in the program. I don't want to run some sort of autoupdate type thing for close out date as not everyone has a close out date until they are actually closed to the program. However, the statement you made about using Boolean value caught my eye. How would I replace the string "Closed" with a boolean statement?

Thanks for all of your help! You guys are a little rough on newbies, but this site has been nothing but educational for me...

 

[kyuball]

One more thing. The user would navigate to the form frmcloseout in three steps (I made it convoluted to make sure that the people using it would be SURE they want to close out a client): 1) They open form called frmopenlist upon opening the database that lists all of the open clients, 2) They click on a button that opens a form called frmclientprofile that shows all of the client's general info, 3) On the bottom of frmclietprofile, there is a button that opens a form called frmeditclient where they can change client's general info like names and such and 4) Finally, on the bottom of frmeditclient, the button called closeout resides.

I also want the button to close out the forms from which it was navigated to (frmclientprofile and frmeditclient) and update/ requery the client list (frmopenlist) to reflect that the client is closed and no longer on that list. Any more generosity of knowledge left out there?

 

[RossWindows]

...However, the statement you made about using Boolean value caught my eye. How would I replace the string "Closed" with a boolean statement?...

In table design view, you will have to change the Data Type of that specific field from 'Text' to 'Yes/No' then;
Optional; change the Format of that field to 'On/Off' to display the field as a checkbox in the table view itself. Also, you may want to set it's default value to True

So instead of interpreting the status of a client as "open" or "closed" you simply look at the checkbox and whether it's checked or unchecked.
There are definately other ways to accomplish this (as Galaxiom pointed out), but I think this is the most intuitive.

Use this line of code for each form you want to close:

DoCmd.Close acForm, "YourForm", acSaveYes

 

[kyuball]

Once again you have saved the day for me, Ross. One last question for this thread and I promise to stop this thread altogether. Would the following line of code make sense if I wanted frmopenlist to requery and reflect that the client that has been closed and is no longer on the list of open clients (which is what the form frmopenlist is for)?

Exit_closeout_Click:
Exit Sub
DoCmd.requery acform, "frmopenlist"

 

[kyuball]

I just thought about it and it occurs to me that I need to place it somewhere on the form frmcloseout's on close subroutine..... is there such a thing as DoCmd.requery?

 

[RossWindows]

I usually use Me.Requery (to refer to the form in which that code resides) or
Forms!YourForm.Requery to refer to any other form.

So, the workflow looks like this right?;
frmopenlist -> frmclientprofile -> frmeditclient -> frmcloseout

You could put the requery code in different places, it really depends on what your workflow looks like AFTER frmcloseout is used.

Will your users typically close out a client and then immediately close
frmcloseout, frmeditclient and frmclientprofile to return to frmopenlist?

If that's the case, you may want to put Forms!frmOpenList.Requery in the OnClose event of frmclientprofile like so;

Private Sub Form_Close()
On Error Resume Next
 
     Forms!frmopenlist.Requery
 
End Sub

Use "On Error Resume Next" because if the user navigated to frmclientprofile without going through frmopenlist,
then trying to requery frmopenlist if it isn't actually open will cause an error.
This error handler will basically ignore the error (if any) and continue on it's way.

 

[DCrake]

The crux of the matter is flagging the client as closed. The effort going into the design of a form can be negated by a simple Yes/No message box

If MsgBox("Are you sure you want to close this account?",vbQuestion+vbYesNo,"Close Account") = vbYes Then

   Run Update query to set the close date to today

End If

If you have a button on your customer profile form that is only available to power users you can set the enable property true or false. also you can make the date closed field editable if a date is prersent and the curernt user is also a power user.

David

 

[kyuball]

Ross, you are Elvis! You have really helped move my project along!

To David:

At this point, this database is so far in that I have decided not to set my open/ closed field to a Boolean type thing, but I was able to incorporate the message box. Your advice as well as Galaxiom's is inching me toward better database design for the future (i.e. moving away from lookup fields...)

Thanks to everyone who helped me out with this!

 

[RossWindows]

I feel honored. Never been compared to the king before!