Dependant Forms Structure

[Bernard Piette]

I have 3 countries provinces cities tables and a locations table that
combines them all.
I want to use the locations table as a base to lookup and then input those
values not the IDs into the country province and city field values in my
other tables such as employees suppliers investors where applicable.

I have two questions:

1. I want to select Canada from the country combo box and only canadian
provinces that have been preentered in the locations provinces field will
show up. I need a simple way to do this b/c I will repeat this functionality
many times in this database application. In the example forms I've seen which
have 1 combo bos rely on another all the options were for lack of a better
term hardcoded into the event handler code. Seems like a lotta work.

2. Can I find a solution for this that I can package as a subform and then
for all forms requiring an entry for like country province city I can always
just point to the subform for those three fields?

I hope I have correctly articulated my question And Thanks in advance
for considering an answer !

Bernard

 

[Allen Browne]

See:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

 

[Michael Cheng [MSFT]]

Hi Bernard,

Welcome to use MSDN Managed Newsgroup!

1. Create a new combo box and set Row Source to be SELECT CountryID FROM
BaseTable
2. Create a new list box and set the Row Srouce to be blank (assuming the
name to be 'list1')
3. Rgith Click combo box (assuming the name to be 'combox1'), click Build
Event, add following codes

Private Sub Combo1_BeforeUpdate(Cancel As Integer)

Me.List1.RowSource = "SELECT [ProvincesID] FROM [Base Table] WHERE
CounteryID = " & Me.Combo1.Value & " ;"

End Sub

Yes, please look into Access sample database Northwind.mdb, check Form
'Orders' and 'Orders Subform'

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!


Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Bernard Piette]

Thanks Allen for the response.

 

[Bernard Piette]

And Micheal comes to the rescue again.

Great clear answer thanks for heklping with what should be a rather simple
task, but isn't the first times you do it

Bernard
--

Hi Bernard,

Welcome to use MSDN Managed Newsgroup!

1. Create a new combo box and set Row Source to be SELECT CountryID FROM
BaseTable
2. Create a new list box and set the Row Srouce to be blank (assuming the
name to be 'list1')
3. Rgith Click combo box (assuming the name to be 'combox1'), click Build
Event, add following codes

Private Sub Combo1_BeforeUpdate(Cancel As Integer)

Me.List1.RowSource = "SELECT [ProvincesID] FROM [Base Table] WHERE
CounteryID = " & Me.Combo1.Value & " ;"

End Sub

Yes, please look into Access sample database Northwind.mdb, check Form
'Orders' and 'Orders Subform'

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!


Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Michael Cheng [MSFT]]

Hi Bernard,

Thanks so much for your great feedback. It's my pleasure to work with you
on this topic. If you have any questions or concerns next time, don't
hesitate to let me know. We are always here to be of assistance!


Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.