Access 2007, Single User on one machine, VBA skill level???? Well, I'm not on the short bus but I'm still not driving my own car. This Code was working spiffy in the past. The DB is not compiled or split yet. It will be in a multiuser environment.
I have a query that displays the Lowest Price Vendor information on a form. The single table under it is Tbl_LPVCurrent. This table is stand alone and not related to any other tables. It is however called up in a combo box in various places when a user needs to change an existing item for a lower price item.
I have a command button on a form. The user clicks the button and it runs the series of queries below recalculating the Lowest Price Vendor. This data then refreshes to the same screen and displays new pricing to the user. A lot of the queries are Make Table. When it works, it works fast.
I have read a lot of forums and see little that substantially addresses this error. I have sprinkled DoEvents everywhere, don't laugh.
Private Sub CreateNewLPVList_Click()
DoCmd.Close acTable, "Tbl_LPVCurrent" (Doesn't work)
DoEvents (DoEvents doesn't work)
DoCmd.SetWarnings False
DoCmd.OpenQuery "Qry_LPV_ArchiveToTbl_LPV_Archive"
DoEvents
DoCmd.OpenQuery "Qry_LPV_Step0_Eliminate_Nulls_inactives"
DoEvents
DoCmd.OpenQuery "Qry_LPV_Step1_ExtractMinLowestPriceVendor"
DoEvents......
DoCmd.OpenQuery "Qry_LPV_Step1b_ExtractMinLowestPriceVendor"
DoCmd.OpenQuery "Qry_LPV_Step2_ExtractCombinedVendors"
DoCmd.OpenQuery "Qry_LPV_Step3_ExtractMinLowestPriceVendor"
DoCmd.OpenQuery "Qry_LPV_Step4_ExtractMinLowestPriceVendor"
DoCmd.OpenQuery "Qry_Lpv_Step5_LPV_Tbl_LVPCurrent"
DoCmd.OpenQuery "Qry_LPV_Step5b_ChangeYestoNO_Tbl_VendorPrices "
DoCmd.OpenQuery "Qry_LPV_Step6_ApplyLPVtoTbl_VendorPrices"
Me.Refresh
DoCmd.SetWarnings True
Exit Sub
End Sub