Updating a Junction Table

[oleBucky]

I am stumped again (which is not too tough to do).

I have a junction table (JT) I want to update via a form. The JT includes an ID from a list of players (PlayerID) and an ID from a list of teams (TeamID) as well as a unique autonumber field. Clearly, one team will have many players. In this situation, a player can play form many teams, hence the JT.

My form has two subforms both in Datasheet format. One lists the roster of a given team and the other allows users to query the table of players to narrow their selection to one category (usually 25 to 30 players). The user must choose the player to add from the category filtered list in hte subform. What I have been unable to make happen is to update the selected team with a selection from the player category. Clearly, this must involve adding a record to the JT with each ID number.



I have experimented with a field looking to the "active" record as selected on the category subform then appending the JT with that value as well as the ID value from the selected team by using a button. However, my knowledge of VBA (or lack thereof) did not allow me to find a set of commands to make that happen. If there is a better method, I am open to suggestions and willing to experiment further.

[oleBucky]

OK... 21 view and no replies. Let my try to narrow things a bit.

Question 1
If I have a subform in datasheet view, how can I best refer (use the primary key value from) the selected record in that datasheet?

[ketbdnetbp]

OK... 21 view and no replies. Let my try to narrow things a bit.

Question 1
If I have a subform in datasheet view, how can I best refer (use the primary key value from) the selected record in that datasheet?

Question 1
If I have a subform in datasheet view, how can I best refer (use the primary key value from) the selected record in that datasheet?[/QUOTE]

I don't know if it's best but, you could try something like:

OnCurrent Event(Subform):

If Me.NewRecord = false then
dim myX as long (Assuming that the PK is a long data type)
myX = [forms]![MyFormName]![MySubFormName].Form![MyPrimaryKeyName]
MsgBox myX
else
exit sub
end if

Replace with actual control names then, see if that gets you what you are looking for.

All the best !

[oleBucky]

Man! Regrets on the delayed reply. This "earn a living" thing has REALLY intruded on my learning to use Access (Although it must not be hard to tell, I don't work with Access for a living). I will give this a shot as soon as I can and get back with the results.

[oleBucky]

Great! That provided a variable with the selected PlayerID I could pass to the main form.

My main form uses the JT as a record source. The JT has only two fields, one for TeamID and one for PlayerID; both related to other tables (duh!). In the main form I have a combo box to select the appropriate team record from one contributing table (TeamID) and now a text box which holds the variable corresponding to the PlayerID selected from a sub form.

I've been fumbling with using a command button to add a record to the JT passing the TeamID from the combo box and the PlayerID in the text box to the new record. Might I say, without success. I am confused about the syntax for the GoToRecord method and how to populate the appropriate field in the new record on the JT with the appropriate values from the form.

Am I even going about this in a reasonalble manner? If so, how might I get assistance with the code to do so? If not, suggestions regarding a more effective path are welcome!