Please Help Bulid VBA Code/Application

[astra84]

Hi Randy,

Just as a trail is used

101 - 001 - 01
to
101 - 004 - 01

I used the default entries on the other fields, Once RUN is clicked the "complete" window does not open and access crashes, If you open the txt file from c: drive it runs into the hundreds of thousands. I will attach a screen dump

Regards Chris

[astra84]

Hi Randy,

Please see the attached file,

I went from 001 to 004 and it ended up generating the following results

MHA Rack Section Y-coor Type MaxWght

11B2 101 001 01 .1 0.1
THROUGH TO
11B2 101 3648022 01 .1 0.1

Regards Chris

[CreganTur]

Got it fixed. Use the attached database- it works correctly now.

FYI, here are the details of the error:

The error occured in this section of code:
[VBA]Do
SectionStart = SectionStart + 3
SectionCheck = SectionStart
If Len(SectionStart) = 1 Then
SectionStart = "00" & SectionStart
ElseIf Len(SectionStart) = 2 Then
SectionStart = "0" & SectionStart
End If
txtfile.Writeline (MHA & vbTab & RackStart & vbTab & SectionStart & vbTab & YStart _
& vbTab & bType & vbTab & MaxWght)
Loop Until SectionCheck = SectionStop[/VBA]

Using the coordinates you tested with, SectionCheck = "4", but SectionStop = "004".

The error was because SectionCheck was missing the leading zeros. Because of this SectionCheck could never = SectionStop, so the loop continues to infinity

By adding the leading zeros in, the code works because now SectionCheck can = SectionStop.

Corrected code:

[VBA]Do
SectionStart = SectionStart + 3
SectionCheck = SectionStart
If Len(SectionCheck) = 1 Then
SectionCheck = "00" & SectionCheck
ElseIf Len(SectionCheck) = 2 Then
SectionCheck = "0" & SectionCheck
End If
If Len(SectionStart) = 1 Then
SectionStart = "00" & SectionStart
ElseIf Len(SectionStart) = 2 Then
SectionStart = "0" & SectionStart
End If
txtfile.Writeline (MHA & vbTab & RackStart & vbTab & SectionStart & vbTab & YStart _
& vbTab & bType & vbTab & MaxWght)
Loop Until SectionCheck = SectionStop[/VBA]

[astra84]

Hi Randy,

I tried it again today before seeing your revised code. I entered just the number 4 and it works fine.

Thanks again

Regards Chris

[CreganTur]

After some more research I finally found a way to calculate Location with a math function, instead of using 47 Select Case steps

Below are the 2 procedures that allow me to correctly calculate the location based on any number provided. It also returns the value as a String (since leading zeros are required by Astra for this txt file).

[vba]Function Ceiling(Number As Double) As Long
Ceiling = -Int(-Number)
End Function

Sub LocationCount(XStart As Integer, Section As String)
Dim i As Integer
i = XStart
If Ceiling((i - 1) / 3) = ((i - 1) / 3) Then
If Len(CStr(i)) = 1 Then
Section = "00" & i
ElseIf Len(CStr(i)) = 2 Then
Section = "0" & i
End If
ElseIf Ceiling((i - 2) / 3) = ((i - 2) / 3) Then
If Len(CStr(i)) = 1 Then
Section = "00" & i-1
ElseIf Len(CStr(i)) = 2 Then
Section = "0" & i-1
End If
Else
If Len(CStr(i)) = 1 Then
Section = "00" & i-2
ElseIf Len(CStr(i)) = 2 Then
Section = "0" & i-2
End If
End If

End Sub[/vba]

Astra, I've attached a new version of the RackForms database that incorporates this new procedure into the Location setup. What this means is that you can calculate the number of any Location you want, no matter what X-coor you use. This also means that you won't have to write more Select Case steps if you want to calculate Locations above X-coor 139.