tmalmond
asked on
Access subform not working for data entry after upsizing backend
I am working on a project where I am upsizing an access database to Sql Server. I had very little trouble converting the tables. The application keeps track of requisitions and has a form for entering header information, and a subform that contains information about the line items. Everything works great when viewing existing data. However, when entering a new requisition, the subform does not work when entering information about the line items. It seems like it is not even there. I have tried changing the properties of the form and the subform, examining the related fields, and recreating the forms. It must be something related to the change to SQL server, as it works fine in access.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That was the problem. The line item table had no primary key. As soon as I created the key and refreshed the links, it started working properly. I also created a foreign key for the linking field in the header table. I don't know if it is necessary, but it seemed like a good idea while I was creating .
Yes, having a FK in the child table is essential, don't know how you had it working in the original "all Access" version without that.
The upsizing must not have created any table relationships either..
Out of interest, how did you perform your "upsizing"? Did you use the Access Upsizing Wizard, or did you use the SQL Server Migration Assistant?
ASKER
To answer Dale's question, The original access version had table relationships. I tried using both of those methods, upsizing wizard and migration assistant. Since the original version was access97, neither would work properly. I imported the tables via excel. The largest table only had 3900 records, so it wasn't a problem. This was a simple exercise in preparation for a more rigorous one! Now that I know some of the things to look for, I am hoping that the next stages go well. My thanks to everyone who contributed.
>when entering a new requisition, the subform does not work when entering information about the line items.
I'd start by performing step-through debugging on the Requisitions form, specifically the code that executes when header information is entered that results in creating row(s) in the line items table.
Chance are there's an 'On Error Resume Next' that is masking any errors from being displayed to the user.
Also there's a change that the line items table had an AutoNumber field somewhere, where the SQL Server equivalent is Identity(), and it wasn't created correctly, forcing the error.
Good luck.
Jim