CODEWITHSUNDEEP
sql-server-2008ms-accessruntime-error
mikimr
mikimr

Reputation: 311

MS Access 2010 Run-Time Error '2467'

I have a MS Access 2010 application that is linked to MS SQL 2008 in the back end.

When I run the application, I encounter a run-time error '2467' - The expression you entered refers to an object that is closed or doesn't exists.

When I select Debug, the line points to:

Forms!FRM_MAIN_HOME!FRM_SUB_EDIT.Form.RecordSource =

I've run the query in the SSMS and works fine.

I've looked for missing references, and there aren't any.

When I run the application with Shift to present the forms, I get a different error: Data Type mismatch in criteria expression. This time I don't have a debug option, but I suspect that this is the real issue.

One more thing: when I run this application on a different database, it loads with no errors.

Any help is much appreciated.

Thanks.

Forms!FRM_MAIN_HOME!FRM_SUB_EDIT.Form.RecordSource = _
   "SELECT V.VendorNumber, V.VendorName, T.DocumentNumber, " _
   & "T.DocumentDate, T.AmountInc, T.GSTClaim, T.GSTBatch " _
   & "FROM TBL_VENDOR_MASTER AS V INNER JOIN TBL_GST_01_TRANSACTIONS AS T " _ 
   & "ON V.AAVendorID = T.AAVendorID WHERE T.GSTBatch =" _
   & Me.BATCH_NUM & " ORDER BY V.VendorName"

When I run this code in the SSMS, it works just fine. Also, when I select a different database, it works without any errors.

Upvotes: 1

Views: 11873

Answers (2)

mikimr
mikimr

Reputation: 311

The issue seems to be related to some memory issues as the tables are quite large (and my machine is not the fastest...), but the error messages that appeared while MS Access is running (every time I click on a field, a tab, or anything really, I'd get the same error, just without the Debug option), is related to a table I have that MS Access didn't like one of the fields I had there.

To troubleshoot this error, I loaded an older backup, and started updating some columns and testing MS Access, until I found the problem. Also, I added some indexes, and changed the default time-out for ODBC to 0 (unlimited) in the registry.

That seemed to fix this problem.

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91356

I am prepared to bet you are running this in the wrong event. You cannot use the Open event to refer to controls on a form. I am not sure why you are dynamically setting the subform recordsource.

The recordsource should be:

SELECT V.VendorNumber, 
       V.VendorName,
       T.DocumentNumber,
       T.DocumentDate, 
       T.AmountInc,
       T.GSTClaim,
       T.GSTBatch 
FROM TBL_VENDOR_MASTER AS V 
INNER JOIN TBL_GST_01_TRANSACTIONS AS T 
ON V.AAVendorID = T.AAVendorID 
ORDER BY V.VendorName

You should set the link fields like so:

Link child field : GSTBatch
Link master field : BATCH_NUM

Upvotes: 0

Related Questions