Actions before Form opens

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a multipurpose form (Frm-A) that is opened from several other forms.
Before the form opens I need to create a TEMP table that is the basis for the
query that selects records for a subform in the form. This works well except
that I have to create the temp table before issuing the OPEN for Frm-A. Once
the OPEN is issued the form appears to lock the temp table.

My question is: is there an EVENT that occurs before the form LOCKS the
temp table? I have tried "on open", "on load" and, "on activate". If I
could create the temp table one place it would save a lot of redundant code.

Steve
 
Hi Steve,

Ok I'm assuming that the temp table has different fields each time therefore
you can't just run an DELETE SQL query to clear out the table instead of
deleting then recreating(?)
This being the case, I can suggest the following method;

Don't set the sub-forms Source Object property until after replacing the
temp table in the OnOpen event of the main form. To do this, clear the
current sub-forms Source Object property in design mode of Access. Then add
something similar to this VBA Code to the OnOpen event:

Private Sub Form_Open(Cancel As Integer)

' Your code here to delete and re-generate the temporary table,

' Now set the source of the sub-form to point at the new table...
Me!subFrmName.SourceObject = "Table.TblTemp"

End Sub

Where 'subFrmName' should be replaced with the name of your sub-form and
'TblTemp' should be replaced with ur table name.

Regards,

Wayne Phillips
http://www.everythingaccess.com/forums

There are a few ways of doing this, probably the simplest is to leave the
 
Thanks Wayne.

What do you mean "the temp table has different fields each time therefore
you can't just run an DELETE SQL query to clear out the table" The table
has the same field names but a unique record set each time. If I knew how I
could just clear it each time. That is a good idea but how do I do that?

Steve S (old IBM Assembler, COBOL, and Fortan programmer)
 
Ah well in that case its much better doing it this way;

1. Clear out the temp table

Either of the following VBA statements would be ok;
DoCmd.OpenQuery "qryMyDeleteQueryName" ' Requires you to create a
Delete Query in access, deleting all records from the temp table
DoCmd.RunSQL "DELETE * FROM TblTemp" ' Deletes all rows from TblTemp
without requiring a query

2. Populate the temp table

I guess you've been using a 'make-table' query to recreate the table
each time?... If so you will need to change this query into a 'insert'
query.
If however you were recreating the table by VBA code, you just need to
remove the extra code that was recreating the table since it will already
exist.

Please note that you should try not to use temp tables in Access as they
never really get deleted or cleared until the 'Compact and Repair' option is
run. It would be better if you were to use dynamic queries instead of temp
tables in most circumstances. If you want help with this, please post
examples of what your actually doing with the temp table.

Regards,

Wayne Phillips
http://www.everythingaccess.com/forums
 
Back
Top