"No current record" after Cancel in combo_BeforeUpdate

Question

I'm trying to prevent circular (or even cascading) references in my data, and it seems it's only working part of the time.

In Access 2007, I have the following table:

create table mfr (
    mfr_id                 Autonumber,
    mfr_nm                 Text(255),
    mfr_is_alias_for_id    Long Integer
)

I'm importing a bunch of data from Excel, and the mfr_nm is one of the columns from the worksheet. I can't control how data gets entered into Excel, so I want a way of capturing alternate spellings as being "really" same thing. So far, so good (I think...).

Now I've built a form off of this table. I've got a ComboBox for the alias--again, so far, so good. However, when I add this code to the BeforeUpdate event, things get "interesting" (error handling omitted):

If Not IsNull(cboMfrAlias) Then
    If Not IsNull(DLookup("mfr_is_alias_for_id", "mfr", "mfr_id=" & cboMfrAlias)) Then
        MsgBox """Alias for"" must not also be an alias.", vbExclamation
        Cancel = True
    End If
End If

This works exactly as I expect it to when the form is in Form View, but if I'm in Datasheet View my MsgBox is immediately followed by an Access-generated "No current record" error that is not caught by error handling within the BeforeUpdate sub.

Can I catch this error? Where?

I think you must have something else going on as well, because this works for me on a test form. — Fionnuala, Nov 05 '10 at 23:04

score 2 · Accepted Answer · answered Nov 05 '10 at 22:54

2

Your dropdown list should filter out entries that are aliases. In other words, don't display a choice the user can't make.

You'd do this by simply eliminating from the dropdown the choices where mfr_is_alias_for_id Is Not Null.

I've implemented this in various guises and it works fine.

answered Nov 05 '10 at 22:54

David-W-Fenton

22,871
4
45
58

Duh. If I was thinking straight I'd have done that first, and not run into the problem. I'm still (idly) curious about the answer to the question as asked.... – RolandTumble Nov 05 '10 at 23:02

score 0 · Answer 2 · answered Nov 04 '14 at 01:45

I'm addressing the "No Current Record" error part of your question, from so long ago. After checking google for an answer, I found this link which was helpful. However, instead of using NZ() to transform the troublesome binary field in an aggregate query, I used troublesomefield: IIF([troublesomefield] IS NULL,NULL,[troublesomefield]) and this worked to banish the error. So my solution is only a small variation on the original answer I found, but it gets around the error and let you keep nulls if you want to...

"No current record" after Cancel in combo_BeforeUpdate

2 Answers2