subform error says it cant find the table

Access 2010
vba
Linked tables to sql server
linked  table  "dbo_t_redbook_pricing_escalation_detail"



I have the following code :
i have a main form. Creating records via DAO and then..taking the master id and applying that id to a subform...(to create a relationship ID) after creating a new record in the subform.

Loading a subform with a new record based on the MASTER id I created manually.
I'm getting the MAX ID Needed for the subform by specifying the Max ID of the master table created with my personal ID.
MY_ID = 999999 'Me![PRICING_ESCALATION_ID]
Strsql = "SELECT dbo_t_redbook_pricing_escalation_detail.[AUTO_ID], dbo_t_redbook_pricing_escalation_detail.[RBP_MASTER_ID], " & _
         "dbo_t_redbook_pricing_escalation_detail.[SKU], dbo_t_redbook_pricing_escalation_detail.[PRODUCT_DESCRIPTION], " & _
         "dbo_t_redbook_pricing_escalation_detail.[QTY], dbo_t_redbook_pricing_escalation_detail.[TARGET_PRICE], " & _
         "dbo_t_redbook_pricing_escalation_detail.[COMPETITOR_PRICE], dbo_t_redbook_pricing_escalation_detail.[TARGET_GP], " & _
         "dbo_t_redbook_pricing_escalation_detail.[CURRENT_PRICE], dbo_t_redbook_pricing_escalation_detail.[CURRENT_GP], " & _
         "dbo_t_redbook_pricing_escalation_detail.[VENDOR_GUIDELINE_GP], dbo_t_redbook_pricing_escalation_detail.[APPROVED_PRICE], " & _
         "dbo_t_redbook_pricing_escalation_detail.[APPROVED_GP],dbo_t_redbook_pricing_escalation_detail.[Escalation_Type_del], dbo_t_redbook_pricing_escalation_detail.[Line_Color], dbo_t_redbook_pricing_escalation_detail.[Line_Color_price], dbo_t_redbook_pricing_escalation_detail.[SEND_TO_LEADER] From dbo_t_redbook_pricing_escalation_detail " & _
         "WHERE dbo_t_redbook_pricing_escalation_detail.RBP_MASTER_ID = " & MY_ID & " "


Me.dbo_t_redbook_pricing_escalation_detail_subform.Form.RecordSource = Strsql


' now update the subform with the correct id.
' get the correct max id here.
    StrSqlc = "SELECT Max(dbo_t_redbook_pricing_escalation_master.PRICING_ESCALATION_ID) AS MaxOfPrc_Id " & _
    "FROM dbo_t_redbook_pricing_escalation_master" & _
    " WHERE (((dbo_t_redbook_pricing_escalation_master.QS_RACFID)=  '" & Str & "' ));"
    Set R = CurrentDb.OpenRecordset(StrSqlc, dbOpenDynaset, dbSeeChanges)
    R.MoveLast
    R.MoveFirst
    tp = R.RecordCount
    R.MoveLast
    MaxOfPrc_Id = R.Fields(0)
    Me.PRICING_ESCALATION_ID = MaxOfPrc_Id
' now update the subform field with the master ID
    Me![dbo_t_redbook_pricing_escalation_detail_subform].Form![RBP_MASTER_ID] = MaxOfPrc_Id    <-----------  GETTING ERROR HERE: CANT FIND THE table
    R.Close
    Set R = Nothing

Open in new window


Problem:
The code has worked fine up to now.
I had to relink the sql server table that the subform is based on and now Access says it cant find the table ?

Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
If this really is a subform, then the Master/Child links will take care of syncin't the main form with the subform and inserting the FK.  NO CODE IS REQUIRED.  If what you are calling a subform is really an independent form then you do need code but the process isn't complicated.

In the main form, you MUST save the record before opening the popup form.  So, your button code would be something like"

If Me.Dirty Then
    DoCmd.RunCommand acCmdSaveRecord
End IF

DoCmd.OpenForm ....  --- this uses the WHERE argument to control what records the popup shows.  You don't use criteria in your query.

Then in the popub form, you need ONE line of code in the Form's BeforeUpdate event.

Me.MyFK = Forms!frmOriginal!txtPK


There is no reason for any of the code you have and in fact your code is dangerous in a multi-user environment since there is no guarantee that you will get the ID of the record just added by this form rather than a record added by a different user.
0
FordraidersAuthor Commented:
the main form is unbound...thats why i'm using DAO...THE SUBFORM IS INDEPENDENT also..
0
PatHartmanCommented:
There seems to be a rash of folks using unbound forms lately.  Someone is giving you people some seriously bad advice.  The whole point of Access is that it is a RAD tool.  Once you elect to not use its RAD features, all you have left is baggage.  You would be far better off using something easier to distribute.

If you are going to use unbound forms, you need to use them correctly.  On a bound form, all of this would be handled and you would have the ID of the record that was just added.  Now that the process is asynchronous, you need to use special techniques to obtain it.  Look for @@Identity.  There are three variations.  The simple one will work unless you have any insert triggers that cause a second Identity value to be generated.

Once you have retrieved the Identity value, use that in the Where clause to open the second form.  You should also pass the ID in the OpenArgs.  That way, the second form's BeforeInsert event can get the ID from the OpenArgs since it isn't available on your form.

Looking at your query, you are not even using a pass-through query so you are getting absolutely NO benefit from this extra work.  At least a pass-through query is slightly more efficient because it doesn't have to be evaluated by ACE before being sent off to the server.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
thanks for the information pat
0
PatHartmanCommented:
you're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.