Not loading the entire table in a form

[Lou_Reed]

In the attached file there is a db that has many errors cleaned up. There is one error that is not cleaned up and that is when I select a meeting date I want the entire date in tblPersonnel to load into sfrmAttendance. At the moment I only loads one employee, that is not what is supposed to happen. The sfrmAttendance is supposed to load all the employees from tblPersonnel. It does not.



This error must be corrected.

What is stopping it from working?

Any help appreciated. Thanks in advance.

Respectfully,


Lou Reed

[June7]

The form is not loading from tblPersonnel. It is loading from Query2. Query2 has filter criteria that retrieves only those records that have the meeting date selected in the combobox. Each meeting has only 1 person associated. If you want every person to be associated with selected meeting then need to enter records into tblAttendance.

If you want to have every person automatically associated with a meeting at the time that meeting is created, that will require code that runs an INSERT SELECT SQL statement.

CurrentDb.Execute "INSERT INTO tblAttendance(ysnMeetingID, PersonnelID) SELECT " & Me.cboMeetingID & " AS MID, PersonnelID FROM tblPersonnel"

For the existing meetings you will just have to either delete them and start over with the above code or manually enter all the records into tblAttendance.

[Lou_Reed]

I have uploaded two zip files. Each contains a db. One works and one fails. The one thta works has an object OptPersonnel. I am not sure what its purpose is.

The db that fails only has OptPersonnel in the SQL code for Query2. What is OptPersonnel and why is it here?
The db that works has it in several places, the one that fails only has it in Query2 SQL code.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

I really need an answer to the question previously posed. I have uploaded two files: one db works and one fails. I am not sure why that fails, fails.

It seems it should work. It should load the entire tblPersonnel in the frmAttendance subform, sfrmAttendance.

Why is it not doing so?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

If I run Query2 just by itself, I get the response shown in the screenshot. It does not show anything more. I am confused
as to why it is not running and showing things.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

Query2 has filter criteria that retrieves only those records that have the meeting date selected in the combobox.

I do not see the filter criteria, but wherever it is, it should be taken off Query 2 because I want all off personnel to load in sfrmAttendance.

How do I take it off.

Respectfully,

Lou Reed

[June7]

I already gave you an answer to the original question in post 2.

The filter criteria in Query2 is the WHERE clause. It references controls on the form for parameter inputs:

WHERE (((tblPersonnel.isDeleted)=False) AND ((tblPersonnel.Organization) Like IIf([Forms]![frmAttendance]![optPersonnel]=0,"*",[Forms]![frmAttendance]![optPersonnel])) AND ((tblAttendance.ysnMeetingID)=[Forms]![frmAttendance]![cboMeetingID]))

If you open the query when the form is not open, then the form controls are not available for the query to find and Access will prompt you for the input the query is looking for.

The filter criteria in Query2 restricts the records to the meeting selected in the combobox. Remove the MeetingID condition and you would see all records from tblAttendance regardless of meeting date but still won't get all records from tblPersonnel.

[Lou_Reed]

Thank you for the input. I will change the entry. I just could find anything about MeetingDate in the query as you described it. I understand the filter criteria is in the where clause. I just wanted to see it in the query as well.

Thanks for your input.

Respectfully,


Lou Reed

[Lou_Reed]

WHERE (((tblPersonnel.isDeleted)=False) AND ((tblPersonnel.Organization) Like IIf([Forms]![frmAttendance]![optPersonnel]=0,"*",[Forms]![frmAttendance]![optPersonnel])))
ORDER BY tblPersonnel.FirstName;

Here is the where clause of Query2 in my db. It was the file was attached and uploaded in Post #2. I can usually understand SQL code, but not this. It is Grek to me.

I see nothing about the MeetingDate in here.

Please explain how it is used.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

In the current db which is in the zipped file attached,, I have a unique issue with the frmAttachment. If one opens it up and before any meeting dates are selected
the roster has two entries of the exact same writing.

The writing is: #Name? Present. I ave uploaded a screenshot of it in the second attached file.

How did thus get there and how do I get rid of it?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

I really need an answer to this question. It seems to have crept up on me. I am not sure why this entry is here now on the frmAttendance form. It certainly was not there in any earlier
iterations of the db. Why is there now?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[June7]

There are two copies of the db in that zip file (there is a subfolder in the zip). One would have been enough to download.

I was working with the first posted db. At some point you removed the MeetingID criteria from the query. As stated in post7: "Remove the MeetingID condition and you would see all records from tblAttendance regardless of meeting date but still won't get all records from tblPersonnel."

There is #Error in Name textbox because there is no [Name] field in Query2, there is LastName and FirstName. To see the names for each record, put an expression in textbox: =[FirstName] & " " & [LastName]

It is still not clear to me what the purpose of the subform is. Should it show only the tblAttendance records associated with date selected in combobox?

[Lou_Reed]

The subform should show all of the employees in a certain branch. Then the BrancHead can mark them off as either present or absent. Then that info goes to tblAttendance. Then the rptAttendance can be created.

So each branch employee in every MeetingID and MeetingDate. Obviously, MeetinggID and MeetingDate will go alongside off each other.

Then I will create for with MeetingDates a form with a combo box for all meetings. The value will be MeetingID, but it will show MeetingDates in the drop down menu.
Then one can see who attended the meeting and on what date, who was absent or present and who was government and who was contractor. Also, since contractors work at different companies
this can be further broken down into company.

But first I need to get all of the members of a branch into the sfrmAttendance; not just one. From there the BranchHead or assistant can record present or absent. Then that info goes into tblAtttendance.

I then work from there.

I have your previous post and will work from it.

thanks for all of your help.

Respectfully,

Lou Reed

[Lou_Reed]

I thought that you said to remove MeetingID, I did it as a test. I wanted to see what happened. I take it putting MeetingID back in the query design would not be sufficient. That something else must be done. What is that? Just putting

=[FirstName] & " " & [LastName]

into the SQL code or the design?

I took out MeetingID because it seemed the correct thing to do. This seemed to make things worse.

Hence, the post.

[Lou_Reed]

I put the MeetingID section back in the SQL code and it works like it used to. At least I get no error like I showed before in post #10. Now I just want as I said all of the members of a branch to fall into sfrmAttendance. Then I can go from there.

How to do this

That is all that I am after.

Respectfully,


Lou Reed


Respectfully,


Lou Reed