HELP - New to this - or Get your laughs here

[Robert Riley]

I am a beginner so come get your laughs here.
First, is this the right group for this type of help?
What I am trying to do (big picture) is make a scoring database for the
Cub Scout Olympics. I have got the scoring for the non-time events.

In trying to score the quarter mile run I have come up with the
following, mostly from lurking in the newsgroup, thanks everyone.

But is does not work (that is why I am here asking for help).

The scoring is 1'st = 100, 2 = 90, .....6'th and greater = 50.

The table has fields for autonumber, scout-name, scout-rank, pack
number, pack score, 400 meter run, 400 meter points (five other events
with result field and score field). I might not need the pack score
field in the table, but the pack with the highest combined score gets a
plaque and I thought I might need it. I am trying to do all of this
from the form I made, with the same fields. Anyway I am at this point in
trying to score the quarter mile (400 meter run) What have I done
wrong? The error is about the select statement.

Oh - the Msgbox's are just for me to see the values, a learning tool.

Rob



Private Sub Ctl400_Meter_Run_LostFocus()

Dim Current_Scout_Rank As String
Dim Current_Event As String

Current_Scout_Rank = Forms("scout-event").[Scout Rank].Value
Current_Event = Screen.ActiveControl.Name
MsgBox Current_Scout_Rank
MsgBox Current_Event

'Dim dbs As DAO.Database 'did not work
'Dim rst As DAO.Recordset 'did not work

Dim rst As Recordset

Dim strSQL As String
strSQL = "Select from mytable Where Scout Rank = " & Current_Scout_Rank
& " And " & Current_Event & " = not null;"
MsgBox strSQL

Set dbs = CurrentDb
'Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset) 'did not work
Set rst = dbs.OpenRecordset(strSQL)
If rst.BOF = False And rst.EOF = Flase Then
' ** there is at least one record in the recordset
rst.MoveFirst
Do While rst.EOF = False
MsgBox rst.Fields(x)
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Sub

 

[Douglas J. Steele]

I suspect you're using Access 2000 or 2002, and your Dim statements are
failing with a message about unknown user type.

You're trying to use DAO objects, but by default, Access 2000 and 2002 use
ADO.

With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it. If you're not going to be
using ADO, uncheck the reference to Microsoft ActiveX Data Objects 2.x
Library

If you have both references, you'll find that you'll need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset


--
Doug Steele, Microsoft Access MVP

I am a beginner so come get your laughs here.
First, is this the right group for this type of help?
What I am trying to do (big picture) is make a scoring database for the
Cub Scout Olympics. I have got the scoring for the non-time events.

In trying to score the quarter mile run I have come up with the
following, mostly from lurking in the newsgroup, thanks everyone.

But is does not work (that is why I am here asking for help).

The scoring is 1'st = 100, 2 = 90, .....6'th and greater = 50.

The table has fields for autonumber, scout-name, scout-rank, pack
number, pack score, 400 meter run, 400 meter points (five other events
with result field and score field). I might not need the pack score
field in the table, but the pack with the highest combined score gets a
plaque and I thought I might need it. I am trying to do all of this
from the form I made, with the same fields. Anyway I am at this point in
trying to score the quarter mile (400 meter run) What have I done
wrong? The error is about the select statement.

Oh - the Msgbox's are just for me to see the values, a learning tool.

Rob



Private Sub Ctl400_Meter_Run_LostFocus()

Dim Current_Scout_Rank As String
Dim Current_Event As String

Current_Scout_Rank = Forms("scout-event").[Scout Rank].Value
Current_Event = Screen.ActiveControl.Name
MsgBox Current_Scout_Rank
MsgBox Current_Event

'Dim dbs As DAO.Database 'did not work
'Dim rst As DAO.Recordset 'did not work

Dim rst As Recordset

Dim strSQL As String
strSQL = "Select from mytable Where Scout Rank = " & Current_Scout_Rank
& " And " & Current_Event & " = not null;"
MsgBox strSQL

Set dbs = CurrentDb
'Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset) 'did not work
Set rst = dbs.OpenRecordset(strSQL)
If rst.BOF = False And rst.EOF = Flase Then
' ** there is at least one record in the recordset
rst.MoveFirst
Do While rst.EOF = False
MsgBox rst.Fields(x)
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Sub

 

[Robert Riley]

Thanks, changing that setting worked for the DAO lines.

Now I am getting a Run-time error 3075 that reads "Syntax error (missing
operator) in query expression 'Weblos 1 AND 400 Meter Run'." (the test
field/record name values from the form). I have tried different ways of
writing the sql query but no luck. Even made a query and looked at the
sql code which is "SELECT [scout-event].[Scout Rank], [scout-event].[400
Meter Run] FROM [scout-event] WHERE ((([scout-event].[Scout
Rank])="Webelos 1") AND (([scout-event].[400 Meter Run]) Is Null));". I
am looking for Null values because if there are any the scoring should
not be done. I took the sqlstring from another newgroup reply and
modified it, thought I would be able to get it to work. Should mytable
be a "Dim mytable" statement?

The code is below.

Thanks for the help.

Private Sub Ctl400_Meter_Run_LostFocus()

Dim Current_Scout_Rank As String
Dim Current_Event As String

Current_Scout_Rank = Forms("scout-event").[Scout Rank].Value
Current_Event = Screen.ActiveControl.Name

Dim dbs As DAO.Database
Dim rsCurr As DAO.Recordset

strSQL = "Select " & Current_Scout_Rank & " AND " & Current_Event & "
From mytable Where Scout Rank = " & Current_Scout_Rank & " And " &
Current_Event & " = Is Null;"
MsgBox strSQL

Set dbs = CurrentDb
Set rsCurr = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If rsCurr.BOF = False And rsCurr.EOF = Flase Then
' ** there is at least one record in the recordset
rsCurr.MoveFirst
Do While rsCurr.EOF = False
MsgBox rsCurr.Fields(x)
rsCurr.MoveNext
Loop
End If
rsCurr.Close
Set rsCurr = Nothing
dbs.Close
Set dbs = Nothing

End Sub

 

[Douglas J. Steele]

You want

strSQL = "Select " & Current_Scout_Rank & ", " & Current_Event & "
From mytable Where Scout Rank = " & Current_Scout_Rank & " And " &
Current_Event & " Is Null;"

If you use AND in the SELECT part of the statement, it tries to do boolean
AND.

Looking at your sample, though, I would suggest very strongly that you look
into database normalization. Having a field name like 400 Meter Run is a
sure sign that you haven't normalized your database.

Here are some references I have saved. Can't guarantee they're all still
active, but it should be a good start:

http://www.webmasterbase.com/article.php/378
http://www.jpmartel.com/bu12_c.htm (aimed at dBase, but still valid)
ACC2002: Database Normalization Basics
http://support.microsoft.com/?id=283878
ACC2000: Database Normalization Basics
http://support.microsoft.com/?id=209534
ACC: Database Normalization Basics
http://support.microsoft.com/?id=100139

http://home.earthlink.net/~billkent/Doc/simple5.htm
http://www.sbpa.csusb.edu/flin/info609/dbdesign/Default.htm
http://www.northern.ac.uk/computing_science/db_design/09_normal/03-1nf.htm
http://burks.bton.ac.uk/burks/foldoc/35/28.htm


Fundamentals of Relational Database Design
http://support.microsoft.com/?ID=129519
Understanding Relational Database Design
http://support.microsoft.com/?ID=234208
Database Design Principles
http://msdn.microsoft.com/library/devprods/vs6/vbasic/vbenlr98/vaobjFileSyst
emObject.htm



--
Doug Steele, Microsoft Access MVP

Thanks, changing that setting worked for the DAO lines.

Now I am getting a Run-time error 3075 that reads "Syntax error (missing
operator) in query expression 'Weblos 1 AND 400 Meter Run'." (the test
field/record name values from the form). I have tried different ways of
writing the sql query but no luck. Even made a query and looked at the
sql code which is "SELECT [scout-event].[Scout Rank], [scout-event].[400
Meter Run] FROM [scout-event] WHERE ((([scout-event].[Scout
Rank])="Webelos 1") AND (([scout-event].[400 Meter Run]) Is Null));". I
am looking for Null values because if there are any the scoring should
not be done. I took the sqlstring from another newgroup reply and
modified it, thought I would be able to get it to work. Should mytable
be a "Dim mytable" statement?

The code is below.

Thanks for the help.

Private Sub Ctl400_Meter_Run_LostFocus()

Dim Current_Scout_Rank As String
Dim Current_Event As String

Current_Scout_Rank = Forms("scout-event").[Scout Rank].Value
Current_Event = Screen.ActiveControl.Name

Dim dbs As DAO.Database
Dim rsCurr As DAO.Recordset

strSQL = "Select " & Current_Scout_Rank & " AND " & Current_Event & "
From mytable Where Scout Rank = " & Current_Scout_Rank & " And " &
Current_Event & " = Is Null;"
MsgBox strSQL

Set dbs = CurrentDb
Set rsCurr = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If rsCurr.BOF = False And rsCurr.EOF = Flase Then
' ** there is at least one record in the recordset
rsCurr.MoveFirst
Do While rsCurr.EOF = False
MsgBox rsCurr.Fields(x)
rsCurr.MoveNext
Loop
End If
rsCurr.Close
Set rsCurr = Nothing
dbs.Close
Set dbs = Nothing

End Sub

 

[Robert Riley]

OK, I am not normal...er.. normalized, in this database.
I looked at the MSKBA. Normalization is kinda overkill for this
application, isn't it? This should only be used one day a year, and if
it works good, every year. For a project this small (I thought this
would be smaller and easier when I started) is is simpler to normalize a
database?

Looks like a had an extra AND in the sql select statement.
But I now get a "Compile error Syntax error", seems like I got this one
before also. I will keep working on it.

Thanks for your help so far.

Rob

 

[Douglas J. Steele]

I overlooked the fact that Scout Rank is a text field and also the fact that
you've got spaces in your field names.

strSQL = "Select [" & Current_Scout_Rank & "], [" & Current_Event & "]
From mytable Where [Scout Rank] = " & Chr(34) & Current_Scout_Rank
& Chr(34) & " And [" & Current_Event & "] Is Null;"

And no, I don't believe it's ever overkill to design your application
properly.

--
Doug Steele, Microsoft Access MVP

OK, I am not normal...er.. normalized, in this database.
I looked at the MSKBA. Normalization is kinda overkill for this
application, isn't it? This should only be used one day a year, and if
it works good, every year. For a project this small (I thought this
would be smaller and easier when I started) is is simpler to normalize a
database?

Looks like a had an extra AND in the sql select statement.
But I now get a "Compile error Syntax error", seems like I got this one
before also. I will keep working on it.

Thanks for your help so far.

Rob

 

[Robert Riley]

Yea, your probably right. It should be designed right.

I have to thank you for your help so far. I would still be searching
the help file with far less progress. I am still searching the help
file because I still have not figured out all this type of programming.
My past programming (?) has been limited to the AutoCAD version of
LISP. I did say I was new to this, right? Learning as I go (I hope).

The strsql string seems to have worked because the current error is
run time error '3131':
Syntax error in FROM clause
I think it has gone past the previous point but do to work I will have
to verify later.

Would it help if I sent you the .mdb file so that you can see how I have
it setup and what I am planning on doing? Is it OK to post attachments
to the newsgroup? I am sure you would be able to find a bunch of stuff
to laugh at and point me in the right direction to doing this.

At this point I have (I hope) the results of the querry, which should be
empty until all of the scouts, of the rank on screen, have their event
result entered. So I need to do nothing until all of that particular
event results are entered. When all event results are entered for the
event per rank I need to do the scoring.

Thanks again for your help.

 

[Douglas J. Steele]

Answers in-line

--
Doug Steele, Microsoft Access MVP

The strsql string seems to have worked because the current error is
run time error '3131':
Syntax error in FROM clause
I think it has gone past the previous point but do to work I will have
to verify later.

What is the exact content of strSQL when you get this error? Try putting

Debug.Print strSQL

in your code right after you've assigned the value to strSQL, and check that
the SQL looks right. (Post it back here if you're not sure)

Would it help if I sent you the .mdb file so that you can see how I have
it setup and what I am planning on doing? Is it OK to post attachments
to the newsgroup? I am sure you would be able to find a bunch of stuff
to laugh at and point me in the right direction to doing this.

No, it's not OK to post attachments to the newsgroup. Many people pay for
their internet connection by the minute (either to their ISP, or to their
telephone company, or both), and some newsreaders are set to automatically
download all articles. Your attachment can cost them money, whether or not
they want it.

Additionally, there is a risk of potential viruses included with them. Most
of the users - especially the regulars - will not even pay attention to
those postings.

And I'm afraid I don't do consulting.

 

[Robert Riley]

Thanks for all of your help. Because of your help I got it to work so
far. Code pasted below. It might not be the best or most efficient
code, but it works so far. I just have to score the results now.
Some day I might even be able to normalize the table(s) and make this
into a real good program.
Thanks again.
Rob

Ya sure you don't do consulting?

Private Sub Ctl400_Meter_Run_LostFocus()
Dim Current_Scout_Rank As String
Dim Current_Event As String
Current_Scout_Rank = Forms("scout-event").[Scout Rank].Value
Current_Event = Screen.ActiveControl.Name
Dim dbs As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb
strSQL = "Select [Scout Name],[Scout Rank],[" & Current_Event & "] From
[scout-event] Where [Scout Rank] = " & Chr(34) & Current_Scout_Rank &
Chr(34) & " And [" & Current_Event & "] Is Null;"
Set rsCurr = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If rsCurr.BOF = False And rsCurr.EOF = False Then
' ** there is at least one record in the recordset
rsCurr.MoveFirst
Do While rsCurr.EOF = False
MsgBox "No scoring provided yet becuse not all of the event results
have been entered for this rank."
'goto the end of the recordset to the end
Do While rsCurr.EOF = False
rsCurr.MoveNext
Loop
Loop
End If
strSQL = "Select [Scout Name],[Scout Rank],[" & Current_Event & "] From
[scout-event] Where [Scout Rank] = " & Chr(34) & Current_Scout_Rank &
Chr(34) & " And [" & Current_Event & "] Is NOT Null ORDER BY [" &
Current_Event & "];"
Set rsCurr = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If rsCurr.BOF = False And rsCurr.EOF = False Then
' ** there is at least one record in the recordset
rsCurr.MoveFirst
Do While rsCurr.EOF = False
MsgBox "Scoring Provided" 'recreate rscurr (rscurr1?)with all non
null events, do scoring
'goto the end of the recordset to the end remove this loop to end of
recordset when providing scoring
Do While rsCurr.EOF = False
rsCurr.MoveNext
Loop
Loop
End If
rsCurr.Close
Set rsCurr = Nothing
dbs.Close
Set dbs = Nothing

End Sub