Subform won't requery ONLY for the FIRST record added/deleted/edit

[Holly]

The following problem occurs only the first time this form is used after
opening the database. I have a form with a subform that lists records that
can be selected to edit or delete or added. After the record is selected, the
user presses a command button that takes them to another form that displays
the entire record. After the user is finished editing, they click on a
command button to close this form and return to the previous form. The
previous forms subform does not show the updates. Also. if the record is
deleted, it still will be on the subform. And if a new record is added, it
does not appear. This only happens once, after that, the subform work fine. I
have tried to requery the subform on the Activate event and when the editing
form closes. Neither one worked. I also have sort buttons on the main form
with the subform, that when pressed, requeries the subform and displays the
changes caused by the first add/edit/delete. Any ideas?

 

[strive4peace]

Hi Holly,

use the UnLoad event of the editing form

forms!subform_controlname.form.requery
doEvents

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

 

[Holly]

This did not work. It's only when you first open the database, and add the
first record. The subform refuses to requery!

 

[strive4peace]

Hi Holly,

Is the record going IN to the table?

What is the exact error message?

Do you have comboboxes or listboxes with criteria to form
controls? Do any of ther RecordSources for your forms
reference values in controls?

'~~~~~~~

Before you open the editing form, save records if they have
changed. Here are some different versions of a statement to
save a record:

if me.dirty then me.dirty = false

if me.subform_controlname.form.dirty then _
me.subform_controlname.form.dirty = false

if forms!subform_controlname.form.dirty then _
forms!subform_controlname.form.dirty = false

'~~~~~~~

also, if you are requerying from behind another form, you
may need
DoEvents
to make the changes show up right away
'~~~~~~~

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

 

[Holly]

The record is definitely going into the table (I checked with a stop in the
Unload event), I am already executing all the code you recommended in the
Unload event, which included a Do Events statement. I am not getting any
error message, it's just that the subform on the previous form won't requery
for the first change to the table, either add, delete, or edit. As soon as I
save the second record, everything works fine. The previous form, which has
the subform, only has the subform and some command buttons, which take you to
the editing/adding/deleting form.

 

[strive4peace]

Hi Holly,

Create a CLOSE button on the popup form and try this for the
Click event:

'~~~~~~~~~~~~~~~~~~~
if me.dirty then me.dirty = false
currentdb.tabledefs.refresh
If CurrentProject.AllForms(strFormName).IsLoaded Then
If Forms(strFormName).CurrentView <> 0 Then
forms!strFormName!subform_controlname.form.requery
'you can take this one step further
'and assign a value if you wish
if not me.newrecord then
if not isnull(me.controlname) then
forms!strFormName!subform_controlname.form _
= me.controlname
end if
End If
End if
End If
doEvents
docmd.close acform, me.name
'~~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

 

[Holly]

Hi Crystal,

I just changed the subform's Recordset Type property to Dynaset, and this
solved the problem. Thanks for all your help.

Holly

 

[strive4peace]

you're welcome, Holly glad you got it!

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

 

[byman2030]

Hi Crystal,

I'm having a similar problem as Holly.

A subform won't show the new record after I call the requery method. In my
case, however, the recordset type is already set to dynaset.

The subform will show new records if I manually do a new sort. (By the way,
if there is a way to do a sort from code that might solve the problem).

I am using Access2000.

Anyway, I got lost in the code sample and thot you might help clarify. I am
new to Access2000 but sort of familiar with VB6.

I have a main form called StartForm, and the subform is called
ReminderNotesForm (which is based on a table called ReminderNotes).

Here are some questions next to your lines of code.

if me.dirty then me.dirty = false [is "me" my main form or the subform]
currentdb.tabledefs.refresh [requires dao library reference?

If so OK I already have done that in this project]

If CurrentProject.AllForms(strFormName).IsLoaded Then [strFormname = main form or subfrom?]
If Forms(strFormName).CurrentView <> 0 Then
forms!strFormName!subform_controlname.form.requery [is

subform_controlname same as subform's name?]

Thanks,

byman2030

 

[strive4peace]

Hi byman2030,

"...if there is a way to do a sort from code ..."

to sort, put this in the code behind your (sub) form:

'~~~~~~~~~~~~~~~~~~~~~~~~
Private Function SortMe()
if me.dirty then me.dirty = false
me.OrderBy = "[fieldname1], ]fieldname2"
me.orderByOn = true
'this will set the recordset to the first record
'and show records in order by the OrderBy clause
me.requery
end function

you can call it on:

form AfterUpdate
form AfterDeleteConfirm
form Load or Open

~~~~~~~~~~~~~~~~

Me is the form in whose code you are behind ...

if you are in a subform, "Me" is the subform and "Me.parent"
is the main form

If you are in the main form, "Me" is the mainform and
"me.subform_controlname.form" is the subform

~~~~~~~~~~~~~~~~

...."currentdb.tabledefs.refresh
[requires dao library reference?..."

I am not sure, though I do not think so... I believe that a
reference to the DAO is only required when you loop through
the collections.


~~~~~~~~~~~~~~~~

"The subform will show new records if I manually do a new sort."

perhaps what you need is

DoEvents

after your Requery

~~~~~~~~

-- DoEvents --

DoEvents is used to make VBA pay attention to what is
currently happening and look to see if the OS (Operating
System) has any requests.

ie: if you have a loop and want to be able to BREAK it with
CTRL-BREAK, put DoEvents into the loop

DoEvents will also update values written to a form by a
general procedure or code behind another form or report

A DoEvents is done when you use MsgBox, or are stepping
through code (since it has to pay attention to the keyboard)

It is a good way to say, "Wake Up!"

~~~~~~~~~~~~~~~~~~~

"is subform_controlname same as subform's name?"

Not exactly. The name that show up for a form in the
database window is what is epcified in the ControlSource for
the subform*... you will have to use the Name property of
the subform in references to it (if the name is ambiguous
such as "Child65", you can, and should, change it to
something meaningful such as the value in the corresponding
ControlSource (ReminderNotesForm).

* me.subform_controlname

refers to the container that the subform is in -- what its
SourceObject is; its Height, Width, Left, and Right
properties; its LinkMasterFields and LinkChildFields; ...

* me.subform_controlname.form

refers to the subform itself -- its Name, Recordset,
Controlname (default collection), etc.

When you design forms, you KNOW if it are going to be a
subform -- (if you are using LinkMasterFields and
LinkChildFields in a subform control to hold it, your link
field controlnames will most likely be Visible=false) and
you can name that accordingly. For instance (and this is my
own notation)

ReminderNotesForm --> fsub_ReminderNotes

Another thing I do to accent the difference between main
forms and subforms is that I capitalize all the letters in a
main form (this makes them easier to spot from the database
window).

ie:
PEOPLE

~~~~~~~~~~~~~~~~

"...I am new to Access2000 but sort of familiar with VB6..."

eMail me and request my VBA chapters... I am writing a book
on programming with VBA ...only have the first few chapters
done, but will be happy to send them to you (and anyone else
who requests).

Warm Regards,
Crystal
Microsoft Access MVP 2006

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

Hi Crystal,

I'm having a similar problem as Holly.

A subform won't show the new record after I call the requery method. In my
case, however, the recordset type is already set to dynaset.

The subform will show new records if I manually do a new sort. (By the way,
if there is a way to do a sort from code that might solve the problem).

I am using Access2000.

Anyway, I got lost in the code sample and thot you might help clarify. I am
new to Access2000 but sort of familiar with VB6.

I have a main form called StartForm, and the subform is called
ReminderNotesForm (which is based on a table called ReminderNotes).

Here are some questions next to your lines of code.

if me.dirty then me.dirty = false [is "me" my main form or the
subform]

currentdb.tabledefs.refresh [requires dao library reference?

If so OK I already have done that in this project]

If CurrentProject.AllForms(strFormName).IsLoaded Then [strFormname =

main form or subfrom?]

If Forms(strFormName).CurrentView <> 0 Then
forms!strFormName!subform_controlname.form.requery [is

subform_controlname same as subform's name?]

Thanks,

byman2030

 

[byman2030]

Crystal, many thanks for those excellent points!

There was pleanty of new stuff there for me.

byman2030

Hi byman2030,

"...if there is a way to do a sort from code ..."

to sort, put this in the code behind your (sub) form:

'~~~~~~~~~~~~~~~~~~~~~~~~
Private Function SortMe()
if me.dirty then me.dirty = false
me.OrderBy = "[fieldname1], ]fieldname2"
me.orderByOn = true
'this will set the recordset to the first record
'and show records in order by the OrderBy clause
me.requery
end function

you can call it on:

form AfterUpdate
form AfterDeleteConfirm
form Load or Open

~~~~~~~~~~~~~~~~

Me is the form in whose code you are behind ...

if you are in a subform, "Me" is the subform and "Me.parent"
is the main form

If you are in the main form, "Me" is the mainform and
"me.subform_controlname.form" is the subform

~~~~~~~~~~~~~~~~

..."currentdb.tabledefs.refresh
[requires dao library reference?..."

I am not sure, though I do not think so... I believe that a
reference to the DAO is only required when you loop through
the collections.


~~~~~~~~~~~~~~~~

"The subform will show new records if I manually do a new sort."

perhaps what you need is

DoEvents

after your Requery

~~~~~~~~

-- DoEvents --

DoEvents is used to make VBA pay attention to what is
currently happening and look to see if the OS (Operating
System) has any requests.

ie: if you have a loop and want to be able to BREAK it with
CTRL-BREAK, put DoEvents into the loop

DoEvents will also update values written to a form by a
general procedure or code behind another form or report

A DoEvents is done when you use MsgBox, or are stepping
through code (since it has to pay attention to the keyboard)

It is a good way to say, "Wake Up!"

~~~~~~~~~~~~~~~~~~~

"is subform_controlname same as subform's name?"

Not exactly. The name that show up for a form in the
database window is what is epcified in the ControlSource for
the subform*... you will have to use the Name property of
the subform in references to it (if the name is ambiguous
such as "Child65", you can, and should, change it to
something meaningful such as the value in the corresponding
ControlSource (ReminderNotesForm).

* me.subform_controlname

refers to the container that the subform is in -- what its
SourceObject is; its Height, Width, Left, and Right
properties; its LinkMasterFields and LinkChildFields; ...

* me.subform_controlname.form

refers to the subform itself -- its Name, Recordset,
Controlname (default collection), etc.

When you design forms, you KNOW if it are going to be a
subform -- (if you are using LinkMasterFields and
LinkChildFields in a subform control to hold it, your link
field controlnames will most likely be Visible=false) and
you can name that accordingly. For instance (and this is my
own notation)

ReminderNotesForm --> fsub_ReminderNotes

Another thing I do to accent the difference between main
forms and subforms is that I capitalize all the letters in a
main form (this makes them easier to spot from the database
window).

ie:
PEOPLE

~~~~~~~~~~~~~~~~

"...I am new to Access2000 but sort of familiar with VB6..."

eMail me and request my VBA chapters... I am writing a book
on programming with VBA ...only have the first few chapters
done, but will be happy to send them to you (and anyone else
who requests).

Warm Regards,
Crystal
Microsoft Access MVP 2006

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

Hi Crystal,

I'm having a similar problem as Holly.

A subform won't show the new record after I call the requery method. In my
case, however, the recordset type is already set to dynaset.

The subform will show new records if I manually do a new sort. (By the way,
if there is a way to do a sort from code that might solve the problem).

I am using Access2000.

Anyway, I got lost in the code sample and thot you might help clarify. I am
new to Access2000 but sort of familiar with VB6.

I have a main form called StartForm, and the subform is called
ReminderNotesForm (which is based on a table called ReminderNotes).

Here are some questions next to your lines of code.

if me.dirty then me.dirty = false [is "me" my main form or the
subform]

currentdb.tabledefs.refresh [requires dao library

reference?

If so OK I already have done that in this project]

If CurrentProject.AllForms(strFormName).IsLoaded Then [strFormname

=

main form or subfrom?]

If Forms(strFormName).CurrentView <> 0 Then
forms!strFormName!subform_controlname.form.requery [is

subform_controlname same as subform's name?]

Thanks,

byman2030

 

[strive4peace]

Hi Byman,

You're welcome happy to help

I did make a mistake which i wish to correct... here is the
correction (and then some ) :

Q: "is subform_controlname same as subform's name?"

Not exactly. The name that show up for a form
in the database window is the the Name of the form and is
what is specified as the SourceObject for the subform
control ... you will have to use the Name* property of the
subform control in references to the control.

You are confusing the name of the form with the name of the
subform control.

This can be a little misleading when you are looking at the
design view of a form -- for what you see displayed in a
subform control is either the actual object or the
SourceObject property, for a textbox you see the
ControlSource, etc.

The Name of the control (controlname) can be looked up and
set by turning on the Properties window (View, Properties
from the menu) and looking at the first property on the ALL
and/or OTHER tabs when the control you want to view is
selected.

The Title bar of the Properties window displays the Type and
Name of what is selected.

example Titlebars for Properties:

Text Box: Firstname

Combo Box: LookupRecord



Warm Regards,
Crystal
Microsoft Access MVP 2006

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

Crystal, many thanks for those excellent points!

There was pleanty of new stuff there for me.

byman2030

Hi byman2030,

"...if there is a way to do a sort from code ..."

to sort, put this in the code behind your (sub) form:

'~~~~~~~~~~~~~~~~~~~~~~~~
Private Function SortMe()
if me.dirty then me.dirty = false
me.OrderBy = "[fieldname1], ]fieldname2"
me.orderByOn = true
'this will set the recordset to the first record
'and show records in order by the OrderBy clause
me.requery
end function

you can call it on:

form AfterUpdate
form AfterDeleteConfirm
form Load or Open

~~~~~~~~~~~~~~~~

Me is the form in whose code you are behind ...

if you are in a subform, "Me" is the subform and "Me.parent"
is the main form

If you are in the main form, "Me" is the mainform and
"me.subform_controlname.form" is the subform

~~~~~~~~~~~~~~~~

..."currentdb.tabledefs.refresh
[requires dao library reference?..."

I am not sure, though I do not think so... I believe that a
reference to the DAO is only required when you loop through
the collections.


~~~~~~~~~~~~~~~~

"The subform will show new records if I manually do a new sort."

perhaps what you need is

DoEvents

after your Requery

~~~~~~~~

-- DoEvents --

DoEvents is used to make VBA pay attention to what is
currently happening and look to see if the OS (Operating
System) has any requests.

ie: if you have a loop and want to be able to BREAK it with
CTRL-BREAK, put DoEvents into the loop

DoEvents will also update values written to a form by a
general procedure or code behind another form or report

A DoEvents is done when you use MsgBox, or are stepping
through code (since it has to pay attention to the keyboard)

It is a good way to say, "Wake Up!"

~~~~~~~~~~~~~~~~~~~

"is subform_controlname same as subform's name?"

Not exactly. The name that show up for a form in the
database window is what is epcified in the ControlSource for
the subform*... you will have to use the Name property of
the subform in references to it (if the name is ambiguous
such as "Child65", you can, and should, change it to
something meaningful such as the value in the corresponding
ControlSource (ReminderNotesForm).

* me.subform_controlname

refers to the container that the subform is in -- what its
SourceObject is; its Height, Width, Left, and Right
properties; its LinkMasterFields and LinkChildFields; ...

* me.subform_controlname.form

refers to the subform itself -- its Name, Recordset,
Controlname (default collection), etc.

When you design forms, you KNOW if it are going to be a
subform -- (if you are using LinkMasterFields and
LinkChildFields in a subform control to hold it, your link
field controlnames will most likely be Visible=false) and
you can name that accordingly. For instance (and this is my
own notation)

ReminderNotesForm --> fsub_ReminderNotes

Another thing I do to accent the difference between main
forms and subforms is that I capitalize all the letters in a
main form (this makes them easier to spot from the database
window).

ie:
PEOPLE

~~~~~~~~~~~~~~~~

"...I am new to Access2000 but sort of familiar with VB6..."

eMail me and request my VBA chapters... I am writing a book
on programming with VBA ...only have the first few chapters
done, but will be happy to send them to you (and anyone else
who requests).

Warm Regards,
Crystal
Microsoft Access MVP 2006

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

Hi Crystal,

I'm having a similar problem as Holly.

A subform won't show the new record after I call the requery method. In

my

case, however, the recordset type is already set to dynaset.

The subform will show new records if I manually do a new sort. (By the

way,

if there is a way to do a sort from code that might solve the problem).

I am using Access2000.

Anyway, I got lost in the code sample and thot you might help clarify. I

am

new to Access2000 but sort of familiar with VB6.

I have a main form called StartForm, and the subform is called
ReminderNotesForm (which is based on a table called ReminderNotes).

Here are some questions next to your lines of code.



if me.dirty then me.dirty = false [is "me" my main form or the

subform]


currentdb.tabledefs.refresh [requires dao library

reference?

If so OK I already have done that in this project]


If CurrentProject.AllForms(strFormName).IsLoaded Then [strFormname

=

main form or subfrom?]


If Forms(strFormName).CurrentView <> 0 Then
forms!strFormName!subform_controlname.form.requery [is

subform_controlname same as subform's name?]

Thanks,

byman2030