I have a mental block... or I'm stupid...
So here is the situation. The main form of my database remains open all the time. When the main form is loaded it links in a bunch of SQL tables. This is the 'default', for almost everything in the database a linked table has no discernible effect on the speed at which my database operates.
When I open a second form (frmSlidingFeeScale) I import some tables from a SQL database, for this particular part of the database I need the tables to be local for the sake of speed. That part works fine. The problem I'm encountering is when I close the second form (frmSlidingFeeScale). I want to drop the imported version of these tables, then re-establish the linked version. I've tried perhaps a dozen different methods to do this and I just can't seem to get it right. This is my current code, there are two tables that the function RELINKTABLE will not work on (RELINKTABLE drops the imported table and re-establishes a linked version instead) because they are used in combo boxes/list boxes on frmSlidingFeeScale.
The do loop is not correctly waiting until frmSlidingFeeScale is closed before firing off the relinks. The only two solutions I've found that work without fail so far are:
1. Put in a timer to wait x many seconds to close the secondary form
2. Set the rowsources for the combo/list boxes on frmSlidingFeeScale when I open it
I would prefer to get away from these two options If I can, but I just can't see to get the isloaded to fire correctly.
If anyone has a suggestion I am all ears.
RELOAD is a field on the main form, it is populated with the text value SF when I close my secondary form (frmSlidingFeeScale) in the ON CLOSE event.
Code:
If RELOAD = "SF" Then
Do Until CurrentProject.AllForms("frmslidingfeescale").IsLoaded = False
Loop
Call RELINKTable("Enrollment", 0)
Call RELINKTable("Individual", 0)
Call RELINKTable("AdjudicatedClaim", 0)
Call RELINKTable("ServiceCode", 0)
Call RELINKTable("Agency", 0)
Call RELINKTable("Business", 0)
Call RELINKTable("SlidingFeeScale", 0)
ElseIf RELOAD = "RA" Then
cmdImportLinked_Click
EvaluateButtons
RELOAD = Null
End If
RELOAD = Null