A form using a query as a source doesn't refresh on changes in the table in some cases, and how to workaround this? (1 Viewer)

[vertus]

0. I have 2 tables Stuff and Contracts.

1. I have Query that joins 2 tables.
It creates a table where there is added info about contracts only to those stuff members who have open contracts.
The query is like this:

SELECT Stuff.*,
       contracts.term,
       contracts.date_start,
       contracts.date_off,
       contracts.etc
FROM Stuff
    LEFT JOIN contracts
        ON Stuff.member_id = contracts.member_id
WHERE ((contracts.date_off > Date()) Or IsNull(contracts.date_off))
ORDER BY Stuff.prior_num;

2. I have Form1 (a spreadsheet) with Query as a source. Form1 is open.

3. I open Form2 that runs a VBA script that makes a change to the table Stuff: It takes data about a stuff member and move to another line of the table (copy to another line and then clear from the previous line).

4. I expect Form1 to be updated automatically, but it happens in weird way if a stuff member has an open contract. I see doubled data.
And I have to click the Refresh All button to get Form1 updated.

The question is, how to make Form1 to be updated automatically in this case?

-------------------------------------------------------
If I change the source of Form1 to table Stuff instead of Query, then I can get an autoupdate on the table changes.

I don't get how the queries work in this part. Why joined lines don't auto-update?

 

[CJ_London]

Changes to an underlying record are usually reflected in the form - subject to the query being updateable. Changes to the actual recordset (adding a record) will not

investigate use of the refresh and requery methods

Form.Refresh method (Access)

Office VBA reference topic

learn.microsoft.com

Form.Requery method (Access)

Office VBA reference topic

learn.microsoft.com

 

[The_Doc_Man]

There is also a setting regarding auto-refresh using File >> Options >> Current Database >> Client Settings (near the bottom of that page) where you can choose how often a Refresh occurs automatically - but that won't give you anything new or changed because seeing recordset changes requires a Requery.

 

[tvanstiphout]

> The question is, how to make Form1 to be updated automatically in this case?
At the bottom of the VBA in Form2, write one more line:
Forms!Form1.Requery

 

[vertus]

Thanks a lot for the hints. I decided to add a script that checks all forms, and Requery them. Unfortunately, it means that the selected lines in Spreadsheet Forms are reset also, but let it be a compromise for now.

 

[Gasman]

Requery the recordset and not the form.
Then the record pointer remains on the current record.

 

[vertus]

Requery the recordset and not the form.
Then the record pointer remains on the current record.

Can you show me an example code, please?
ChatGPT proposes me the following but I'm in doubt:

Sub UpdateForms()
    Dim frm As Form
    For Each frm In Forms
        Dim rs As Recordset
        Set rs = frm.RecordsetClone ' Create a copy of the form's recordset
        rs.Requery ' Requery the recordset associated with the form
    Next frm
End Sub

 

[Gasman]

Can you show me an example code, please?

No, but Google can.

access vba recordset requery - Google Search

 

[Gasman]

The more I see of ChatGPT code, the more I think you have to be so exact with your question to get anything worthwhile.

Hell, people find Google difficult to use.

Create yourself a test form in your Db whilst a table or query is selected. Use the multiple items option.

In the header of your form, put two buttons, you can see what I called mine.
Put the code in the sub into the respective button subs.

Go into form view.
Select a record which is not the first record.

Now click each button in turn.

Private Sub cmdForm_Click()
Me.Requery
End Sub

Private Sub cmdRecordset_Click()
Me.Recordset.Requery
End Sub

You have been give excellent advice in your other thread. If you cannot find out how to requery a recordset, you have a huge task on your hands, so good luck with that. You have a mountain ahead of you, and only you can climb it.

 

[Pat Hartman]

Thanks a lot for the hints. I decided to add a script that checks all forms, and Requery them. Unfortunately, it means that the selected lines in Spreadsheet Forms are reset also, but let it be a compromise for now.

Having multiple forms open is always problematic. In your situation, it also looks like you have multiple forms that update data in a single table which is generally poor practice. And it's even worse because you don't even know what windows are open at any one time. I prefer to have much more control over my interface. In the case where I have a form open but updates to the underlyng data are done on a separate form, I open the second form as a dialog. That stops the first forms code from running once the OpenForm is executed. The code in the first form stays stopped until the second form closes. At that point, you can use Me.Refresh to redisplay the updated first form. However, if the second form has the ability to insert or delete records, then you need to use Me.Requery and that will in fact reposition the first form's recordset.