Requery

  • Thread starter Thread starter Michelle
  • Start date Start date
M

Michelle

I have the following code....
Private Sub Inmate_Numb_AfterUpdate()
Dim Inmate_Numb As Variant
Inmate_Numb = Me!InName.Value
Me.Requery
If Not IsNull(Inmate_Numb) Then
Me.Recordset.FindFirst "InName=" & Inmate_Numb
End If
End Sub
What I want the for to do display the data I was working
on before it did the Requery commnad, but it seems to send
it to the first record not the one that was being worked
on.
Thanks
Michelle
 
Hi,
You could use bookmarks for this.

Dim varBookmark as Variant

varBookmark = Me.Bookmark
Me.Requery
Me.Bookmark = varBookmark

HTH
DAn Artuso, MVP
 
It gives me an error when I put your code in.
-----Original Message-----
Hi,
You could use bookmarks for this.

Dim varBookmark as Variant

varBookmark = Me.Bookmark
Me.Requery
Me.Bookmark = varBookmark

HTH
DAn Artuso, MVP




.
Click to expand...
 
Hi,
You'll have to provide a bit more detail than that for me to troubleshoot.
The code works fine here.
What line does it error on?
What is the exact error?

Dan Artuso, MVP
 
This is what the code looks like now...

Private Sub Inmate_Numb_AfterUpdate()
Dim varBookmark As Variant
varBookmark = Me.Bookmark
Me.Requery
Me.Bookmark = varBookmark
End Sub

Now it say I enter info into record 4 when I hit tab it
puts me into record 3 and not back into record 4.

Also another thing I noticed, if I delete a record it
deletes the corrisponding data out of the look up table,
why? can you help with this also?
-----Original Message-----
Hi,
You'll have to provide a bit more detail than that for me to troubleshoot.
The code works fine here.
What line does it error on?
What is the exact error?

Dan Artuso, MVP

"Michelle" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
Hi,
A couple of things.
The code works fine for me here in the after update event of my control.
But more importantly, why are you requerying after you update this control?
Is this a new record you are adding or are you updating an existing one?

Dan Artuso, MVP
 
Dan Artuso said:
Hi,
You could use bookmarks for this.

Dim varBookmark as Variant

varBookmark = Me.Bookmark
Me.Requery
Me.Bookmark = varBookmark
Click to expand...

Dan, it has always been my understanding that requerying a form
invalidates any bookmarks set on it. It says so in the help file (not
that the help file is always right), and that fits in with my
understanding of how bookmarks relate to recordsets. Am I wrong about
this?
 
Hi Dirk,
I'm not sure about the help files, but that code has always worked for me.
I tested it out again before replying just to make sure :-)
I'll test it out right now on a different PC with a different version of Access.
 
Dirk,
It works here. I just tested it out on 97.
Does that code not work for you?
Is it just a coincidence that it's been working for me?
 
The reason I need the rquery is that I want to pull a name
bassed on a # from another table. Say you put in AA1111
then tab it should pull in name John Doe into the Name
field and any other information that is available.
However the only way I can seem to get this to work is
with requery. Is there something else, it is still taking
me back to the record prior to the one that was last
edited. Say I was adding info to record 4 I hit tab and
it requery's an puts me into record 3.
 
Dan Artuso said:
Dirk,
It works here. I just tested it out on 97.
Does that code not work for you?
Is it just a coincidence that it's been working for me?
Click to expand...

At least in Access 2002 (and I wouldn't expect it to be different in
Access 97), if the act of requerying adds or deleted records from the
form's recordset, the bookmark you saved before requerying won't
necessarily take you to the same record.

I did this to test:

1. Create a form based on a test table with some records in it.
Autonumber primary key "ID". The form has records with ID values
through 72, then the next record has ID 170 (so there's a gap), and then
the next after that has ID 188.

2. Put a command button, cmdRequery, on that form. Here's the Click
event code:

Private Sub cmdRequery_Click()

Dim varBookmark As Variant

varBookmark = Me.Bookmark
Me.Requery
Me.Bookmark = varBookmark

End Sub

3. Positioned the form to the record with ID 170.

4. Inserted a record by code from the Immediate Window:

currentdb.Execute "INSERT INTO Table1 (ID, [Desc], Modified)
VALUES(150, 'Inserted record', Date())"

5. Back in the application window, clicked the cmdRequery button.

RESULT: the form is now positioned on record ID 150, the record I added
in code, not on the record I was on when I clicked the button.

Continuing the test, I did this:

6. Position the form again to record ID 170.

7. Deleted record ID 150 with code from the Immediate Window:

currentdb.Execute "DELETE FROM Table1 WHERE ID=150"

8. Clicked the cmdRequery button.

RESULT: the form is now positioned on record ID 188, the record *after*
the one I was on when I clicked the button.

So plainly the bookmark property is positional, and not based on a
physical record key or anything like that. Thus, you can't rely on a
saved bookmark to return you to the same record after a requery, unless
you are sure no records have been added or deleted -- in which case, why
requery at all?
 
Hi,
I'm really not clear on what you are trying to do.
Are you adding new records or edititng existing ones? or both?
Where do you want to pull the name from, what table?
Why do you have to requery?
If you want to look up a value in a tabel that's not part of your form's
recordset, you can use DLookup. Does that help?

See, Dirk's reply to using bookmarks. They will not work if you are adding (or deleting)
records. I was under the impression that you were *updating* existing records.
 
Hi Dirk,
I somehow got the impression that the OP was only editing existing records, which is why I
asked him why he needed to requery. I do realize that if you add a record then requery, the bookmark
technique will not work. I still don't really understand what he's trying to do.
Feel free to jump in if you think you can point him in the right direction!

--
HTH
Dan Artuso, Access MVP


Dirk Goldgar said:
Dan Artuso said:
Dirk,
It works here. I just tested it out on 97.
Does that code not work for you?
Is it just a coincidence that it's been working for me?
Click to expand...

At least in Access 2002 (and I wouldn't expect it to be different in
Access 97), if the act of requerying adds or deleted records from the
form's recordset, the bookmark you saved before requerying won't
necessarily take you to the same record.

I did this to test:

1. Create a form based on a test table with some records in it.
Autonumber primary key "ID". The form has records with ID values
through 72, then the next record has ID 170 (so there's a gap), and then
the next after that has ID 188.

2. Put a command button, cmdRequery, on that form. Here's the Click
event code:

Private Sub cmdRequery_Click()

Dim varBookmark As Variant

varBookmark = Me.Bookmark
Me.Requery
Me.Bookmark = varBookmark

End Sub

3. Positioned the form to the record with ID 170.

4. Inserted a record by code from the Immediate Window:

currentdb.Execute "INSERT INTO Table1 (ID, [Desc], Modified)
VALUES(150, 'Inserted record', Date())"

5. Back in the application window, clicked the cmdRequery button.

RESULT: the form is now positioned on record ID 150, the record I added
in code, not on the record I was on when I clicked the button.

Continuing the test, I did this:

6. Position the form again to record ID 170.

7. Deleted record ID 150 with code from the Immediate Window:

currentdb.Execute "DELETE FROM Table1 WHERE ID=150"

8. Clicked the cmdRequery button.

RESULT: the form is now positioned on record ID 188, the record *after*
the one I was on when I clicked the button.

So plainly the bookmark property is positional, and not based on a
physical record key or anything like that. Thus, you can't rely on a
saved bookmark to return you to the same record after a requery, unless
you are sure no records have been added or deleted -- in which case, why
requery at all?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top