Form to autopopulate from other forms.

[gscma1]

I have a database which works fine for inputting weekly wages and calculating gross and nett totals etc.

each week a certain amount of cash is paid to workers as part of their wage.

we have a cash distribution form which is currently on microsoft excel, which means it is completed out of the database, which is not ideal.

i want their to be a form in access which has all the fields of the cash distribution form, but which also draws information from the data already input.

e.g.

Cash Distribution Form

Week No
Cash Available
Sub-Contractors Cash
PAYE Cash
Petty Cash
Other Receipts
Balance

The SubContractors Cash, PAYE Cash and Other Receipts can come from tblHours and tblP11 where the SubContractors and PAYE weekly hours are input.

Each week there are about 40 employees all with different amounts of cash or usually no cash. the field for SC Cash and PAYE Cash would need to add up the cash for all employees.

Also Petty Cash can be a default value each week.

The user should only need to input into the Cash Available cell.

From this i would then like to print a report in the above format with a balance. i can do this.

If anyone understands please help! thank you.

 

[BSman]

If you identify the amount of cash paid to each employee in your current data, then when you enter a new week and cash available, the calculation for SCCash and PAYE Cash would be a total of the sources you mention plus the total of the cash payments to employees from your existing table that identifies that money.

Bob

 

[gscma1]

that sounds like what i want to do but how do i do it?


i have set up my form from a very basic table lay out.

my SCCash field - do I use the control source to make it look at the tblHours SCCash field?

I'm a bit confused.

thanks

 

[gscma1]

any help on this would be much appreciated?

 

[Remou]

Have you looked at DSum? You could use it with, say, a calculate button. For Example:

Me.[Sub-Contractors Cash]=DSum("[Sub-Contractors Cash]","tblHours","WeekNo=" & Me.WeekNo)

 

[gscma1]

I have tried this however it returns #Name? in the field

i am unsure of what else to try or why this isnt working.

 

[Remou]

In the above you have an example, not finished code. You need to fill in the correct fields. Do you have a date or a week number in your table? Is Sub-Contractors Cash the name of a field?

 

[gscma1]

I am aware that was an example, and have used my own field names. which are WeekNo and SCCash.

I have also tried this method previously, although it doesn't want to return the correct results.

 

[Remou]

If it returns #name, then it seems likely that there is an error in the spelling of a field. Are you using Dsum as the control source of a textbox? When you say it does not return the correct results, how do you mean? Please post the line as amended for your application.