Viewing filtered information from a specific record

[Guest]

I have a database that tracks clients, their sites, and my staff that work
there. I have approximately 50 clients; each client may have a number of
sites that I maintain (anything between 1 and 40). I have a number of
employees that work at one or more site. Some clients may have more than one
of my employees working at their site. Keeping track of who is working where
can be difficult.

My database includes an employee table, a client table, a site table, and a
number of other tables that are not relevant t my table. I want to create a
form that will give me a drop-down list of clients so that if I select a
client I can see a drop-down list of the sites that they have which if I make
a selection will present me with site details in form view not datasheet
view. I also want to do something similar with employee information (see who
they work for and then at what sites).

Is this possible and if so, how?

Thanks

David

 

[Guest]

In the form header, make an unbound combo box ClientID. Make its RowSource
the ClientID & ClientName. Bind column 1, and make column widths 0;1. Add
this code;

Private Sub ClientID_AfterUpdate()
Site = null
End If

In the RowSource of the (unbound) Sites combo box (also in the form header),
build your query from the Sites table, and have SiteID, SiteName, and
ClientID. In the criteria for ClientID, enter this:
[Forms]![thisFormName]![ClientID]

In the Enter event of the Sites combo box, have this:

Private Sub Sites_Enter()
Sites.Requery
End Sub

This in AfterUpdate

Private Sub Sites_AfterUpdate()
Me.Requery
End Sub

The sites combo box will now show sites for only the currently-selected
client.

Make the RecordSource of your form the site details with
[Forms]![thisFormName]![SiteID] as the criteria for SiteID. If more than 1
client shares a site, you will also need to stipulate the Client ID criteria
here. Put all the site detail text boxes in the detail section of your form.

 

[Guest]

Hi Brian

Thanks for your advice. That all appears reasonably straight-forward. I will
give it a try and get back to you if I have further problems.

Regards

David

In the form header, make an unbound combo box ClientID. Make its RowSource
the ClientID & ClientName. Bind column 1, and make column widths 0;1. Add
this code;

Private Sub ClientID_AfterUpdate()
Site = null
End If

In the RowSource of the (unbound) Sites combo box (also in the form header),
build your query from the Sites table, and have SiteID, SiteName, and
ClientID. In the criteria for ClientID, enter this:
[Forms]![thisFormName]![ClientID]

In the Enter event of the Sites combo box, have this:

Private Sub Sites_Enter()
Sites.Requery
End Sub

This in AfterUpdate

Private Sub Sites_AfterUpdate()
Me.Requery
End Sub

The sites combo box will now show sites for only the currently-selected
client.

Make the RecordSource of your form the site details with
[Forms]![thisFormName]![SiteID] as the criteria for SiteID. If more than 1
client shares a site, you will also need to stipulate the Client ID criteria
here. Put all the site detail text boxes in the detail section of your form.

I have a database that tracks clients, their sites, and my staff that work
there. I have approximately 50 clients; each client may have a number of
sites that I maintain (anything between 1 and 40). I have a number of
employees that work at one or more site. Some clients may have more than one
of my employees working at their site. Keeping track of who is working where
can be difficult.

My database includes an employee table, a client table, a site table, and a
number of other tables that are not relevant t my table. I want to create a
form that will give me a drop-down list of clients so that if I select a
client I can see a drop-down list of the sites that they have which if I make
a selection will present me with site details in form view not datasheet
view. I also want to do something similar with employee information (see who
they work for and then at what sites).

Is this possible and if so, how?

Thanks

David