Make Break down to contract items in invoice form (1 Viewer)

mike60smart

Registered User.
Local time
Today, 10:15
Joined
Aug 6, 2017
Messages
1,899
Hi the "Qry_Contract_Details_Subform" makes no sense to me at all.

You have the following fields:_

Description
Item_Value
Item_Value_Previous
Item_Value_Current
item_Value_Total

If the user enters data into the subform what would be the values they would enter for the following:-

item_codeContractCodeDescriptionitem_Valueitem_Value_Previousitem_Value_Currentitem_Value_Total
4 56mechanical3000
5 56electrical2500
 

mobarak ahmed

Member
Local time
Today, 12:15
Joined
May 28, 2021
Messages
95
Hi the "Qry_Contract_Details_Subform" makes no sense to me at all.

You have the following fields:_

Description
Item_Value
Item_Value_Previous
Item_Value_Current
item_Value_Total

If the user enters data into the subform what would be the values they would enter for the following:-

item_codeContractCodeDescriptionitem_Valueitem_Value_Previousitem_Value_Currentitem_Value_Total
4 56mechanical3000
5 56electrical2500
this form is subform in invoice form >> when you choose invoice to and project name it should show the details of the project from Qry_Contract_Details query and i have made form for it in order to make total field to "item_Value_Current"
the "item_Value_Current" is the invoice value
item_Value_Previous is last "item_Value_Total "
and item_Value_Total is item_Value_Current +item_Value_Previous
 

mike60smart

Registered User.
Local time
Today, 10:15
Joined
Aug 6, 2017
Messages
1,899
Ok so in the screen shot for Invoice 10 what are the values you expect to see in the Subform ?
 

Attachments

  • Invoice 10.JPG
    Invoice 10.JPG
    114.1 KB · Views: 103

mobarak ahmed

Member
Local time
Today, 12:15
Joined
May 28, 2021
Messages
95
Ok so in the screen shot for Invoice 10 what are the values you expect to see in the Subform ?
If you open "Qry_Contract_Details_Subform" form in design view you will see total field in the footer .. this total is the invoice value
I want this total to go to invoice value in invoice form after update or after change it should be 1500 .. sum of all "item_Value_Current" items
 

mike60smart

Registered User.
Local time
Today, 10:15
Joined
Aug 6, 2017
Messages
1,899
Hi

OK See that attached
 

Attachments

  • Mobarak - 6-9-2021.accdb
    6.6 MB · Views: 105

mobarak ahmed

Member
Local time
Today, 12:15
Joined
May 28, 2021
Messages
95
but still "Item_Value_Previous" get sum of both items
i used this Expression before
Code:
Previous: DSum("[item_value_current]","inv_contract_details","[contractcode] = " & [ContractCode] & " AND [item_code] = " & [item_code])
and it was working but don't know what happend
 

mike60smart

Registered User.
Local time
Today, 10:15
Joined
Aug 6, 2017
Messages
1,899
Hi

As explained previously you cannot use [item code] in your DSum because each record will have a different Item Code value (Item Code is a Primary Key)
 

mobarak ahmed

Member
Local time
Today, 12:15
Joined
May 28, 2021
Messages
95
Hi

As explained previously you cannot use [item code] in your DSum because each record will have a different Item Code value (Item Code is a Primary Key)
thank you Mr Mike i have changed item code in inv_Contract_details table to number and make new field Id and make it the primary key instead and now it's working well
 

mobarak ahmed

Member
Local time
Today, 12:15
Joined
May 28, 2021
Messages
95
Hi Dears
i want to set "item_Value_Current" to null every new invoice so i can write the new progress value
you can use invoice to "Engineering for the Petroleum and Process Industries - Enppi"
and project name "Abo Rudes" for try
another thing if possible i want to move the total of "item_Value_Current" which appeared in the form in Text343 text box
to "Current" text box with notice that the event of invoicing after update "Current" text box
 

Attachments

  • Mobarak - 13-9-2021.zip
    2.2 MB · Views: 103

mike60smart

Registered User.
Local time
Today, 10:15
Joined
Aug 6, 2017
Messages
1,899
I added code to the Save Invoice Button but you still have problems with your append query.
 

Attachments

  • Mobarak - 13-9-2021.zip
    1.9 MB · Views: 96

mobarak ahmed

Member
Local time
Today, 12:15
Joined
May 28, 2021
Messages
95
I added code to the Save Invoice Button but you still have problems with your append query.
it has to be in different button cause the events of calculation in " Current " not working now try to write any value in current to get what i mean
about append query if you change the value in current it will work fine
also the value in "item_Value_Current" still have value when choose project
 

mobarak ahmed

Member
Local time
Today, 12:15
Joined
May 28, 2021
Messages
95
i think it's better now the value moved when click move the value button and click on invoice value field to make the calculations
any better idea ?
 

Attachments

  • Mobarak - 12-9-2021.zip
    2.2 MB · Views: 107

mike60smart

Registered User.
Local time
Today, 10:15
Joined
Aug 6, 2017
Messages
1,899
Sorry but someone else will need to take a look at this, as I am completely lost with your process,
 

Cotswold

Active member
Local time
Today, 10:15
Joined
Dec 31, 2020
Messages
521
Beats me why you need such huge variables, forms and no doubt table and field names Mobarak.
Aim for 8 max is my advice. What is wrong with IvalPrev ?
 

mobarak ahmed

Member
Local time
Today, 12:15
Joined
May 28, 2021
Messages
95
Beats me why you need such huge variables, forms and no doubt table and field names Mobarak.
Aim for 8 max is my advice. What is wrong with IvalPrev ?
it's for contract clients accounting , all this tables i need it in my work
 

Cotswold

Active member
Local time
Today, 10:15
Joined
Dec 31, 2020
Messages
521
it's for contract clients accounting , all this tables i need it in my work
I understand that Mobarak......I think. But your references in Queries, IIF() Functions etc must be a mile long. (or maybe 1.6Km!)

From the 1980s through to the end of 1990s, using MS-DOS you couldn't have a filename longer than 8 characters, let alone
field names and variables.

Whilst some may find this restrictive by current coding rules, it did have advantages in forcing clarity in creating file and
variable naming techniques. The file and variable naming systems that I developed nearly 40 years ago I still use today, but
with slight variations due to Access and SQL type databases.

Only the other day @isladogs strongly advised against using spaces in names, which is good advice to be followed.
However, far from it for me to tell you what to do Mobarak, I just think that you could benefit from using shorter names
in future. But as I say, your choice.
 

Users who are viewing this thread

Top Bottom