Access form with SQL recordsource containing a join having problems.

[nburch]

Hi,

I am currently working on converting an access front end to work with Microsoft SQL Server 2008 express. The problem I am having comes when dealing with a recordsource that contains an inner join. From my testing I can see that the command 'docmd.gotoreco rd,,acnewrec' is only adding the new record in the joined table (Lot Master) and not into the primary table the query was made against. This ends up leaving my data entry form blank and does not allow me to key any data in.

I am hoping to have this setup so when the new record is created in lot master a record is also created in Case up based on the join.

Here is the query:

SELECT [Lot Master].LNum, [Lot Master].LPart, [Lot Master].LBDate, [Lot Master].LQty, [Case Up].MXRFSn, [Case Up].MXRFdeck, [Case Up].MXLdSn, [Case Up].MXLoader, [Case Up].MXSqSn, [Case Up].MXSqPn, [Case Up].MXFlag1, [Case Up].MXFlag2, [Case Up].MXFlag3, [Case Up].MXFlag4, [Case Up].MXSpecial
FROM [Case Up] INNER JOIN [Lot Master] ON [Case Up].MXLot = [Lot Master].LNum
ORDER BY [Case Up].MXLot;

Thanks,

[Rabbit]

Please use code tags when posting code.

Use a form/subform approach instead of trying to use two tables in one form.

[nburch]

Thanks for adding the code tags to my original post.

So the solution would be to house the first table [Lot master] and have a subform containing all of the fields from my secondary table?

Would I then just issue a 'docmd.gotoreco rd,,acnewrec' to both of these forms at the form_load event of each of the forms?

Thanks again,
Neil

[Rabbit]

Just on the parent form. The subform will be linked to the main one. If you go to a new record on the parent form, it will filter the subform for all records relating to the record the parent form is on. Since it's a new record, there won't be any related records in the subform so it will automatically be on a new record.