Requery Form adds new record - why?

[Pecanfan]

I've got a form that I need to Requery after a certain control is changed.
The control (which is an Option Group) simply has this code behind it:-

Private Sub frmJobType_AfterUpdate()
Requery
End Sub

This works fine if I open the form in normal mode (to edit an existing
record) however if I open the form in Add mode (using DoCmd.OpenForm
FRM_Jobs, , , , acFormAdd) the Requery seems to make the form add another
new record, instead of just refreshing the current one.

I don't particularly want to Requery the whole form but I have a DCount
control that refers to an external query and I can't find any other way of
getting the value from the DCount to update properly other than Requery-ing
the whole form. The DCount control contains:-

=DCount("[ItemId]","QRY_MyQuery","[SomethingId] = " & [txtSomethingId])

Alternatively, is there any way I could replace the 'QRY_MyQuery' of the
above DCount statement with:-

SELECT TBL_Table1.ItemId, TBL_Table1.Type, TBL_Table2.SomethingId
FROM TBL_Table1 LEFT JOIN TBL_Table2 ON TBL_Table1.Id = TBL_Table2.Id
WHERE (((TBL_Table1.Type)=2));

Any ideas?

TIA!

Andy

 

[Guest]

If you just want to update the value in the control where you display the
count after an option is selected, take the Requery out of and put your
DCount in the After Update event of the option group:

Private Sub frmJobType_AfterUpdate()
Me.txtSomeControl = DCount("[ItemId]","QRY_MyQuery","[SomethingId] = " &
[txtSomethingId])
End Sub

No, the DCount cannot use an SQL statement. It needs a table or query name.

 

[Pecanfan]

If you just want to update the value in the control where you display the
count after an option is selected, take the Requery out of and put your
DCount in the After Update event of the option group:

Private Sub frmJobType_AfterUpdate()
Me.txtSomeControl = DCount("[ItemId]","QRY_MyQuery","[SomethingId] = " &
[txtSomethingId])
End Sub

No, the DCount cannot use an SQL statement. It needs a table or query name.
"Pecanfan" wrote:

Doesn't seem to work :-( - looks like the QRY_MyQuery isn't getting a chance
to update before the DCount runs. The Option Group updates values (the Type
field referred to in my previous SQL statement) which the QRY_MyQuery needs
to know about.

Any other ideas?

Andy

 

[Guest]

It isn't that your query isn't updating, it is that the record is still a new
record in the form and will not be in the underlying data until the form is
closed or requeried.

Since I don't use separate forms for adds and edits, I was not aware a
requery would add a new record to the recordset. I am not suprised, because
the form is strictly for adding records; therefore, when the Requery
executes, the record is added to the underlying table and now the record is
no longer a new record so the form moves to a new record. If you used the
same form for both, you would not have the problem.

I know others may disagree, but what is the point of having to maintain two
forms instead of one?

If you just want to update the value in the control where you display the
count after an option is selected, take the Requery out of and put your
DCount in the After Update event of the option group:

Private Sub frmJobType_AfterUpdate()
Me.txtSomeControl = DCount("[ItemId]","QRY_MyQuery","[SomethingId] = " &
[txtSomethingId])
End Sub

No, the DCount cannot use an SQL statement. It needs a table or query name.
"Pecanfan" wrote:

Doesn't seem to work :-( - looks like the QRY_MyQuery isn't getting a chance
to update before the DCount runs. The Option Group updates values (the Type
field referred to in my previous SQL statement) which the QRY_MyQuery needs
to know about.

Any other ideas?

Andy

 

[Pecanfan]

I don't use two forms - just the one - although having 2 forms would fix the
problem. Either in acFormAdd mode (for new records) or in normal mode for
edits, opened using something along the lines of:-

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "FRM_Jobs"
stLinkCriteria = "[TBL_Table1.ItemId]=" & Me![ItemId]
DoCmd.OpenForm stDocName, , , stLinkCriteria

I've even tried forcing a Save before the Requery but it still does the same
thing...

....unless I could open the form for new records without putting it in
acFormAdd mode... will investigate....

Andy

It isn't that your query isn't updating, it is that the record is still a new
record in the form and will not be in the underlying data until the form is
closed or requeried.

Since I don't use separate forms for adds and edits, I was not aware a
requery would add a new record to the recordset. I am not suprised, because
the form is strictly for adding records; therefore, when the Requery
executes, the record is added to the underlying table and now the record is
no longer a new record so the form moves to a new record. If you used the
same form for both, you would not have the problem.

I know others may disagree, but what is the point of having to maintain two
forms instead of one?

If you just want to update the value in the control where you display the
count after an option is selected, take the Requery out of and put your
DCount in the After Update event of the option group:

Private Sub frmJobType_AfterUpdate()
Me.txtSomeControl = DCount("[ItemId]","QRY_MyQuery","[SomethingId]

= "
&

[txtSomethingId])
End Sub

No, the DCount cannot use an SQL statement. It needs a table or query name.
"Pecanfan" wrote:

Doesn't seem to work :-( - looks like the QRY_MyQuery isn't getting a chance
to update before the DCount runs. The Option Group updates values (the Type
field referred to in my previous SQL statement) which the QRY_MyQuery needs
to know about.

Any other ideas?

Andy

 

[Guest]

I usually have a command button on my form that says New. Behind it:

Docmd.GotoRecord acNewRec

Now you get a new record.

I don't use two forms - just the one - although having 2 forms would fix the
problem. Either in acFormAdd mode (for new records) or in normal mode for
edits, opened using something along the lines of:-

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "FRM_Jobs"
stLinkCriteria = "[TBL_Table1.ItemId]=" & Me![ItemId]
DoCmd.OpenForm stDocName, , , stLinkCriteria

I've even tried forcing a Save before the Requery but it still does the same
thing...

....unless I could open the form for new records without putting it in
acFormAdd mode... will investigate....

Andy

It isn't that your query isn't updating, it is that the record is still a new
record in the form and will not be in the underlying data until the form is
closed or requeried.

Since I don't use separate forms for adds and edits, I was not aware a
requery would add a new record to the recordset. I am not suprised, because
the form is strictly for adding records; therefore, when the Requery
executes, the record is added to the underlying table and now the record is
no longer a new record so the form moves to a new record. If you used the
same form for both, you would not have the problem.

I know others may disagree, but what is the point of having to maintain two
forms instead of one?

If you just want to update the value in the control where you display the
count after an option is selected, take the Requery out of and put your
DCount in the After Update event of the option group:

Private Sub frmJobType_AfterUpdate()
Me.txtSomeControl = DCount("[ItemId]","QRY_MyQuery","[SomethingId] = "
&
[txtSomethingId])
End Sub

No, the DCount cannot use an SQL statement. It needs a table or query
name.
:

Doesn't seem to work :-( - looks like the QRY_MyQuery isn't getting a chance
to update before the DCount runs. The Option Group updates values (the Type
field referred to in my previous SQL statement) which the QRY_MyQuery needs
to know about.

Any other ideas?

Andy

 

[Pecanfan]

Looks like I got it sorted - took out the Requery and replaced it with:-

Me.Dirty = False
Me.Recalc

Woohoo!!

Andy

I don't use two forms - just the one - although having 2 forms would fix the
problem. Either in acFormAdd mode (for new records) or in normal mode for
edits, opened using something along the lines of:-

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "FRM_Jobs"
stLinkCriteria = "[TBL_Table1.ItemId]=" & Me![ItemId]
DoCmd.OpenForm stDocName, , , stLinkCriteria

I've even tried forcing a Save before the Requery but it still does the same
thing...

...unless I could open the form for new records without putting it in
acFormAdd mode... will investigate....

Andy

It isn't that your query isn't updating, it is that the record is still

a

new
record in the form and will not be in the underlying data until the form is
closed or requeried.

Since I don't use separate forms for adds and edits, I was not aware a
requery would add a new record to the recordset. I am not suprised, because
the form is strictly for adding records; therefore, when the Requery
executes, the record is added to the underlying table and now the record is
no longer a new record so the form moves to a new record. If you used the
same form for both, you would not have the problem.

I know others may disagree, but what is the point of having to maintain two
forms instead of one?

display

the

DCount("[ItemId]","QRY_MyQuery","[SomethingId]
= "

&
[txtSomethingId])
End Sub

No, the DCount cannot use an SQL statement. It needs a table or query
name.
:

Doesn't seem to work :-( - looks like the QRY_MyQuery isn't getting a chance
to update before the DCount runs. The Option Group updates values

(the

Type