Issue with Refreshing

[AccessHelp]

Good morning,

One of my forms is designed to change the information on records from a
table. In the Form Header section of the form, I have a search function to
search the records that users want to change in the Detail section of the
form. One of the search fields is a wild card text field (meaning the users
can type in something and press a command button. Then the Detail section
will show the records closed to that words or letters). The wild card text
search field (Search A) is connected to the student name field (Field A) in
the Detail section.

The Field A is not a field in a table, and I use "DLookup" to show the
student name on the form. Next to the Field A, I have a field for Student ID
(Field B). When the users change the Field B on the form, the Field A will
show the student name of that student ID.

I have no problem pulled up the records when the users type in a criterion
and press the command button to search for the records. I have problems
after the users find the records and make changes to the Field B (Student
ID). When the users change the Field B, the Field A changes; however, the
record is still showing up on the form. Technically, the record(s) should go
away because the student name is no longer matched with the criterion in
Search A. Additionally, if I type in a criterion on that changed student
name, that record would not show because the form is still memorizing the old
student name (before change). If I leave the form and come back in, then the
form recognizes the change to the student name.

How can I do to make the form to recognize the form without leaving the form
and adding a student name field in a table? I have tried adding "Me.Refresh"
in the command button and in the BeforeUpdate of Student ID and Student Name,
and they don't seem to work.

Please help. Thanks.

 

[Klatuu]

Have you tried a Requery rather than a Refresh?

 

[AccessHelp]

Hi Dave,

Thanks for your response. I just tried again (on the BeforeUpdate of the
Form, Student ID and Student Name) with the Requery (rather than Refresh) to
make sure, and it didn't work.

Thanks.

 

[Klatuu]

Based on your description, a Requery should do the trick. Is a single record
form or a datasheet or continuous form? I believe it is most likely a matter
of where the requery should go. If it is a single form, it may be you need
to move to another record before it will disappear.

 

[AccessHelp]

It is a continuous form. Where do you think I should have the Requery?

Instead of using the DLookup on the student name field, if I have the
student name field in the table of RecordSource, I know I would not have that
problem. However, I tried to save the size of the database and to save from
bloating I chose not to have the student name field in the table. Thanks.

 

[Klatuu]

Carrying only the student id and not the name is the correct way to do it. I
believe you could avoid the Dlookup (time hog - performance hit), by using a
query rather than the table itself. You would join the student table on the
student id and use it only form display purposes.

Now, you say this is in the detail section, but let me ask, is this
continuous form a sub form? If so, you may be requerying the wrong form.
Based on your description, it seems to me that when the user changes Field B,
the student id, the After Update event of Field B would be the place to
requery.

 

[AccessHelp]

I just tried using the "Me.Requery" in AfterUpdate of Student ID (Field B),
and it didn't work.

I do not have a subform. It is a continous form in the Detail section.

I do have the student ID joined to the student table in the Record Source of
the form; however, when I change the Field B (Student ID), the Field A does
not change to reflect the student name for the Field B. That is why, I use
the DLookup. I don't know that would matter. There are two student ID
fields. One is an autonumber field generated by the database, and the other
one is the actual student ID of the students. The join field in the Record
Source is an autonumber field, and the field (Field B) on the form is a real
student ID, not an autonumber.

I don't really know why I use two student ID fields. The more I think I
only one student ID field. I think I am making myself more complicated.

Thanks.