Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2006
    Location
    New Kensington, PA
    Posts
    17

    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
    ' DoCmd.Save

    Is this code wrong?
    Is this a good approach or can anyone suggest a more straighter approach?

  2. #2
    Join Date
    Mar 2006
    Posts
    163
    As far as I'm aware OpenTable just opens a table, nothing else.

    Tables don't need to be 'active' to put data in them.

    I suggest you look at either OpenRecordset or Execute.

    With the latter you could run an INSERT query.

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    You can do that using the DAO.RecdordSet

  4. #4
    Join Date
    Mar 2004
    Location
    UK
    Posts
    82
    if i understand what you're trying to achieve??

    have you tried basing the form on a query and using an expression in the query to make the calclation.

    you can then open the form on the desired reocrd and the calculation will be there.

  5. #5
    Join Date
    Oct 2006
    Location
    New Kensington, PA
    Posts
    17
    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].

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •