CODEWITHSUNDEEP
ms-access
emihir0
emihir0

Reputation: 1250

Subform doesn't requery in Access form

I have an OnOpen event in my main form which creates a new (rather complicated) SQL statement which it then assigns to a Query.

ie.:

CurrentDb.QueryDefs("Confirmed_Operations").SQL = allOpSQL & " UNION " & opSQL & " UNION " & CurrentDb.QueryDefs("qryOrder_Additional_Operations").SQL

This so far works perfectly and does exactly what it is supposed to. On my mainForm I have a subForm whose Recordset is the said Query (Confirmed_Operations). Straight after my line which defines the QueryDef, I have:

Me.subConfirmed_Operations.Form.Requery

Which should display the new data. However, it does not.

It actually displays the previous data. What that means is if I have 3 different sets of data, A, B, C and I try to open A, it loads up empty.

If I try to open up B, it shows the data of A, and then if I open the form (it's a popUp form) for the C data, it shows the data of B.

All other operations in the form work, just this one subForm doesn't.

Any idea on how to fix it?

P.S.: I have several other Requery statements on the buttons in the form and none affect this form as they should.

Upvotes: 0

Views: 105

Answers (1)

emihir0
emihir0

Reputation: 1250

Workaround:

Instead of doing:

CurrentDb.QueryDefs("Confirmed_Operations").SQL = allOpSQL & " UNION " & opSQL & " UNION " & CurrentDb.QueryDefs("qryOrder_Additional_Operations").SQL
Me.subConfirmed_Operations.Form.Requery

do:

CurrentDb.QueryDefs("Confirmed_Operations").SQL = allOpSQL & " UNION " & opSQL & " UNION " & CurrentDb.QueryDefs("qryOrder_Additional_Operations").SQL
Me.subConfirmed_Operations.Form.RecordSource = CurrentDb.QueryDefs("Confirmed_Operations").SQL

Or (if the Query itself is only used in this form and nowhere else):

Me.subConfirmed_Operations.Form.RecordSource = allOpSQL & " UNION " & opSQL & " UNION " & CurrentDb.QueryDefs("qryOrder_Additional_Operations").SQL

I don't like workarounds and this is a peculiar bug, so if someone has an idea on why the simple Requery did not work, please share it.

Upvotes: 1

Related Questions