Combo box question

[MikeB]

I have a form to record the results of each chess game. There is a
white player and a Black player. On the form, the players simply note
whether the Result was White, Black or Draw.

On my form I've designed a combo box with White, Black and Draw as the
options.

When the combobox is changed, then I wrote some code to put the actual
scores into the Game record.

Unfortunately, if I don't actually click and reselect a value in the
combo box, then the code isn't driven, so if two consecutive White
wins need to be recorded, I still have to click the combo box.

I'd like to know what event at "add record" time I can use to look at
the combo box and update the score fields in the record.

Also, when browsing the database, the combo box field doesn't display
the results. So I would also like to know how to, on each new record
displayed, to look at the score and set the combo box accordingly.
Also, will this be sufficient for the first record or do I have to
also code the form open event to have the value displayed for the
initial record?

Thanks.

 

[Brian]

It is not clear from your post whether/where you are storing teh
White/Black/Draw.

You probably don't want the code that updates the table to run from the
change of the combo box.

Instead, make a button named Save or Post or somthing similar that uses the
current value of the combo box to create a new record in the table.

In the end, you will likely want, perhaps, to do the following:

Make your form a continuous-view form (if it does not have a lot of fields).
Bind your form to the table so that it reflects the existing records as well
as enables you to enter new records.
Have a "New game" button that simply goes to a new game. Keep in mind that
this will also save the contents of the current game, as will closing the
form.

 

[MikeB]

It is not clear from your post whether/where you are storing teh
White/Black/Draw.

My bad. The combobox is an unbound control. I'm not storing the value
anywhere, contrary to your next recommendation, this is unfortunately
exactly what I've been doing.

You probably don't want the code that updates the table to run from the
change of the combo box.

Why? How should I do this?

Instead, make a button named Save or Post or somthing similar that uses the
current value of the combo box to create a new record in the table.

The record has multiple fields, the MatchID as foreign key to the
Matches table, the GameID that is the Autonumber field for this game,
the WhitePlayerID and BlackPlayerID that are foreign keys for the
Players. And additional fields to indicate the type of game (there are
some special games such as Byes, BeginnerGames, LessonGames, etc.) as
well as a field to indicate whether the record has been used in
calculating the players' ELO rating.

So frankly, I don't understand how making a button is different than
clicking on the Add button that Access provide, unless you're
indicating that I should make all the controls on the form unbound,
and then I don't know how to retrieve the data when I want to browse
the Games table via the form.

In the end, you will likely want, perhaps, to do the following:

Make your form a continuous-view form (if it does not have a lot of fields).
Bind your form to the table so that it reflects the existing records as well
as enables you to enter new records.

OK, this is how it is already. Except it is not a continuous-view
form. That does sound like an interesting idea, as it may make for
faster data entry of all the games in a match. Right now I always have
to click the Add button to add each new record.

Have a "New game" button that simply goes to a new game. Keep in mind that
this will also save the contents of the current game, as will closing the
form.

Is this different from the first recommendation? And I still don't
understand how this recommendation helps me to go from the result
(White, Black, Draw) to the specific scores for the two players. If I
enter the result, I cannot make a mistake by accidentally entering
unmatched result values. If I enter the two players' results
separately, it is more typing, and I may make a mistake, then I have
to add code to compare the two fields and make sure the data in the
two result fields are consistent.

 

[MikeB]

I have a form to record the results of each chess game. There is a
white player and a Black player. On the form, the players simply note
whether the Result was White, Black or Draw.

On my form I've designed a combo box with White, Black and Draw as the
options.

When the combobox is changed, then I wrote some code to put the actual
scores into the Game record.

Unfortunately, if I don't actually click and reselect a value in the
combo box, then the code isn't driven, so if two consecutive White
wins need to be recorded, I still have to click the combo box.

I'd like to know what event at "add record" time I can use to look at
the combo box and update the score fields in the record.

Also, when browsing the database, the combo box field doesn't display
the results. So I would also like to know how to, on each new record
displayed, to look at the score and set the combo box accordingly.
Also, will this be sufficient for the first record or do I have to
also code the form open event to have the value displayed for the
initial record?

Thanks.

I've done some playing around with this in my test database.

I have found the Form Current event, that seems to be driven at the
beginning of each display of a record. If I put code in there then I
can look at (for instance) WhiteScore (field name in the Games Table
and bound control name) and based on it's value (1.0, 0.5, 0.0) assign
a value to the Combo-box (White, Draw, Black).

However, I ran into a snag. If I go to the end of the table to insert
a new record, the default display of the WhiteScore is 0.0, so now
current always shows "Black" as the value on a new record. I'd like it
to show an empty combo box. Any suggestions on how I can make it still
appear empty? I thought of looking at the Autonumber field, but I'm
not sure what it's value is on a new record - is it null?

 

[Brian]

Sorry. It does help to get a more detailed view of your project.

The reasons to use a button instead of the change of value on the combo box,
I think, are twofold:

1. A user could accidently pick the wrong one, immediately saving the data
and having to navigate back to the affected record(s) to change the results.
A button for the save separates the change of value and the save into two
separate actions, thus pretty much avoiding, or at least greatly reducing the
likelihood of, this pitfall.
2. The value of the combo box does not necessarily change at the points when
you want to save the value, as your original question indicates - it could be
the same for two consecutive games.

Assuming that your existing code uses the value of the combo box to set the
value of WhiteScore, I would just remove any record-saving code from there
and leave it to just leave there code that sets the value of WhiteScore. The
record save will happen automatically when you go to a new record using the
Add button, and having just the value-setting code on the combo box's
AfterUpdate will allow you to go back and easily correct an existing record
by just changing the combo box, then closing the form or going to a new game
(either of which will save the modified record).

Your code could be as simple as this:

Private Sub ResultBox_AfterUpdate()
If IsNull (ResultBox) then Exit Sub
Select Case ResultBox
Case "Black"
WhiteScore = 0
Case "White"
WhiteScore = 1
Case "Draw"
WhiteScore = .5
End Select
End Sub

This code will set ResultBox to Null on a new record but to the correct
value based on WhiteScore for existing records.

Private Sub Form_Current()
If Form.NewRecord then 'set it null on new/blank record
ResultBox= Null
Else
Select Case WhiteScore
Case 1
ResultBox = "White"
Case 0.5
ResultBox = "Draw"
Case 0
ResultBox = "Black"
End Select
End If

The only thing left is to ensure that the ResultBox is not blank when you
save the current record. You could do this by making its value required in
the table or by something like this in the form's BeforeUpdate:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(ResultBox) Then
MsgBox "Please select result before saving."
Cancel = True
End If
End Sub

 

[John Spencer]

Just check the NewRecord property of the form.

If Me.NewRecord = True then
Me.Combobox = Null
Else
'Whatever you are doing now
End if

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[MikeB]

Just check the NewRecord property of the form.

If Me.NewRecord = True then
Me.Combobox = Null
Else
'Whatever you are doing now
End if

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

John Thanks for the tip. That's exactly what I've been looking for,

Now for my next stumbling block. I've written a BeforeInsert event for
the form.

Private Sub Form_BeforeInsert(Cancel As Integer)
send_msg "Form Before Insert Event with Cancel as " & Cancel
Read_Results cmbResult.Value, "Form Before Insert Event"
End Sub

Sub Read_Results(ByVal strResult As String, ByVal strCaller As String)
send_msg "In Read_Results with Result as " & strResult
Select Case strResult
Case "White"
WhiteScore = 1#
BlackScore = 0#
Case "Black"
BlackScore = 1#
WhiteScore = 0#
Case "Draw"
WhiteScore = 0.5
BlackScore = 0.5
Case Else
send_msg "Invalid setting of Results" & vbCrLf & strCaller
End Select
End Sub


This blows up with a Null value passed from the cmbResult.Value.

The reason this seem to happen is that the Before Insert event is
driven for every one of the controls on the form that is bound to a
field in my database? Is this correct?

Is there a different place I can code an event handler that will be
called only once on form completion? I'm starting to think the button
idea, but then I don't know how to handle the case where the user
simply tabs through all the data fields and then by default the record
will be inserted into the table when the last field is tabbed through.

 

[John Spencer]

You need to handle null values. Either check that the value is Null and don't
call read result or change the declaration for readresult to a variant instead
of string.

Sub Read_Results(ByVal strResult As Variant, ByVal strCaller As String)

Your case handles the null value in the Case Else section, or you could
specifically test for it at the beginning

IF IsNull(strResult) = True THEN
'Do something
ELSE
Select Case StrResult
...
End Select
End IF


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County