Goto Record in acHidden Form

[craigachan]

I'm trying to sort a string of numbers. Each number is separated by a comma and there may be spaces. Example: 4,6,2,35,26 ,24

I've written a routine to strip out the spaces, then sort the numbers by using tempTables, then reassembling the string in sorted order. So now my string is: 2,4,6,24,26,35.

My code works well but I get a screen flash when I run the code, mostly from my forms opening and closing. So I tried to hide the Forms while it runs, but then I get a runtime 2105 error: can't go to the specified record. If I unhide my forms again, it works great.

Question: Why can't I goto a record on a form that is hidden? or am I missing something else.

Question: If I can't do anything else, how can I get rid of the flashing screen from my forms opening and closign?

Here is the code:

Private Sub cmdSort_Click()
'------------Get rid of spaces
Dim cc As Integer
Dim x As Integer
Dim Rm As String
Dim StrNms As String
Dim strNew As String
Dim strRem As String ' string remaining
Dim NOfC As Integer
Dim c2c As Integer 'count to comma
cc = Len(Me.Before)
StrNms = Me.Before
strNew = ""
strRem = StrNms
 
For x = 1 To cc
If Left(strRem, 1) <> Chr(32) Then
strNew = strNew & Left(strRem, 1)
Else
strNew = strNew
 
End If
 
If Left(strRem, 1) = "," Then
NOfC = NOfC + 1 ' count the commas
End If
 
strRem = Right(StrNms, cc - x)
 
Next x
 
Me.After = strNew ' REMOVE LATER
 
'-------------------Put strNew in table as numbers Then Sort
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDeltempChartsortNum"
DoCmd.Close acQuery, "qrydeltempchartsortnum"
DoCmd.SetWarnings True
 
DoCmd.OpenForm "tempChartSortNum", acFormDS, , , acFormEdit
 
Dim ftemp As Form
Dim strRemHead As String
Dim strPreHead As String
Dim pc As Integer 'previous comma
Dim st As Integer 'starting point
 
Set ftemp = Forms!tempChartSortNum
 
st = 1
pc = 1
strRemHead = strNew
x = 1
For x = 1 To (NOfC + 1)
 
DoCmd.GoToRecord , , acGoTo, x
c2c = InStr(st, strRemHead, ",")
 
If c2c <> 0 Then
ftemp.Number = Mid(strRemHead, st, c2c - 1)
strRemHead = Right(strRemHead, Len(strRemHead) - c2c)
 
Else
ftemp.Number = strRemHead
End If
Next x
DoCmd.Close acForm, "tempChartsortNum"
DoCmd.OpenForm "tempChartsortedNum"
 
'-------------------Rebuild numbers in sort order
 
Dim RC As Integer 'Record Count
strNew = ""
RC = DCount("*", "tempChartSortNum")
 
For x = 1 To RC
DoCmd.GoToRecord , , acGoTo, x
 
If strNew = "" Then
strNew = Forms!tempChartsortedNum.Number
Else
strNew = strNew & "," & Forms!tempChartsortedNum.Number
End If
 
Next x
Me.Sorted = strNew
DoCmd.Close acForm, "tempchartsortednum"
 
End Sub

Thanks for your help

 

[HiTechCoach]

I am not sure you you are using a form to do this when you could just use a recordset that uses the same record source as the form which is already "hidden".

Curious, why a form?

What version of Access?

Tipe: There is a Replace() function in Acess 2000 and later.

 

[HiTechCoach]

If you will put this code in a stand module named modSort

Option Compare Database
Option Explicit


Public Function SortDelimitedStringOfNumbers(pInString As String, pDelimiter As String) As String

Dim i As Long, ii As Long
Dim temp
Dim strParts() As String

strParts() = Split(pInString, pDelimiter)

For i = 1 To UBound(strParts)

   strParts(i) = Trim(strParts(i))
Next


For i = 0 To UBound(strParts)
    For ii = i To UBound(strParts)
        If Val(strParts(i)) > Val(strParts(ii)) Then
        temp = strParts(ii)
        strParts(ii) = strParts(i)
        strParts(i) = temp
    End If
    Next
Next

SortDelimitedStringOfNumbers = Join(strParts, pDelimiter)

End Function

You could call this function in a query, form, report, VBA code as needed.

Example:

? SortDelimitedStringOfNumbers("4,6,2,35,26 ,24", ",")
2,4,6,24,26,35

 

[HiTechCoach]

If I understand your code correctly, you could use my function like this:

Private Sub cmdSort_Click()

  Me.Sorted = SortDelimitedStringOfNumbers( Me.Before, ",")
 
End Sub

 

[craigachan]

Thanks HiTechCoach for your reply.

I'm a self taught vba, amature and have only been able to learn this code as I see examples. I've used the form as this is the way I've learned it from the examples I've seen so far. I'm not sure what you mean (because I'm reletively new to VBA) by

I am not sure you you are using a form to do this when you could just use a recordset that uses the same record source as the form which is already "hidden".

In the past I've tried to use examples using recordset, but I just can't quite get the syntax. I must not be understanding something about it. So I've learned from other examples to do it by using forms. I'm by no means stuck on getting these numbers sorted by a form. Could you give me an example of using the recordset?

Your SortDelimitedStringOfNumbers() is a lot less code then mine. Thanks so much. I love this forum!


I'm been trying to learn VBA and have learned quite a bit from this forum. The code that you gave me will give me quite a bit to chew on for a while. Thanks again.