Form not opening

[david.paton]

I have changed something and now my form (frmCarers) won't open. Could someone show me what is wrong with it please? It displays an error box that says ID.

Thanks,
Dave

 

[pbaldy]

Opens fine for me. What exact steps are required to recreate the problem with this sample db?

 

[david.paton]

Opens fine for me. What exact steps are required to recreate the problem with this sample db?

I open the db then try and open the form and it gives me an error and I don't know why. It displays a little window saying "enter parameter value" in the heading and says "ID" below.

 

[pbaldy]

In this sample db? All forms open without error for me. The parameter prompt is Access telling you it can't find something, so make sure that field is present in the record source in your real db if this is just a sample.

 

[david.paton]

I must have zipped the wrong file but I have worked it out now. Thanks anyway

 

[pbaldy]

No problem.

 

[david.paton]

I now have a problem that I am sure I have encountered before but I don't know how to solve it. I try and enter carer notes into frmcarers and I get the error message: you cannot add or change a record because a related record is required in table 'tblchild'. This doesn't make sense as you should be able to enter carers into the db before you enter children. What do I do and how do I avoid it happening?

I have uploaded a recent version of the db.

 

[david.paton]

In addition to the previous problem, when I open frmcarers, nothing is displayed, despite having data in the db. I have checked the allow edits property and it is set to yes.

 

[Gasman]

In addition to the previous problem, when I open frmcarers, nothing is displayed, despite having data in the db. I have checked the allow edits property and it is set to yes.

Well the query that is the recordsource does not supply any data?

SELECT tblCarers.*, tblNotes.NotesRecords, tblNotes.NotesDate, tblTown.TownsName, tblTown.TownsPostCode, tblState.State
FROM tblState INNER JOIN (tblTown INNER JOIN (tblCarers INNER JOIN tblNotes ON tblCarers.[ID] = tblNotes.NotesCarerID) ON tblTown.[ID] = tblCarers.CarersTownID) ON tblState.[ID] = tblTown.TownsStateID;

 

[isladogs]

As Gasman explained, the SQL for your form record source returns no records.
Try changing to use one or more outer joins & it will return data where it exists. Updated db attached

SELECT tblCarers.*, tblNotes.NotesRecords, tblNotes.NotesDate, tblTown.TownsName, tblTown.TownsPostCode, tblState.State
FROM tblState LEFT JOIN (tblTown LEFT JOIN (tblCarers LEFT JOIN tblNotes ON tblCarers.[ID] = tblNotes.NotesCarerID) ON tblTown.[ID] = tblCarers.CarersTownID) ON tblState.[ID] = tblTown.TownsStateID;

 

[david.paton]

Thanks but I found the answer, I just had to set data entry to no and allow edits to yes on the form.

 

[missinglinq]

...I just had to set data entry to no and allow edits to yes on the form...

Sure...despite its name, Date Entry does not have to be set to Yes in order to enter data! Having it set to Yes means that you can only enter new Records! Existing Records will not be displayed...hence your 'empty' Form.

Linq ;0)>

 

[Gasman]

Thanks but I found the answer, I just had to set data entry to no and allow edits to yes on the form.

The DB I downloaded, already had those settings for the form?

 

[isladogs]

The DB I downloaded, already had those settings for the form?

Also the case when I looked at it.
As already explained there was no data shown as your SQL returned no records

 

[david.paton]

I have some more specific questions.

1. Regarding frmCarers,
a. I already have some data in the form when I open it and when I tried to add a comment, I got the error You cannot add or change a record because a related record is required in table ‘tblChild’. I am not an access expert and have been getting that error a lot so can someone explain it to me so that I can understand it and I will know how to fix the problem please?


2. I have one notes form for all my tables, but should I have a notes form for each table as they all seem to be having the above problem?


Thanks

 

[Gasman]

You have made tblNotes a 1 to many from tblChild. To have a record in tblNotes, you first must have a record in tblChild, that will be the parent of the tblNotes record.

 

[pbaldy]

tblNotes is related to tblChild, with referential integrity enforced. You can't enter a note for a child that isn't in the child table. If you want to leave it blank, there would need to be a Null entry in tblChild.

 

[pbaldy]

Ah geez, I didn't realize there was a second page. Sorry Gasman. :banghead:

 

[Gasman]

Ah geez, I didn't realize there was a second page. Sorry Gasman. :banghead:

No probs. Good to get confirmation I was on the right track.

 

[david.paton]

tblNotes is related to tblChild, with referential integrity enforced. You can't enter a note for a child that isn't in the child table. If you want to leave it blank, there would need to be a Null entry in tblChild.

The problem with that is I can't enter any notes on the form for a previous record as it won't show any previous records. It will only show new records and I don't know why.