First record is showing (1 Viewer)

[Joy83]

Hi
I am facing a very strange issue.
I have a form
Inside that form there is a subform
When you click in any record in the subform. Some fields are filled in the main form
I update those fields and save them through a save button

the save is fine
But when I refresh the main form
It is refreshed but I always see the first record of the table
Even though that record doesn’t belong to the record I am updating.

its just a view issue. The first record of the table is always showing after the update.

to refresh I am using form.filter

Did this happened to anyone

 

[arnelgp]

on the ribbon, the Refresh there is equivalent to VBA Requery.
when you requery it will return all the records again and start again in record 1.

what you can do in VBA is save the Record PK, Requery, then goback to the same Record PK.

 

[Joy83]

I tried it
It is brining the old record again
Very strange

Is there any other tricks

 

[jdraw]

No obvious tricks.
Do you have a sample database that shows the issue that you can post/share?

Have you seen the video whose link is in this post.? It may offer some insight.

 

[LarryE]

It's not strange at all. It's the way ACCESS was designed. If you have updated and saved a record then why do a refresh or requery anyway? If you have calculated fields that need updateing then use Recalc. It will not return you to the first record. It just recalcs the fields.

 

[KitaYama]

When you click in any record in the subform. Some fields are filled in the main form
I update those fields and save them through a save button

I'm trying my best to think of a situation where a sub form needs to update several fields of its parent...

 

[GPGeorge]

I'm trying my best to think of a situation where a sub form needs to update several fields of its parent...

The only situation I can think of where a subform could be used to update fields in a main form would be when the two forms are bound to the wrong tables....

In other words, if the subform has a query that joins multiple tables, one of which is the same table as the one bound to the main form, this could be the result. Not a good design, but certainly something Access would allow.

In general, you use main form and sub form designs to handle situations where you need to manage tables on the one-side of a relationship -- i.e. the "parent" table -- and tables on the many-side of that relationship -- i.e. the "child" tables. One or more many-side tables could each have a foreign key in them relating them to the one-side table via its primary key.

The main form is bound to a single table, or a query based on that table. Each subform is bound to a single table, or a query based on that table. Anything else is an experiment in seeing how far one can push beyond the boundaries of common sense design. Possible, but risky at the very least.

 

[CJ_London]

You could have a split form design where user edits the list subform form and needs to reflect the change in the record in the main form

not sure what effects record locking may have

 

[GPGeorge]

You could have a split form design where user edits the list subform form and needs to reflect the change in the record in the main form

not sure what effects record locking may have

Split forms are bound to a single recordsource. The "split" means there are two sections to the form. One is a single view of the current record. The other is a datasheet view of the form's recordsource.

However, editing a record in either the datasheet or the form occurs for the same record, i.e. the current record.

If you embed a subform in a split form, it appears as a normal subform in the single view section and as a Subdatasheet in the datasheet section. However, the normal rules of main form and sub form designs apply there, so it seems highly unlikely you'd have a subform bound to the same table as the main form and we're back to the same issue.

 

[CJ_London]

I meant a homegrown one - the way it used to be done before the access version was released

 

[GPGeorge]

I meant a homegrown one - the way it used to be done before the access version was released

Ah, gotcha.

 

[Gasman]

I believe if you requery the recordource and not the form, then the record pointer stays where it is?
Perhaps that would be an option?

 

[Joy83]

I tried all options!

I ended up excluding the first record by hardcoding it.

 

[Gasman]

I tried all options!

I ended up excluding the first record by hardcoding it.

Surely, however you hide the first record, then the second record is going to be displayed?

 

[Pat Hartman]

If you in fact are using a main form and a subform bound to the same table, you are headed for a disaster. That is one of the cases that can disrupt the seed of the autonumber on the main form table under certain conditions.

This is a really bad design. Please confirm that this is what you are doing.

I also don't understand why you are refreshing the main form. You must not have the master/child links set. Keep in mind that Refresh and Requery do different things. Refresh will NOT show new records but Requery will. I know that requery will reposition the recordset to the first record so you will need code to get it back to the record you were working on. Refresh may also reposition the recordset.