This is the code I am using for the form I have to enter and/or edit scores.
The other code was from my form for courses and was triggered by a command
button. I hope that this is more helpful.
Private Sub cboTees_AfterUpdate()
'Fill form with course details ie par, yardage,handicap
Dim FindIt As Integer
Dim MyForm As Form
Dim i As Integer
Dim db As Database
Dim rst As Recordset
Dim Criteria As String
Dim ParControl, YardControl, HandicapControl, PuttControl, ScoreControl
'Check to see if Course & Tee exist
FindIt = DCount("[CourseID]", "tblCourseDetails", "[CourseID] = " &
Me!cboCourse & " And " & "[teeID] = " & Me!cboTees)
Set MyForm = Forms!frmRoundsNew
Set db = CurrentDb
Criteria = "[CourseID] = " & MyForm!cboCourse & " and " & "[TeeId] = " &
MyForm!cboTees
Set rst = db.OpenRecordset("select * from tblCourseDetails " & _
"Where " & Criteria)
'"[CourseID] = " & MyForm!cboCourse & " and " & "[TeeId] = " &
MyForm!cboTees)
'Fill fields from recordset
If FindIt > 1 Then
rst.MoveLast
rst.MoveFirst
'Fill the form from the recordset
For i = 1 To 18
ParControl = "Par" & i
YardControl = "Yardage" & i
HandicapControl = "Handicap" & i
'PuttControl = "Putt" & i
FHControl = "FH" & i
'GIRControl = "GIR" & i
Me(ParControl).Value = rst!Par
'Disable the FairwayHit Control on Par 3s
If Me(ParControl).Value = "3" Then
Me(FHControl).Enabled = False
Else
Me(FHControl).Enabled = True
End If
Me(YardControl).Value = rst!Yardage
Me(HandicapControl).Value = rst!Handicap
rst.MoveNext
Next i
Else 'Clear all the fields and get ready for new record
For i = 1 To 18
ParControl = "Par" & i
YardControl = "Yardage" & i
HandicapControl = "Handicap" & i
ScoreControl = "Score" & i
PuttControl = "Putt" & i
FHControl = "FH" & i
GIRControl = "GIR" & i
Me(ParControl).Value = 0
Me(YardControl).Value = 0
Me(HandicapControl).Value = 0
Me(ScoreControl).Value = 0
Me(ScoreControl).ForeColor = 0
Me(PuttControl).Value = 0
Me(FHControl).Enabled = True
Me(FHControl).Value = False
Me(GIRControl).Value = False
Next i
End If
Score1.SetFocus
End Sub
Mark Senibaldi said:
When using this code, I am getting a compile error (sub or Function not
defined) on line: HolePar(i) = Me(ParControl).Value
I'm using this code on the afterupdate event on my cbotee since that should
drive what information is being shown on the for as far as Par, Yards, etc...
goes.
I created an unbound form with text boxes and named them Par1, Par2, etc...
Yards1, yards2, etc...
Any thoughts? Do i need a separate sub or function in my modules?
--
MSS
Barry said:
Wow! Sorry I've been MIA for all of this. I have created a form with
unbound text boxes to hold my data. I have named them Par1, Par2, Yards1,
Yards2, Handicap1, Handicap2 etc. Then I looped through the form to load an
arrays for each of the 18 elements. Next I check to see if the round is new
or if I am updating a record and perform the appropriate action, AddNew or
Edit. I don't know if it's the best job in the coding world but it looks
like this.
Dim ParControl As String, YardsControl As String, HandicapControl As
String
'Load up All the Arrays
For i = 1 To 18
ParControl = "Par" & i
HolePar(i) = Me(ParControl).Value
YardsControl = "Yardage" & i
HoleYards(i) = Me(YardsControl).Value
HandicapControl = "Handicap" & i
HoleHandicap(i) = Me(HandicapControl).Value
HoleNumber(i) = i
Next i
'Set criteria to check if record with course and tee ids exist
'Add if new record, edit if already exists
Criteria = "[CourseID]=" & MyForm!CourseID & " and " & "[TeeID]=" &
MyForm!cboTee
Set rst = db.OpenRecordset("select * from tblCourseDetails " & _
"Where " & Criteria)
If rst.RecordCount = 0 Then 'Add new record
For i = 1 To 18
rst.AddNew
rst!CourseID = MyForm!CourseID
rst!TeeID = MyForm!cboTee
rst!HoleNumber = i
rst!Par = HolePar(i)
rst!Yardage = HoleYards(i)
rst!Handicap = HoleHandicap(i)
rst.Update
Next i
Else
For i = 1 To 18
rst.Edit 'Edit existing record
rst!CourseID = MyForm!CourseID
rst!TeeID = MyForm!cboTee
rst!HoleNumber = i
rst!Par = HolePar(i)
rst!Yardage = HoleYards(i)
rst!Handicap = HoleHandicap(i)
rst.Update
rst.MoveNext
Next i
End If
rst.Close
db.Close
Let me know what you guys think! Thanks.
Barry
:
Thx. That must be what I'm missing (the VBA coding to do this). Does anyone
know where I can find a sample dbase that does this?
--
MSS
:
If the form were unbound then you would use recordsets and vba to
populate the controls. And then after the user saves the record you
would use vba to save the data.
On Sep 14, 2:58 pm, Mark Senibaldi
How would I get reference data on the form if it's unbound? For example, I
would like to see the yardage & par for each hole that I am entering data
for. I would like to enter Strokes, Fairways, GIR and # of Putts on the
form. See below for example.
Course: PineValley Golf Course
Hole: 1 2 3
Yards: 315 500 165
Par: 4 5 3
Strokes:
Fairways:
GIR:
Putts:
--
MSS
:
The form would be unbound and you would use recordsets to update the
data.
Place a bunch of text boxes, one for each hole, then when you select
the round and populate the data and click save, the recordsets get
updated.
On Sep 14, 1:58 pm, Mark Senibaldi
Even if I hardcode field names, how would I create a form to input values
Horizontal as opposed to vertically? I can set the form up to do this, but
only by setting the form properties to "single form" but then I can only see
1 hole at a time. I must be missing something- possibly VB Code- that allows
the user to input data Horizontally on a form that uses a vertical table as
the record source. Should I create a temporary table that is Horizontal and
append each value to the Round_Details table?
--
MSS
:
Your table exactly matches Barry's Course Detail table.
Anyways, Barry has assumed that there are 18 holes on a golf course so
he's hardcoded the fields onto his form. Your method works
dynamically and will have to continue to be vertical or follow Barry's
method. Cross-Tab Queries are not updatable as far as I can tell.
Cheers,
Jason Lepack
On Sep 13, 10:26 am, Mark Senibaldi
Thx. My table structure is set up almost identical, with the exception of I
have an additional table:
tbl_CourseHoles
HoleID
CourseID
TeeID
Hole
Par
Yardage
ReportDate
My dilema is figuring how to input my scores(strokes, Fairways, GIR, putts,
etc... onto a form that goes from left to right (like a typical score card)
as opposed to the way I currently have it up and down in a continuous form.
I tried using a crosstab query, but wasn't able to update the tables this way
(it's quite possible I did something wrong) I was hoping to get some guidance
on how he accomplished this.
--
MSS
:
Mark,
Barry's table structure is as such: (based on a previous post)
tblCourse
CourseID
Name
Address
etc.
tblCourseDetails
CourseID
TeeID
Hole
Par
Yardage
Handicap
tblRounds
RoundID
CourseID
GolferID
etc
tblRoundDetails
RoundID
Hole
Score
The CourseDetails table holds the data about the golf holes. The
RoundDetails table holds the scores that the player got on a hole in a
given round.
Cheers,
Jason Lepack
On Sep 13, 9:42 am, Mark Senibaldi
Thanks for the Info Barry.
Is your underlying table set up with a field name for each hole? Or do you
have one field called hole where each hole is a separate record? I have the
latter and am having a hard time figuring out how to create a form to enter
data from left to right. I was thinking of using a temp table that has a
field for each hole and then append the data to the main round detail table.
Any advice you could provide would be GREATLY appreciated.
