OpenForm Does Not Update Embedded Query

[SteveM]

I have a Form with a Query source. The associated Table content is
updated with outputs from an optimization routine.

When I FIRST open the form using code or even from a command button on
a switchbox, the underlying query is not updated to reflect the
optimization results. However if I close the form and open it again
from the switchboard, the query updates properly. This behavior is
not absolute. Sometimes the query will update properly when opened
the first time.

Any ideas on how to ensure the underlying query executes ALL the time
when the Form is opened?

Thanks Much,

SteveM

 

[SteveM]

If I understand you correctly, you have a form (lets call it frmMain), which
has a RecordSource which we will call tblMain.  From your description, it
sounds like you also have a query (qryOptRoutine) which is supposed to update
tblMain whenever frmMain is loaded.  Is that accurate?

If so, you could put a line of code in frmMains Load event to run the
qryOptRoutine, and then requery the current form.  Something like:

Private Sub Form_Load()

    currentdb.execute "qryOptRoutine", dbfailonerror
    me.requery

endif

Another way to do this would be to put the line of code that executes
qryOptRoutine in the Click event of the switchboard form button that opens
the form.  But if you are using a true switchboard generated by Access rather
than one you have created from scratch, I don't think that is possible.

HTH
Dale








--
HTH

Dale Fye

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200905/1

Dale,

Thanks for the reply. I apologize for not being clear. I have a Data
Table which feeds the optimizer. The optimizer populates a Solution
table. The Form only has an embedded source query which links up the
Data Table with the Solution table to give the user something he can
understand. So the Form is just the query that I open as a Pivot
Table. I've done a fair amount of modeling with Excel/VBA, but I'm
still trying to get the hang of Access. It does some quirky stuff.
The optimizer is a Dll that takes control of the Solution table to
fill it. But I think it's released after execution. I assumed that
DoCmd.OpenForm would execute the requery automatically. I suppose it
does, but only on the second try. Hmm, now why is that...?

I'll give your suggestions a shot.

Best,

SteveM

 

[SteveM]

If I understand you correctly, you have a form (lets call it frmMain), which
has a RecordSource which we will call tblMain.  From your description, it
sounds like you also have a query (qryOptRoutine) which is supposed to update
tblMain whenever frmMain is loaded.  Is that accurate?

If so, you could put a line of code in frmMains Load event to run the
qryOptRoutine, and then requery the current form.  Something like:

Private Sub Form_Load()

    currentdb.execute "qryOptRoutine", dbfailonerror
    me.requery

endif

Another way to do this would be to put the line of code that executes
qryOptRoutine in the Click event of the switchboard form button that opens
the form.  But if you are using a true switchboard generated by Access rather
than one you have created from scratch, I don't think that is possible.

HTH
Dale








--
HTH

Dale Fye

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200905/1

Dale,

Thanks for the reply. I apologize for not being clear. I have a Data
Table which feeds the optimizer. The optimizer populates a Solution
table. The Form only has an embedded source query which links up the
Data Table with the Solution table to give the user something he can
understand. So the Form is just the query that I open as a Pivot
Table. I've done a fair amount of modeling with Excel/VBA, but I'm
still trying to get the hang of Access. It does some quirky stuff.
The optimizer is a Dll that takes control of the Solution table to
fill it. But I think it's released after execution. I assumed that
DoCmd.OpenForm would execute the requery automatically. I suppose it
does, but only on the second try. Hmm, now why is that...?

I'll give your suggestions a shot.

Best,

SteveM

 

[SteveM]

Steve,

OpenForm will do just that.  It opens the form.  If the form is basedon a
table, you will see the records from the table in the form.  If the forms
RecordSource is a query (it does not sound like yours is), then it will run
the query and display the query results in the form.  Almost all of my forms
are based on a query, because I generally want to return the records in some
logical order, and if you don't assign an order, Access will generally return
them in order based on the tables primary key.

HTH
Dale

If I understand you correctly, you have a form (lets call it frmMain),which
has a RecordSource which we will call tblMain.  From your description, it

[quoted text clipped - 42 lines]

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200905/1

Dale,

Thanks for the reply.  I apologize for not being clear.  I have a Data
Table which feeds the optimizer.  The optimizer populates a Solution
table.  The Form only has an embedded source query which links up the
Data Table with the Solution table to give the user something he can
understand.  So the Form is just the query that I open as a Pivot
Table.  I've done a fair amount of modeling with Excel/VBA, but I'm
still trying to get the hang of Access.  It does some quirky stuff.
The optimizer is a Dll that takes control of the Solution table to
fill it.  But I think it's released after execution.  I assumed that
DoCmd.OpenForm would execute the requery automatically.  I suppose it
does, but only on the second try.  Hmm, now why is that...?

I'll give your suggestions a shot.

SteveM

--
HTH

Dale Fye

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200905/1

Dale,

Thanks again. Let me iterate further if I can. I isolated the
Solution table after the optimization without opening the Pivot Table
Form that executes the query. The Solution table does NOT reflect the
changes made by the optimizer. It's like they were made but not
posted. If I open the Solution table and then Refresh it from the
Access user interface, then manually open the Pivot Table Form, the
form information is correct the first time. So the embedded query
works, but it's using the old Solution data. So I need to code up a
Refresh Solution table statement and insert that prior to my OpenForm
statement. I've looked through the VB help and how to do that is not
clear. I'm sure it's trivial though. Any help with that statement?

BTW, the database is in 2003 format because the optimizer does not
read in 2007 tables.

Steve

 

[SteveM]

Steve,

OpenForm will do just that.  It opens the form.  If the form is basedon a
table, you will see the records from the table in the form.  If the forms
RecordSource is a query (it does not sound like yours is), then it will run
the query and display the query results in the form.  Almost all of my forms
are based on a query, because I generally want to return the records in some
logical order, and if you don't assign an order, Access will generally return
them in order based on the tables primary key.

HTH
Dale

If I understand you correctly, you have a form (lets call it frmMain),which
has a RecordSource which we will call tblMain.  From your description, it

[quoted text clipped - 42 lines]

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200905/1

Dale,

Thanks for the reply.  I apologize for not being clear.  I have a Data
Table which feeds the optimizer.  The optimizer populates a Solution
table.  The Form only has an embedded source query which links up the
Data Table with the Solution table to give the user something he can
understand.  So the Form is just the query that I open as a Pivot
Table.  I've done a fair amount of modeling with Excel/VBA, but I'm
still trying to get the hang of Access.  It does some quirky stuff.
The optimizer is a Dll that takes control of the Solution table to
fill it.  But I think it's released after execution.  I assumed that
DoCmd.OpenForm would execute the requery automatically.  I suppose it
does, but only on the second try.  Hmm, now why is that...?

I'll give your suggestions a shot.

SteveM

--
HTH

Dale Fye

Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200905/1

Dale,

Thanks again. Let me iterate further if I can. I isolated the
Solution table after the optimization without opening the Pivot Table
Form that executes the query. The Solution table does NOT reflect the
changes made by the optimizer. It's like they were made but not
posted. If I open the Solution table and then Refresh it from the
Access user interface, then manually open the Pivot Table Form, the
form information is correct the first time. So the embedded query
works, but it's using the old Solution data. So I need to code up a
Refresh Solution table statement and insert that prior to my OpenForm
statement. I've looked through the VB help and how to do that is not
clear. I'm sure it's trivial though. Any help with that statement?

BTW, the database is in 2003 format because the optimizer does not
read in 2007 tables.

Steve