subform with linked table as source

[supmktg]

I am having trouble displaying records in a subform where the record source is a linked table. I have an ODBC linked database that contains invoice records that are split into 2 tables. One table has the invoice header information (date, Inv#, Bill to, etc.) and the 2nd table has the line items (item. qty, price, etc.).

The main form displays the header data an it works fine. The subform is a continuous form that should show the line items. If I open the form by itself, it displays all of the records correctly. However, when I open it as part of the main form, I get the following error:
"Reserved error (-7711); there is no message for this error."

The master/child relationship is the invoice# in the header table and the invoice# in the line item table, both of which are numeric - double. A query using the same join between the 2 tables results in showing all of the records.

I researched this error and found one post indicating that this is an issue with linked tables. I ran make-table queries off of the 2 tables, and changed the record sources of the main and subform, and they work perfectly using these new native tables. So I now believe this is an Access(I'm running 2K) issue. Unfortunately, the item did not include a solution to the issue.

Is anyone here familiar with this issue? Does anyone here have a solution?

Thanks,
Sup

 

[PNGBill]

are you using reserved words as names?

 

[boblarson]

I use linked tables all of the time with SQL Server. It could be that your problem has to do with your data type choice. Double is a very BAD choice for use as an invoice number. You should use Long Integer on the Access side and Integer on the SQL Server side. Double has a tendency to have phantom numbers in the decimal places due to its rounding problems.

 

[supmktg]

PNGBill - No reserved words being used.

Bob - Unfortunately, the linked tables are part of a proprietary software package and are not under my control. I am stuck with double. Please note that when I make native access tables using make table queries, it sends the invoice# through as double and the form/subform - master/child relationship works perfectly.

I guess I can use temp tables, but it seems wasteful of both time and space to do that for this one form.

Any other ideas of how I might deal with this problem?

Thanks,
Sup

P.S. I just changed the record source on both forms to int(InvNo) and it still doesn't work.

P.P.S. using int(InvNo) does work. Thanks Bob!