Access 2010 Subform The data was added to the database but the data won't be displayed

Question

I have a strange one here that I just can't seem to figure out.

My Access front-end project runs on an SQL 2005 express backend.

I have been using subforms for donkeys years and it's the only reason why I haven't migrated the application to a VB/VS front end.

However, since upgrading to Access 2010 I cannot get subforms to work. Instead, when I try to add a row, I get the following error (The data was added to the database but the data won't be displayed in the form because it doesn't satisfy the criteria in the underlying record source.):

enter image description here

The master and child forms are linked on poid and PONo.

I have created forms from scratch with all defaults, but still the issue remains.

My SQL tables are

PURCHASE: - - poid, int, PK, Identity, seed 1, inc 1 supplierID, int orderdate, DateTime deliverydate, datetime ordersent, bit ordercomplete, bit initials, nvarchar supplierinvoiceno, nvarchar branchid, int bookedin, bit deliverycharge, money

[STOCK - Detail]: - - stockid, int, PK, Identity, Seed 1, inc 1 CodeID, int service, bit costprice, money PONo, int Instock, bit SerialNo, char StockTake, bit Branch, Char ProductID, int

Any help would be very much appreciated.

Many thanks,

Abe

Perhaps that bit data type? http://stackoverflow.com/questions/11753862/run-time-error-3197-ms-access-2010-and-sql-server-2008r2 — Fionnuala, Nov 15 '12 at 12:45
Hmmm, interesting, what data type would you suggest instead? — alcomcomputing, Nov 15 '12 at 15:12
Nope, that didn't work. I spent the weekend updating all the bit fields and I still have the same issue. Grrrrrrr! — alcomcomputing, Nov 19 '12 at 15:58

score 0 · Accepted Answer · answered Nov 19 '12 at 21:15

Solved! Access 2010 does not support multiple tables with identical column names unless it is in a stored procedure / query on the SQL server.

I've been trying to come away from stored procs & queries, but A2010 will not, under any combination work with hard coded SQL as the record source.

Once I created a query and selected it as the record source the subforms worked perfectly as expected.

Also I had to alias any fields that have the same name in both tables EVEN if not selected in the query. And yes, the Alias only worked in the query too!

I love Microsoft! ;-)

Access 2010 Subform The data was added to the database but the data won't be displayed

1 Answers1