Recordset help

[Paulsburbon]

I tried to figure out something and need some help. I forgot when I started on this database a field to sort Products on. So I added a field intSort to the table and was hoping to have the user press a button to order the list of Items on a subform by twos. That way when they add an Item they could type in 5 and get it to be inbetween 4 and 6. Then when they hit the button again it would resort. My code is not doing what I would like it to do though. It is just adding up the records in the group and putting it in the second record in the group. Here is my code:

Private Sub Toggle97_Click()
On Error GoTo Toggle97_Click_Err

Dim Count As Integer
Dim intAnswer As Integer
Dim rsProductItems As New ADODB.Recordset
Dim strSQLStmt As String

intAnswer = MsgBox("This will sort the current Items on this Claim. Do you wish to proceed?", vbQuestion + vbYesNo, "Sort Items")
If intAnswer = vbYes Then
strSQLStmt = "SELECT * FROM tblClaimLineItems WHERE tblClaimLineItems.[lngClaimNumber] = '" & Me.TXTClaimNumber & "';"
rsProductItems.Open strSQLStmt, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
Count = 2
Do Until rsProductItems.EOF
Text95.Value = Count
Count = Count + 2
rsProductItems.MoveNext
Loop
End If
Exit Sub
Toggle97_Click_Err:
MsgBox "Error is " & Err.Description
Exit Sub
End Sub

What am I doing wrong? I havn't even attempted sorting the records yet. Thank you for your help.

Paul

 

[x0reset]

Don't try to do this using code! Instead, build a query in the "Record Source" property of the form. (There is a handy query builder button next to the property box.) Use the "Sort: Ascending" option in the query under the intSort field. Then add the following code to the "On Click" event of your button:

Private Sub Toggle97_Click()

Me.Requery

End Sub

This should do the trick.

 

[Paulsburbon]

Oh but there is nothing in the intSort fields currently. I'm trying to populate the fields (because Some have 1000 records already typed in and would be a nightmare to type in 1 thru 1000)

Thanks

 

[x0reset]

Ah, try this then:

First, make sure the "Record Source" property of your form is set to tblClaimLineItems. Then, enter the following code:

Private Sub Toggle97_Click()

'Filtering the data to show only records relating to the
'Claim Number entered in TXTClaimNumber.

Dim stLinkCriteria As String

stLinkCriteria = "[lngClaimNumber]= '" & Me.TXTClaimNumber & "'"
DoCmd.ApplyFilter , stLinkCriteria

'Updating intSort for each record.

Dim intSortNumber As Integer

DoCmd.GoToRecord , , acFirst

For Count = 1 To Me.Recordset.RecordCount
intSortNumber = Me.Recordset.CurrentRecord * 2
Me.Text95 = intSortNumber
DoCmd.GoToRecord , , acNext
Next Count

End Sub

 

[Paulsburbon]

Getting an error with the filter part. I'm not familar with the filter command so I'm not able to fix it myself. A text box is coming up asking for lngClaimNumber.

 

[Paulsburbon]

My database gets a little upset too when I use that method. I have to Compact and repair after running it.

 

[x0reset]

Did you set the "Record Source" property of your form to tblClaimLineItems?

 

[Paulsburbon]

Yea, It is a subform but it is set.

 

[x0reset]

Oh, if its a subform you need to replace this line:

DoCmd.ApplyFilter , stLinkCriteria

With:

Me.MySubFormControlName!Form.ApplyFilter , stLinkCriteria

You may also need to play around with a SetFocus command before you run the For Loop, just to be sure the code is looking at the subforms recordset rather then the main forms recordset.

 

[Paulsburbon]

Now I'm getting a type mismatch error, Trying to fool around with it but still no go.

 

[Paulsburbon]

I got it to work. Thank you for your help x0reset but I was able to do it with this code:

Private Sub Toggle97_Click()
On Error GoTo Toggle97_Click_Err

Dim Count As Integer
Dim intAnswer As Integer
Dim rsProductItems As New ADODB.Recordset
Dim strSQLStmt As String

intAnswer = MsgBox("This will sort the current Items on this Claim. Do you wish to proceed?", vbQuestion + vbYesNo, "Sort Items")
If intAnswer = vbYes Then
strSQLStmt = "SELECT intSort " & _
"FROM tblClaimLineItems " & _
"WHERE tblClaimLineItems.[lngClaimNumber] = '" & Me.TXTClaimNumber & "'" & _
"ORDER BY intSort,idsLineItemID;"
rsProductItems.Open strSQLStmt, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Count = 2
rsProductItems.MoveFirst
Do Until rsProductItems.EOF
rsProductItems!intSort = Count
Count = Count + 2
rsProductItems.MoveNext
Loop
End If
Me.Form.Refresh
rsProductItems.Close
Exit Sub
Toggle97_Click_Err:
MsgBox "Error is " & Err.Description
Exit Sub
End Sub

The bold part of the code was the part I needed to update my recordset. It still has to be run twice in order to show the updated numbers. I'll work on it a little more and hopefully this will help someone too.

 

[jgrayek]

Try changing Me.Form.Refresh to Me.Form.Requery.

I think Refresh just tells windows that the form needs to be redrawn, Requery does just that, reloads the forms data based on its current Recordsource

jesse