Question Add Same clients/cleaners...

[DanJames]

Hi, I have two tables [tblPersonData] and [tblStaff]. When I go on a form to add a client (tblPersonData) there is a cleaner textbox with a dropdown list with cleaner names. I'd like it if when you select a cleaner and save the record for the database to copy the client name from [tblPersonData] to the last column of the selected cleaners field in the table [tblStaff]. So then both people are linked? Also if the last column on the [tblPersonData] has a value in it, it would be great if the table could add an extra column called Client# and copy it there. This may be confusing and there may be a better way to do it, but any ideas? Thanks in advance, Dan?

 

[Scooterbug]

Hi, I have two tables [tblPersonData] and [tblStaff]. When I go on a form to add a client (tblPersonData) there is a cleaner textbox with a dropdown list with cleaner names. I'd like it if when you select a cleaner and save the record for the database to copy the client name from [tblPersonData] to the last column of the selected cleaners field in the table [tblStaff]. So then both people are linked?

What you want to do is to store the PK value of one table into another when you need to save a reference. In your case, you would store the ID of the record that has the cleaner information in the client table. Now, if you want to either A) record the history of cleaners or B) if there can be more than one cleaner per client, you would create a Junction Table to store that data:

tblClientCleanerJunction
ClientCleanerID (PK)
ClientID (FK)
CleanerID (FK)

Also if the last column on the [tblPersonData] has a value in it, it would be great if the table could add an extra column called Client# and copy it there. This may be confusing and there may be a better way to do it, but any ideas?

No need to do that if the relationship between Person and Client is setup properly.

 

[DanJames]

OK so create a new table called
tblClientCleanerJunction
and then add those field names, in the design view?
I would like to do this, but unsure on how to link the cleaner with more than one client as they can clean for loads? and how to get in the tblpersondata cleaner name to have the cleaner, and in the tblstaff, to get all of the clients they are cleaning for?

Thanks, and please describe in easier terms, Dan.

 

[Scooterbug]

I would like to do this, but unsure on how to link the cleaner with more than one client as they can clean for loads?

Because the Junction table will hold the cleanerID and the ClientID in the same record, you can record multiple cleaner/client combanations.

and how to get in the tblpersondata cleaner name to have the cleaner, and in the tblstaff, to get all of the clients they are cleaning for?

You dont store the cleaner in the tblpersondata. Since the list of cleaners for any given client (ie tblPersonData) is stored in the Junction table, you dont need to store it in the tblPersonData. Besides, what happens if you send more than one person to clean a client? Same for storing the client in the tblstaff. With the junction table, you can easily, with a query, list all the clients a particular staff cleans for.

I've attached a simple database to illustrate. Form 1 will allow you to assign cleaners to a client. Form 2 will display the cleaner information and in the list box those clients they clean for.

 

[DanJames]

Hi, I have used this idea and integrated it with my database. I have had trouble with one of the forms as it says an error code and does not work, wheras the client form does. I will attach my database to this (.zip), and if it is possible for you to change one of the forms to work (the one that says type mismatch error doesn't work). Thanks in advance, Dan. PS also if you could check over the original code in this database, as it seems to be adding the same client to each client, on the form. Thanks.

 

[Scooterbug]

The problem is in the list box on the CleanerClientView. You have the PersonID linked, but in the tblPersonData you have the field as Text. Change the data type for PersonID in the tblPersonData to Number and you should be good to go.

also if you could check over the original code in this database, as it seems to be adding the same client to each client, on the form.

Not sure what you mean by that. Which form are you referring to? Also, what do you mean by adding the same client to each client???

 

[DanJames]

It all works great.. Now I'm wondering how you can delete a client/cleaner relationship in a form with a control source to tblcleaners. I'd like a button saying "Delete client cleaner relationship" - Any Ideas?