Changing record source of subform (2000)

Topic

New Reply

Hi all,

I’m hoping someone can help me out … I am a programmer, but have little experience with Access programming. I am working on an Access project (linking to SQL Server 7) to maintain a book database. The particular form I have a problem withis based on 3 tables:

Title (key ISBN)
GroupTitle (key field GroupISBN)
GroupLinkISBN (fields ISBN, GroupISBN)

and serves to link titles into groups using ISBNs.

I have a main form based on the Title table with a subform, linked via a key field (ISBN). The subform is a continuous form using GroupLinkISBN as a record source. It contains a single visible field, selectable using a combo box, which pulls up records from GroupTitle so that the title in the main form can be added to one or more groups. The two forms are linked using the ISBN field. This works very well, allowing users to select values from the GroupTitle table, and insert matching records into GroupLinkISBN. But now I want to use different tables as the record source for the subform and combo box, depending on the value of a field in the main form. I have half got this working using the following code for the subform:

Private Sub frmselTitleMulti_Enter()
If Me.PPCFlag = “P” Then
strSQL = “SELECT ISBN, GroupTitle FROM PPGroupTitle ORDER BY GroupTitle”
Me.frmselTitleMulti.Form.RecordSource = “PPGroupLinkISBN”
Else
strSQL = “SELECT ISBN, MultiTitle FROM GroupTitle ORDER BY GroupTitle”
Me.frmselTitleMulti.Form.RecordSource = “GroupLinkISBN”
End If

‘ combo box source
Me.frmselTitleMulti!Multibuy.RowSource = strSQL
End Sub

The combo box is being populated with values from the correct table, depending on whether the PPCFlag field is P or not. And if I select a value and save the record, it is inserted to the PPGroupLinkISBN or GroupLinkISBN table as it should be. My problem is that if I close the form and return to it, if there is a P in the PPCFlag field the subform is not populated with existing records from the PPGroupTitleISBN title as it should be — although it still works fine for “non-P” titles. Can anyone tell this Access newbie what I am missing??

Hope I’ve made this clear — it was difficult to explain!

Thanks

Reply | Quote

Replies

You situation is fairly complex, so I may have missed something, but I believe your code is runnin on the On Enter event for your combo box. However the way Access works, when you bring up the main form it has focus, and you would need code to initialize the RecordSource property of the subform to which ever case you have opened. If however you click into the combo box it should then update and disply the correct records, although you may need to do a requery.

On of the more compelling features of Access is that you can do lots of this kind of thing without doing any programming. In your case I think you might be able to by using two fields in your link properties between the subform and the main form. However your data sources are in two different tables it would appear – PPGroupTitle and MultiTitle – is there a reason for them being different tables? If not, I would simply put an extra field in one or the other and combine them. Then there isn’t a need to change the SQL behind the continuous subform, and Access should be able to do all the work behind the scenes. (My bias is to avoid writing code whenever I can – I’ve done enough to know that mine is nearly always buggier than the MS folks. I realize that may not be a good thing, but I’m a pragmatist.) Hope this helps.

Reply | Quote

Wendell Bell wrote:
>>However your data sources are in two different tables it would appear – PPGroupTitle and MultiTitle – is there a reason for them being different tables? If not, I would simply put an extra field in one or the other and combine them. Then there isn’t a need to change the SQL behind the continuous subform, and Access should be able to do all the work behind the scenes. <<

DOH! Why didn’t I think of that?

There is a French saying, “Why make things simple when you can make them complicated?” that applies here, I think. I’ve added the extra field, joined the forms on 2 fields instead of 1, and removed all my VBA code. Now it works like a dream and I have saved myself a whole lot of work. Thanks for pointing out my basic error before I’d gone too far down my winding and error-prone road!

Veronica

Reply | Quote

Hi Veronicay,

> table as it should be. My problem is that if I close
> the form and return to it, if there is a P in the PPCFlag
> field the subform is not populated with existing records
> from the PPGroupTitleISBN title as it should be —
> although it still works fine for “non-P” titles. Can
> anyone tell this Access newbie what I am missing??

From your code I suppose you’re using the Enter event of the subform, at least that’s what I’m reading from these lines, where frmselTitleMulti seems to be the name of the subform:

> Private Sub frmselTitleMulti_Enter()
> If Me.PPCFlag = “P” Then
> strSQL = “SELECT ISBN, GroupTitle FROM PPGroupTitle
> ORDER BY GroupTitle”
> Me.frmselTitleMulti.Form.RecordSource = “PPGroupLinkISBN”

Please correct me if this is ot so. However, this is the wrong place. You need to change the record source in 2 other events:
AfterUpdate and Current of the main form.

The AfterUpdate takes care of new main form records and PPCFlag field changes on old ones, the Current event fires for each record when you’re navigating the main form, so the subform will show the right data according to the current PPCFlag.

The Enter event fires only if you klick in the subform, so it’s useless when you’re just navigating or doing changes on the main form. The subform is showing the right data for non-P flags because GroupLinkISBN is the hardcoded record source in the first place.

Reply | Quote

Hi Emilia

Thanks for your reply. In fact Wendell provided me with a very simple solution to my problem.

Regards

Veronica

Reply | Quote