Problem with datasheets in Subform.

[Martyh]

I seem to have a problem with a datasheet in subforms. I am trying to rush this job along but not having very much luck? Please see my attempts at the problem (mdb) and the commentary (pdf)

TIA for any help

Marty



View attachment Microsoft Word - subform.pdf

View attachment SampleComboWDatasheet.zip

 

[llkhoutx]

Pick Township first and on Township AfterUpdate requery cbo County which is filtered by Township.

 

[Martyh]

Reversed ??

Pick Township first and on Township AfterUpdate requery cbo County which is filtered by Township.

Thanks for your prompt reply.

... but I have to pick County first ... still confused

I guess I just have a mental block on this county -township thing!!


Marty

 

[llkhoutx]

There's multiple townships per county, but one county per township.

Picking a township first would cause county to fill automatically, but not visa versa.

Picking County first, the township cbo should be filtered by countyID and requeried on county AfterUpdate. You will them have a list of the townships in the county specified.

 

[Martyh]

A Problem still!!

I have revised the database accordingly by adding a second piece of code:

Private Sub LivesInTwp_AfterUpdate()
Me.cboLivesInCounty.Requery '<---seems to take the whole column for
' for revaluation purposes
End Sub

Private Sub cboLivesInCounty_AfterUpdate()
Me.cboLivesInTwp.Requery
End Sub

Although that seemed to neaten up the entries to the fields at first, when I go to revise any of the County entries, say the last one, it promptly wipes out ALL the rest of the entries to Township column.

In addition, there seems to be a problem in entering the data: It seems to recalculate ALL the entries (as opposed to just the current one)

I think this may be due to my entering the WRONG thing in the requery... but I am not sure about how to make it right!

I hope to have been clearer.... but I am still confused!!

Marty

 

[llkhoutx]

You filter township with

[cboLivesInCounty]

It should be filtered with

[Forms]![Test]![cboLivesInCounty]

because the relative county reference is on the form. Furthermore, the query behind township should be sorted so that the user can merely type a character and Access will just down the list to the first entry beginning with that character, etc.

 

[Martyh]

I've done what you've suggested, but still no luck! It still seems to recalculate ALL the entries (as opposed to just the current one)...

Marty

 

[llkhoutx]

Your zipped corrected database is attached.

Look closely at the City and County combo box row source.

Then you'll understand what's going on.

 

[Martyh]

I guess I have a real problem then because the correction.zip db DOES NOT work on my system. In fact, the first time through it still deleted the entire column of twp data.

Still confused ... but I've got to do something !!

 

[llkhoutx]

I downloaded and unzipped what I sent in my previous post and it works fine.

It's an Access 2000 mdb.

The cboTownship row source is

SELECT [lkpTownships].[TwpID], [lkpTownships].[Township], [lkpCounty].[CtyID] FROM lkpCounty INNER JOIN lkpTownships ON [lkpCounty].[CtyID]=[lkpTownships].[CtyID] WHERE ((([lkpCounty].[CtyID])=[Forms]![Test]![cboLivesInCounty])) ORDER BY [lkpTownships].[Township];

I don't know what else I can tell you.