SQL Server Data won't show up in subform - Repost

[Paul H]

*** Reposting due to lack of response. It may be because I replied to my own comment, not sure. ***

Part 1

We just migrated some tables from an local Access database to a SQL Server back end. The tables have the exact same structure and data as the original. The form is a continuous form that reads the data in this table (the other one is a look up table) stored by patient number and admit date. Using the Access table the form works as designed, looking up codes and descriptions in lkDiagnosisCode_ICD10 and storing them in tblDiagnosis_ICD10. When I switch tblDiagnosis_ICD10 to the copy on the SQL Server, the form vanishes. I get a blank white square where the subform is supposed to be. The combo boxes that do the lookups are able to read SQL Server version of lkDiagnosisCode_ICD10 identically to the Access table.

With Access table


With Sql Server table



Only the SQL Server version of tblDiagnosis_ICD10 causes this problem. I've opened both these tables in table view and I can read and edit the data in both versions. As far as I can tell they are identical. My thoughts were that this would be a seamless transition. What am I missing?

Part 2.

Here's a critical piece I left out. I copied my form and switched back and forth between the two tables, SQL Server and Access versions. When I open the form using the SQL version I get this error.


I do not know enough about SQL Server to diagnose this. Can you give me something I can take to our DBA?

Part 3
Just added a Primary Key to the Sql Server table. Results are the same. The subform does not appear when referencing the SS table. Works just fine with the Access table.

I expected switching tables to be easy, but this has stopped me dead in my tracks. Any help will be appreciated.

[Bulzie]

Seems like a datatype issue between your search field and the SQL field. Post the key Fields in SQL and Access tables and their Datatypes from each.

Test a few things:

1. On the form, take out the Search criteria and see if the form shows with all records from SQL.
2. Create a new Form based on some fields from the SQL table. If it works, create a lookup fields and see if it works.

[Paul H]

I tried copying the form, editing and deleting combo boxes all the way to deleting everything on the form with no results. I found this puzzling and finally decided to rebuild this form from the bottom up. I created a blank form, hooked to my SQL Server table, then viewed it in form view. Step by step I copied all the controls into the new form and displayed the results in form view. Finally I copied all my code from the original form into the new form module. So far, so good. I changed the form to a Continuous form, success. Finally I put the new form in the subform of my master form and wa la! It worked. Right now I am back in business. I am attributing all this to some Access bug. I am still suspicious and a little worried of this problem reappearing. I fail to see how the source (SQL Server) of the table would cause this to happen to my form, but who am I to look a gift horse in the mouth. I'll leave this open for now, but at least I can go home today with a clear conscience.

Thanks to the forum owners for letting me work out my problems in public. Sometimes that is the only way.