By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,461 Members | 2,184 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,461 IT Pros & Developers. It's quick & easy.

Access form with SQL recordsource containing a join having problems.

P: 2

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.gotorecord,,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:
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM [Case Up] INNER JOIN [Lot Master] ON [Case Up].MXLot = [Lot Master].LNum
  3. ORDER BY [Case Up].MXLot;

May 21 '12 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 10K+
P: 12,355
Please use code tags when posting code.

Use a form/subform approach instead of trying to use two tables in one form.
May 21 '12 #2

P: 2
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.gotorecord,,acnewrec' to both of these forms at the form_load event of each of the forms?

Thanks again,
May 21 '12 #3

Expert Mod 10K+
P: 12,355
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.
May 21 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.