Datasheet appears after requery

[CDG]

I am creating a database to be used on a multiuser environment. New records
will be added and records will be updated with several users. I have a form
that displays all the records - kind of like a datasheet view. I understand
that I must have some type of requery. Currently, I have a requery command
button that goes to a macro OnClick. The macro performs the function of the
requery. I have several questions:

1. When the command button is clicked, a datasheet appears with the records
listed. When you close the datasheet you are returned back to the original
form. How do I make this datasheet not appear?
2. Is there a way to have the records requery automatically - not use the
command button?
3. After the requery, the cursor returns to the first record. How can the
cursor remain at the current record or at best go to the last record?

Thanks,

 

[Dirk Goldgar]

I am creating a database to be used on a multiuser environment. New
records will be added and records will be updated with several users.
I have a form that displays all the records - kind of like a
datasheet view. I understand that I must have some type of requery.
Currently, I have a requery command button that goes to a macro
OnClick. The macro performs the function of the requery. I have
several questions:

1. When the command button is clicked, a datasheet appears with the
records listed. When you close the datasheet you are returned back
to the original form. How do I make this datasheet not appear?

A simple requery action wouldn't normally open a separate datasheet. I
suspect there's more in that macro than just a Requery action. Please
check it and list the details of the macro actions performed. Maybe
you're executing an OpenQuery action instead.

2. Is there a way to have the records requery automatically - not
use the command button?

You could conceivably have a Timer event procedure that periodically
requeries the form. That can cause problems of its own, though, and I
would avoid it unless you really *have* to have the most current data
displayed at all times. If this form will only be used for display, and
not for editing data, it won't be so bad.

3. After the requery, the cursor returns to the first record. How
can the cursor remain at the current record or at best go to the last
record?

You need to save the primary key of the current record in a variable,
requery the form, and then relocate that record afterward. I'd use code
for this, not a macro. Basic example code would look like this:

'----- start of example code -----
Dim varID As Variant

varID = Me!ID ' where "ID" is the primary key

Me.Requery

If Not IsNull(varID) Then
With Me.Recordset
.FindFirst "ID=" & varID
If .NoMatch Then
If .RecordCount <> 0 Then
.MoveLast
End If
End If
End With
End If
'----- end of example code -----

Note: that's just "air code", but something along those lines, suitably
adjusted for your form, ought to work.

 

[CDG via AccessMonster.com]

Thank you so much for your help.

1. The requery macro s now working. I deleted the old macro and recreated
it with just the requery command in the macro.

2. Unfortunately, the data will be updated by several users, so I guess the
manual requery button will have to do.
Would a requery macro work after a user updates a certain field perhaps? (I
do like the idea of a timed interval, however I am not that advanced with
coding.)

3. I entered the example code and it did not work. The data did requery
fine, but the cursor still returned to the first record. My primary key is
ID. Where there any changes in the code that needed to be changed?

Thanks for your help.

I am creating a database to be used on a multiuser environment. New
records will be added and records will be updated with several users.

[quoted text clipped - 7 lines]

records listed. When you close the datasheet you are returned back
to the original form. How do I make this datasheet not appear?

A simple requery action wouldn't normally open a separate datasheet. I
suspect there's more in that macro than just a Requery action. Please
check it and list the details of the macro actions performed. Maybe
you're executing an OpenQuery action instead.

2. Is there a way to have the records requery automatically - not
use the command button?

You could conceivably have a Timer event procedure that periodically
requeries the form. That can cause problems of its own, though, and I
would avoid it unless you really *have* to have the most current data
displayed at all times. If this form will only be used for display, and
not for editing data, it won't be so bad.

3. After the requery, the cursor returns to the first record. How
can the cursor remain at the current record or at best go to the last
record?

You need to save the primary key of the current record in a variable,
requery the form, and then relocate that record afterward. I'd use code
for this, not a macro. Basic example code would look like this:

'----- start of example code -----
Dim varID As Variant

varID = Me!ID ' where "ID" is the primary key

Me.Requery

If Not IsNull(varID) Then
With Me.Recordset
.FindFirst "ID=" & varID
If .NoMatch Then
If .RecordCount <> 0 Then
.MoveLast
End If
End If
End With
End If
'----- end of example code -----

Note: that's just "air code", but something along those lines, suitably
adjusted for your form, ought to work.

 

[Dirk Goldgar]

Thank you so much for your help.

You're welcome.

1. The requery macro s now working. I deleted the old macro and
recreated it with just the requery command in the macro.

Very good.

2. Unfortunately, the data will be updated by several users, so I
guess the manual requery button will have to do.
Would a requery macro work after a user updates a certain field
perhaps? (I do like the idea of a timed interval, however I am not
that advanced with coding.)

Sure, you could do that.

3. I entered the example code and it did not work. The data did
requery fine, but the cursor still returned to the first record. My
primary key is ID. Where there any changes in the code that needed
to be changed?

The code works for me. I wonder if it ever got called. If you're using
a macro to requery your form, as you said above, then there's nothing I
know about that would ever call the code. Where did you put it? If you
want to use that code, you should use an event procedure, rather than a
macro, for whatever event you decide on to requery the form; and then
you'd use that code for the event procedure.

 

[CDG via AccessMonster.com]

Back to #3. Believe it or not, the code is now working. (By the way, I did
use the event procedure - at least I did that right.) When I first tested it,
the cursor was not at an entered record. It was a record that was not saved,
so therefore the cursor returned to the first record. So I began to play
some more with different cursor locations, and it now works.

Thanks again.

 

[Dirk Goldgar]

Back to #3. Believe it or not, the code is now working. (By the
way, I did use the event procedure - at least I did that right.)
When I first tested it, the cursor was not at an entered record. It
was a record that was not saved, so therefore the cursor returned to
the first record. So I began to play some more with different cursor
locations, and it now works.

Thanks again.

You're welcome.