Unanswered: How do you save data to a table this is not active?
(Access 2003/VBA) I have a form that collects bid information and selects a bid based on parameters supplied by user imputs. The form goes through some complex calculations for cost savings based on a comparison of the average of all bids. I have set this form up to allow all bids to be entered and a command button to perform the final calculations of the average and cost savings (this works well).
However. when a user opens this form (previously completed), they would have to parse thru all the bids before pressing the "Calulate" button to get the desired results. I was thinking that if the calculated results were saved to a separate table, these fields (unbound) can display the calculated results with out the parcing.
I inserted this VBA code within the calculation but does not work???
' DoCmd.OpenTable "tblPO_SuccessBidvsAvg_Saving", acViewNormal, acEdit
' [strPurchaseOrder] = Me.strPurchaseOrder
' [curAvgBid] = Me.txtAverageBid
' [curCostSave] = Me.[txtCostSavings]
' [numBidsUsed] = Me.txtBidsUsed
Is this code wrong?
Is this a good approach or can anyone suggest a more straighter approach?
I don't think a query can perform the calculations that I need. I am collecting a series of bids, and base on the dollar amount the code calculates the cost of savings i.e.: [CostSavings]=[AveragedBid]-[WinningBid]
The cavaught is that the bids to use are determined by one of the following conditions:
- All bids with in a 25% variance of the awarded bid
or - All high bids with a 25% lower awarded bid
or - All low bids with a 25% higher awarded bid
or - All bids in variance of awarded bid and exclude any bids higher or lower than 25% of rest of the bids.
To net it all out, data goes thru a series of VBA condition test and captures the correct amounts in variables and after user completes all the bid entries,
a command button to (Calculate Results) with more VBA code does the final calculation. If called up later, the final calculation results will only display by having to tab thru all the bid fields to capture their values and click the (Calculate Results) button again. The average amount of bids for each instance are usually 4 plus. The (unbound) fields I am trying to populate are the [Cost Savings], [Average Bid], [Bid Count].