Need help with a bit of broken code.

[Stang70Fastback]

Hi guys. Did what everyone here suggested I do and converted my back end to SQL Server. I'm still learning SQL, so I'm tracking down all the things that broke during the migration and am trying to fix them one at a time. Here is a bit of code that worked fine when the back end was in Access, but acts funny now that it's SQL-based.

This runs in the "On Current" event of the form:

    If (Me.SubmitNameTXT = fOSUserName() And Me.SubmitTimestampTXT.Value > (Now - TimeSerial(0, _
        DLookup("Delay", "RecordLocking", "Form = 'CDL Exam'"), 0))) Or Me.NewRecord = True Or _
        DCount("[Username]", "USER", "[Username] = '" & fOSUserName & "' AND [Authority] = 'Admin'") = 1 Then

In order, basically it checks that you (have the right username AND the current date/time hasn't exceeded the "timeout" value for that form stored in another table), OR it's a new record, OR you're an admin. If any of those are true, you can edit the form, otherwise you can only view it.

Now that I have the back end in SQL, the code seems to work properly UNLESS I scroll to a new record. Then I get: "Run-time error '2427': You entered an expression that has no value."

Does anyone have any idea what is different in SQL? I know offhand SQL seems to handle dates a bit differently, so maybe that's my issue?

EDIT: My issue lies in the "greater than" statement. Me.SubmitTimestampTXT.Value will be a null value on a new record. Now that I see this, it's obvious, but this code worked before I converted to SQL, which makes me wonder why it is being handled differently. Hmm... how to solve this conundrum.

 

[MarkK]

I'm not an SQL Server expert, but from a programming standpoint I would break up that expression into smaller bits so each bit is easy to understand, test, and maintain.

I would want an expression that looks like this . . .

If UserIsValid AND Not TimeIsExpired AND Not Me.NewRecord Then

. . . where UserIsValid() is a function that does exactly what you think, and TimeIsExpired() is a different function that does exactly what you'd expect. Use functions. Name them clearly. This makes your code way easier to understand, test and fix.

I suspect that if that expression was not the monolithic lump that it is, you would have already identified and fixed this bug.

Hope this helps,

 

[Stang70Fastback]

Understood. There is definitely a lot of cleaning up of things that needs to be done.

In the meantime, does anyone have a solution for how to use a 'greater than' statement with a value that might potentially be null?

 

[MarkK]

Handle nulls using the VBA.IsNull() function, or the Access.Nz() function.