Not loading the entire table in a form

[Lou_Reed]

However, now Qery3 is messed up. It uses Query2 says something like file renamed or renumbered. They have been.

Do I just redo query3 and use Query2 as a Source. it would seem the easiest way?



Respectfully,

Lou Reed

[June7]

Some records in tblPersonnel do not have Organization value, most of them have 1 as the value. Enter more varied Organization values and you will see the list change in the subform when each radio button is pressed.

Again, the records displayed on this subform are dictated by records in tblAttendance.

[Lou_Reed]

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

This is the SQL code you said to use if I wanted all branch members to be associated with each meeting data. that is fine, but where to put it?

I obviously have repaired the query, but where does this go?

The original and working query is:

SELECT tblAttendance.Status, tblAttendance.Notes, tblPersonnel.isDeleted, [LastName]+",  "+[FirstName] AS Name, tblPersonnel.Organization, tblPersonnel.LastName, tblPersonnel.FirstName, tblAttendance.ysnMeetingID
FROM tblPersonnel INNER JOIN tblAttendance ON tblPersonnel.PersonnelID = tblAttendance.PersonnelID
WHERE (((tblPersonnel.isDeleted)=False) AND ((tblPersonnel.Organization) Like IIf([Forms]![frmAttendance]![optPersonnel]=0,"*",[Forms]![frmAttendance]![optPersonnel])) AND ((tblAttendance.ysnMeetingID)=[Forms]![frmAttendance]![cboMeetingID]))
ORDER BY tblPersonnel.LastName, tblPersonnel.FirstName;

So where does your line go?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[June7]

That line would go in some event, perhaps a button Click or combobox AfterUpdate. That is for you to determine based on your preferences and business process.

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

That code will create records in tblAttendance to associate EVERY person from tblPersonnel to the meeting selected in cboMeetingID. And if you want to make sure there are no duplicate entries (maybe user accidentally clicks button twice), suggest setting tblAttendance with compound index to prevent duplicate ysnMeetingID and PersonnelID pairs.

[Lou_Reed]

SELECT tblAttendance.Status, tblAttendance.Notes, tblPersonnel.isDeleted, [LastName]+",  "+[FirstName] AS Name, tblPersonnel.Organization, tblPersonnel.LastName, tblPersonnel.FirstName, tblAttendance.ysnMeetingID
FROM tblPersonnel INNER JOIN tblAttendance ON tblPersonnel.PersonnelID = tblAttendance.PersonnelID
WHERE (((tblPersonnel.isDeleted)=False) AND ((tblPersonnel.Organization) Like IIf([Forms]![frmAttendance]![optPersonnel]=0,"*",[Forms]![frmAttendance]![optPersonnel])) AND ((tblAttendance.ysnMeetingID)=[Forms]![frmAttendance]![cboMeetingID]))
ORDER BY tblPersonnel.LastName, tblPersonnel.FirstName;

Now where does this query go. I would imagine that it stays right where it is.

The INSERT query as you aid goes into the afterupdate.

Is this true?

I at first thought it was just another line in the original query we discussed, query2; which would make it a compound query.

You said this is separate from query2. Correct?

Please elaborate.

Respectfully,


Lou Reed

[Lou_Reed]

And if you want to make sure there are no duplicate entries (maybe user accidentally clicks button twice), suggest setting tblAttendance with compound index to prevent duplicate ysnMeetingID and PersonnelID pairs.

Please explain this statement. I clearly do not want duplicates in my db.

I already have a check for duplicates query in my db.I inherited it along with this project.

Any help appreciated. Thanks in advance.

Respectfully,


Lou Reed

[Lou_Reed]

I am curious abut the statement that you gave me above. I have put in the after update event for the cboMeetingID. it is of course SQL code, but I have seen SQL code
in after locations before. I think that is alright.

However, the attachment before it is another thing entirely. The CurrentDb.Execute prefix I assume goes before the SQL in the after update location. Is this correct?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

I tried what you said and the result was a error message that MS Access cannot find...

The db is in the zip file and the screenshot is in the other file. What do I need to change to make this
right?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[June7]

Remove that SQL statement from the OnChange event property. Should be [Event Procedure] then put code from post 19 into the VBA. As well as Me.sfrmAttendance.Requery.

Code looks like:

Private Sub cboMeetingID_Change()
CurrentDb.Execute "INSERT INTO tblAttendance(ysnMeetingID, PersonnelID) SELECT " & Me.cboMeetingID & " AS MID, PersonnelID FROM tblPersonnel"
Me.sfrmAttendance.Requery
End Sub

Right now, if user later selects a meeting previously selected, then another set of personnel records for that meeting will be saved to tblAttendance. To prevent this suggest you set compound index in the table. Delete records from tblAttendance and then set the index as shown in image.

[Lou_Reed]

I used your syntax in the last line of the onchange Sub, but still got an error when I tried to update after selecting the meeting date.
It is error 3061.

What is wrong here?

Respectfully,

Lou Reed

[June7]

You did not exactly follow the code example. Must concatenate the combobox.

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

[Lou_Reed]

Okay, what did I do wrong? I thought that strQuery must be on one line and must not have double quote (actually no quotes single or double) and no &.

It seems that I am wrong.

The strQuery must be devoid of double quotes?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

Okay, I made the correction. The tblAttendance took everyone except Boris Karloff. Why was he left off?

Also, I thought that strQuery had to be one line and no double quotes? Is this ture.

Respectfully,

Lou Reed

[Lou_Reed]

It seems in tblAttendance that Boris Karloff is in there, his PersonnelID is 3. But he does not show up in sfrmAttendance. That is confusing to me.

Also, do we use a composite or a compound key? I am not sure of the difference.

Since, I have run the query twice now for the same MeetingDate, I guess that I must delete all the rows or something will happen when i put in the composite key in tblAttendance?

There are many duplicates in tblAttendance right now.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

I really need an answer as to why Boris Karloff was let out in sfrmAttendance. He is in tblAttendance.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed