Do until Loop - make it STOP!

[Guest]

I would like the user to enter a number between 1 and 26 - to create a
numbered list of records for them, so all they have to do is type the pallet
number. Here is my code:

Dim positionlines, totallines, shpmntid As Integer
Dim dnmbr As String

positionlines = Forms!frm_Shipment_Entry!positionlinedefault
totallines = 0
dnmbr = Forms!frm_Shipment_Entry!Dienumberdefault
shpmntid = Forms!frm_Shipment_Entry!ShipmentID

Do
Do While totallines < positionlines ' Inner loop.
totallines = totallines + 1
strSQL = "Insert Into Tbl_Shipments_Dies ( ShipmentID, DieNumber,
PositionNumber )values(" & Chr$(34) & shpmntid & Chr$(34) & ", " & Chr$(34) &
dnmbr & Chr$(34) & ", " & Chr$(34) & totallines & Chr$(34) & ")"

CurrentDb.Execute strSQL
If totallines >= positionlines Then
Exit Do
End If
Loop
Loop Until totallines = positionlines

What did I do wrong that it doesn't exit the loop when the totallines =
positionlines?

Thanks,

Tammy

 

[Guest]

Hullo Tammy,
Why you do two nested loop?
I think it's useless...

 

[Steve]

Remove the lines:
Do
Loop Until totallines = positionlines

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

Because I cobbled it together from different sources... can you help me
un-nest it to make it work?

 

[Guest]

The code will still not stop on it's own. I'm getting all the right data
filled in, just 1,000's of records if I don't hit ctrl + break.

What am I missing that won't stop the code?

 

[Guest]

I tried to run your code and it works.
Check if the variable positionlines is initialised correctly (i.e. it
contains something)

 

[Roger Carlson]

This may or may not work, but you've declared your variables incorrectly if
you intend them to all be integer.

This:
Dim positionlines, totallines, shpmntid As Integer

Should be this:
Dim positionlines As Integer, totallines As Integer, shpmntid As Integer

or

Dim positionlines As Integer
Dim totallines As Integer
Dim shpmntid As Integer

You are currently declaring your first two variables as Variants, which may
be interfering with the comparison.

Something to try anyway.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[John Spencer]

Dim positionlines as Integer , totallines as Integer, shpmntid As Integer
Dim dnmbr As String

positionlines = Forms!frm_Shipment_Entry!positionlinedefault
totallines = 0
dnmbr = Forms!frm_Shipment_Entry!Dienumberdefault
shpmntid = Forms!frm_Shipment_Entry!ShipmentID


Do While totallines < positionlines
totallines = totallines + 1
strSQL = "Insert Into Tbl_Shipments_Dies ( ShipmentID, DieNumber,
PositionNumber )values(" & Chr$(34) & shpmntid & Chr$(34) & ", " &
Chr$(34) &
dnmbr & Chr$(34) & ", " & Chr$(34) & totallines & Chr$(34) & ")"

CurrentDb.Execute strSQL
Loop

OR

Dim positionlines as Integer , shpmntid As Integer
Dim dnmbr As String
Dim I as Integer

For I = 1 to PositionLines
strSQL = "Insert Into Tbl_Shipments_Dies ( ShipmentID, DieNumber,
PositionNumber )" & _
" VALUES (" & Chr(34) & shpmntid & Chr(34) & ", " & _
Chr(34) & dnmbr & Chr(34) & ", " & _
Chr(34) & I & Chr(34) & ")"

CurrentDb.Execute strSQL
Next I


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

This was the issue - thank you very much!

This may or may not work, but you've declared your variables incorrectly if
you intend them to all be integer.

This:
Dim positionlines, totallines, shpmntid As Integer

Should be this:
Dim positionlines As Integer, totallines As Integer, shpmntid As Integer

or

Dim positionlines As Integer
Dim totallines As Integer
Dim shpmntid As Integer

You are currently declaring your first two variables as Variants, which may
be interfering with the comparison.

Something to try anyway.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L