473,890 Members | 2,059 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

ADP bound form - force record save, recordsetclone not populating after requery

Br
First issue:

When using ADPs you no longer have the ability to issue a me.refresh to
save the current record on a form (the me.refresh does a requery in an
ADP).

We usually do this before calling up another form or report that uses
some of the same data.

We came up with a work around that saves the current record's ID, does a
requery, then uses a recordsetclone to search for the ID and then set
the form's bookmark to the recordset clone's. This is the same method we
use for our search functions on our forms (without the requery
obviously).

This doesn't always work because of the second issue below.

How can you save the current record on a bound form in an ADP?

Second issue:

The above searching method seemed to work until we encountered larger
recordsets. It seems that Access doesn't get all the data immediately
after you requery so the recordsetclone only contains a small subset of
records... thus the search routine fails to find the record for higher
ID values. If you trace through the code a line at a time it works fine.
Adding a pause loop in the code doesn't work (and is a bad practice
IMO).

How can you make sure a recordsetclone contains all the records after a
requery?

For now I've had to implement a terrible practice... in code move the
form to the next record and then move back which causes the record to be
saved.

Using A2000, SQL2000
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response
Nov 25 '05 #1
22 5067
"Br@dley" <br**@usenet.or g> wrote in news:d1thf.3241 $ea6.486@news-
server.bigpond. net.au:
Second issue:

The above searching method seemed to work until we encountered larger
recordsets. It seems that Access doesn't get all the data immediately
after you requery so the recordsetclone only contains a small subset of
records... thus the search routine fails to find the record for higher
ID values. If you trace through the code a line at a time it works fine.
Adding a pause loop in the code doesn't work (and is a bad practice
IMO).


This post addresses the problem you describe, I believe, but in different
circumstances.

http://groups.google.ca/group/comp.d...6d7ed38ae217d8

(I tried posting this through google groups but it not seem to go;
apologies if it appears more than once).

--
Lyle Fairfield
Nov 25 '05 #2
Br
Br@dley wrote:
First issue:

When using ADPs you no longer have the ability to issue a me.refresh
to save the current record on a form (the me.refresh does a requery
in an ADP).

We usually do this before calling up another form or report that uses
some of the same data.
And when returning from a form based on some of the same recordset to
refresh the main form to reflect any changes.
We came up with a work around that saves the current record's ID,
does a requery, then uses a recordsetclone to search for the ID and
then set the form's bookmark to the recordset clone's. This is the
same method we use for our search functions on our forms (without the
requery obviously).

This doesn't always work because of the second issue below.

How can you save the current record on a bound form in an ADP?

Second issue:

The above searching method seemed to work until we encountered larger
recordsets. It seems that Access doesn't get all the data immediately
after you requery so the recordsetclone only contains a small subset
of records... thus the search routine fails to find the record for
higher ID values. If you trace through the code a line at a time it
works fine. Adding a pause loop in the code doesn't work (and is a
bad practice IMO).

How can you make sure a recordsetclone contains all the records after
a requery?

For now I've had to implement a terrible practice... in code move the
form to the next record and then move back which causes the record to
be saved.

Using A2000, SQL2000

--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response
Nov 25 '05 #3
This article addresses the problem you describe, but not in the same
circumstances.
http://groups.google.ca/group/comp.d...6d7ed38ae217d8

Nov 25 '05 #4
Br
Lyle Fairfield wrote:
"Br@dley" <br**@usenet.or g> wrote in news:d1thf.3241 $ea6.486@news-
server.bigpond. net.au:
Second issue:

The above searching method seemed to work until we encountered larger
recordsets. It seems that Access doesn't get all the data immediately
after you requery so the recordsetclone only contains a small subset
of records... thus the search routine fails to find the record for
higher ID values. If you trace through the code a line at a time it
works fine. Adding a pause loop in the code doesn't work (and is a
bad practice IMO).


This post addresses the problem you describe, I believe, but in
different circumstances.

http://groups.google.ca/group/comp.d...6d7ed38ae217d8

(I tried posting this through google groups but it not seem to go;
apologies if it appears more than once).


Thanks, I'll give that a try.
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response
Nov 25 '05 #5
Br
Lyle Fairfield wrote:
"Br@dley" <br**@usenet.or g> wrote in news:d1thf.3241 $ea6.486@news-
server.bigpond. net.au:
Second issue:

The above searching method seemed to work until we encountered larger
recordsets. It seems that Access doesn't get all the data immediately
after you requery so the recordsetclone only contains a small subset
of records... thus the search routine fails to find the record for
higher ID values. If you trace through the code a line at a time it
works fine. Adding a pause loop in the code doesn't work (and is a
bad practice IMO).


This post addresses the problem you describe, I believe, but in
different circumstances.

http://groups.google.ca/group/comp.d...6d7ed38ae217d8

(I tried posting this through google groups but it not seem to go;
apologies if it appears more than once).


He's a generic function I wrote based on your post:

Function RefreshForm(myF orm As Form, myField As String)
' sample call : RefreshForm Me, "EmployeeID "
On Error GoTo RefreshForm_err
Dim rs As ADODB.Recordset
Dim myvalue As long
myValue = myForm.Controls (myField).Value
Set rs = New ADODB.Recordset
rs.CursorLocati on = adUseClient
rs.ActiveConnec tion = CurrentProject. Connection
rs.Open myForm.RecordSo urce
rs.Find myfield & " = " & myValue
If rs.EOF Then rs.MoveFirst
myForm.Recordse t = rs.Source
Set rs = Nothing
RefreshForm_exi t:
Set rs = Nothing
Exit Function
RefreshForm_err :
MsgBox "Could not move to record." & vbCrLf & Err.Description ,
vbCritical + vbOKOnly, "Error in RefreshForm()"
Resume RefreshForm_exi t
End Function

--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response
Nov 25 '05 #6
Br
Br@dley wrote:
Lyle Fairfield wrote:
"Br@dley" <br**@usenet.or g> wrote in news:d1thf.3241 $ea6.486@news-
server.bigpond. net.au:
Second issue:

The above searching method seemed to work until we encountered
larger recordsets. It seems that Access doesn't get all the data
immediately after you requery so the recordsetclone only contains a
small subset of records... thus the search routine fails to find
the record for higher ID values. If you trace through the code a
line at a time it works fine. Adding a pause loop in the code
doesn't work (and is a bad practice IMO).


This post addresses the problem you describe, I believe, but in
different circumstances.

http://groups.google.ca/group/comp.d...6d7ed38ae217d8

(I tried posting this through google groups but it not seem to go;
apologies if it appears more than once).


He's a generic function I wrote based on your post:


<>

Sorry, the correct code is:

Function RefreshForm(myF orm As Form, myField As String)
' sample call : RefreshForm Me, "EmployeeID "
On Error GoTo RefreshForm_err
Dim rs As ADODB.Recordset
Dim myvalue As Long
myvalue = myForm.Controls (myField).Value
Set rs = New ADODB.Recordset
rs.CursorLocati on = adUseClient
rs.ActiveConnec tion = CurrentProject. Connection
rs.Open myForm.RecordSo urce
rs.Find myField & " = " & myvalue
If rs.EOF Then rs.MoveFirst
Set myForm.Recordse t = rs
Set rs = Nothing
RefreshForm_exi t:
Set rs = Nothing
Exit Function
RefreshForm_err :
MsgBox "Could not move to record." & vbCrLf & Err.Description ,
vbCritical + vbOKOnly, "Error in RefreshForm()"
Resume RefreshForm_exi t
End Function

--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response
Nov 25 '05 #7
Br
Br@dley wrote:
<>
He's a generic function I wrote based on your post:


<>

Sorry, the correct code is:

Function RefreshForm(myF orm As Form, myField As String)
' sample call : RefreshForm Me, "EmployeeID "
On Error GoTo RefreshForm_err
Dim rs As ADODB.Recordset
Dim myvalue As Long
myvalue = myForm.Controls (myField).Value
Set rs = New ADODB.Recordset
rs.CursorLocati on = adUseClient
rs.ActiveConnec tion = CurrentProject. Connection
rs.Open myForm.RecordSo urce
rs.Find myField & " = " & myvalue
If rs.EOF Then rs.MoveFirst
Set myForm.Recordse t = rs
Set rs = Nothing
RefreshForm_exi t:
Set rs = Nothing
Exit Function
RefreshForm_err :
MsgBox "Could not move to record." & vbCrLf & Err.Description ,
vbCritical + vbOKOnly, "Error in RefreshForm()"
Resume RefreshForm_exi t
End Function

Well this doesn't actually work yet.

Two issues:

- If user makes changes to the record and runs the code the changes
disappear. However running the code again, the changes reappear???

- the recordset is no longer editable once the code has run (i'm
probably missing something). I tried setting the UniqueTable value but
that didn't work.
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response
Nov 25 '05 #8
"Br@dley" <br**@usenet.or g> wrote in
news:d1******** ********@news-server.bigpond. net.au:
First issue:

When using ADPs you no longer have the ability to issue a me.refresh
to save the current record on a form (the me.refresh does a requery in
an ADP).

We usually do this before calling up another form or report that uses
some of the same data.

We came up with a work around that saves the current record's ID, does
a requery, then uses a recordsetclone to search for the ID and then
set the form's bookmark to the recordset clone's. This is the same
method we use for our search functions on our forms (without the
requery obviously).

This doesn't always work because of the second issue below.

How can you save the current record on a bound form in an ADP?
1. Press <Shift> <Enter>
or
2. From the Form Menu Click Records, Then Save Record
or
3. Run Code (from a button [UGH!]) or User Menu:
With DoCmd
.RunCommand acCmdSelectReco rd
.RunCommand acCmdSaveRecord
End With

I'm not sure I completely understand your second issue, but TTBOMK if
you run the code above the form record pointer will remain on the
correct record; resetting it will be unnecessary.
Second issue:

The above searching method seemed to work until we encountered larger
recordsets. It seems that Access doesn't get all the data immediately
after you requery so the recordsetclone only contains a small subset
of records... thus the search routine fails to find the record for
higher ID values. If you trace through the code a line at a time it
works fine. Adding a pause loop in the code doesn't work (and is a bad
practice IMO).

How can you make sure a recordsetclone contains all the records after
a requery?

For now I've had to implement a terrible practice... in code move the
form to the next record and then move back which causes the record to
be saved.

Using A2000, SQL2000


--
Lyle Fairfield
Nov 25 '05 #9
Br
Lyle Fairfield wrote:
"Br@dley" <br**@usenet.or g> wrote in
news:d1******** ********@news-server.bigpond. net.au:
First issue:

When using ADPs you no longer have the ability to issue a me.refresh
to save the current record on a form (the me.refresh does a requery
in an ADP).

We usually do this before calling up another form or report that uses
some of the same data.

We came up with a work around that saves the current record's ID,
does a requery, then uses a recordsetclone to search for the ID and
then set the form's bookmark to the recordset clone's. This is the
same method we use for our search functions on our forms (without the
requery obviously).

This doesn't always work because of the second issue below.

How can you save the current record on a bound form in an ADP?
1. Press <Shift> <Enter>
Just worked that one out :)

SendKeys "+{ENTER}"
or
2. From the Form Menu Click Records, Then Save Record
or
3. Run Code (from a button [UGH!]) or User Menu:
With DoCmd
.RunCommand acCmdSelectReco rd
.RunCommand acCmdSaveRecord
End With
That sounds better than SendKeys.

Why couldn't MS have made Me.Refresh do that?
I'm not sure I completely understand your second issue, but TTBOMK if
you run the code above the form record pointer will remain on the
correct record; resetting it will be unnecessary.


The remaining issue is if I open a second form I need to requery the
first form when it closes to reflect any changes on the second form.
After the requery I need to move back to what was the current record.
Second issue:

The above searching method seemed to work until we encountered larger
recordsets. It seems that Access doesn't get all the data immediately
after you requery so the recordsetclone only contains a small subset
of records... thus the search routine fails to find the record for
higher ID values. If you trace through the code a line at a time it
works fine. Adding a pause loop in the code doesn't work (and is a
bad practice IMO).

How can you make sure a recordsetclone contains all the records after
a requery?

For now I've had to implement a terrible practice... in code move the
form to the next record and then move back which causes the record to
be saved.

Using A2000, SQL2000


--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response
Nov 25 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

19
4126
by: James Fortune | last post by:
I have a lot of respect for David Fenton and Allen Browne, but I don't understand why people who know how to write code to completely replace a front end do not write something that will automate the code that implements managing unbound controls on forms given the superior performance of unbound controls in a client/server environment. I can easily understand a newbie using bound controls or someone with a tight deadline. I guess I need...
14
10158
by: Abhi | last post by:
FYI: This message is for the benefit of MS Access Community. I found that this prblem has been encounterd by many but there is hardly any place where a complete solution is posted. So I thought I should give back to the community by posting our findings. Thanks you all for all your help till now by posting problems and their solutions. ~Abhijit
9
3056
by: Paradigm | last post by:
I am using an Access2K front end to a MYSQL database. If I enter a new record in a continuous form the record appears as #deleted as soon as I move to a different record in the form until I requery the form. After the requery the records are in different order to the order that they may be entered in. The record does not seem to be assigned an ID (autonumber ID field) until after it is requeried. My problem is that after requerying the...
8
12123
by: Zlatko Matiæ | last post by:
There is a form (single form) and a combobox. I want that current record of the form is adjusted according to selected value in the combobox. Cuurrent record should be the same as the value in the combobox. What is the solution? Thank you in advance.
3
3124
by: Susan Bricker | last post by:
I might not have phrased the question correctly in the Subject of this post. Please read the entire explanation. I have a form with a command button (Add New Person). This button opens up another form (frmPeople) with a NewRecord (blank fields). The frmPeople form has a command button to save the record. I call this scenario ADD Mode. When the record is saved we stay in ADD Mode and get another NewRecord. This form (frmPeople)...
6
8540
by: Henry Stockbridge | last post by:
Hi, I have a popup that is used to update the records on an open form. I cannot get form to refresh with the new values. Any help you can lend would be appreciated. Here is the code for the popup form: '----------------------------------------------- Sub AddRep_Click() Dim db As Database
3
2110
by: Kaur | last post by:
Hi, I would appriceate any help to correct the code error that I am getting for the onclick event of a cmd button. I have two forms. Main Form "frmQuestion" and form 2 "SfrmQuestion". FrmQuestion shows the details about Question and by clicking on one for the cmd button on this form opens up SfrmQuestions that lets me add new questions. The frmQuestion Form remains opened at the backend. My problem is when I save the question in...
1
1933
by: Jimmy | last post by:
There is a command button on the form I'm working on. The form displays one record. The command button creates a new record by copying selected data from the current record: Dim dbs As Database Dim rst As Recordset Set dbs = CurrentDb Set rst = dbs.OpenRecordset("tblProjects", dbOpenDynaset)
2
2639
by: Fa | last post by:
Hi, I'm using this code to pass "codice cliente" values from a form1 to form2. Private Sub Form_Open(Cancel As Integer) Me.RecordsetClone.FindFirst "=" & Forms! ! If Me.RecordsetClone.NoMatch Then Me.RecordsetClone.AddNew Me.RecordsetClone! = Forms!
0
9812
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11210
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10795
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10898
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9612
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8001
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7154
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
2
4255
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3261
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.