Validation using events (Acc2k2 SP2 (as 2k, DAO))

Topic

New Reply

Hi All,
I have 2 comboboxes in a form header to aid in generating a case number (not the PK). They are cboStateCode and cboLocalityCode and are only visible and enabled during a NewRecord. The customer wants this case number generated BEFORE any other data is entered into the form. The cboStateCode may or may not be empty, but the cboLocalityCode should never be empty. The RowSource of cboLocalityCode is determined by the choice (or lack of) from cboStateCode. Right now I have the case number generation code in the OnEnter event of the ‘next’ control the user can Tab into (the ‘first’ control a user is allowed to Tab into in the Details section).
I am stumped as to where I can place my validation code to ensure this. I have tried LostFocus, but I can’t control the SetFocus to return to cboLocalityCode; OnExit, if I am in cboLocalityCode already, won’t let me even escape and Undo the record I have started if I wish to cancel and leave data entry; BeforeUpdate and AfterUpdate do not fire if all I do is to tab into and then out of the ComboBox; the BeforeUpdate of the form is too late because of the above mentioned customer’s wishes.
Where and/or how can I accomplish this?
Thank you.

gdr

Reply | Quote

Replies

Rather than a passive trap, perhaps try an active one with a “Create Case” command button situated just to the right of the two combo boxes. You’ll know when an OnClick events happens there! You can use the OnClick event to run any validation or error checking you’d like on the two combo boxes, and assuming an “all’s clear” from that, create the case number and unlock/reveal the remainder of the form.

Reply | Quote

Hi Shane and Thank You,
I had thought of that way as well and certainly agree that validation and error checking would be tons easier using a command button. I have brought that idea up to the customer, related my concerns, etc., but, ……… they would rather not, if at all possible. So I still may end up using the command button, but not just yet. So, at the moment, I need to find a way of doing it, as you said, passively.
Thanks again.

gdr

Reply | Quote

Are you trying to establish a Case number only after the Location combo has been chosen?
If so, why not use the OnExit from the Location combo box?
Or am I not even close in my assessment?
Pat

Reply | Quote

Hi Pat and Thanks,
Yes, I am trying to generate the case number after the LocalityCode has been entered. cboLocalityCode must have data, whereas cboStateCode will not necessarily have data because of various Tribal affliliations, which are not state related, but do have a Locality. If I use the OnExit of cboLocalityCode and I find that I have entered that box prematurely (i.e. forgot to enter a needed StateCode), or need to exit the form from adding a new record all together, the OnExit code, as I have it written, will not let me escape or Undo. The code below is what I am using in the OnExit event now

Private Sub cboLocalityCode_Exit(Cancel As Integer)
Dim strLocality As String
strLocality = Me!cboLocality.Text
If IsNull(strLocality) Or strLocality = "" Then
    MsgBox "You need to enter a Locality Code.", vbOKOnly, "Enter A Locality Code"
    Cancel = True
Else
    Me.cboRequestingAgency.SetFocus
End If
End Sub

So what I need is the proper coding for a work around to this or any of the other ‘event dilemmas’ mentioned in my original post OR direction to the correct event(s) to use. ‘Cause I certainly don’t know what I can do.
Thanks again.

gdr

Reply | Quote

Some thoughts.
You have a dirty little problem here.
First you say that you want to go back to State if you have forgotten to enter State, or exit the form.
Of course, if you go back to State, and you go to any another control (except Location) you must check if you have entered Location, that means you need code in all controls (except State and Location).
Are the State and Location bound controls?

Pat

Reply | Quote

Nope, they’re unbound and both draw their RowSource from tblStateLocality_List.
cboStateCode filters the table for the RowSource of cboLocalityCode.
The default Rowsource for cboLocalityCode is the Localities which have NO StateCode.

gdr

Reply | Quote

Why don’t you generate the Case number in the AfterUpdate event of the Location combo and put a check if you have entered a value for the Location number (and hence generated a Case number) in the BeforeUpdate event of the form.
Pat

Reply | Quote

Thanks Pat,
I’ll have to do that (or the Command Button), for now at least. I’d been hoping to catch it earlier than the BeforeUpdate of the form. The customer/user is going to have to count on whomever is entering data to catch a ‘missing’ LocalityCode.
Right now I’ve an appoiontment to meet with them.
Thanks again.

gdr

Reply | Quote

What you put in the BeforeUpdate is a SetFocus to the Location combo box if there has been nothing entered in the Location combo, thus alleviating the need for the Date Entry person to “catch” the missing Location code.
Pat

Reply | Quote

Hi Pat,
Yes, that’s a good idea.
Thank you.

gdr

Reply | Quote