Need some help

[The Rev]

So far no one has been able to help me. I have 3 tables. One is for Members. I have each member assigned to a unique MemberID. The Second is for classes. I have each class assigned to a unique ClassID. The third table is being used so each member can be assigned to all classes. I have designed a form to add a new user with a subform to assign the classes to that new person. I would like to automate the process so when I add a new member I don't have to manually add each ClassID to the third table via my subform. Any clues????

 

[ColinEssex]

Hi

Sorry if I appear a bit thick today, just trying to interpret your prob.

So basically what you're saying is that everytime you add a new member to your Db you also want them to be added to the classes table?

So if you have 6 classes numbered 1 to 6 then each new member is added to the classes table 6 times (one for each class)

Col

 

[The Rev]

Thanx for replying Colin. Not exactly what i need, but close. When I add a new member, I want to pull all of the existing classes out of my Classes table and add them to my Training table with the new MemberID assigned to them. Example... In table 1 (Members) I have Member ID (AutoNumber), last name and first name as my column identifiers. In table 2 (Classes), I have Class ID (AutoNumber) and Class name. In Table 3 (Training), I have RecordID (AutoNumber) MemberID, ClassID, and expiration date. I created a form to add last name and first name, which will generate a new autonumber. On the form I want to have all of the classes populate my training child subform instead of adding each one individually thru the ClassID field in my training table. In a round about way it makes the additions to the end of my training table. I'd like to have it automatically pull the data instead. Any clearer??

 

[ColinEssex]

Perhaps it might be better if you post your Db here and then it may be clearer to understand.

When you say-

"I want to pull all of the existing classes out of my Classes table and add them to my Training table with the new MemberID assigned to them."

I thought that was the same as I said..........

Col

 

[The Rev]

Ok, Here's the a picture of some of the Db. Please no laughs. I am very unskilled at Access. What I want to do is on the switchboard. When I open it with switchboard command 2 for add to the end mode(??) it comes up with no classes listed and no name. when I click this button, I want a blank name to come up and I want it to come up with all 33 ClassID's like when I open it up for an existing person. I was thinking maybe a macro here?? or not?? Do I need to start from scratch?? I hope not. If adding manually is the only way, I can leave it as is. Thanks a lot.

Bill

 

[ColinEssex]

Hi

It looks like your subform is linked to the main form by person ID or something - so if there's no person shown in the main form there's no link, therefore your subform will show no records.

Why do you want to show the classes like this?

Col

 

[The Rev]

It's the best thing I could come up with to suit my need to add someone. Any suggestions on how I could add someone and have all 33 ClassID added in Training table with the new MemberID?

 

[ColinEssex]

We don't seem to be getting very far do we!!!

Ok - here's a scenario

You add a new member to the members table, that member needs to be automatically added to the training table 33 times (one for each class) right???


Col

 

[The Rev]

Yes. That is correct. I'm sorry my knowledge of Access is so limited!! I apreciate your patience

 

[Rich]

What's the objective behind adding a member ID to the classes table en-masse
Does it mean you expect them to attend all classes or that they have attended all classes, I suspect there's a better way than filling a table with what may be redundant data

 

[The Rev]

They have to attend all classes. My objective is when we get a new member in, I can add their Last name and first name and schedule all 33 classes at once.

 

[Rich]

Use an Append query, set the criteria for the pupilID to that of the Main form, the code builder will do that part for you, test it on a copy of your db before you use it, to check that it's returning the correct records click on the datasheet icon from the toolbar, don't select Run untill you satisfied with the results

 

[The Rev]

Well, I went ahead and did the append and it added everything all over again. I need it to only add unique records from my member table. For example, if I already have MemberID 2 taking ClassID 1 I don't want it to duplicate. But if I have just added new MemberID 35, I want it to append all 33 records. What kind of criteria statement do I need in the MemberID field??

 

[Rich]

Forms!MyForm!MyIDfield, if you click on the Code builder while in the criteria field it will add the correct reference, you may have to save the record first

 

[The Rev]

Sorry, I'm kinda thick!! I've built my append query. I have no idea where to go from here. Here's a picture of the query. When I add a new MemberID in Members table, I want it to add the new id with each class (each member ID is listed 33 times , once for each ClassID) to the end of my training table.

 

[The Rev]

Got the answer. I coded a button with this:

Private Sub cmdMyButton_Click(...)
Dim rstClasses As DAO.Recordset
Dim rstMemberClasses As DAO.Recordset
Dim strSQL As String

' "ClassID" below should be the name of the primary key field in the Classes table
strSQL = "SELECT ClassID FROM Classes"
Set rstClasses = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
' "ClassID" and "MemberID" in the below should be the foriegn key field
'names in your Training table
strSQL = "SELECT ClassID, MemberID FROM Training"
Set rstMemberClasses = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

Do While Not rstClasses.EOF
With rstMemberClasses
.AddNew
!MemberID = Me.[MemberIDFieldName]
!ClassID = rstClasses!ClassID
.Update
End With
rstClasses.MoveNext
Loop
Set rstClasses = Nothing
Set rstMemberClasses = Nothing
End Sub


Special thanks to paulf of utteraccess forum!!!!!
Feel free to use at your discression