Method or data member not found

M

Martin

Thank you for your help up front.

I have a qry that lists all student grades for a particular lesson. I am
trying to update this data based on a form where the instructor enters all
the student grades.

Here is my code:

Set myset = dbb.OpenRecordset("Student Grades", dbOpenTable)
Set qdf = db.QueryDefs("qryGradGrades")
qdf.Parameters(0) = [Forms]![frmSelectClass]![cboClassNum]
Set mysetstud = qdf.OpenRecordset
Stop

myset.Index = "grades" ' SSN, Class, Annex

Debug.Print iclass, sannex, sannex9, sSQLannex, sSQLannex9
If iclass < 10 Then
myset.Seek "=", mysetstud!SSN, Me.CLASS, sannex9
Else
myset.Seek "=", mysetstud!SSN, Me.CLASS, sannex
End If
'Stop
Do Until mysetstud.EOF
If myset.NoMatch Then
' Stop ' Add new record
myset.AddNew
myset![SSN] = iSSN
myset![Annex] = sannex
myset![CLASS] = iclass
myset![PX] = Me.PX
myset![WX] = Me.WX
'myset![QzWts] = Me.QW
myset![Wwts] = Me.WW
myset![Pwts] = Me.PW
myset![GrAvg] = Me.AGPA
myset![ovrall] = Me.OGPA
myset![RemedWX] = Me.chkW
myset.Update
Else ' If record already exists update for remediation only
myset.Edit
myset![SSN] = mysetstud!SSN
If iclass < 10 Then
myset![Annex] = sannex9
Else
myset![Annex] = sannex
End If
myset![CLASS] = [Forms]![frmSelectClass].[cboClassNum]
'*********************************
' these following lines are giving me the error
myset![PX] = mysetstud.['" & sSQLannex9 & iclass & "'PX]
myset![WX] = mysetstud.['" & sSQLannex9 & iclass & "'WX]
myset![Wwts] = mysetstud.['" & sSQLannex9 & iclass & "'WW]
myset![Pwts] = mysetstud.['" & sSQLannex9 & iclass & "'PW]
myset![GrAvg] = mysetstud.['" & sSQLannex9 & iclass & "'AGPA]
myset![ovrall] = mysetstud.['" & sSQLannex9 & iclass & "'OGPA]
myset![RemedWX] = mysetstud.['" & sSQLannex9 & iclass & "'chkW]
'************************************************
myset.Update
End If
mysetstud.MoveNext
If mysetstud.EOF Then
Exit Do
End If
Loop


Table student grades has SSN and then GD-01 grades, GD-02 grades, GD-03
grades etc.

I have code that uses an append SQL statement and it works just fine except
that it creates duplicate records. I want to add a new record if one does
not exist or update the record if one already exists. That is why I am going
down this road.

Thank you again for any help you can provide.
 
M

Martin

I forgot to tell you that variable sSQLannex9 = "GD0" and iclass = "6" at
this point in time.

Thank you again for any help on this problem.
 
D

Dirk Goldgar

Martin said:
Thank you for your help up front.

I have a qry that lists all student grades for a particular lesson. I am
trying to update this data based on a form where the instructor enters all
the student grades.

Here is my code:

Set myset = dbb.OpenRecordset("Student Grades", dbOpenTable)
Set qdf = db.QueryDefs("qryGradGrades")
qdf.Parameters(0) = [Forms]![frmSelectClass]![cboClassNum]
Set mysetstud = qdf.OpenRecordset
Stop

myset.Index = "grades" ' SSN, Class, Annex

Debug.Print iclass, sannex, sannex9, sSQLannex, sSQLannex9
If iclass < 10 Then
myset.Seek "=", mysetstud!SSN, Me.CLASS, sannex9
Else
myset.Seek "=", mysetstud!SSN, Me.CLASS, sannex
End If
'Stop
Do Until mysetstud.EOF
If myset.NoMatch Then
' Stop ' Add new record
myset.AddNew
myset![SSN] = iSSN
myset![Annex] = sannex
myset![CLASS] = iclass
myset![PX] = Me.PX
myset![WX] = Me.WX
'myset![QzWts] = Me.QW
myset![Wwts] = Me.WW
myset![Pwts] = Me.PW
myset![GrAvg] = Me.AGPA
myset![ovrall] = Me.OGPA
myset![RemedWX] = Me.chkW
myset.Update
Else ' If record already exists update for remediation only
myset.Edit
myset![SSN] = mysetstud!SSN
If iclass < 10 Then
myset![Annex] = sannex9
Else
myset![Annex] = sannex
End If
myset![CLASS] = [Forms]![frmSelectClass].[cboClassNum]
'*********************************
' these following lines are giving me the error
myset![PX] = mysetstud.['" & sSQLannex9 & iclass & "'PX]
myset![WX] = mysetstud.['" & sSQLannex9 & iclass & "'WX]
myset![Wwts] = mysetstud.['" & sSQLannex9 & iclass & "'WW]
myset![Pwts] = mysetstud.['" & sSQLannex9 & iclass & "'PW]
myset![GrAvg] = mysetstud.['" & sSQLannex9 & iclass & "'AGPA]
myset![ovrall] = mysetstud.['" & sSQLannex9 & iclass & "'OGPA]
myset![RemedWX] = mysetstud.['" & sSQLannex9 & iclass & "'chkW]
'************************************************
myset.Update
End If
mysetstud.MoveNext
If mysetstud.EOF Then
Exit Do
End If
Loop


Table student grades has SSN and then GD-01 grades, GD-02 grades, GD-03
grades etc.

I have code that uses an append SQL statement and it works just fine
except
that it creates duplicate records. I want to add a new record if one does
not exist or update the record if one already exists. That is why I am
going
down this road.

Thank you again for any help you can provide.
Click to expand...


The lines you say are giving you an error are not built properly. You need
to use something like this:

myset![PX] = mysetstud.Fields(sSQLannex9 & iclass & "PX")
myset![WX] = mysetstud.Fields(sSQLannex9 & iclass & "WX")
myset![Wwts] = mysetstud.Fields(sSQLannex9 & iclass & "WW")
myset![Pwts] = mysetstud.Fields(sSQLannex9 & iclass & "PW")
myset![GrAvg] = mysetstud.Fields(sSQLannex9 & iclass & "AGPA")
myset![ovrall] = mysetstud.Fields(sSQLannex9 & iclass & "OGPA")
myset![RemedWX] = mysetstud.Fields(sSQLannex9 & iclass & "chkW")

Your table design seems to be unnormalized, with repeating elements (grades)
in each row, rather than having a related table in which each grade is a
separate row. That design issue is likely to cause you more difficulty down
the road.
 
M

Martin

Thank you very much for your help. I changed the lines to how you indicated
and it worked perfectly.

I know my code is not normalized and have tried to learn how to do it but I
have been working on someone else's dbase.

Do you know of any good websites to get information of normalizing a dbase
with good examples?

Again thank you for you help. Have a Happy New Year.

Dirk Goldgar said:
Martin said:
Thank you for your help up front.

I have a qry that lists all student grades for a particular lesson. I am
trying to update this data based on a form where the instructor enters all
the student grades.

Here is my code:

Set myset = dbb.OpenRecordset("Student Grades", dbOpenTable)
Set qdf = db.QueryDefs("qryGradGrades")
qdf.Parameters(0) = [Forms]![frmSelectClass]![cboClassNum]
Set mysetstud = qdf.OpenRecordset
Stop

myset.Index = "grades" ' SSN, Class, Annex

Debug.Print iclass, sannex, sannex9, sSQLannex, sSQLannex9
If iclass < 10 Then
myset.Seek "=", mysetstud!SSN, Me.CLASS, sannex9
Else
myset.Seek "=", mysetstud!SSN, Me.CLASS, sannex
End If
'Stop
Do Until mysetstud.EOF
If myset.NoMatch Then
' Stop ' Add new record
myset.AddNew
myset![SSN] = iSSN
myset![Annex] = sannex
myset![CLASS] = iclass
myset![PX] = Me.PX
myset![WX] = Me.WX
'myset![QzWts] = Me.QW
myset![Wwts] = Me.WW
myset![Pwts] = Me.PW
myset![GrAvg] = Me.AGPA
myset![ovrall] = Me.OGPA
myset![RemedWX] = Me.chkW
myset.Update
Else ' If record already exists update for remediation only
myset.Edit
myset![SSN] = mysetstud!SSN
If iclass < 10 Then
myset![Annex] = sannex9
Else
myset![Annex] = sannex
End If
myset![CLASS] = [Forms]![frmSelectClass].[cboClassNum]
'*********************************
' these following lines are giving me the error
myset![PX] = mysetstud.['" & sSQLannex9 & iclass & "'PX]
myset![WX] = mysetstud.['" & sSQLannex9 & iclass & "'WX]
myset![Wwts] = mysetstud.['" & sSQLannex9 & iclass & "'WW]
myset![Pwts] = mysetstud.['" & sSQLannex9 & iclass & "'PW]
myset![GrAvg] = mysetstud.['" & sSQLannex9 & iclass & "'AGPA]
myset![ovrall] = mysetstud.['" & sSQLannex9 & iclass & "'OGPA]
myset![RemedWX] = mysetstud.['" & sSQLannex9 & iclass & "'chkW]
'************************************************
myset.Update
End If
mysetstud.MoveNext
If mysetstud.EOF Then
Exit Do
End If
Loop


Table student grades has SSN and then GD-01 grades, GD-02 grades, GD-03
grades etc.

I have code that uses an append SQL statement and it works just fine
except
that it creates duplicate records. I want to add a new record if one does
not exist or update the record if one already exists. That is why I am
going
down this road.

Thank you again for any help you can provide.
Click to expand...


The lines you say are giving you an error are not built properly. You need
to use something like this:

myset![PX] = mysetstud.Fields(sSQLannex9 & iclass & "PX")
myset![WX] = mysetstud.Fields(sSQLannex9 & iclass & "WX")
myset![Wwts] = mysetstud.Fields(sSQLannex9 & iclass & "WW")
myset![Pwts] = mysetstud.Fields(sSQLannex9 & iclass & "PW")
myset![GrAvg] = mysetstud.Fields(sSQLannex9 & iclass & "AGPA")
myset![ovrall] = mysetstud.Fields(sSQLannex9 & iclass & "OGPA")
myset![RemedWX] = mysetstud.Fields(sSQLannex9 & iclass & "chkW")

Your table design seems to be unnormalized, with repeating elements (grades)
in each row, rather than having a related table in which each grade is a
separate row. That design issue is likely to cause you more difficulty down
the road.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

Martin said:
Thank you very much for your help. I changed the lines to how you
indicated
and it worked perfectly.

I know my code is not normalized and have tried to learn how to do it but
I
have been working on someone else's dbase.

Do you know of any good websites to get information of normalizing a dbase
with good examples?
Click to expand...

You might start with Crystal's tutorial here:

http://www.accessmvp.com/strive4peace/
Again thank you for you help. Have a Happy New Year.
Click to expand...

You're welcome, and may the new year be good to you.
 
Top