populating records based on field input

[KRosier]

Hi folks! I'm using Access2000 and would like to know if it is possible,
and how to accomplish the following in a form:

I have two tables-

tbl_wk_stats
wk_statsID (one to many relationship with tbl_gm_stats.wk_statsID)
gm_date (date the series of games is played)

tbl_gm_stats
gm_statsID
wk_statsID
game_no (always game 1, 2, or 3)
score

I would like to make a form that once I enter the date, three related
records are populated with 1, 2, and 3 in the game_no field, so that my
user sees three different text boxes to enter the appropriate scores in.

Your help is very much appreciated.

Kathy

 

[Ken Snell \(MVP\)]

May I suggest a different approach?

Put three unbound textboxes (name them "txtScore1", "txtScore2", and
"txtScore3") on the form (label them as "1", "2", and "3") and let the user
type the scores into those textboxes. Then let the user click a command
button to "Save" the scores. The button then runs code that uses an append
query to write three records to "tbl_gm_stats" table.

Private Sub CommandButton_Click()
Dim dbs As DAO.Database
Dim strSQL As String
Dim lngCount As Long
Set dbs = CurrentDb
For lngCount = 1 To 3
strSQL = "INSERT INTO tbl_gm_stats " & _
"(gm_statsID, wk_statsID, game_no, score) " & _
"VALUES (" & Me.gm_statsIDTextboxName.Value & ", " & _
Me.wk_statsIDTextboxName.Value & ", " & _
lngCount & ", " & Me.Controls("txtScore" & lngCount).Value _
& ");"
dbs.Execute strSQL, dbFailOnError
Next lngCount
dbs.Close
Set dbs = Nothing
End Sub

 

[KRosier]

Hi Ken,
This works well, but only if I remark out the line
Dim dbs As DAO.Database

If I leave it in there I get the error message "Compile Error: User
defined type not defined"

I assume it's important since you included it. What are the consequences
of not using that line?

Unfortunately, when it comes to VBA I am a beginner, and self-taught.
Which means, I know enough to know something can be done, but when it
comes to implementation, it's a struggle. And I find Microsoft's help is
only helpful if you know exactly what question to ask it.

 

[Ken Snell \(MVP\)]

It's always good practice to explicitly declare variables and the variable
types in your code. By doing so, you can ensure that the data are properly
interpreted by ACCESS when the code runs. Not declaring a variable (Dim ...
As ... ) means that ACCESS will establish the variable as a Variant type,
and such types may misinterpret values when you then set the variable to a
value (thinking a date is a number, or a number is a date, or a string is a
date, etc.).

The error that you get when you include the Dim dbs As DAO.Database suggests
that you do not have a reference set to DAO library. This is common problem
if you're using ACCESS 2000 or ACCESS 2002 because those versions do not set
a reference to DAO by default. Open the Visual Basic Editor, click on Tools
| References, and check the box to the left of the Data Access Objects 3.x
library in the list. Close the References window and then try to compile
your code again, with the Dim dbs As DAO.Database as a code step.
--

Ken Snell
<MS ACCESS MVP>

 

[KRosier]

Thank you, Ken. That was exactly what it was. I had remembered
encountering that before and fixed it shortly before I rechecked here.
I appreciate your input and patience.

Now, however, I am running into:
Syntax Error in INSERT INTO statement.

And the debug button highlights:
dbs.Execute strSQL, dbFailOnError

Here is the routine, as I have it:

Private Sub cmd_gm_info_Click()

Dim dbs As DAO.Database
Dim strSQL As String
Dim lngCount As Long
Set dbs = CurrentDb

For lngCount = 1 To 3
strSQL = "INSERT INTO tbl_gm_stats " & _
"(wk_statsID, game_no, score) " & _
"VALUES (" & Me.txtwk_statsID.Value & ", " & _
lngCount & ", " & Me.Controls("txtScore" & lngCount).Value _
& ");"
dbs.Execute strSQL, dbFailOnError
Next lngCount

dbs.Close
Set dbs = Nothing

End Sub

 

[Ken Snell \(MVP\)]

Is wk_statsID, game_no, or score fields a text field? If yes, you must
delimit the value that you concatenate into the SQL statement with '
characters.

 

[KRosier]

Those three fields are all number fields (long integer). The wk_statsID
is the key field in the table.

 

[Ken Snell \(MVP\)]

Any chance that txtwk_statsID, txtScore1, txtScore2, or txtScore3 could
contain a Null value?

 

[KRosier]

Yep, that seemed to be the problem. I changed it so that txtScore#
fields are automatically filled with "0" and it is now working as intended.

Thanks for the help!

Kathy

 

[Ken Snell \(MVP\)]

Glad it's working. You're welcome.