How to Run Query from Form on current records & Update field

[deepakg27]

Hi Guys,

Probably this is very foolish question to ask. I am trying to make a billing database. I have main table tblSale and connected table tblSaleDetails. In tblSaleDetails I have qty, Price, Amount, TaxRate, & TaxAmt as fields. I have also made a query in which Amount is calculated for all the fields of tblSaleDetails.



FIRST PART:
Now I was wondering how can I run the query automatically as soon as qty & Price is updated in FORM. Also query needs to run just for that particular record. Lastly I want to save the result in Amount field. I know We should not save calculated values, but still I would like to do it. Also Should I do it with query or Expression builder? ( With Expression builder I am able to do it, how to do it with Query?)

SECOND PART:
I have made query QryCGST in which I am using tables tblSale , tblSaleDetails , tblBuyer. I am calculating Tax Amount based on ([qty]*[Price]) * Tax Rate /100 , with an iff statement in which checking buyer code whether Tax Rate Applies to him or not. I am able to get the desired results for the whole table. Now I want to perform this query on current record once the Amount field has been updated in the FIRST PART & save the result in CGST field.

Thanks for your help and guidance.
Regards
Deepak Gupta

[ranman256]

1st:
The query where you enter data would have the expression as a field:
LineTotal: Qty*Price


this is the query in the subform as you enter data, and it updates immediately.


2nd:
I would advise against using TAX as an IIF, instead use a tTaxTable. This way you can set it for regions, states, counties, etc.
It would go into the query and calc tax based on the region.

[deepakg27]

Dear Ranman256,

Thanks for your help.

As for the 2nd part, I don't want to create an extra table. I have made query which gives perfect results now I want to know how to run the query through a subform while data entry process is being done for that particular record set only. My query gives me result for the complete table.

As for the first part, I have the query as suggested by you. All I want to do is again run the query for that particular record set only not the whole table. Lastly need to save the result to required field of the query.

Thanks and Regards
Deepak Gupta

[aytee111]

The amount field is already in the record source of the form. In the AfterUpdate event of both the quantity and the price, run code to say that if both are <> 0 then calculate the value.

Same for the tax amount.

I would still remove them from the tables, however, there is no need at all for them and it adds a lot of overhead and maintenance.

[deepakg27]

Dear Aytee111,

Thanks for your suggestions. I am not very fluent with VBA coding. Would it be possible for you to kindly write the code and and give it to me. Thanks.

As for the Tax Amount part, I agree it can also be done in similar way with field amount and TaxRate, but I need to check for StateCode (field in tblBuyer) and then apply the formula if tax is applicable or make value 0.

If you could give me the coding for that also it would be really helpful.

Thanks and Regards
Deepak Gupta

[aytee111]

First, create the two queries - one for the amount (qty * price) and one for the tax amount. Get them working external to the form. These two queries will be now used everywhere in the database whenever you want to display them, forms, reports, queries, etc. The "primary key" for these queries will be the PK from the sales order details table.

Once you have those working, set the control source of the amount field to =DLookup("amountfield","amountqueryname","sod_id=" & Me!sod_id). Same for the tax amount. In the AfterUpdate event of the form, have Me.Requery.

[deepakg27]

Hi Aytee111,

Thanks for your help. But it is still not givig the desired result as required. Following code works:
=DLookUp(" [qryAmt]![AMT] ","qryAmt","[SaleDetailID_PK]= 1")
but As soon as I put code in following order
=DLookUp(" [qryAmt]![AMT] ","qryAmt","[SaleDetailID_PK]= & Me!SaleDetailID_PK") this gives error. Amount field shows #ERROR. Please help in fixing the highlighted part.

Also Working code i.e. =DLookUp(" [qryAmt]![AMT] ","qryAmt","[SaleDetailID_PK]= 1") is just showing the top result of the query. Query in background is running on all the records. Changing the "[SaleDetailID_PK] =2" just shows the same result in second record of SaleDetail Form.

Awating your guidance.
Thanks and Regards
Deepak Gupta

First, create the two queries - one for the amount (qty * price) and one for the tax amount. Get them working external to the form. These two queries will be now used everywhere in the database whenever you want to display them, forms, reports, queries, etc. The "primary key" for these queries will be the PK from the sales order details table.

Once you have those working, set the control source of the amount field to =DLookup("amountfield","amountqueryname","sod_id=" & Me!sod_id). Same for the tax amount. In the AfterUpdate event of the form, have Me.Requery.

[aytee111]

What you are doing is called String Concatenation, in case you want to google it. Putting things together to make a string(s), which means putting the opening and closing of the quotes in the correct places.
=DLookup("Amt","qryAmt","SaleDetailID_PK=" & Me!SaleDetailID_PK)
(Note that this is when the key field is a number data type, text and date fields require opening and closing delimiters.)

[deepakg27]

Tried doing it as re instructed by you
=DLookUp("[qryAmt]![AMT]","qryAmt","SaleDetailID_PK=" & Me!SaleDetailID_PK) Every time I enter this comand access converts it to following:
=DLookUp("[qryAmt]![AMT]","qryAmt","SaleDetailID_PK=" & [Me]![SaleDetailID_PK]) As for the result I am getting #Name?

For your ready reference I can email you the Accdb file. Unable to attach (file is 3.37MB).

Regards

What you are doing is called String Concatenation, in case you want to google it. Putting things together to make a string(s), which means putting the opening and closing of the quotes in the correct places.
=DLookup("Amt","qryAmt","SaleDetailID_PK=" & Me!SaleDetailID_PK)
(Note that this is when the key field is a number data type, text and date fields require opening and closing delimiters.)

[aytee111]

Try zipping the file, that may bring it down to a better size.

Those square brackets is a normal thing for Access.

My example said "Amt", you changed it to qryAmt!Amt - is this what is causing the error?

[deepakg27]

Tried =DLookUp("Amt","qryAmt","SaleDetailID_PK=" & [Me]![SaleDetailID_PK]) Same Error #Name?

File Attached.

Thanks And Regards

[aytee111]

The first thing I noticed is that your DLookup is referring to a field SaleDetailID_PK which does not exist in the query,

[aytee111]

The other issue is that this is in the control source property and therefore cannot refer to "Me" as the calculation is being done external to the form. I thought it was being done in VBA! So change the ME to Forms!frmSale!frmSaleDetailSubform!...

[deepakg27]

Dear Aytee111,

Thanks for your help. Problem is solved and now it is working perfectly. Thanks for pointing out my mistake.

Regards
Deepak Gupta

The other issue is that this is in the control source property and therefore cannot refer to "Me" as the calculation is being done external to the form. I thought it was being done in VBA! So change the ME to Forms!frmSale!frmSaleDetailSubform!...