|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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 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
|
|
"DS" <bootybox[ at ]optonline.net> wrote in message
news:Zi0Lg.37$fm1.14[ at ]newsfe10.lga
[Quoted Text] > 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, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
"DS" <bootybox[ at ]optonline.net> wrote in message
news:vt0Lg.38$fm1.2[ at ]newsfe10.lga
[Quoted Text] > 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
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
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 wrote:
[Quoted Text] > 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 wrote:
[Quoted Text] > "DS" <bootybox[ at ]optonline.net> wrote in message
> news:vt0Lg.38$fm1.2[ at ]newsfe10.lga
>
>>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
>
Yep! That did it! It was simple as you said!
|
|
J. Goddard wrote:
[Quoted Text] > 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 wrote:
>
>> 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
>
>
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
|
|
|