asked on

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

PatHartman

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.

Fordraiders

ASKER

the main form is unbound...thats why i'm using DAO...THE SUBFORM IS INDEPENDENT also..

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fordraiders

ASKER

thanks for the information pat

PatHartman

you're welcome.