Case statement help

[vjb]

A text box named statcurx holds the value of the current status of a case.
A text box named NewStatusx holds the value of the new status of a case.
I have the following code at the on close event of the form -

Select Case stacurx
Case 22
If Me![NewStatusx] = 14
'save a date to a field in the underlying table
DoCmd.OpenForm "frmPart2",,, "Key = forms![CaseChange]![Key]"
End If
End Select

The value of the current record's stacurx is 22 and NewStatusx is 14.

When I close the form nothing happens - no error message and the form doesn't open.

Thanks!

PS. I also need some help with the 'save a date... part.

 

[DBL]

Try

Select Case Me.[stacurx]
Case 22
If Me![NewStatusx] = 14
Me.[Datefieldname] = Date
DoCmd.OpenForm "frmPart2",,, "Key = " & forms![CaseChange]![Key]
End If
End Select

Another suggestion is to step through the code and see where it's falling down. Put "Stop" (without quotes) before the select case and as the code runs it will open the module, highlight the active row in yellow and you can see if it's working as it should. F8 moves to the next line of code. Remember to take the stop out when you've fixed it or it will open up the module every time the code runs!

 

[Pat Hartman]

Putting edits in the close event won't do you any good. The record has ALREADY BEEN SAVED. You need to put final edits of this type in the form's BeforeUpdate event so you can Cancel the event if the edits fail. If you don't cancel the update event, the bad data gets put in the table and your users can just blow by the error message.

 

[vjb]

Thanks so much for the help....

I tried the stop and found the solution to another problem I was having....(the bound column on a combo box filled based on the value in a text box)

BUT...

If I put the code in the BeforeUpdate property then the query to fill a combo box based on the value of a text box can't find the criteria it needs.

Any ideas on how to best to handle the situation?

Based on what status a case is in it can only proceed to certain status codes - that's why I wanted to limit the combo box for NewStatus to certain values.

Based on CurrentStatus (stacur) and NewStatus certain parts of forms are opened and various values are stored to fields in the underlying table BUT...

those fields are not on the form. I'm wanting to make the db more automated, less data entry, etc.

So.....

If a case is moving from a CurrentStatus of 02 to a NewStatus of 06 then ....
open the right form (got that part to work)
save the ChangeDate (from the form) to c-dat06 (not on the form) in the underlying table and other values stored to other fields in the table

If from 06 to 08 then ...
other parts of the form and...
other values saved to fields in the underlying table

and so on....

I've tried this code -

Dim db as DAO.Database
Dim rs as DAO.Recordset
Select case Me![stacurx]
Case 18
If Me![NewStatusx]=14
DoCmd.OpenForm "frmPart2ItemsBtoQ",,,"Key = forms![CaseChange]![Key]"
Set db = CurrentDb
Set rs = db.OpenRecordset("DEMO",dbOpenDynaset)
rs.AddNew
rs![c-dat06] = [changeDate]
rs.Update
Set db = Nothing
rs.Close
Set rs = Nothing
End If
End Select

The ChangeDate from the form is not being saved to the c-dat06 field in the table.

Do you have any suggestions?

Thanks so much for your help already....
Making progress thanks to you!!

 

[Pat Hartman]

If I put the code in the BeforeUpdate property then the query to fill a combo box based on the value of a text box can't find the criteria it needs.

What good was the code doing you in the close event of the form?

Just because a field isn't displayed on a form doesn't mean that you can't update it. Make sure the field is in the recordsource. If you need to modify the query to add it, you'll need to do the following to make the form "see" the additional field - delete the queryname currently in the recordsource property. Move focus to some other property so that Access recognizes the property change. Then paste the query name back where it was. Then in the BeforeUpdate event of the FORM, you only need a single line of code:
Me.[c-dat06] = Me.[changeDate]

 

[vjb]

Thanks so much for your help. Looks like it's working the way I'd like...

Except....

How do I close the form that I just came from?

The user opens the SelectCaseChange form and enters the case number of the case they need to work on (make changes to). Then they click a button that opens the CaseChange form. The SelectCaseChange form is still open on top of the CaseChange form.

I really appreciate all your help! I'm learning so much!

Thank you!