Sub form with filters

[Tips]

I am looking for some advice or guidance on this please.

I have set up 2 tables, and got the tables linked with a foreign key. These tables store street names and house numbers.
Ideally I would like to enter a street name, and then number from and number to, ie: Main Street, from 1 to 30.

Each house number will has 4 fields associated to it.

The desired result in a form will be to enter the street name, numbers from and to, and then a sub form open up showing a list of numbers covering the range entered with 4 "Yes/No" fields next to each house number.

Some streets could go up to as most as 300 houses, but I would not want that number shown for every street when it is not necessary. only the relevant number according to what is entered.

All data would need to be stored and then queries and reports run against it.

I hop this makes sense. I have tried to explain it as best as what I can.

I am using access 2007 on windows 7.

thank you in advance from this novice user.

I have attached a copy of the db for reference.

 

[JHB]

...
Some streets could go up to as most as 300 houses, but I would not want that number shown for every street when it is not necessary. only the relevant number according to what is entered.
..

I've made the form for you.

All data would need to be stored and then queries and reports run against it.

Sorry - but it doesn't make sense to store the data, refer to the form in you queries instead.
Build your report on a query.

 

[Tips]

Hi JHB,

I really appreciate your assistance and guidance with this.
I am unable to open the attachment that you have posted for me, tells me unrecognisable format?

Thanks in advance.

 

[JHB]

Try it now.

 

[Tips]

That is great, thank you very much.
One more query for you.
when I edit data in the subform, everything according to houses it stored, but the street details for are not updated.

I hope that makes sense.

 

[Tips]

Hi JHB,

sorry to be a nuisance.
I have tried with no luck to edit the form, so that if I enter a new street that does not currently exist it will add it and update the relevant table.

Also, when editing any house detail or adding any detail the street details are not being updated back to the streets table,

Please can you assist or point me in the right direction,

Thanks in advance.

 

[JHB]

Then you need to design your form in another way as you have, then what you've is a search form, not a input/edit form.
I've made some small changes to your forms, database attached.

 

[Tips]

That is great.
Appreciate your assistance with this.

 

[Tips]

Hi JHB.

Sorry to bug you with this again.
I could end up with the situation of more than one main street.
How can I achieve what you have done so far with adding an additional field such as area.
so I would select street, then area (or other way around) then the house number range and filter it as such.
I would still need any info entered to then update back to the tables.

thanks in advance.

 

[JHB]

I would keep it simple and just type in the area beside the street name like -"Main Street - TheAreaName".
Else create a table for the areas - draw the relevant relationships between the tables and apply the form filter with the area.