Access-> Sql Server migration: Forms and subforms

[Preka]

Apologies for the crosspost, but I was informed that this would be a more appropriate locale for this topic:

Not entirely certain this is the best topic for this, but it seemed close enough.

I'm fairly new to SQL Server, and I'm trying to migrate an Access (2000, though I doubt it matters for this particular question) database.

For the most part, I haven't had many issues thus far, but one fairly big one stands out.

I have a number of forms that have subforms. The purpose of these forms is data entry. So, for example, the main form might be for order information (orderID, date, customerIDs, etc.), and then the subform is for entry of the individual order items, and the two are associated by way of the OrderID.

While this works fine in Access, it no longer works with the tables having been migrated to SQL server. The order item data is there for pre-existing orders, but for new ones, the subform just comes up blank.

I did a bit of research and found this in an article:

"One of the nastier problems is the way that Autonumbers are handled. In Access a new Autonumber is assigned as soon as you create a new record, in other words BEFORE it has been saved. SQL Server on the other hand only generates a new Identity value (SQL Server's equivalent to an Autonumber) AFTER the record is saved. This may sound esoteric but if, for example, you are using a sub-form linked to a main form to enter data, such as Order Header and Order Details, and you are using Autonumbers, then your data entry will probably fail under SQL Server. This normally means that the logic for the form/subform needs to be rewritten!"

So, that's clearly what my problem is. Unfortunately, I don't know what to do about it. Any advice, or just a nudge towards the proper reading material would be greatly appreciated.

I'm sure I could kludge together some sort of solution, but since this is obviously a fairly common problem with Access->SQLServer migrations, I'm hoping there is an appropriate way to deal with it.

 

[sparkbyte]

I may be off on this...

What about using the Onload form action to save the record there by getting the new identity from SQL server. Then fill out the form as normal.

Thanks

John Fuhrman
faq329-6766
faq329-7301
thread329-1334328
thread329-1424438

 

[Axworthy]

Preka,

What I have done to display the subform data is to build a query for the subform that uses the main form's Order ID in its where clause. I have it requery the subform on the main form's On Current event.

With the subform data, I have the Order ID hidden on the form (if in datasheet view, in the form header) and have its default value set to =Forms!frmMainForm!txtOrderID so that any adds gets the main form's id number.

The same thing has to be done for subreports - referencing the main report's primary key.