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

mobarak ahmed

Member
Local time
Today, 17:36
Joined
May 28, 2021
Messages
95
hi Friends , i have contract form which contain Contract_details subform
every contract have his own details items
in invoice form i have feild called invoice value
it should be sum of "item_Value_Current" in contract_details table
now i want When I choose project name , the items of this contract appeared in invoice form according to contract_details table
in order to write the current progress value in "item_Value_Current" and the sum of this cell appeared in "invoice value " in invoices form
notice : every invoice has it's own "item_Value_Current"
"item_Value_Previous" is the sum of all previous "item_Value_Current"
"item_Value_Total" is sum of Previous and "item_Value_Current"

after this report "Invoices" should show every invoice with it's own details
can use project name "tagreba" and "Tagreba2" to try as i add
items
value in both of them
 

Attachments

  • Mobarak - 29-8-2021.zip
    2.2 MB · Views: 437
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 15:36
Joined
Jul 9, 2003
Messages
16,245
I can't read your post on my mobile. The coloured text is invisible.

I gather you have problems with your invoicing system.

I thought I should mention the northwind sample database provided by Microsoft contains an example of an invoicing system. There are a couple of problems with it, you can read about them on my website. It might be worth you having a look at it.

See Here:-


(You need the oldest one)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:36
Joined
Feb 19, 2002
Messages
42,988
From your description, it seems that you are storing calculated data.. That is always a problem since it is very difficult to ensure that the calculated values are always valid. This is why storing calculated valises violates second normal form. Calculations should be performed in queries or on forms so that the results are always current. On your form, you can use a dSum() to calculate the value you want or if the data is displayed in a subform, you can add controls to the subform footer to sum the data and then have your controls in the main form reference the totals fields in the subform footer to display the values in the main form.
 

mobarak ahmed

Member
Local time
Today, 17:36
Joined
May 28, 2021
Messages
95
I can't read your post on my mobile. The coloured text is invisible.

I gather you have problems with your invoicing system.

I thought I should mention the northwind sample database provided by Microsoft contains an example of an invoicing system. There are a couple of problems with it, you can read about them on my website. It might be worth you having a look at it.

See Here:-


(You need the oldest one)
thank you for helping i have edit the post to one black color in order to be clear i have download the sample database but unfortunately i didn't find what i need
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:36
Joined
Feb 19, 2002
Messages
42,988
And you didn't like my suggestion or you didn't understand it?
 

mobarak ahmed

Member
Local time
Today, 17:36
Joined
May 28, 2021
Messages
95
And you didn't like my suggestion or you didn't understand it?
I think you didn’t get my point
sorry ,, I were about to reply you but i slept Lol
I want somehow to link contract details to invoice form
So if i choose project name ,it’s items details appeared in the sub form so i can write the current progress on it and the total of it moved to invoice value field
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:36
Joined
Feb 19, 2002
Messages
42,988
So, you don't want the sum of the amounts, you want to actually copy the detail records???? Why do you want the details to be in two places?
 

mobarak ahmed

Member
Local time
Today, 17:36
Joined
May 28, 2021
Messages
95
So, you don't want the sum of the amounts, you want to actually copy the detail records???? Why do you want the details to be in two places?
Ok let me try to explain it is construction company
We make contract to make something by total value let’s say 1000,000 this value is break down to items let’s say electrical works 500,000
Mechanical works 500,000 if you see add new contract form you can see what i talk about

Now let’s move to make invoice 1
In invoice form
Choose the client name for example ac boilers
Then choose the project name “tagreba”
You will see that the total value of contract appeared in the field
I want to make contract details items appeared in order to writ the current progress value on it and sum of both items will go to invoice value field
Note every invoice to the same project has the same items total value but the current value changed or linked to the progress status
Do you get what i mean ?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:36
Joined
Feb 19, 2002
Messages
42,988
You added information but did not address my question. WHY do you want to DUPLICATE the detail items? WHY can you not link to them where they are if you want to include them on the invoice? Is this a one time invoice or progress payments? Do the details change over time? Do you need a history of how they appeared on the invoice?
 

mobarak ahmed

Member
Local time
Today, 17:36
Joined
May 28, 2021
Messages
95
You added information but did not address my question. WHY do you want to DUPLICATE the detail items? WHY can you not link to them where they are if you want to include them on the invoice? Is this a one time invoice or progress payments? Do the details change over time? Do you need a history of how they appeared on the invoice?
yes exactly the details changed from invoice to another by the progress
the total value of item is fixed but the progress changed
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:36
Joined
Feb 19, 2002
Messages
42,988
OK, we're making progress. If you need to keep the history, then use an append query when you are creating the invoice. In the AfterUpdate event of the invoice header form, run a query that selects the details and appends them to the invoice details table. The queyr needs two variables. The ID of the new invoice header to be used as the foreign key and the id of the project so it knows what project to select details from. You might have to requery the subform. The subform should NOT allow updates or deletes.
 

mobarak ahmed

Member
Local time
Today, 17:36
Joined
May 28, 2021
Messages
95
OK, we're making progress. If you need to keep the history, then use an append query when you are creating the invoice. In the AfterUpdate event of the invoice header form, run a query that selects the details and appends them to the invoice details table. The queyr needs two variables. The ID of the new invoice header to be used as the foreign key and the id of the project so it knows what project to select details from. You might have to requery the subform. The subform should NOT allow updates or deletes.
thank you i will give it a try i hope i can do it myself Lol
 

mobarak ahmed

Member
Local time
Today, 17:36
Joined
May 28, 2021
Messages
95
now i have made query for "Contract_details" Table and Append Query to Add Values to "inv_Contract_details" Table referring to Invoicenumber And ContractCode when i click Tarhel Button in "invoice" Form
but i face this MSG in Attach what is the problem here !!
" you can choose invoice to "Egyptian Electricity Transmission Company (EETC)"
and Project Name " Ali Mobarak" to try it as i have put break down items for it
any help will be appreciate
 

Attachments

  • 897987.jpg
    897987.jpg
    119.5 KB · Views: 390
  • Mobarak - 6-9-2021.zip
    2.1 MB · Views: 310

mike60smart

Registered User.
Local time
Today, 15:36
Joined
Aug 6, 2017
Messages
1,899
Hi mobarak
The attached now works.
When you do an append query you do not include any Primary Key as this gets added automatically.
 

Attachments

  • Mobarak - 6-9-2021.zip
    1.8 MB · Views: 270

mobarak ahmed

Member
Local time
Today, 17:36
Joined
May 28, 2021
Messages
95
great as usual Mr. Mike it's worked
if you let me
**previous field in "Qry_Contract_Details" should dSum all "item_Value_Current" in inv_Contract_details table by to criteria contractcode and item_code i have try it before it was working now it's not , don't know why
** can we make the default value of "item_Value_Current" in "Qry_Contract_Details" in order to write new value every time
 

mike60smart

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

This will work:- Previous: DSum("[item_value_current]","inv_contract_details","[contractcode] = " & [ContractCode]) but you cannot add [Item_Code] as this is the Foreign Key in your table "inv_contract_details"
 

mike60smart

Registered User.
Local time
Today, 15:36
Joined
Aug 6, 2017
Messages
1,899
Which Form are you using "Qry_Contract_Details" as the record Source ?
 

Users who are viewing this thread

Top Bottom