Requery does not refresh values

T

Terry Holland

Its been a while since I did any work on Access so bear with me.

I have a continuous form (frmPSUA_List) that has the following query as its
recordsource
=======================
SELECT drawingdata.drawingname, [Product name] & " - " & [title] AS Product,
drawingdata.[Universal code] AS [Stock Code], drawingdata.[Drawn by],
drawingdata.Date, tblProductSafeUseAnalysis.psua_lng_id
FROM drawingdata LEFT JOIN tblProductSafeUseAnalysis ON
drawingdata.drawingname = tblProductSafeUseAnalysis.psua_drda_DrawingNumber
WHERE (((drawingdata.drawingname) Like "d*" Or (drawingdata.drawingname)
Like "p*") AND
((drawingdata.Date)>[Forms]![frmProductSafeUseAnalysis]![txtDateFilter]))
ORDER BY drawingdata.Date
WITH OWNERACCESS OPTION;
=======================

On my form I have a button that opens an instance of another form (frmPSUA)
that is used to populate a record in the table tblProductSafeUseAnalysis.
When the user clicks OK on this form they return to frmPSUA_List. I want to
have this screen refreshed so that the textbox that displays the value for
tblProductSafeUseAnalysis.psua_lng_id does in fact show the id for the newly
created record AND the focus remains on this record.
I thought the refreshing would have been a simple Me.Requery but this seems
to have no effect. If I manually type Shift+F9, that value appears. With
this in mind I thought I'd try a SendKeys ("+{f9}") and this refreshes the
record but focus moves to first record.
I then thought that if I recorded the bookmark of the current record before
doing my processing and then set bookmark back to this after, that would
solve my problem but with the following code Im not having any success

=============================
Private Sub cmdPSUA_Click()
Dim frm As New Form_frmPSUA
Dim varBookmark As Variant
varBookmark = Me.Bookmark

With frm
.DrawingNumber = Me.txtdrawingname
.StockCode = Nz(Me.txtStockCode)
.Modal = True
.Visible = True

While .Visible
DoEvents
Wend

If Not .Cancelled Then
Me.txtStockCode = .StockCode
SendKeys ("+{F9}")
Me.Bookmark = varBookmark
End If
End With
End Sub
=============================

The code above does refresh my textbox but focus moves to top record. If I
include a True parameter on my SendKeys call, the focus does move to the
correct record, but that textbox is not refreshed.

Ive tried a similar thing using AbsolutePosition and GoToRecord but this has
not work either.

Any suggestions?


Terry Holland
 
D

Daniel Doyle

Is StockCode unique? You could find the record after it has been added.

Me.StockCode.SetFocus
Docmd.FindRecord .StockCode

Dan.
 
T

Terry Holland

This only works if I put the set the Wait - parameter of SendKeys ("+{F9}")
equal to true. BUT as I mentioned, if I do this the textbax that contains
the value that I want to see refreshed is not refreshed. I gues I could put
a delay in but to be honest I think this is too crude - I have another
legacy application where I have had to resort to this and it can take
anything up to 5 secs for the refreshed value to come through. There must
be another way.



Daniel Doyle said:
Is StockCode unique? You could find the record after it has been added.

Me.StockCode.SetFocus
Docmd.FindRecord .StockCode

Dan.

Terry Holland said:
Its been a while since I did any work on Access so bear with me.

I have a continuous form (frmPSUA_List) that has the following query as its
recordsource
=======================
SELECT drawingdata.drawingname, [Product name] & " - " & [title] AS Product,
drawingdata.[Universal code] AS [Stock Code], drawingdata.[Drawn by],
drawingdata.Date, tblProductSafeUseAnalysis.psua_lng_id
FROM drawingdata LEFT JOIN tblProductSafeUseAnalysis ON
drawingdata.drawingname = tblProductSafeUseAnalysis.psua_drda_DrawingNumber
WHERE (((drawingdata.drawingname) Like "d*" Or (drawingdata.drawingname)
Like "p*") AND
((drawingdata.Date)>[Forms]![frmProductSafeUseAnalysis]![txtDateFilter]))
ORDER BY drawingdata.Date
WITH OWNERACCESS OPTION;
=======================

On my form I have a button that opens an instance of another form (frmPSUA)
that is used to populate a record in the table tblProductSafeUseAnalysis.
When the user clicks OK on this form they return to frmPSUA_List. I want to
have this screen refreshed so that the textbox that displays the value for
tblProductSafeUseAnalysis.psua_lng_id does in fact show the id for the newly
created record AND the focus remains on this record.
I thought the refreshing would have been a simple Me.Requery but this seems
to have no effect. If I manually type Shift+F9, that value appears. With
this in mind I thought I'd try a SendKeys ("+{f9}") and this refreshes the
record but focus moves to first record.
I then thought that if I recorded the bookmark of the current record before
doing my processing and then set bookmark back to this after, that would
solve my problem but with the following code Im not having any success

=============================
Private Sub cmdPSUA_Click()
Dim frm As New Form_frmPSUA
Dim varBookmark As Variant
varBookmark = Me.Bookmark

With frm
.DrawingNumber = Me.txtdrawingname
.StockCode = Nz(Me.txtStockCode)
.Modal = True
.Visible = True

While .Visible
DoEvents
Wend

If Not .Cancelled Then
Me.txtStockCode = .StockCode
SendKeys ("+{F9}")
Me.Bookmark = varBookmark
End If
End With
End Sub
=============================

The code above does refresh my textbox but focus moves to top record.
Click to expand...
If
I
include a True parameter on my SendKeys call, the focus does move to the
correct record, but that textbox is not refreshed.

Ive tried a similar thing using AbsolutePosition and GoToRecord but this has
not work either.

Any suggestions?


Terry Holland
Click to expand...
Click to expand...
 
S

Sophie Guo [MSFT]

Hello,

I have tested the issue on my side and the following steps works fine on
Access 2003:

1. Open Northwind database.
2. Copy the Orders table as myOrders.
3. Create a new form testFrm which is bound to the myOrders table.
4. Create a new command button on the new testFrm.
5. Add the following code for the On Click Event of the button:


Private Sub Command3_Click()

Dim varBookmark As Variant
varBookmark = Me.Bookmark

DoCmd.Requery

Me.Bookmark = varBookmark


End Sub

6. Open the testFrm form in "Form view". Keep the form is open.
7. Open myOrders table to add a new record.
8. Close myOrders table. This step is important.
9. Click the button on the testFrm form.

The form is refreshed (The record count is latest). The focus remains on
the current record. I hope the information is helpful.

Sophie Guo
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Requery does not refresh values 1

Top