Refresh Unbound Subform 1

[Aidy680]

I have an unbound form which contains an unbound subform, set to datasheet view.

I click a button on the main form and it updates a record in the subform, depending on the position of the cursor.

I want that update to be reflected immediately to the user. Currently it isnt.

I have to freeze the screen, close and re-open the form and then unfreeze the screen, for the update to be viewed. Plus the subform scrolls back to the top.

I've tried various combinations of Requery and Refresh so far without much luck.

There must be a less crude way!!

 

[MajP]

AFAIK, there is no such thing as an unbound datasheet. And if it is unbound then the term requery would have no meaning. So I assume the subform is bound to some recordsource, but maybe there is no parent child relationship between the main form and subform.

Can you provide the code in the update button? And provide a little more detail of the subform's recordsource, the tables queries in question.

If they were both truly unbound forms then you would have to write a tremendous amount of code to do this. The subform would have to be something like a flexgrid. I am assuming that is not exactly what you have.

 

[Aidy680]

My apologies for not being clear.

When I say it isnt bound I mean that I haven't bound it to a table/query in the properties window.

I do however bind it to a stored proc in code using the following syntax:

Set Me.Form.Recordset = rstSourceFile

where the recordset is the proc executed.

This, obviously, prevents it from being updated directly by the users.

The update button parses some parameters into some procs and updates the SQL BE directly. I want these updates to be reflected in the FE dataform and currently they're not.

Hope this makes it a bit clearer.

 

[MajP]

Got it. If the sql is update, can you just get it to refresh by doing retrieving and update recordset and then just setting it again.
Set Me.Form.Recordset = rstSourceFile

you will not be able to use a book mark because you will have new bookmarks. But you should be able to do a findfirst to return to the update value. Is this a very large rs?

 

[Aidy680]

Worked a treat Majp! Thanks for your help.

 

[Aidy680]

Sorry I thought that was the end of it!

I'm using an ADODB recordset and I want to now move to the record that I updated.

ADO recordsets dont support FindFirst and Find only allows one field. I need to specify multiple fields to find my record (some fields have multiple NULL values for example).

I've considered Filter but that will return the record on it's own and all I want to do is return to a specific place in the recordset, so that I can edit the next record in the list, for example.

Any other tips at all?

 

[Aidy680]

Think I spoke too soon!

You can use the Filter method successfully.

I found the link below very useful:

http://support.microsoft.com/kb/q195222/

 

[MajP]

Sorry did not notice that you are using a SQL back end and thus ADO not DAO.

Without seeing your code I am not sure if you can do this. When you say "update" if that means writing to the original RS then you are OK. If you mean create a new recordset then no.

If I have a recordset and clone the recordset I have a shareable set of bookmarks. If reset the recordset using the same sql and pull all the same records, I get a whole new set of bookmarks.

However, I would think you have a single primary key or a composite key. Return the primary key first then find the record using the primary key. If you did not include the primary key in your original rs then include that and it should make thinks easier.

This seems like a bad database design:
" I need to specify multiple fields to find my record (some fields (I think you mean records) have multiple NULL values for example)"

 

[Aidy680]

Yes I have to agree that the lack of unique identifier is far from ideal. It would have made such transactions far easier.

Thanks for your help. Appreciated.