Can't enter data in related table (1 Viewer)

david.paton · Apr 12, 2018

I originally had a notes table in which I was going to store notes regarding everyone in the various tables, such as carers, notes, children, etc. I couldn’t get that working so I decided to create a table for each entity that I wanted to record notes for but that won’t work either. I don’t understand why as there is a field linking the two tables together in a one to many relationship.

I am trying to link a table, tblCaseManagers to a table to write notes in, tblCMNotes. It looks like the relationship is entered correctly but when I go into design view and enter the fields from tblCMNotes, CMNotesDate and CMNotesNotes I have a problem. I enter form view and the table just goes blank from the form header down.

I have just realized that I just reopen the form without saving it and the form appears to now be only able to enter data.

Could someone help me please?

bob fitz · Apr 12, 2018

In the attached db I used the wizard add a sub form for the notes. A sub form is usually used for the many side of a one to many relationship.
I have changed the main forms DataEntry property to NO and the AllowEdits property to YES. Hope this helps.

david.paton · Apr 12, 2018

That was great, thanks Bob. I tried to do that but when I dragged the notes form onto the carer form, I deleted the ID field instead of hiding it. You helped me work that out so thanks

david.paton · Apr 12, 2018

Could you help me with another problem I have now please? I need a way to assign a case manager and a carer to a child, or for that matter, could you please tell me how to do this as I keep encountering this problem with other areas of this and other dbs.

This will need to have an option to change it, as the child may be assigned a different carer or case manager at a later date. I have been searching a lot online for how I can do this but I just didn’t know what to search for.

My attempts were in the frmChildOld and frmChildold2.

mike60smart · Apr 12, 2018

Hi David

See the attached

View attachment Carers.v10.accdb.zip

david.paton · Apr 13, 2018

mike60smart said:
Hi David

See the attached

View attachment 70363

The row source for the combo box, cboCarer in the form, frmChildCarerssubform is SELECT tblCarers.CarersID, [CarersLastName] & " ," & [CarersFirstName] AS Carer FROM tblCarers ORDER BY tblCarers.CarersLastName;

Could you explain this to me please?

david.paton · Apr 13, 2018

SELECT tblCarers.CarersID, [CarersLastName] & " ," & [CarersFirstName] AS Carer FROM tblCarers ORDER BY tblCarers.CarersLastName;

I understand that you are selecting the carerID row from tblCarers to identify that record.

I just had some questions:

1. Do the brackets specify field data that is shown for that record selected?
2. Why does it say AS Carer?

I also tried it myself:

SELECT tblCarers.CarerID, [CarersFirstName]&" "&[CarersLastName] AS Carer FROM tblCarers ORDER BY tblCarers.tblCarersFirstName;

Where did I go wrong as it won't work?

mike60smart · Apr 13, 2018

Hi David

The Row source for the cboCarer is using a Select Statement.

To see the Select Statement - go into the Design View of the Form and if you then click into the Row Source Row you will see 3 dots at the end (...)
Click on this and it will open the Select Statement as shown below:-

The column for the Carer is shown using what is known as an Alias

So I Type the name "Carer:" followed by a colon and then I Concatenate the 2 fields Lastname & Firstname as shown

Best to use the Lastname to Sort by as it makes it easier to find the Carer

david.paton · Apr 14, 2018

I had a couple more questions. What are the square brackets used for and could you explain to me what "AS carer" does please?

mike60smart · Apr 14, 2018

Hi David

In my previous post I explained how to create a new Column (Field) in the Query grid by typing Carer in a new Column.

This would then display in SQL as this:-

Code:

SELECT tblCarers.CarersID, [CarersLastName] & " ," & [CarersFirstName] AS Carer
FROM tblCarers
ORDER BY tblCarers.CarersLastName;

To see this SQL View when you are looking at the Row Source Select statement in the Query, if you Right-Click in the area that displays the 2 tables and select SQL View you will see what I mean.

david.paton · Apr 14, 2018

So you basically write a query to display in the combo box?

mike60smart · Apr 14, 2018

Hi David

No The Select Statement is created by the Combox Wizard which you can then modify by using the Method I described.

When you create the Combobox initially it would look like this when you look at the Row Source:-

You would then modify this select query so that it would now look like this:

All we have done is Concatenate the Surname & First name by using Concatenation i.e.

[CarersLastName]&" ,"&[CarersFirstName]

The we name this string using the field name of "Carer:" to make it like this:-

Carer:[CarersLastName]&" ,"&[CarersFirstName]

You can also create a query as normal and save it with a name and then when you create the Combobox you can make the Row Source the name of the query

david.paton · Apr 16, 2018

What do I need to do to get my subforms on my child form to look like yours:

I can only get my form to look like this:

I understand you need to set it to a continuous form but how do you seperate the label from the text box for each field to put in the header?

david.paton · Apr 16, 2018

I worked that bit out, thanks anyway

Can't enter data in related table (1 Viewer)

david.paton

Registered User.

Attachments

bob fitz

AWF VIP

Attachments

david.paton

Registered User.

david.paton

Registered User.

Attachments

mike60smart

Registered User.

david.paton

Registered User.

david.paton

Registered User.

mike60smart

Registered User.

david.paton

Registered User.

mike60smart

Registered User.

david.paton

Registered User.

mike60smart

Registered User.

david.paton

Registered User.

david.paton

Registered User.

Similar threads

Users who are viewing this thread