SubForm Links

Topic

New Reply

Okay..

So i’ve setup my subform to link parent and child fields. BUT..

I have a listbox which ideally I would use to select various schools for credentialing purposes. However, when I select a new school, it fails to update the rest of the sub-form, or the parent data. What can I do to make this happen?

thanks!

Reply | Quote

Replies

Where is the listbox, on main for subform? I don’t quite understand the situation, but you will have to force a requery of the subform. If you on the mainform, you can requery the subform via it’s control name:
subformControlName.Requery

On the subform, just do:
Me.requery

Reply | Quote

The listbox is on the subform…

When I have the subform open alone, it works, no problem. Click an item in the list, records update, i’m a happy camper. It’s when attempting to display it in the main form that I run into problems.. Nothing is updated.

The relational structure is such that only one key is used to determine what school i’m selecting… Perhaps I need to update that?

I’m at a LOSS.. thanks for all your help!

Reply | Quote

Whenever a subform works only when opened by itself, that usually means that you have made a reference to Forms!SubFormName that is NOT valid when it is actually a subform.

Reply | Quote

Okay.. so how can I make my subform update when I select an item from a list or combo box?

The update is using this code:

Sub LstSchool_AfterUpdate()
‘ Find the record that matches the control.
Me.RecordsetClone.FindFirst “[SchoolID] = ” & Me![LstSchool]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

I’m not quite clear what it is about the form/sub-form relationship that makes this code invalid.. can you help?

Thanks!

Reply | Quote

have you tried this
me.requery
at the end of your code

Reply | Quote

Nope..

The act of placing these forms as subforms is nullifying the code…

any help would be greatly appreciated!

Thanks!

Reply | Quote

Assuming that the listbox or combo box is the “master” link in the connection between the mainform and the subform, just put a line of code in the AfterUpdate event of the list box that requeries the subform control:
Me.subformControlName.requery

Reply | Quote

Thanks for all your help, but this isn’t quite working..

My list box is on the sub form, I used the list box wizard to specify that I want my listbox to find data in my subform. But, when I click on an item in my listbox, nothing happens.

The code to update the rest of the sub-form is already in the afterupdate event, so when I add the line you’ve specified, all it does is requery the listbox. If I make it requery the subform, it tells me it doesn’t recognize the control.

This may be a flaw in design, if you feel it is, please let me know, I’ll have other questions on this, For Example, I’ve got the link parent and link child properties set on the subform, but what happens if I want to change these properties?

I haven’t troubleshooted? this extensively yet, as I’m looking to correct the list box problem to verify that my child/parent linked fields will update when the requested change is made…

Thanks again for all your help!

Reply | Quote

have you tried using a query in the subform with the list box as the criteria

Reply | Quote

My relational structure wouldn’t let that work…

I have [schoolID] there is only one school for each SchoolID, however many doctors may have the same school id.

When I pull up a doctor, it grabs the corresponding school, but only from existing data. Because there is no link when I add new data, I am unable to specify the school the doctor graduated from.

Does anyone have some insight on how to correct this problem?

Thanks!

Reply | Quote

The code you listed before should work if the listbox is on the subform:

Sub LstSchool_AfterUpdate()
‘ Find the record that matches the control.
Me.RecordsetClone.FindFirst “[SchoolID] = ” & Me![LstSchool]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

There are a couple of things that could, however, that might prevent it from working. Is [SchoolID] a number? Also, and probably most important, your listbox must have as it’s rowsource a query which will produce the same recordsource as the subform! If the listbox has items not in the subforms recordset, then it can’t find those items.

Reply | Quote

Yep.. School ID is a number..

I’ll check on that… You’re suggesting I add the ‘schoolID’ as a field in my listbox, and ‘hide’ it as it is the primary key.. right?

Reply | Quote

yes. You need to identify a unique record if you want to “find” the same record in the subform.

Reply | Quote

Edited by DrkRealm on 18-Jun-01 19:56 (server time).

the list box properties are such that SchoolID is listed, it’s width is 0″ to hide it. SchoolName is listed, wide enough for it to appear to be the only thing listed in the box.

The bound column is 2, (schoolname) i’ve change it to 0, and 1, but am getting errors “Object not found” when I attempt to select a record…

this works as it should when it is opened by itself, but fails when it’s included as a sub-form.

Any ideas?

Reply | Quote

*Where* are you getting the Object not found? If it’s on something like .FindFirst, then make sure you have the DAO reference set and dim an object variable as a DAO.Recordset. Then set that object variable to Me.Recordsetclone and proceed from there. ADO doesn’t have a FindFirst method, so the object not found error might be caused by confusion as to which object model you’re using.

Reply | Quote

First of all, the bound column needs to be the column that contains the SchoolID, which I guess is column 1 in your list box.

The last line of your message, in which you indicate that the subform works standalone, seems to indicate to me that (as I said before) that the recordset used by the listbox is not the same as the recordset used by the subform. Remember, the subform’s recordset is filtered by the Master/child links on the subform control on the main form.

Reply | Quote

Well, they both have the same data source…

I’m getting the following:

Run-Time error ‘3021’

No current Record.

When I debug, It’s highlighting this part of the code:
Me.Bookmark = Me.RecordsetClone.Bookmark

Any idea what might be wrong here?

Thanks again for all your help!

Reply | Quote

You have to test for NoMatch before you try to set the bookmark. Otherwise, if you hit the EOF marker then there is no current record.

It would also be less reference intensive if you dimmed a recordset object, set it to point at the form’s recordsetclone and then referenced the object variable in your code. You could modify it like this:

Dim rst as DAO.Recordset
Set rst = Me.RecordsetClone

rst.FindFirst "[SchoolID] = " & Me![LstSchool]
If Not rst.NoMatch Then
  Me.Bookmark = rst.Bookmark
Else
  'Do whatever is appropriate under the circumstances
End If
Set rst = Nothing

Reply | Quote

While they may have the same source, it doesn’t mean that they have the same records. The subform only has those records that meet the criteria of the Master/Child linking fields. So, let’s suppose that your listbox rowsource is “select * from sometable”, and suppose your subform’s record source is the same. The listbox will contain as many entries as there are in [sometable]. But your subform may not contain any records, since it depends on the value of the Master link field on the main form.

Reply | Quote

Okay.. so i’m starting to think that there are problems with my database construction.

I have a list of some 2000+ individuals. These individuals all have thier own Organization, School, and State Agencies.

So, for each of these individuals, I have OrgID, SchoolID, and StateID.

Then I have the following tables:

Individuals
Organizations
Schools
State Associations

If I link the subform, (Intended to display SchoolID) with the PrsnSchoolID, then the sub form displays the schoolID where the school ID matches a record in the School ID table. However, if I want to change that, I am unable to do so.

Do I have to break the parent/child links before I can manipulate the ‘SchoolID’ for the individual by selecting a school from the subform?

Reply | Quote

I’m assuming, from your description, that an individual can have only 1 schoolID; and that this ID is stored in the individual’s record. I don’t know why you have a subform at all. It seems that all you need is a combo box on the mainform. In design mode, click on the combo box icon in the tool box, then click and drag the schoolID field from the field list to a place on your form. The combo box wizard should then pop-up and guide you through creating a combo box in which the school name is displayed.

Reply | Quote

Alas, you’re right..

Although I want to keep my subform only to display the data, I don’t need my subform to control my main form, which is essentially what I was trying to do.

By dragging a reference from the main sheet, I’m able to do exactly what I wanted to do. Thanks for all your help!

Regards,

Reply | Quote

I usually place the list/combo box on the main form to select whatever record I require, the subform data automatically displays provided the parent child fields are set correctly.
It seems to me you’re trying to work back to front, finding one record from many rather than many from one. It may be able to be done but I’ve never tried.

Reply | Quote