How to refresh a single record in a datasheet subform?

R

rocketD

Hello,

I have a form with a subform displayed in datasheet view. The subform
filters all records for the personID in the main form.

The subform looks like this:
PersonID Clinic Survey; where Clinic stores ClinicID and
displays clinic name, and Survey stores SurveyID and displays survey
name. ClinicID has its own lookup table, and SurveyID does too - with
surveyID, clinicID, and survey name in it. Clinic and Survey in the
subform are unbound comboboxes based on rowsource queries, which pull
data from the respective lookup tables.

I have set the subform to allow data entry so that people can add new
records for that personID, and I'm trying to use 2 unbound combo boxes
to limit survey by clinicID so they can't enter a survey name that's
not related to the clinic they chose, on accident. I can limit survey
by clinic if I use this criteria in the clinicID field of the
rowsource query for surveyID: " Like [Forms]![fmParticipantRecords]!
[subfmTracking].[Form]![ClinicID] ", but it only works I show only the
surveyID number in the combobox.

That's useless to a user - they need to see the survey name, which is
in the same table as the surveyID. As soon as I add survey name to
the rowsource query, I get a refresh issue. Only two surveys show up,
and they are related to the clinicID displayed in the subform of the
first record that displays in the Main form when the form opens. If I
go to another personID record, the surveys associated with other
clinicIDs are not displayed at all - they are blank. I know this is a
refresh issue because if I reselect the clinicID for a record, the
choices for survey refresh and display, but ONLY for records with the
same clinicID. All records that have different clinicIDs stay blank
(show no survey at all) until I reselect the clinicID for those
records (and then of course the surveys for different clinicIDs all
disappear). I have Me.SurveyID.Requery set in the onChange event of
the clinicID box.

Is there a way to refresh/requery EACH record displayed in the subform
datasheet, so that all survey names show up where they are supposed
to? Why does this work when the survey IDs (numbers) are shown, but
not the survey names? Those records are all associated.

Thanks,
Dara
 
A

a a r o n . k e m p f

I think that you're looking at form.refresh instead of form.requery,
right?
 

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

Top