SQL INSERT INTO SUBFROM from QUERY in ms access

Question

I have a receipt form where i want to receive payments from customer and knock off the invoices

I have created the Table Of Receipt Master

Field are , TID (Auto Number), Cutomer ID , Chq No, Amount, Bank, RVDate

Another Form Rvdetails:

ID (AutoNumber), Receipt ID (Join with TID of Receipt Master) SINVOICE ID, INvoice No, Totalinvamt, Customer ID, Bal, Amount

I want to insert RV Query Which has follwing data in to Rvdetail subform when select the customer which i afterupdate vba on Customer ID

Private Sub Cust_AfterUpdate()
Me.Refresh
DoCmd.RunSQL "INSERT INTO rvdetails ( [Sinvoice ID],[Invoice No],TotalInvamt,Bal,Amount, [Customer ID]) SELECT RVquery.[Sinvoice ID],RVquery.[Invoice No], RVquery.Totalinvamt, RVquery.bal,0, Rvquery.[customer id]" & Me.[Customer ID] & " from [RVquery] where [RVquery].[Customer ID] = " & Me.Cust
Me.Refresh
End Sub

attached images of RV Query & Form Design

the problem is that the when selecting the customer the rvquery filed not inserting in the subform

RVQUERY fileds are

SINVOICE ID , INVOICE NO, CUSTOMER ID, TOTALINVAMT, SUMOFAMOUNT, BAL

Please tell me where i m mistaking

Now the problem has been solved changed the Rvdetails reciept id filed in to TID and then correct the code — Naveed Kandohi

Naveed Kandohi Naveed Kandohi · Accepted Answer · 2014-06-17T07:26:07

Private Sub Cust_AfterUpdate()
Me.Refresh
DoCmd.RunSQL "INSERT INTO rvdetails ([Sinvoice ID],[Invoice No],TotalInvamt,Bal,[Customer ID], TID) SELECT rvquery.[Sinvoice ID],rvquery.[Invoice No],rvquery.totalinvamt, rvquery.bal, rvquery.[customer id]," & Me.TID & " from [rvquery] where [rvquery].[Customer ID] = " & Me.Cust
Me.Refresh
End Sub

Now the problem has been solved changed the Rvdetails reciept id field in to TID and then correct the code the corrected code is mention above

SQL INSERT INTO SUBFROM from QUERY in ms access

1 Answers