Hi there,
I have a project at hand and it's been a predecessor of mine (you know where I'm going, yeah?) and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/
A little introduction to the functionality - again this is as far as I have looked into the system as it was built many moons ago.
The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.
At the last column of each row is the sub total of those particular items i.e.
Qty Unit | Item ID | Total
-----------------------
2 | 1234 | 80.00
------------------------
1 | 43526 | 20.00
------------------------
> | |
So the total is a function of =[Qty Unit] * [Unit Price]
Then in the Footer of this SubForm is the Sub Total
=SUM([Qty Unit] * [Unit Price])
All fine and well..... However, the additional functionality kicks in.
Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.
So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.
Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.
So what I have done was made a function that would do as below:
Code:
Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer)
Dim SPSelect As String
SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE"
SPSelect = SPSelect & " ItemID = '" & ItemID
SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "
Dim SaleUnit As Variant
SaleUnit = CurrentDb.OpenRecordset(SPSelect).OpenRecordset.Fields("Price")
CalculateSpecialPrice = SaleUnit * Unit
End Function
I changed the subform's total to equal
=CalculateSpecialPrice([Item ID], [Form]![FormName]![CustomerID], [Qty Unit])
Each row works out fine. Brilliant (I think)
BUUUUUUUUT, its the sub total I just keep on getting #Error on and I have NO idea why. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.
=SUM(CalculateSpecialPrice([Item ID], [Form]![FormName]![CustomerID], [Qty Unit]))
#Error
Am I missing something fundamental ?
Any help would be most appreciated.