Using field values from a table

[Rose B]

I am not sure whether what I am wanting to do is possible - would appreciate
some guidance! There are two scenarios.

Scenario 1

I have a form with 2 subforms, each continuous forms. One subform has data
being entered by the user and, depending upon what they enter I need to
calculate one of the fields using information from the other subform - which
actually contains the contents of a table. So - a user will enter and
activity type, a date and a value in a field. I then want the value to be
multiplied by a rate that is found in another table where the activities are
equal and the date is between a from and a to date. (Similarly I have another
field that I want to set to the value in another table where the activities
match plus one other condition)

I have tried various methods and come unstuck each time - I am guessing that
VBA is possible?

Scenario 2.

I want to create entries into a table (invoice lines) based upon entries in
a table (services taken). The value of the invoice (and whether it is
invoiceable at all) will depend upon test similar to those in scenario 1. For
example, if the service taken needs to be invoiced (tested by activity on a
table indicating whether or not this is an invoiceable service) pick up the
rate for the service based upon the activity and the date that it took place
from a rates table.

My intention is to do all of his "in batch" once the user clicks a button to
prepare invoices.

Appreciate any thoughts - I realise that this is a potential can of worms

 

[John W. Vinson]

I am not sure whether what I am wanting to do is possible - would appreciate
some guidance! There are two scenarios.

Scenario 1

I have a form with 2 subforms, each continuous forms. One subform has data
being entered by the user and, depending upon what they enter I need to
calculate one of the fields using information from the other subform - which
actually contains the contents of a table. So - a user will enter and
activity type, a date and a value in a field. I then want the value to be
multiplied by a rate that is found in another table where the activities are
equal and the date is between a from and a to date. (Similarly I have another
field that I want to set to the value in another table where the activities
match plus one other condition)

I have tried various methods and come unstuck each time - I am guessing that
VBA is possible?

Bear in mind that the data that you want is only *displayed* on the other
subform - it's actually stored in the table, and it's more readily available
by getting it directly from the table. You can use the DLookUp() function to
do this:

<some expression> * DLookUp("[Rate]", "[RatesTable]", "[StartDate] <= #" &
[MyDateField] & "# AND [EndDate] >= #" & [MyDateField] & "#")

for example.

Scenario 2.

I want to create entries into a table (invoice lines) based upon entries in
a table (services taken). The value of the invoice (and whether it is
invoiceable at all) will depend upon test similar to those in scenario 1. For
example, if the service taken needs to be invoiced (tested by activity on a
table indicating whether or not this is an invoiceable service) pick up the
rate for the service based upon the activity and the date that it took place
from a rates table.

My intention is to do all of his "in batch" once the user clicks a button to
prepare invoices.

Appreciate any thoughts - I realise that this is a potential can of worms

An Append query based on both tables sounds appropriate here, but since you
don't specify the logic of the tests I can't be specific.

John W. Vinson [MVP]

 

[Rose B]

Thanks for coming back so quickly John. I will try the DLookup on scenario 1
tomorrow (Sat night here in UK and just off out!).

For Scenario 2 it would something like this....

- Get records in question from table 1 that need to be invoiced
- Calculate the amount to be invoiced
If the record is of a particular type then
find all of the clients who have to be invoiced
(will be more than 1)
based upon the rule (in a 3rd table)for this
particular service create an invoice line for each client either all with the
same charge (specified in the 3rd table), or taking a share of the total cost
(calculated by multiplying a no of units field in the original record set by
a price in another table, depending on service and date (similar to scenario
1) dividing by a count of the no of clients sharing the cost and insert a
record into an invoice line table
else
calculate the amount to be invoiced (as per scenario
1) and insert a record into an invoice line table.

In either eventuality, update the original record with the key from the
invoice line table to ensure that we do not invoice again.

All of this is "top and tailed" by creation of an invoice batch number, and
then the invoice line records are to be sorted by client and for each client
a record to be inserted into an invoice header table which also contains the
batch number, and the invoice line record updated with the invoice number.
This should then allow the invoices that have been 'prepared' to be backed
out, printed or re-printed in a controlled way.

Hope that this makes some kind of sense - as I said - a bit of a can of worms!

I will post back tomorrow with Scenario 1 results.

Hope you have a good day/evening,

Rose

I am not sure whether what I am wanting to do is possible - would appreciate
some guidance! There are two scenarios.

Scenario 1

I have a form with 2 subforms, each continuous forms. One subform has data
being entered by the user and, depending upon what they enter I need to
calculate one of the fields using information from the other subform - which
actually contains the contents of a table. So - a user will enter and
activity type, a date and a value in a field. I then want the value to be
multiplied by a rate that is found in another table where the activities are
equal and the date is between a from and a to date. (Similarly I have another
field that I want to set to the value in another table where the activities
match plus one other condition)

I have tried various methods and come unstuck each time - I am guessing that
VBA is possible?

Bear in mind that the data that you want is only *displayed* on the other
subform - it's actually stored in the table, and it's more readily available
by getting it directly from the table. You can use the DLookUp() function to
do this:

<some expression> * DLookUp("[Rate]", "[RatesTable]", "[StartDate] <= #" &
[MyDateField] & "# AND [EndDate] >= #" & [MyDateField] & "#")

for example.

Scenario 2.

I want to create entries into a table (invoice lines) based upon entries in
a table (services taken). The value of the invoice (and whether it is
invoiceable at all) will depend upon test similar to those in scenario 1. For
example, if the service taken needs to be invoiced (tested by activity on a
table indicating whether or not this is an invoiceable service) pick up the
rate for the service based upon the activity and the date that it took place
from a rates table.

My intention is to do all of his "in batch" once the user clicks a button to
prepare invoices.

Appreciate any thoughts - I realise that this is a potential can of worms

An Append query based on both tables sounds appropriate here, but since you
don't specify the logic of the tests I can't be specific.

John W. Vinson [MVP]

 

[Rose B]

Scenario 1 coded and working fine - thanks!

I am not sure whether what I am wanting to do is possible - would appreciate
some guidance! There are two scenarios.

Scenario 1

I have a form with 2 subforms, each continuous forms. One subform has data
being entered by the user and, depending upon what they enter I need to
calculate one of the fields using information from the other subform - which
actually contains the contents of a table. So - a user will enter and
activity type, a date and a value in a field. I then want the value to be
multiplied by a rate that is found in another table where the activities are
equal and the date is between a from and a to date. (Similarly I have another
field that I want to set to the value in another table where the activities
match plus one other condition)

I have tried various methods and come unstuck each time - I am guessing that
VBA is possible?

Bear in mind that the data that you want is only *displayed* on the other
subform - it's actually stored in the table, and it's more readily available
by getting it directly from the table. You can use the DLookUp() function to
do this:

<some expression> * DLookUp("[Rate]", "[RatesTable]", "[StartDate] <= #" &
[MyDateField] & "# AND [EndDate] >= #" & [MyDateField] & "#")

for example.

Scenario 2.

I want to create entries into a table (invoice lines) based upon entries in
a table (services taken). The value of the invoice (and whether it is
invoiceable at all) will depend upon test similar to those in scenario 1. For
example, if the service taken needs to be invoiced (tested by activity on a
table indicating whether or not this is an invoiceable service) pick up the
rate for the service based upon the activity and the date that it took place
from a rates table.

My intention is to do all of his "in batch" once the user clicks a button to
prepare invoices.

Appreciate any thoughts - I realise that this is a potential can of worms

An Append query based on both tables sounds appropriate here, but since you
don't specify the logic of the tests I can't be specific.

John W. Vinson [MVP]