Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SO STUCK!! HELP!!!! UPDATE TABLE FROM RECORDSET????

Status
Not open for further replies.
LBERNARDE

LBERNARDE

Programmer
Mar 14, 2001
21
US
I want to assign awards and update a table. How the heck do I do this? The code below is updating the table, however, it updates the same record every time. Is there something wrong with my sort? How can I get the code to know which record to update in the table. I've been stuck on this for some time, so if anyone has any ideas, I'd appreciate it.

Thanks Lisa

'Sub ProneAwards(rstclass2 As Recordset)

proneawards:
With rstclass2
.MoveLast
strreccount = rstclass2.recordcount
.MoveFirst
End With

If strreccount <= 5 Then
mplace = 1
If strreccount > 5 And strreccount <= 10 Then
mplace = 2
If strreccount > 10 Then
mplace = 3
End If
End If
End If

mplacecounter = 0
Do
Do While mplacecounter <= mplace
mplacecounter = mplacecounter + 1
STRSORTON = &quot;PRONEMMA&quot;
'With rstClass2
rstclass2.Sort = STRSORTON
rstclass2.MoveFirst
VARBOOKMARK = rstclass2.Bookmark
If mplacecounter = 1 Then
rstclass2.Bookmark = VARBOOKMARK
rstclass2.Edit
rstclass2!proneaward = &quot;1st Prone&quot;
rstclass2.Update
rstclass2.MoveNext
VARBOOKMARK = rstclass2.Bookmark
mplacecounter = mplacecounter + 1
Else
If mplacecounter = 2 Then
rstclass2.Bookmark = VARBOOKMARK
rstclass2.Edit
rstclass2!proneaward = &quot;2nd Prone&quot;
rstclass2.Update
rstclass2.MoveNext
VARBOOKMARK = rstclass2.Bookmark
mplacecounter = mplacecounter + 1
Else
If mplacecounter = 3 Then
rstclass2.Bookmark = VARBOOKMARK
rstclass2.Edit
rstclass2!proneaward = &quot;3rd Prone&quot;
rstclass2.Update
rstclass2.MoveNext
VARBOOKMARK = RSTLCASS2.Bookmark
mplacecounter = mplacecounter + 1
End If
End If
End If
Loop
Loop Until mplacecounter >= mplace
 
Sort by date Sort by votes
Hi,
Could it be that in your loop you are ALWAYS doing .MoveFirst before you do .Update & .MoveNext??? Skip,
metzgsk@voughtaircraft.com
 
Upvote 0 Downvote
Try using:
cnt = 0
do while rstclass2.eof = False
'run your update
cnt = cnt + 1 'track the rec num
rstclass2.movenext
loop

this will move through each record starting at the top of the record set.

 
Upvote 0 Downvote
I'd take the SORT and MoveFirst out of the loop...
Code: 
Sub proneawards(rstclass2 As Recordset)
    Dim sAward As String
    
    With rstclass2
        .MoveLast
        strreccount = rstclass2.RecordCount
        .MoveFirst
    End With
        
    Select Case strreccount
        Case Is <= 5
            mplace = 1
        Case Is <= 10
            mplace = 2
        Case Else
            mplace = 3
    End Select
    
    mplacecounter = 0
    
    STRSORTON = &quot;PRONEMMA&quot;
    rstclass2.Sort = STRSORTON
    rstclass2.MoveFirst
    
    Do
        Do While mplacecounter <= mplace
            mplacecounter = mplacecounter + 1
            VARBOOKMARK = rstclass2.Bookmark
            Select Case mplacecounter
                Case 1
                    sAward = &quot;1st Prone&quot;
                Case 2
                    sAward = &quot;2nd Prone&quot;
                Case 3
                    sAward = &quot;3nd Prone&quot;
            End Select
            rstclass2.Bookmark = VARBOOKMARK
            rstclass2.Edit
            rstclass2!proneaward = sAward
            rstclass2.Update
            rstclass2.MoveNext
            VARBOOKMARK = RSTLCASS2.Bookmark
            mplacecounter = mplacecounter + 1
        Loop
    Loop Until mplacecounter >= mplace
End Sub
Skip,
metzgsk@voughtaircraft.com
 
Upvote 0 Downvote
Thanks I will give this a try. Sometimes it seems you just get so frustrated you can't see the little things. Hopefully this will work.
Thanks again, I'll be back if I'm still stuck!
 
Upvote 0 Downvote
Well, It's now now updating the same record, but still is not updating the correct information. Do I maybe need to use a tabledef? To be honest with you I was surprised that opening and updating the table as a recordset would put any information into the table. If I'm sorting the recordset, when I open the related table, should that be sorted the same way? If it is something's not working.
 
Upvote 0 Downvote
Hi!

Maybe you can give us a more detailed description of what you are trying to do. That might help us to help you. One thing I did notice is that you need to put your mplacecounter = 0 inside the first loop and use another counter to determine when to leave the outside loop, otherwise you will go through the inside loop only once and you will never read through all of the records. Also, in the code Skip gave you, mplacecounter is incremented twice everytime through the inner loop. With more information we can give you a better idea of how to do what you need.

hth
Jeff Bridgham
bridgham@purdue.edu
 
Upvote 0 Downvote
I have a table with shooters in it and there total scores. There are 3 different classes (Master, Expert and Sharpshooter) and there are 3 different postiions (Standing, Kneeling and Prone). The number of awards handed out depends on the number of competitors. What I’ve done is create a recordset with only one class, I then count the number of competitors to determine number of places to be awarded. I want to sort in Descending order, start @ record number 1 and assign first place, advance to next record and assign #2, etc. I want this ultimately to be updated in the actual table. It IS updating the actual table, but with no rhyme or reason. I thought adding the bookmark would make it know which record to place the award in, but it is not working. I’m not really sure my sorts are working. ANY advice is appreciated. I’m kind a new @ VBA. I will post all of the current code later today, but this is a basic explanation of what I’m trying to do. Hope it makes sense.
Thanks Lisa
 
Upvote 0 Downvote
I have a table with shooters in it and there total scores.
There are 3 different classes (Master, Expert and Sharpshooter) and
there are 3 different postiions (Standing, Kneeling and Prone).
The number of awards handed out depends on the number of competitors.
What I’ve done is create a recordset with only one class, I then count
The number of competitors to determine number of places to be awarded.
I want to sort in Descending order, start @ record number 1 and assign
first place, advance to next record and assign #2, etc. I want this
ultimately to be updated in the actual table. It IS updating the actual
table, but with no rhyme or reason. I thought adding the bookmark would
make it know which record to place the award in, but it is not working.
I’m not really sure my sorts are working. ANY advice is appreciated.
I’m kind a new @ VBA. I will post all of the current code later today,
but this is a basic explanation of what I’m trying to do. Hope it makes
sense.
Thanks Lisa

******************
*CODE*
******************
Option Compare Database
Sub SQLX()
Dim dbsOshkosh As Database
Dim qdfTemp As QueryDef
Dim RSTCLASS As Recordset
Dim strsqloutput As String
Dim VARBOOKMARK As String
Dim VARAUTONUMBER As Variant
Set dbsOshkosh = CurrentDb
Set qdfTemp = dbsOshkosh.CreateQueryDef(&quot;&quot;)
Set RSTCLASS = CurrentDb().OpenRecordset(&quot;tblclass&quot;, dbOpenTable)
strClass = RSTCLASS!CLASS
RSTCLASS.MoveFirst
' Open Recordset using temporary QueryDef object
'SQLOutput &quot;SELECT Count(tblSMALBOREPOSITIONAWARDS.NUMBER) AS CountOfNUMBER,&quot; & _
'&quot;FROM tblSMALBOREPOSITIONAWARDS&quot; & _
'&quot;WHERE (((tblSMALBOREPOSITIONAWARDS.CLASS)= &quot; & strClass & &quot; ))&quot;, qdfTemp

'SQLOutput &quot;SELECT * FROM tblsmalborepositionawards &quot; & _
' &quot;WHERE (((tblsmalborepositionawards!class = &quot; & strClass & &quot;))&quot; & _
' &quot;ORDER BY CLASS&quot;, qdfTemp

'SQLOutput &quot;SELECT * FROM tblsmalborepositionawards &quot; & _
' &quot;WHERE tblsmalborepositionawards!class = rstclass!class &quot; & _
' &quot;ORDER BY CLASS&quot;, qdfTemp

SQLOutput &quot;SELECT * FROM tblsmalborepositionawards &quot; & _
&quot;WHERE tblsmalborepositionawards!class = 'MASTER' &quot; & _
&quot;ORDER BY CLASS&quot;, qdfTemp

SQLOutput &quot;SELECT * ,&quot; & _
&quot;FROM tblSMALBOREPOSITIONAWARDS&quot; & _
&quot;WHERE (((tblSMALBOREPOSITIONAWARDS.CLASS)= &quot; & strClass & &quot; ))&quot;, qdfTemp
dbsOshkosh.Close
End Sub

Function SQLOutput(strSQL As String, qdfTemp As QueryDef)
Dim rstclass2 As Recordset
Dim strreccount As String
' Set SQL property of temporary QueryDef object and open
' a Recordset.
qdfTemp.SQL = strSQL
Set rstclass2 = qdfTemp.OpenRecordset
GoSub proneawards
'End Function

'Sub ProneAwards(rstclass2 As Recordset)

proneawards:
With rstclass2
.MoveLast
strreccount = rstclass2.recordcount
.MoveFirst
End With

If strreccount <= 5 Then
mplace = 1
If strreccount > 5 And strreccount <= 10 Then
mplace = 2
If strreccount > 10 Then
mplace = 3
End If
End If
End If

mplacecounter = 0

STRSORTON = &quot;RSTCLASS2!PRONEMMA&quot;
'With rstClass2
rstclass2.Sort = STRSORTON
rstclass2.MoveFirst
VARBOOKMARK = rstclass2.Bookmark
VARAUTONUMBER = rstclass2!AUTONUMBER

'VARBOOKMARK = rstclass2.AbsolutePosition + 1

Do
Do While mplacecounter <= mplace
mplacecounter = mplacecounter + 1
If mplacecounter = 1 Then
rstclass2.Bookmark = VARBOOKMARK
rstclass2.Edit
rstclass2!proneaward = &quot;1st Prone&quot;
rstclass2.Update
rstclass2.MoveNext
VARBOOKMARK = rstclass2.Bookmark
mplacecounter = mplacecounter + 1
Else
If mplacecounter = 2 Then
rstclass2.Bookmark = VARBOOKMARK
rstclass2.Edit
rstclass2!proneaward = &quot;2nd Prone&quot;
rstclass2.Update
rstclass2.MoveNext
VARBOOKMARK = rstclass2.Bookmark
mplacecounter = mplacecounter + 1
Else
If mplacecounter = 3 Then
rstclass2.Bookmark = VARBOOKMARK
rstclass2.Edit
rstclass2!proneaward = &quot;3rd Prone&quot;
rstclass2.Update
rstclass2.MoveNext
VARBOOKMARK = RSTLCASS2.Bookmark
mplacecounter = mplacecounter + 1
End If
End If
End If
Loop
Loop Until mplacecounter >= mplace


GoSub StandAwards
'End Sub
Return

'Sub StandAwards()
StandAwards:
With rstclass2
.MoveLast
strreccount = rstclass2.recordcount
.MoveFirst

End With

If strreccount <= 5 Then
mplace = 1
If strreccount > 5 And strreccount <= 10 Then
mplace = 2

If strreccount > 10 Then
mplace = 3
End If
End If
End If

mplacecounter = 0
STRSORTON2 = &quot;STANDMMA&quot;
'With rstClass2
rstclass2.Sort = STRSORTON2
rstclass2.MoveLast
'VARBOOKMARK = rstclass2.Bookmark
VARAUTONUMBER = rstclass2!AUTONUMBER

Do
Do While mplacecounter <= mplace
mplacecounter = mplacecounter + 1
If mplacecounter = 1 Then
rstclass2.Bookmark = VARBOOKMARK
rstclass2.Edit
rstclass2!standaward = &quot;1st Standing&quot;
rstclass2.Update
rstclass2.MovePrevious
VARBOOKMARK = rstclass2.Bookmark
mplacecounter = mplacecounter + 1

Else
If mplacecounter = 2 Then
rstclass2.Bookmark = VARBOOKMARK
rstclass2.Edit
rstclass2!standaward = &quot;2nd Standing&quot;
rstclass2.Update
rstclass2.MovePrevious
VARBOOKMARK = rstclass2.Bookmark
mplacecounter = mplacecounter + 1
Else
If mplacecounter = 3 Then
rstclass2.Bookmark = VARBOOKMARK
rstclass2.Edit
rstclass2!standaward = &quot;3rd Standing&quot;
rstclass2.Update
rstclass2.MovePrevious
VARBOOKMARK = rstclass2.Bookmark
mplacecounter = mplacecounter + 1
End If
End If
End If
Loop
Loop Until mplacecounter >= mplace
GoSub KneelAwards


'End Sub
'Sub KneelAwards()
KneelAwards:
With rstclass2
.MoveLast
strreccount = rstclass2.recordcount
.MoveFirst
End With

If strreccount <= 5 Then
mplace = 1
If strreccount > 5 And strreccount <= 10 Then
mplace = 2
If strreccount > 10 Then
mplace = 3
End If
End If
End If

mplacecounter = 0
STRSORTON3 = &quot;RSTCLASS2!KNEELMMA&quot;
'With rstClass2
rstclass2.Sort = STRSORTON3
rstclass2.MoveLast
VARBOOKMARK = rstclass2.Bookmark
VARAUTONUMBER = rstclass2!AUTONUMBER
Do
Do While mplacecounter <= mplace
mplacecounter = mplacecounter + 1

If mplacecounter = 1 Then
rstclass2.Bookmark = VARBOOKMARK
rstclass2.Edit
rstclass2!kneelaward = &quot;1st Kneeling&quot;
rstclass2.Update
rstclass2.MovePrevious
VARBOOKMARK = rstclass2.Bookmark
mplacecounter = mplacecounter + 1
Else
If mplacecounter = 2 Then
rstclass2.Bookmark = VARBOOKMARK
rstclass2.Edit
rstclass2!kneelaward = &quot;2nd Kneeling&quot;
rstclass2.Update
rstclass2.MovePrevious
VARBOOKMARK = rstclass2.Bookmark
mplacecounter = mplacecounter + 1
Else
If mplacecounter = 3 Then
rstclass2.Bookmark = VARBOOKMARK
rstclass2.Edit
rstclass2!kneelaward = &quot;3rd Kneeling&quot;
rstclass2.Update
rstclass2.MovePrevious
VARBOOKMARK = rstclass2.Bookmark
mplacecounter = mplacecounter + 1
End If
End If
End If
Loop
Loop Until mplacecounter >= mplace
End Function
'End Sub

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

morechocolate
  • Locked
  • Question
run-tune error 3704 operation is not allowed when the object is codes: I don't think so.
Replies
7
Views
609
morechocolate
morechocolate
thanhthanh12
  • Locked
  • Question
Help vba
Replies
3
Views
333
combo
combo
SLW89
  • Locked
  • Question
VBA newbie needs help
Replies
4
Views
287
Andrzejek
Andrzejek
remeng
  • Locked
  • Question
CTRL + BREAK from second macro button
Replies
5
Views
356
strongm
strongm
remeng
  • Locked
  • Question
Delete dynamic table rows without removing password protection
Replies
7
Views
255
remeng
remeng

Part and Inventory Search

Sponsor

Back
Top