Cannot get subform to requery (clear data with new parent record)

[lmh24]

Hello,

I have a survey questionnaire that I'm building in Access where each unique record corresponds to a household, designated by the primary key FILE. Most of the data to be entered in this database will be in one row, i.e. data about one selected individual in the household.

However, I want to collect a limited amount of information about every member of the household also, so I have a subform [Roster] in which data for each household member can be entered.

I have an integer field [hhnum] in the Parent form [Household] to indicate how many people live in the household -- after this is updated the user is taken to the subform, which I have set up so that only the number of records indicated in [hhnum] can be entered in the subform.

For some reason, however, I cannot get the subform to clear its data when I begin a new parent record. I have related the Household and Roster tables using FILE (my unique ID), and I have tried the following command both OnOpen and OnCurrent:

Forms![Household]![Roster].Form.Requery

But the data still won't clear with a new parent record. This is beginning to drive me insane, and I would appreciate any assistance!

 

[RainLover]

Have you set up the Master/Child links.

With the Form in Design look at the properties of the Sub Form.

 

[lmh24]

Yes, I have set them up - there is a field in both forms by which I have linked them. This does not solve the problem.
Thanks.

 

[Galaxiom]

Can you display existing data from the tables and everything works fine when you move between main form records?

 

[lmh24]

Can you display existing data from the tables and everything works fine when you move between main form records?

Yep, everything else seems to be working fine. And when I don't have the "requery" code commented out I don't get an error -- it just doesn't do anything.

Thanks.

 

[Galaxiom]

Maybe it is something to do with the code you have to limit the number of entries?

Is there a value getting left in textbox on the main form (the one associated with the LinkMasterFields property) or perhaps a default value.

 

[RainLover]

For some reason, however, I cannot get the subform to clear its data when I begin a new parent record

Are you wanting to delete the existing Data or do you just want the SubForm to open without showing pervious records but be able to enter new Data.

 

[lmh24]

Maybe it is something to do with the code you have to limit the number of entries?

Is there a value getting left in textbox on the main form (the one associated with the LinkMasterFields property) or perhaps a default value.

There is a (numerical) value on the main form (hhroster) that tells the subform in how many records to allow entry for that particular unique ID (FILE). Once a number is selected in that field in the main form, the focus is set to the subform and only that many records can be entered. Then the user will move on to a series of other forms, all linked via the FILE number.

What I have is a survey questionnaire in which each primary key ID (FILE) is associated with a single household. In that household there will be a set of responses (one row) that are associated with the randomly selected adult in that household.

However, my subform is a household roster, i.e. I need to collect brief info about each member of the household such that in the end I'll have a query linking the selected adult with each member of his/her household -- in a household of 3 this will look like 1.1, 1.2, 1.3 for example.

So for each household approached, a new parent record will be initiated, and the subform (Household_Roster) should then be associated only with that FILE, i.e. the records listed in that iteration of the subform are associated only with that household (FILE).

Currently when I initiate a new parent record, I still see the values from the previous subform record in the subform after selecting number of people in the household (hhnum - the field in the parent that triggers the record set limit in the subform).

I don't want to delete the subform values for the previous FILE, I just want them to clear when I initiate data entry with a new FILE.

The syntax I'm using to limit the recordset is below:

Dim rst As DAO.Recordset

Set rst = Forms![hhroster]![Household_Roster].Form.RecordsetClone

rst.MoveLast

If rst.RecordCount = Forms![hhroster]![hhnum] Then
Forms![hhroster]![Household_Roster].Form.AllowAdditions = False
DoCmd.Save
'DoCmd.OpenForm "Household", acNormal

Else
Forms![hhroster]![Household_Roster].Form.AllowAdditions = True
End If

If Err.Description = "No Current Record" Then
DoCmd.GoToRecord , , acLast
End If

Thanks!

 

[RainLover]

Again everything is pointing the the Master Child links.

What do you have for a Primary key and a Foreign key.

 

[lmh24]

Again everything is pointing the the Master Child links.

What do you have for a Primary key and a Foreign key.

The primary key is FILE -- a unique ID that I've assigned -- and the foreign key is the same, i.e. the corresponding FILE ID in the subform table.

It's been awhile since I've used Access, and the relationship structure is a little fuzzy, so if I'm relating them improperly, I would be grateful to know that.

All of the forms in my database are currently related via the assigned FILE id, but most of them are one-to-one relationships whereas this needs to be many (child form) to one (main form).

Thanks.

 

[lmh24]

Okay, so I figured out the problem, but I don't know how to fix it.

What I have is a series of forms connected by their primary keys, all called FILE. When a certain value is entered on the first form (Screening), the user is taken to the next form (Household). It is this form that is the Main/Parent for my subform.

The problem is that after updating the trigger field (begin) in the Screening form, I am being taken to the first record in the Household form rather than the record that corresponds to the FILE number in the Screening form. Again, these are related via the unique FILE number that acts as primary keys for both the Screening and Household forms.

How do I get the Household record that corresponds to the Screening record show up rather than the first record in the Household table? I'll need to appropriate corresponding record to show up in every subsequent form as well. I hope I didn't set this up completely wrong.

Thanks.

 

[Galaxiom]

Can you post a cut down version of the database with just the relevant objects and a few dummy records?

 

[lmh24]

Can you post a cut down version of the database with just the relevant objects and a few dummy records?

I will try to do this shortly, however I think I found a possible solution in some code I found on another site. But I get an error in one portion of it. If anyone can help me figure out what's wrong with this, it may solve the issue.

In the After Update section for the last field (begin) on the Screening form I have now placed the following code (hhroster is the next form/table name and file is my primary key in all of the main forms):

DoCmd.OpenForm "hhroster", acNormal, , , , , "me.file"

Then in the On Open area of the next form (hhroster) I have this code:

If Not IsNull(Me.OpenArgs) Then
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[file] = " & Me.OpenArgs
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If

The error message I'm getting says that the database engine does not recognize 'me.file' as a valid name or expression. I have this field in both tables and both forms, so I'm not sure why I'm getting this error or how to fix it.

Thanks!

 

[RainLover]

Try this.

DoCmd.OpenForm "hhroster"

 

[lmh24]

Figured it out:

In the first form:

DoCmd.OpenForm "hhroster", acNormal, , , , , Me.file

Second form OnOpen:

If Not IsNull(Me.OpenArgs) Then
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "hhroster.file = " & Me.OpenArgs
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If

 

[RainLover]

Glad you got it sorted.