Relationships & Tables

[brucey54]

Hi folks,

Please could you help me with the following?

Why is it when I click on the TblCarerdetails and try to open a record with the plus sign I am present with the following error message

“Enter Parameter value CarerID”

The frmCarerRota seems to be working ok, the relationships seem to be ok and they make sense to me i.e.

Carers can have many rotas i.e. a different rota each week

Clients can have different carers each week

Any help or feedback would be much appreciated

Tom

Happy New Year

 

[StarGrabber]

Hi brucey54,

seems that nobody takes care of you. So this will change immediately!

Open 'TblCarerdetails' in design mode, right click somewhere in the middle, go to 'Properties' and change the property 'Subdatasheet name' from 'Table.TblCarerDetails' to '[Automatic]'.

I'm not sure if these are the correct expressions because I'm not working with the english Access version.

 

[brucey54]

Hi brucey54,

seems that nobody takes care of you. So this will change immediately!

Open 'TblCarerdetails' in design mode, right click somewhere in the middle, go to 'Properties' and change the property 'Subdatasheet name' from 'Table.TblCarerDetails' to '[Automatic]'.

I'm not sure if these are the correct expressions because I'm not working with the english Access version.

Thank you StarGrabber, thank you very much.

Please could you help me with the following?

I would like to pre populate the rota table with the Client Name & TelNo via a Combo box

i.e. the Combo box would display the Clients names and depending on what name is select the TelNo field would then be filed in automatically.

I’ve had a lot of trouble with this as I’m not really sure where to store the Combo data
i.e. if I store the Combo data in the Client table.

I will have duplicate information i.e TeleNo and Combo numbers relating to a specific Clients.

Could you also give me any advice on my Relationships i.e

TblCare can have many TblRotas (1 – M)

TblRota can have many TblClients (1 – M)

Any advice would be much appreciated

Brucey54


Ps. Would I also be able to create reports including the Client 'address' from the FrmRota, without having the Clients address on the form?

 

[brucey54]

Hi brucey54,

seems that nobody takes care of you. So this will change immediately!

Open 'TblCarerdetails' in design mode, right click somewhere in the middle, go to 'Properties' and change the property 'Subdatasheet name' from 'Table.TblCarerDetails' to '[Automatic]'.

I'm not sure if these are the correct expressions because I'm not working with the english Access version.

Thank you StarGrabber, thank you very much.

Please could you help me with the following?

I would like to pre populate the rota table with the Client Name & TelNo via a Combo box

i.e. the Combo box would display the Clients names and depending on what name is select the TelNo field would then be filed in automatically.

I’ve had a lot of trouble with this as I’m not really sure where to store the Combo data
i.e. if I store the Combo data in the Client table.

I will have duplicate information i.e TeleNo and Combo numbers relating to a specific Clients.

Could you also give me any advice on my Relationships i.e

TblCare can have many TblRotas (1 – M)

TblRota can have many TblClients (1 – M)

Any advice would be much appreciated

Brucey54


Ps. Would I also be able to create reports including the Client 'address' from the FrmRota, without having the Clients address on the form?

 

[StarGrabber]

Hi brucey54,

I don't understand the need for pre populating the rota table with the Client Name & TelNo as you have a 'n:1' relationship to 'TblClientDetails' already. And 'pre populating' is against all database rules.

Why do you want to store the combobox data? You already have them in ''TblClientDetails'. Do I have an understanding problem here?

"TblCare can have many TblRotas (1 – M)" is correct.

"TblRota can have many TblClients (1 – M)" is not correct as 'TblRota' has no related child records (i.e. no plus sign) but parent records: precisely one in 'TblCarerDetails' and one in 'TblClientDetails'.

Regarding your last question ('Ps'): yes, you are able to create reports including the Client 'address' from the FrmRota. Well, not from 'FrmRota' directly but with a query similar to the following example:

SELECT TblRota.Datum AS Ausdr1, TblRota.[Start Time], TblRota.[End Time], TblClientDetails.ClientSurname, TblClientDetails.ClientID
FROM TblClientDetails INNER JOIN TblRota ON TblClientDetails.ClientID = TblRota.ClientIDFK
WHERE (((TblClientDetails.ClientID)=[Please enter a 'ClientID':]));

In your application you should then replace the expression "[Please enter a 'ClientD':]" by a control reference to 'FrmRota'.

 

[brucey54]

Hi StarGrabber, I understand what you are saying.

I’m trying to create a database that will hold the Client details and the Carer details.

Then I’m trying to create Rota table that will hold the rota details, each carer will receive a New Rota each week.

I am trying to make it as easy as possible for creating the rota's i.e. a Combo box to complete the Client details & Carer details. Then you only have to manually enter the Date & Time.

I have changed the Relationship as following;

TblCarer (1 – M) TblRota Each Carer can have many Rotas

TblRota (1 – M) TblClient Each Rota can have many Clients

I hope this makes sense.

Brucey54

 

[brucey54]

Hi StarGrabber,

I am having nightmare, I am trying to use Lookup field for Client details & Carer details.

On the FrmRota I am unable to select the Client names more than once before I am present with the following error message; this is driving me mad!!!

I have also attached the database

Error Message

“The Changes you requested to the table were not successful because they would create duplication values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.”

Brucey54

 

[StarGrabber]

Hi brucey54,

nightmare? Driving mad? - Let me put you out of your misery.

Lookup fields are a very practical thing... but not convenient in your case.

For a few hours I did like as it would be my database. See the result in the file attached.

It would take several pages to explain all my actions to you because I changed almost everything in your database! In the right way, I hope.

I recommend you to have a thorough look at my file. Compare properties, settings, values, etc.! Maybe you can take some advantage to continue the development of your file.

Of course this is still a 'building site'. - But just to give you an idea.

Note that 'frmRota' has a code module now. Without some lines of code there was no way (for me) to fill your lookup fields. As they are not bound to the form's record source, they're displayed in grey - and locked (see properties).

What to do next? Develop forms for the carer's and client's details.

Good luck.

 

[brucey54]

Thanks StarGrabber, I will have a look at the code and the changes you have made and try to understand it.

Brucey54

 

[brucey54]

Hi StarGrabber,

I need your help again, every time I open up the database I am presented with a message box asking me for the Formulare!FrmRota!CboCarerId

Could you please stop this, I would like to open the frmRota and use the navigation menu to find the carer or add a new record.

Thank you

Brucey54

 

[StarGrabber]

Replace "Formulare" by "Forms" in the criteria field of 'qryRotaDetailsPerCarer'.

I apologize for the detail I've missed.

 

[brucey54]

Hi StarGrabber, thanks you, your idea worked great, could you please help me with the following problem!

If I allocate a shift to a carer i.e. Joe Smith, date 14/03/2013 1700 – 1800hrs – would it be possible to include some sort of validation to make sure that this carer is not already booked out on another job at the same date and time?

If there is no validation and I have 100 cares on the database, how would I know if I’ve not already allocated him/her a shift already!

If I have no validation, I could double book the same carer out on two jobs at the sametime and date!!!! This would be impractical.

Brucey54

 

[brucey54]

Just a thought!!!

Would it be possible to remove the carer from the dropdown list from the FrmRota when they have been allocated a shift? And then have them remitted back on to the dropdown list the next day? This would minimise mistakes.

 

[StarGrabber]

With VBA nearly everything is possible, but this is not a good idea. Far too complicated. It would mean the carers are restricted to only one shift a day. But in post #12 you ask for a validation logic that refers to more than one shift a day. You'll find a solution for this case attached.

First I tried it using the DLookup function but it didn't work reliable (at least on my computer). Therefore I tried another way using a DAO QueryDef object (see code module of 'sfrRotaDetails').

 

[StarGrabber]

And here is a slightly improved version. Finally the DLookup function worked (so the code module of 'sfrRotaDetails' contains only one procedure now). Beyond that it is now possible to start a new shift at the same minute the previous one ends.

 

[brucey54]

Hi StarGrabber, thank you again, I don’t understand the code 100% but I can follow it to some extent.

I just have one small matter!

The FrmRota works great but if I select a carer from the dropdown list, the form will display all their shifts/clients.

So if the carer has worked for, let’s say 4 months and I then select him again from the dropdown list, the FrmRota will display all his shifts/Clients.

This would make working with the form quite difficult, would it be possible to stop this from occurring i.e.

If I select carer Joe Smith from the FrmRota and allocate him 3 shifts\Clients and then I select him again, the 3 shifts\clients will not show up on the new record?

I would like to print report for each rota, do you have any suggestions how I can achieve this, each FrmRota would need to be filtered to each report not really sure how to do this?

StarGrabber I would like to thank you for all your help.

Brucey54

 

[StarGrabber]

Hi Tom,

please find attached a different solution for your project.

 

[brucey54]

Hi StarGrabber, Thank you; I like your new design of the database, however I quite like having the carer details at the top and the client details at the bottom.

The reason for this, it’s a lot easy to work with the carer details at the top and the client details at the bottom. i.e. I can look at the carer’s availability before allocating them a shift,

The report should print off each carer rota separately i.e. carer name Joe Smith and his shifts i.e. his client’s names and times of care.

Would it be possible to have a week commenced start date and end date under the carer details on the rota forum?

I hoping to use the database to print off each carer rota week by week, and post the rota out to them, each carer will then have a rota for the whole week of their client’s times of care.

Brucey54

 

[StarGrabber]

Change whatever you want as convenient.

 

[brucey54]

Hi StarGrabber, I have tried to modify the database but I have totally failed, nothing seems to work any moreL

I have created a new form called FrmCarerNew

The carer combo box when changed will change all the carer’s names to the same name.

The client combo box will not retain or allow me to enter a new client name.

The validation code also does not work anymore, I have attached the database if you can offer any help it would be much appreciated.

Brucey54