• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

Can't get subform to refresh without pressing F9

I have an unbound recordset that will only refresh when I click F9. I have tried requery, refresh, etc. but nothing works.

Private Sub cmdEditCompany_Click()


If Me.cboCompany = "" Then
    MsgBox "Please select a company.", vbInformation, "Company"
    Me.cboCompany.SetFocus
    Exit Sub
End If


strSQL = "DELETE tblActiveCorpSub.* " _
    & "     FROM tblActiveCorpSub;"
mConn.Execute strSQL

strSQL = "INSERT INTO tblActiveCorpSub ( numCompanyID, strPlanExt, strPlanCD, strGroupExt, strXRef )" _
    & "        SELECT tblActiveCorp.numCompanyID, tblActiveCorp.strPlanExt, " _
    & "               tblActiveCorp.strPlanCD, tblActiveCorp.strGroupExt, tblActiveCorp.strXRef " _
    & "          FROM tblActiveCorp " _
    & "         WHERE tblActiveCorp.numCompanyID=" & numCompany & ";"
mConn.Execute strSQL

DoCmd.OpenForm "frmActiveEdit"
Forms!frmActiveEdit!frmActiveSubEdit.Form.Requery
Forms!frmActiveEdit.txtCompanyID = numCompany

End Sub
0
smm6809
Asked:
smm6809
  • 13
  • 8
  • 2
  • +1
1 Solution
 
Dale FyeCommented:
You indicate "unbound recordset", do you mean "unbound form"?  If the form is not bound to a recordset then it cannot be refreshed.

Is your subform bound to the parent form via a master/child relationship?
0
 
Rey Obrero (Capricorn1)Commented:
try placing this codes

Forms!frmActiveEdit!frmActiveSubEdit.Form.Requery
 


in the load event or current event of form "frmActiveEdit"
0
 
smm6809Author Commented:
Subform has a recordsource but main form does not. Tried in both places capricorn but no go. If i click on the subform and then click F9 it works great.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
Dale FyeCommented:
where is the button that launches this code, on the main form, or the subform?

What is the RecordSource for the subform?
0
 
Rey Obrero (Capricorn1)Commented:
what is the RecordSource of the subform "frmActiveSubEdit", table or query?

if table, try replacing it with a query like this

select * from NameOftable

then see if it make a difference.
0
 
smm6809Author Commented:
the button that launches it is on another form
0
 
smm6809Author Commented:
table... I will try it as query
0
 
smm6809Author Commented:
same thing with query
0
 
Rey Obrero (Capricorn1)Commented:
how about


Forms!frmActiveEdit!frmActiveSubEdit.setfocus    '<< ADD this line
Forms!frmActiveEdit!frmActiveSubEdit.Form.Requery
0
 
smm6809Author Commented:
Tried it before. No go
0
 
Rey Obrero (Capricorn1)Commented:
oh, well, need to see the db..
0
 
smm6809Author Commented:
We chatted about something similar before about how I can't send it to you. Give me a few to create dummy records.
0
 
smm6809Author Commented:
Ok, so the code will look for the db on the C drive. You need to open frmActive, then click Edit\Delete Company. Select a company and then click edit. on the next form if you click on the subform and hit F9 it will refresh. Code is not in the greatest shape yet. You have been warned.
db1.mdb
0
 
Rey Obrero (Capricorn1)Commented:
if I select D, what do i suppose to see before hitting F9 and after hitting F9
0
 
Nick67Commented:
From here
http://www.ddmcomputing.com/access/keys/ac_fkeys.htm

F9 recalculates controls on a Form.
If I recall, you have a combobox mucking with the subform.
F9 may very well be firing the afterupdate event of that control.
Put a Break point in the afterupdate events on the form, and then F9.
See if the code stops in any or all of them.

Then code accordingly.
0
 
smm6809Author Commented:
Capricorn, you should see some items with BUL in them
another example if you select A there is only 1 record and it will have ALG in it.
0
 
smm6809Author Commented:
Actually,I don't have a combobox on the form.
0
 
smm6809Author Commented:
Any luck on this Capricorn?
0
 
Rey Obrero (Capricorn1)Commented:
i suggest that you do the following first
1. add this two lines on top of all your modules

   Option Compare Database
   Option Explicit

2. Do a Debug > Compile
    make sure that the VBA project compiles without error

* you are using a lot of Public variables in your codes.
  - public variables loses their values if unhandled errors occurs.

* why is the need to connect to "c:\db1.mdb"
   



*Note:
<another example if you select A there is only 1 record and it will have ALG in it.>

i see it, but i have change  "mconn" with currentproject.connection and place variables in unbound textbox in the load event of from "frmActive"



0
 
smm6809Author Commented:
Did steps 1 and 2. Will look at the need for all public variables. I connected to the database that way because I am used to using vb. not sure what you mean by place variables in unbound textbox.
As far as the whole F9 issue. what is going on there?

Thanks!
0
 
Rey Obrero (Capricorn1)Commented:
<not sure what you mean by place variables in unbound textbox.>
this is just to make sure that i don't have variables with Null or "" values when running test.
something like this, create a textbox txtWorksheet and in the load event of form

me.txtWorkSheet="ActCorpCkReq"

i used the value of the textbox as value of your global variable "strWksName" in testing the codes.


I connected to the database that way because I am used to using vb.
As far as the whole F9 issue. what is going on there?


i could only think of timing issue..

when i did the test as i have stated above using currentproject.connection i don't even hit F9 to requery the subform


0
 
smm6809Author Commented:
Ok thanks. I have never used currentproject.connection. Do I replace every reference to mConn with that? And then if I understand correctly, I don't have to point to the database location as in C:\db1.mdb?
0
 
Rey Obrero (Capricorn1)Commented:
using currentproject.connection, you will be referring to the db where you are running the codes from,
yes, you don't need to point to c:\db1.mdb
0
 
smm6809Author Commented:
so do I only reference that once? or do I replace all mconn references individually. Could you give me an example?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 13
  • 8
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now