Unexpected run-time error on MS Access form

Something bizzare is going on.
I have a data entry form with a bunch of unbound textboxes and one bound textbox that is bound to a foreign key field.  When I open the form, I open it filtered to the value of the foreign key.  For new entries, there won't be a value there, as expected, so I trap for a null value.  That now fails in the trapping  WFT!?!
I am getting a run-time error '-2147352567 (80020009) You entered an expression that has no value.
Of course it has no value.  It's on a form that is filtered to having no records by design.
So I trap it.
Dim TheJobID As Long
TheJobID = Nz(Me.JobID, Forms!frmFieldReport!JobID)

Except the error is now happening within the trap.
Any idea what has changed that this no longer works?
The backend is SQL Server.
LVL 26
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Nick67Connect With a Mentor Author Commented:
Everything except the foreign key textbox (which is locked) is unbound, so the user never gets an opportunity to dirty anything  They get to work with unbound data, and the command button code looks after validation and sending it to tables.  Keeps the 'whoops!' to a minimum.  And I've got 20-some forms that all operate on the same paradigm, and then just this one went south within the Nz() function.  Given that Nz()'s very existence is to deal with a non-existent value, that was highly annoying.

run-time error '-2147352567 (80020009) You entered an expression that has no value.

Now normally a VBA error number like 2427 is what would get thrown if I had failed to handle the null
As for what made the table go read-only--who knows!
I deleted it, re-linked it, and identified the PK and was off to the races.
Very strange -- but then a month ago MS broke all the SQL Native Client connections I had been using since September.
Only boxes with SQL Server on them have the native client anymore, and I had to switch DSN's.
Maybe this table didn't get properly relinked at that time
(I've got code that fixes connections for tables and querydefs to make that much easier)
Dale FyeCommented:

I assume that Forms!frmFieldReport is not the same as the form referenced by "Me"?

Where are you trapping this (Current event)?

If so, you might want to try something like:
if me.newRecord then
    TheJobID = Forms!frmFieldReport!JobID
    TheJobID = me.JobID

Open in new window

But I'm guessing that Forms!frmFieldReport is either not open, or that the JobID in that form is also NULL.
Nick67Connect With a Mentor Author Commented:
I assume that Forms!frmFieldReport is not the same as the form referenced by "Me"?
Me refers to  frmHoistDetails
I was trapping it OnLoad first, because if there's a record, I then populate the unbound textboxes.
I was also trapping it in the command button that adds/edits the underlying values.

In the OnLoad, I twisted the code around to pass in an opening argument instead of opening filtered.
I then called a recordset to test for lack of records.

In the command button code, I did something similar.

Then I found the evil
I should have guessed from run-time error '-2147352567 (80020009) and not a VBA error code.
Somehow or another the underlying linked table had gone read-only
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Dale FyeCommented:
I rarely use the OnLoad event, preferring the CurrentEvent, and when I do, I don't do anything that has to do with a specific record except like you have indicated, passing an OpenArgs value and either filtering or going to the appropriate record.

So, have you figured out how the linked table "had gone read-only"?
The best event to use to populate a foreign key is the BeforeInsert event.  That event fires only once, only for new records,  and only after the user has actually dirtied the record.  So, if the user navigates to this form but doesn't start a new record, then the record isn't dirtied and so Access doesn't try to save it.  So, the user doesn't get validation errors because of missing fields, etc.

Another advantage of using the correct event is that if your form allows multiple records to be created, each one will get the foreign key from the form that opened this one.

If Forms!frmFieldReport!JobID & "" = "" Then
    Msgbox "There is no valid JobID",vbOKONLY
End if

Me.JobID = Forms!frmFieldReport!JobID
Dale FyeConnect With a Mentor Commented:

"MS broke all the SQL Native Client connections I had been using since September"

Can you elaborate?  I've got a number of clients who have the client installed on their computers so that they can access SQL Servers using DSN-less connections, and none of them have reported any errors.  I do however, refresh the links and the connection string every time they open the applications, so that may be why they have not reported any problems.

Nick67Author Commented:
Can you elaborate?
In September, I discovered that one of my forms could navigate a full order of magnitude faster if I replaced the DAO code below
Set qdf = db.QueryDefs("pthrSpDirectoryChecks")
SQL = "exec SpDirectoryChecks  " & myJobID
qdf.SQL = SQL

Set rs = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)

If rs.RecordCount = 0 Then
    test2 = 0
    test3 = 0
    HasFiles = 0
    test2 = Nz(rs!HasPics, 0)
    test3 = Nz(rs!HasExtras, 0)
    HasFiles = Nz(rs!HasExtras, 0) <> 0
End If

Set rs = Nothing

Open in new window

With the equivalent ADO code
Dim dbCon As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
dbCon.ConnectionString = Forms!tblDummy!ADOConnString.Value
'"Provider=SQLNCLI11;SERVER=MyServer\SQLEXPRESS;DATABASE=MyDataBase;Integrated Security=SSPI"

With cmd
    .CommandType = adCmdStoredProc
    .CommandText = "SpDirectoryChecks"
    .Parameters.Append .CreateParameter("@MyJobID", adInteger, adParamInput, , Me.JobID)
    .ActiveConnection = dbCon
    .NamedParameters = True
    Set rst = .Execute
End With

If rst.RecordCount = 0 Then
    test2 = 0
    test3 = 0
    HasFiles = 0
    test2 = Nz(rst!HasPics, 0)
    test3 = Nz(rst!HasExtras, 0)
    HasFiles = Nz(rst!HasExtras, 0) <> 0
End If
Set rst = Nothing
Set cmd = Nothing

Open in new window

I rolled it out to all the boxes and it worked great!
In the mid-January patch Tuesday timeframe, all but the SQL Server began to complain 'Provider not installed' and I had to replace
"Provider=SQLNCLI11;SERVER=MyServer\SQLEXPRESS;DATABASE=MyDataBase;Integrated Security=SSPI"
Provider=SQLOLEDB;Data Source=MyServer\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=SSPI

Something outside my purview had clearly changed.
Dale FyeCommented:
One of my clients had several people with various versions of the SQL Server client installed on their computer, so in my code for refreshing links, the first thing I do is check my ability to generate a recordset from a particular file on the server.  It starts out with:

Driver=SQL Server Native Client 12.0"

and if that generates an error, it loops through the following drivers until it finds one the works:
SQL Server Native Client 11.0
SQL Server Native Client 10.0
SQL Server
By order of magnitude, what are you saying, 1 sec vs 10 sec, .1 vs 1.0 sec?  I'm surprised that calling the SP through the pass-thru is significantly different response time.  What connection string are you using for your linked tables and pass-thru queries?  Is it a DSN or DSN-less connection?
Nick67Author Commented:
It went from about 2.5 seconds to complete the entirety of the Current event to about a quarter-second.
Everything except this ADO connection is a standard DAO ODBC connection for the linked tables through a DSN using the SQL Server driver and a Trusted connection

ODBC;DRIVER=SQL Server;SERVER=MyServer\SQLEXPRESS;DATABASE=MyDatabase;Network=DBMSSOCN;Trusted_Connection=Yes

' I'm surprised that calling the SP through the pass-thru is significantly different response time. '
Shocked would be a better word.
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
I've had linked table issues when a change is made to the backend (even if that change doesn't involve the table that goes flonky). I don't recall ever having to change providers or drivers, but it may have happened to me.

I usually include code to recreate the links at each startup. It does slow the startup down a little - perhaps a few seconds for a couple of dozen tables - but that seemed to resolve things for me (or at least let me catch troubles at startup, before the user got to the meat of the program).
Dale FyeCommented:
What version of SQL Server are you using?


I use this as my last choice for drivers, using the native client versions I mentioned above should significantly improve your response times.

Nick67Author Commented:
I haven't installed the native client anywhere but on the dev unit and the server.
And yet the ADO connection
"Provider=SQLNCLI11;SERVER=MyServer\SQLEXPRESS;DATABASE=MyDataBase;Integrated Security=SSPI"
worked fine universally until mid-January, when the 'provider not installed' error began to pop.
I don't know what changed.

'perhaps a few seconds for a couple of dozen tables '
159 tables and 496 queries, 36 of which are pass-throughs
So, no, I don't do that on load :)
I do have code that looks at the Application.Path
If the app is in a folder like M:\Azure and the connection doesn't reflect that environment, everything updates.
I've got some air-gapped units for testing as well, and those update the backend when the app is fired from an appropriate folder.
But the lag on updating is fairly significant.

The bitchy part is when linked Indexed Views lose their PK.
I built a form for fixing those.
A dropdown with the linked objects that can have the grief, which cascade-updates a listbox with all the objects fieldnames and then a command button with the following code
Private Sub cmdReCreate_Click()
Dim db As Database
Set db = CurrentDb
Dim qdf As QueryDef
Dim SQL As String
SQL = "CREATE UNIQUE INDEX _PK_" & Me.cboTables.Value & " ON " & Me.cboTables.Value & " ([" & Me.lstFields.Value & "]);"
Set qdf = db.CreateQueryDef("", SQL)
MsgBox "Index _PK_" & Me.cboTables.Value & " was created!"
End Sub

Open in new window

But it was a full-blown table that had gone read-only in this case, so that was quite strange.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
My biggest app which uses the "on load" technique has somewhere around 75 tables, and generally starts in about 10 - 15 seconds. The users typically start it up at the beginning of the day, and don't close/reopen often, so it's no real burden for them.

No pass-throughs, and queries don't impact it at all, and it completely eliminated the issue with failure to link, so IMO it's worth the time. Of course, your mileage may vary.
Nick67Author Commented:
I've built deployment scripts to push out new front-ends, and depending on what's in the pipe I can rev multiple times per day.  So I REALLY need load times to be quick -- otherwise the end-users get into the habit of leaving the app open to avoid the delay -- and then they don't get updates.

And that makes for phone calls and desk visits.
Although, with the deployment scripts in place, everybody pretty much knows that if weirdness is encountered they should close/re-open the app first.  I get the deployment scripts to look for *-backup.mdb and if it exists, to kill and refresh the frontend.

That's the only benefit to the stupid NavPain.  The first time the frontend opens after deployment, the NavPain is flown open and you can't code that shut.  Afterwards, it behaves nice and can be hidden, but not on first deployment.  But the user then sees immediately that they've got a renewed frontend, so that works.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Also, this seems to be somewhat odd syntax:

TheJobID = Nz(Me.JobID, Forms!frmFieldReport!JobID)

Essentially, you're saying if Me.JobID is NULL to get the value from frmFieldReport!JobID. If that value (frmFieldReport!JobID) is Null, you'd presumably get the error?

Generally speaking Nz should return a constant or immutable value (like 0, or 100, or whatever) and shouldn't depend on another object being non-null - unless, of course, you nest the Nz function:

TheJobID = Nz(Me.JobID, Nz(Forms!frmFieldReport!JobID, 0))

You could then check for a 0 value in TheJobID and take action from there ...
Nick67Author Commented:

I had a post here that appears to have gotten lost.
The setup precludes Forms!frmFieldReport!JobID EVER being null
(can't filter to zero records, can't get on a new record -- the current event see to that)
Forms!frmFieldReport is the only place that frmHoistDetails can get opened from, too
And code unseen check to ensure .IsLoaded = TRUE, so it is a good value to take if frmHoistDetails.JobID is null.
Nick67Author Commented:
Thanks to everyone who responded.
The key was that it was a non-VBA error code thrown: -2147352567 (80020009)
If I had noticed that earlier I might have guessed the problem's cause sooner and not posted

Thanks again.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.