Loop Returning One Value

[DS]

I have this loop statement that is almost working, the problem is that
instead of starting at the first record, making it the next highest
LineID available, then going to the next and makeing that the next
LineID +1 its just finding the highest Line ID at the end of the
recordset and making all the LineID's in every record that one.
I should have this//
1
2
3
4
5
6
but I'm getting this
6
6
6
6
6
6

Any Help Appreciated,
Thanks
DS

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM SplitHold WHERE
SplitHold.SalesID = " & Me.TxtLeftID & "", dbOpenDynaset)
With rst
Do Until .EOF
Me.TxtNextLine = DMax("LineID", "SplitHold", "SalesID =
Forms!MoveItem!TxtLeftID")
Me.TxtNextLine = Me.TxtNextLine + 1
Dim NewSQL As String
DoCmd.SetWarnings False
NewSQL = "UPDATE SplitHold SET
SplitHold.LineID=Forms!MoveItem!TxtNextLine " & _
"WHERE SplitHold.SalesID = Forms!MoveItem!TxtLeftID;"
DoCmd.RunSQL (NewSQL)
DoCmd.SetWarnings True
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

 

[DS]

DS wrote:
So I think this is closer but I need the Edit Method?

With rst
Do Until .EOF
Me.TxtNextLine = DMax("LineID", "SplitHold", "SalesID =
Forms!MoveItem!TxtLeftID")
Me.TxtNextLine = Me.TxtNextLine + 1
.Edit
.Update
!LineID = Me.TxtNextLine
.MoveNext
Loop
End With

Thanks
DS

 

[Dirk Goldgar]

I have this loop statement that is almost working, the problem is that
instead of starting at the first record, making it the next highest
LineID available, then going to the next and makeing that the next
LineID +1 its just finding the highest Line ID at the end of the
recordset and making all the LineID's in every record that one.
I should have this//
1
2
3
4
5
6
but I'm getting this
6
6
6
6
6
6

Any Help Appreciated,
Thanks
DS

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM SplitHold WHERE
SplitHold.SalesID = " & Me.TxtLeftID & "", dbOpenDynaset)
With rst
Do Until .EOF
Me.TxtNextLine = DMax("LineID", "SplitHold", "SalesID =
Forms!MoveItem!TxtLeftID")
Me.TxtNextLine = Me.TxtNextLine + 1
Dim NewSQL As String
DoCmd.SetWarnings False
NewSQL = "UPDATE SplitHold SET
SplitHold.LineID=Forms!MoveItem!TxtNextLine " & _
"WHERE SplitHold.SalesID = Forms!MoveItem!TxtLeftID;"
DoCmd.RunSQL (NewSQL)
DoCmd.SetWarnings True
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

Pardon my saying so, DS, but that code looks a bit odd to me. What
exactly are you trying to do? As it is, it's going to repeatedly update
all the records in SplitHold with the matching SalesID, setting the
LineID of all those matching records to the latest one you calculated.
So for the last record in the recordset, you'll set all the matching
records to the last LineID you calculated.

Could you describe in words what you have and what you are trying to
accomplish? I'm pretty sure it's a lot simpler than you're making it.

 

[Dirk Goldgar]

DS wrote:
So I think this is closer but I need the Edit Method?

With rst
Do Until .EOF
Me.TxtNextLine = DMax("LineID", "SplitHold", "SalesID =
Forms!MoveItem!TxtLeftID")
Me.TxtNextLine = Me.TxtNextLine + 1
.Edit
.Update
!LineID = Me.TxtNextLine
.MoveNext
Loop
End With

At the very least you need to put the .Update line after the line that
changes !LineID:

.Edit
!LineID = Me.TxtNextLine
.Update

 

[J. Goddard]

DS -

You are initializing Me!txtnextline inside your loop on each iteration;
me!txtnextline = Dmax.... should be above the Do Until... line.

As Dirk has pointed out, you need the !lineid = me!txtnextline between
the .edit and the .update.

But what are you trying to do? From what I can see from the code, all
it does is change the values for one SalesId from

4
5
6
7
8

to

9
10
11
12
13

Is that what you want?

John

 

[DS]

At the very least you need to put the .Update line after the line that
changes !LineID:

.Edit
!LineID = Me.TxtNextLine
.Update

Yep! That did it! It was simple as you said!

 

[DS]

DS -

You are initializing Me!txtnextline inside your loop on each iteration;
me!txtnextline = Dmax.... should be above the Do Until... line.

As Dirk has pointed out, you need the !lineid = me!txtnextline between
the .edit and the .update.

But what are you trying to do? From what I can see from the code, all
it does is change the values for one SalesId from

4
5
6
7
8

to

9
10
11
12
13

Is that what you want?

John

Yes that is what I want. It works now. Just had to take sometime to
work through it and also a little help from the newsgroup for good measure!
Thanks
DS