Solved

Unexpected run-time error on MS Access form

Posted on 2015-02-17
17
398 Views
Last Modified: 2016-02-11
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.
0
Comment
Question by:Nick67
  • 8
  • 5
  • 3
  • +1
17 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
Nick,

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
Else
    TheJobID = me.JobID
endif

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.
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 0 total points
Comment Utility
I assume that Forms!frmFieldReport is not the same as the form referenced by "Me"?
Correct.
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
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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"?
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
    Me.Close
End if

Me.JobID = Forms!frmFieldReport!JobID
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 0 total points
Comment Utility
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)
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
Comment Utility
Nick,

"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.

Dale
0
 
LVL 26

Author Comment

by:Nick67
Comment Utility
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
Else
    test2 = Nz(rs!HasPics, 0)
    test3 = Nz(rs!HasExtras, 0)
    HasFiles = Nz(rs!HasExtras, 0) <> 0
End If

rs.Close
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"
dbCon.Open

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
Else
    rst.MoveFirst
    test2 = Nz(rst!HasPics, 0)
    test3 = Nz(rst!HasExtras, 0)
    HasFiles = Nz(rst!HasExtras, 0) <> 0
End If
rst.Close
Set rst = Nothing
Set cmd = Nothing
dbCon.Close

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"
with
Provider=SQLOLEDB;Data Source=MyServer\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=SSPI

Something outside my purview had clearly changed.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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?
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 26

Author Comment

by:Nick67
Comment Utility
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.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
Comment Utility
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).
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
What version of SQL Server are you using?

ODBC;DRIVER=SQL Server;

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

Dale
0
 
LVL 26

Author Comment

by:Nick67
Comment Utility
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)
qdf.Execute
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.
0
 
LVL 84
Comment Utility
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.
0
 
LVL 26

Author Comment

by:Nick67
Comment Utility
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.
0
 
LVL 84
Comment Utility
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 ...
0
 
LVL 26

Author Comment

by:Nick67
Comment Utility
@Scott

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.
0
 
LVL 26

Author Closing Comment

by:Nick67
Comment Utility
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.
Nick67
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now