Question Data from 1 field in 1 table to another

[Bhoc]

I have been knocking my head on this one.:banghead: Have been helped before in this forum with great results.
I have attached part of my database. 2 tables - the main table in players that has numerous fields. The second table is tee off times. (There are of course numerous other reports etc etc in the complete database but can't post all as it is too big)
Using the form shotgun tee off the user selects which players tee off with each other. What I want to do is have both the tee off time and tee number when created by this form to be copied to each record in players table under the appropriate fields labelled teeofftimeday1 and teeoffteeday1 respectively. Hope this makes sense

 

[GinaWhipp]

So, you want to overwrite the data that is in there with the new data for the matching Players?

 

[Bhoc]

Just the data in the tee off time and tee number fields so that it shows what time and tee that each player will tee off etc

 

[GinaWhipp]

Yes, I know that, what I am asking is if doing it that way will overwrite anything that is there (if there is anything there). Is that okay?

 

[Bhoc]

Gina sorry about that - I quickly answered your question on my way out. Yes it will need to overwrite anything in those fields thanks

 

[GinaWhipp]

No problem, will look at shortly...

 

[GinaWhipp]

So, is this what you are looking for?

UPDATE Players INNER JOIN tblTeeofftimesshotgun ON Players.PlayerID = tblTeeofftimesshotgun.Player1Id SET Players.teeofftimeday1 = [tblTeeofftimesshotgun]![Teeofftime], Players.Teeoffteeday1 = [tblTeeofftimesshotgun]![Teenumber];

 

[Bhoc]

Gina
thanks for that - dumb question but is this an after update in the form or players1 field or is it a new inner join query?????

 

[GinaWhipp]

It is for the After_Update event of the Player 1 field. However, if that is what you want will need to do the same for the other three Players but changing the number.

 

[Bhoc]

Thanks Gina -that's exactly what I need - will try in a couple of days when i get home

 

[GinaWhipp]

Okay, if you have any questions (or problems) post back, we'll be here!

 

[Bhoc]

Gina
Tried this code - continued to get compile error . I have other update procedures in there. Even removing them it did not make any difference. Tried placing brackets and parentheses in different spots - didn't make difference. have copied the code below
Private Sub Player1_AfterUpdate()
Me.player1h.Value = Me.Player1.Column(3)
Me.Player1Id = Me.Player1.Column(1)
Me.player1club = Me.Player1.Column(4)
Me.bringcart = Me.Player1.Column(5)
Me.needscart1 = Me.Player1.Column(6)
Update Players
INNER Join tblTeeofftimesshotgun ON Players.PlayerID = tblTeeofftimesshotgun.Player1Id
Set Players.teeofftimeday1 = [tblTeeofftimesshotgun]![teeofftime],
Players.Teeoffteeday1 = [tblTeeofftimesshotgun]![Teenumber];
End Sub

I know it is only something minor but it has me stuck

Steve

 

[GinaWhipp]

Okay, you can't just stick the UPDATE line in there like that. I didn't realize you were unaware of that. You need to use...

DoCmd.SetWarnings False
DoCmd.RunSQL = "UPDATE Players " & _
               "INNER JOIN tblTeeofftimesshotgun ON Players.PlayerID = tblTeeofftimesshotgun.Player1Id " & _
               "SET Players.teeofftimeday1 = [tblTeeofftimesshotgun]![Teeofftime], Players.Teeoffteeday1 = [tblTeeofftimesshotgun]![Teenumber];"
DoCmd.SetWarnings True

 

[Bhoc]

Gina

worked great thanks - am going into testing of database soon with full data - any problems will yell
Made small changes as below and it worked great
Dim SQL As String
DoCmd.SetWarnings False
SQL = "UPDATE Players " & _
"INNER JOIN tblTeeofftimesshotgun ON Players.PlayerID = tblTeeofftimesshotgun.Player1Id " & _
"SET Players.teeofftimeday1 = [tblTeeofftimesshotgun]![Teeofftime], Players.Teeoffteeday1 = [tblTeeofftimesshotgun]![Teenumber];"
DoCmd.RunSQL SQL
DoCmd.SetWarnings True

 

[GinaWhipp]

No problem with change and I'll be around if you start yelling!