MoveNext won't move!

O

ormazd

I've been scratching my head on this for a while. In theory, this function
will open 2 tables, perform comparisons and update records when 3 conditions
are met. I'm not popping any errors, but it won't advance past the first
record. Any help would be appreciated!

------------------------------------------------------------------------------------------------
Option Compare Database
Option Explicit
Dim dbMyDB As DAO.Database, MyWorkspace As DAO.Workspace
'This routine looks for two files...one with available drop locations, one
with pallets to locate
Dim DROPS As DAO.Recordset
Dim LOCS As DAO.Recordset
Dim Unassigned As Integer
Dim Capacity As Integer
Dim In_Use As Integer
Dim Drop_1 As String
Dim Pallets As Integer
Dim LOCS_Run As String
Dim DROPS_Run As String
Dim LOCS_UOW As String
Dim DROPS_UOW As String
Dim LOCS_Location As String

Function ASSIGN_LOCS()

Set MyWorkspace = DBEngine.Workspaces(0)
Set dbMyDB = CurrentDb
Set DROPS = dbMyDB.OpenRecordset("Current Auto Drops", , dbOpenDynaset)
Debug.Print "Recordset name and type: " & DROPS.Name & vbTab & _
DROPS.Type
Debug.Print "Recordset updatable?: " & DROPS.Updatable

Set LOCS = dbMyDB.OpenRecordset("Auto Drop Locs", , dbOpenDynaset)
Debug.Print "Recordset name and type: " & LOCS.Name & vbTab & _
LOCS.Type
Debug.Print "Recordset updatable?: " & LOCS.Updatable


Unassigned = DROPS![Unassigned Pallets]
Capacity = LOCS![Capacity]
In_Use = LOCS![In Use]
Drop_1 = DROPS![Drop Zone Loc 1]
Pallets = DROPS![Pallets]
LOCS_Run = LOCS![Run Number]
DROPS_Run = DROPS![Run Number]
LOCS_UOW = LOCS![UOW]
DROPS_UOW = DROPS![UOW]
LOCS_Location = LOCS![Location]

DROPS.MoveFirst
LOCS.MoveFirst

Do While Not DROPS.EOF
With LOCS
Do While Not .EOF
If Unassigned >= Capacity Then
If In_Use = 0 Then
If Drop_1 = "*" Then
'Record must meet all 3 conditions to call update
ASSIGN_LOC
ASSIGN_DROP
Else: .MoveNext
If .EOF Then Exit Function
End If
Else: .MoveNext
If .EOF Then Exit Function
End If
Else: .MoveNext
If .EOF Then Exit Function
End If
Loop
End With
DROPS.MoveNext
If DROPS.EOF Then Exit Function
Loop
MsgBox ("All done!")
DROPS.Close
Set DROPS = Nothing
LOCS.Close
Set LOCS = Nothing
 
R

Robert Morley

It looks to me like you're not advancing if all three conditions are satisifed.

Typically, it's a good idea not to have .MoveNext inside an If/Then
structure, though obviously there are lots of exceptions to that. Your
Else: .MoveNext is also a bit hard to read...more normal indentation would
be like this:

If <whatever> Then
Else
.MoveNext
If .EOF Then Exit Function
End If

All told, your main loop would probably be better written as:

Do While Not DROPS.EOF
With LOCS
Do While Not .EOF
If (Unassigned >= Capacity) _
And (In_Use = 0) And (Drop_1 = "*") Then
'Record must meet all 3 conditions to call update
ASSIGN_LOC
ASSIGN_DROP
End If
.MoveNext
Loop
End With
DROPS.MoveNext
Loop

I'm a little puzzled by the Exit Function statements, as you're looping
until .EOF, but also exiting the function if .EOF is reached, making the
loops redundant; I've removed the comparisons in the above loop, though you
could certainly re-instate them if you felt the need. What exactly had you
intended?


Rob

I've been scratching my head on this for a while. In theory, this function
will open 2 tables, perform comparisons and update records when 3 conditions
are met. I'm not popping any errors, but it won't advance past the first
record. Any help would be appreciated!

------------------------------------------------------------------------------------------------
Option Compare Database
Option Explicit
Dim dbMyDB As DAO.Database, MyWorkspace As DAO.Workspace
'This routine looks for two files...one with available drop locations, one
with pallets to locate
Dim DROPS As DAO.Recordset
Dim LOCS As DAO.Recordset
Dim Unassigned As Integer
Dim Capacity As Integer
Dim In_Use As Integer
Dim Drop_1 As String
Dim Pallets As Integer
Dim LOCS_Run As String
Dim DROPS_Run As String
Dim LOCS_UOW As String
Dim DROPS_UOW As String
Dim LOCS_Location As String

Function ASSIGN_LOCS()

Set MyWorkspace = DBEngine.Workspaces(0)
Set dbMyDB = CurrentDb
Set DROPS = dbMyDB.OpenRecordset("Current Auto Drops", , dbOpenDynaset)
Debug.Print "Recordset name and type: " & DROPS.Name & vbTab & _
DROPS.Type
Debug.Print "Recordset updatable?: " & DROPS.Updatable

Set LOCS = dbMyDB.OpenRecordset("Auto Drop Locs", , dbOpenDynaset)
Debug.Print "Recordset name and type: " & LOCS.Name & vbTab & _
LOCS.Type
Debug.Print "Recordset updatable?: " & LOCS.Updatable


Unassigned = DROPS![Unassigned Pallets]
Capacity = LOCS![Capacity]
In_Use = LOCS![In Use]
Drop_1 = DROPS![Drop Zone Loc 1]
Pallets = DROPS![Pallets]
LOCS_Run = LOCS![Run Number]
DROPS_Run = DROPS![Run Number]
LOCS_UOW = LOCS![UOW]
DROPS_UOW = DROPS![UOW]
LOCS_Location = LOCS![Location]

DROPS.MoveFirst
LOCS.MoveFirst

Do While Not DROPS.EOF
With LOCS
Do While Not .EOF
If Unassigned >= Capacity Then
If In_Use = 0 Then
If Drop_1 = "*" Then
'Record must meet all 3 conditions to call update
ASSIGN_LOC
ASSIGN_DROP
Else: .MoveNext
If .EOF Then Exit Function
End If
Else: .MoveNext
If .EOF Then Exit Function
End If
Else: .MoveNext
If .EOF Then Exit Function
End If
Loop
End With
DROPS.MoveNext
If DROPS.EOF Then Exit Function
Loop
MsgBox ("All done!")
DROPS.Close
Set DROPS = Nothing
LOCS.Close
Set LOCS = Nothing
Click to expand...
 
P

Paolo

Hi Ormazd,

for you comparisons you are using the values you assign to your variables
outside the loop (and so they keep the same values for all the comparisons in
the loop without regard if you move to another record) and those values are
the ones contained in the first record of your tables.
So that, if you wanna use for the comparisons the values of the record you
are pointing after the movenext you have to reassign the values to your
variables after the movenext.

HTH Paolo
 
O

ormazd

Thanks for the replies.
Paolo,
I've created sub routines to reset the variables after each MoveNext
statement. I'm sure that was part of my problem, but the records still don't
update and move. Any other suggestions?


Paolo said:
Hi Ormazd,

for you comparisons you are using the values you assign to your variables
outside the loop (and so they keep the same values for all the comparisons in
the loop without regard if you move to another record) and those values are
the ones contained in the first record of your tables.
So that, if you wanna use for the comparisons the values of the record you
are pointing after the movenext you have to reassign the values to your
variables after the movenext.

HTH Paolo

ormazd said:
I've been scratching my head on this for a while. In theory, this function
will open 2 tables, perform comparisons and update records when 3 conditions
are met. I'm not popping any errors, but it won't advance past the first
record. Any help would be appreciated!

------------------------------------------------------------------------------------------------
Option Compare Database
Option Explicit
Dim dbMyDB As DAO.Database, MyWorkspace As DAO.Workspace
'This routine looks for two files...one with available drop locations, one
with pallets to locate
Dim DROPS As DAO.Recordset
Dim LOCS As DAO.Recordset
Dim Unassigned As Integer
Dim Capacity As Integer
Dim In_Use As Integer
Dim Drop_1 As String
Dim Pallets As Integer
Dim LOCS_Run As String
Dim DROPS_Run As String
Dim LOCS_UOW As String
Dim DROPS_UOW As String
Dim LOCS_Location As String

Function ASSIGN_LOCS()

Set MyWorkspace = DBEngine.Workspaces(0)
Set dbMyDB = CurrentDb
Set DROPS = dbMyDB.OpenRecordset("Current Auto Drops", , dbOpenDynaset)
Debug.Print "Recordset name and type: " & DROPS.Name & vbTab & _
DROPS.Type
Debug.Print "Recordset updatable?: " & DROPS.Updatable

Set LOCS = dbMyDB.OpenRecordset("Auto Drop Locs", , dbOpenDynaset)
Debug.Print "Recordset name and type: " & LOCS.Name & vbTab & _
LOCS.Type
Debug.Print "Recordset updatable?: " & LOCS.Updatable


Unassigned = DROPS![Unassigned Pallets]
Capacity = LOCS![Capacity]
In_Use = LOCS![In Use]
Drop_1 = DROPS![Drop Zone Loc 1]
Pallets = DROPS![Pallets]
LOCS_Run = LOCS![Run Number]
DROPS_Run = DROPS![Run Number]
LOCS_UOW = LOCS![UOW]
DROPS_UOW = DROPS![UOW]
LOCS_Location = LOCS![Location]

DROPS.MoveFirst
LOCS.MoveFirst

Do While Not DROPS.EOF
With LOCS
Do While Not .EOF
If Unassigned >= Capacity Then
If In_Use = 0 Then
If Drop_1 = "*" Then
'Record must meet all 3 conditions to call update
ASSIGN_LOC
ASSIGN_DROP
Else: .MoveNext
If .EOF Then Exit Function
End If
Else: .MoveNext
If .EOF Then Exit Function
End If
Else: .MoveNext
If .EOF Then Exit Function
End If
Loop
End With
DROPS.MoveNext
If DROPS.EOF Then Exit Function
Loop
MsgBox ("All done!")
DROPS.Close
Set DROPS = Nothing
LOCS.Close
Set LOCS = Nothing
Click to expand...
Click to expand...
 
P

Paolo

I noticed that you move the record pointer just if you don't match one of the
three conditions, so you must add a movenext also if you match the three
conditions, otherwise you'll stay forever on the first record that match the
conditions.

Cheers Paolo

ormazd said:
Thanks for the replies.
Paolo,
I've created sub routines to reset the variables after each MoveNext
statement. I'm sure that was part of my problem, but the records still don't
update and move. Any other suggestions?


Paolo said:
Hi Ormazd,

for you comparisons you are using the values you assign to your variables
outside the loop (and so they keep the same values for all the comparisons in
the loop without regard if you move to another record) and those values are
the ones contained in the first record of your tables.
So that, if you wanna use for the comparisons the values of the record you
are pointing after the movenext you have to reassign the values to your
variables after the movenext.

HTH Paolo

ormazd said:
I've been scratching my head on this for a while. In theory, this function
will open 2 tables, perform comparisons and update records when 3 conditions
are met. I'm not popping any errors, but it won't advance past the first
record. Any help would be appreciated!

------------------------------------------------------------------------------------------------
Option Compare Database
Option Explicit
Dim dbMyDB As DAO.Database, MyWorkspace As DAO.Workspace
'This routine looks for two files...one with available drop locations, one
with pallets to locate
Dim DROPS As DAO.Recordset
Dim LOCS As DAO.Recordset
Dim Unassigned As Integer
Dim Capacity As Integer
Dim In_Use As Integer
Dim Drop_1 As String
Dim Pallets As Integer
Dim LOCS_Run As String
Dim DROPS_Run As String
Dim LOCS_UOW As String
Dim DROPS_UOW As String
Dim LOCS_Location As String

Function ASSIGN_LOCS()

Set MyWorkspace = DBEngine.Workspaces(0)
Set dbMyDB = CurrentDb
Set DROPS = dbMyDB.OpenRecordset("Current Auto Drops", , dbOpenDynaset)
Debug.Print "Recordset name and type: " & DROPS.Name & vbTab & _
DROPS.Type
Debug.Print "Recordset updatable?: " & DROPS.Updatable

Set LOCS = dbMyDB.OpenRecordset("Auto Drop Locs", , dbOpenDynaset)
Debug.Print "Recordset name and type: " & LOCS.Name & vbTab & _
LOCS.Type
Debug.Print "Recordset updatable?: " & LOCS.Updatable


Unassigned = DROPS![Unassigned Pallets]
Capacity = LOCS![Capacity]
In_Use = LOCS![In Use]
Drop_1 = DROPS![Drop Zone Loc 1]
Pallets = DROPS![Pallets]
LOCS_Run = LOCS![Run Number]
DROPS_Run = DROPS![Run Number]
LOCS_UOW = LOCS![UOW]
DROPS_UOW = DROPS![UOW]
LOCS_Location = LOCS![Location]

DROPS.MoveFirst
LOCS.MoveFirst

Do While Not DROPS.EOF
With LOCS
Do While Not .EOF
If Unassigned >= Capacity Then
If In_Use = 0 Then
If Drop_1 = "*" Then
'Record must meet all 3 conditions to call update
ASSIGN_LOC
ASSIGN_DROP
Else: .MoveNext
If .EOF Then Exit Function
End If
Else: .MoveNext
If .EOF Then Exit Function
End If
Else: .MoveNext
If .EOF Then Exit Function
End If
Loop
End With
DROPS.MoveNext
If DROPS.EOF Then Exit Function
Loop
MsgBox ("All done!")
DROPS.Close
Set DROPS = Nothing
LOCS.Close
Set LOCS = Nothing
Click to expand...
Click to expand...
Click to expand...
 
O

ormazd

Paolo,
The subs should handle that:

Sub ASSIGN_LOC()
Set LOCS = dbMyDB.OpenRecordset("Auto Drop Locs", , dbOpenDynaset)

With LOCS
Debug.Print "EditMode before editing: " & .EditMode
.Edit
In_Use = Capacity
LOCS_Run = DROPS_Run
LOCS_UOW = DROPS_UOW
Debug.Print "EditMode after Edit : " & .EditMode
.Update
Debug.Print "EditMode after updating: " & .EditMode
.MoveNext
End With
End Sub

Sub ASSIGN_DROP()
Set DROPS = dbMyDB.OpenRecordset("Current Auto Drops", , dbOpenDynaset)
With DROPS
.Edit
Unassigned = Pallets - Capacity
Drop_1 = LOCS_Location
.Update
.MoveNext
End With
End Sub

Paolo said:
I noticed that you move the record pointer just if you don't match one of the
three conditions, so you must add a movenext also if you match the three
conditions, otherwise you'll stay forever on the first record that match the
conditions.

Cheers Paolo

ormazd said:
Thanks for the replies.
Paolo,
I've created sub routines to reset the variables after each MoveNext
statement. I'm sure that was part of my problem, but the records still don't
update and move. Any other suggestions?


Paolo said:
Hi Ormazd,

for you comparisons you are using the values you assign to your variables
outside the loop (and so they keep the same values for all the comparisons in
the loop without regard if you move to another record) and those values are
the ones contained in the first record of your tables.
So that, if you wanna use for the comparisons the values of the record you
are pointing after the movenext you have to reassign the values to your
variables after the movenext.

HTH Paolo

:

I've been scratching my head on this for a while. In theory, this function
will open 2 tables, perform comparisons and update records when 3 conditions
are met. I'm not popping any errors, but it won't advance past the first
record. Any help would be appreciated!

------------------------------------------------------------------------------------------------
Option Compare Database
Option Explicit
Dim dbMyDB As DAO.Database, MyWorkspace As DAO.Workspace
'This routine looks for two files...one with available drop locations, one
with pallets to locate
Dim DROPS As DAO.Recordset
Dim LOCS As DAO.Recordset
Dim Unassigned As Integer
Dim Capacity As Integer
Dim In_Use As Integer
Dim Drop_1 As String
Dim Pallets As Integer
Dim LOCS_Run As String
Dim DROPS_Run As String
Dim LOCS_UOW As String
Dim DROPS_UOW As String
Dim LOCS_Location As String

Function ASSIGN_LOCS()

Set MyWorkspace = DBEngine.Workspaces(0)
Set dbMyDB = CurrentDb
Set DROPS = dbMyDB.OpenRecordset("Current Auto Drops", , dbOpenDynaset)
Debug.Print "Recordset name and type: " & DROPS.Name & vbTab & _
DROPS.Type
Debug.Print "Recordset updatable?: " & DROPS.Updatable

Set LOCS = dbMyDB.OpenRecordset("Auto Drop Locs", , dbOpenDynaset)
Debug.Print "Recordset name and type: " & LOCS.Name & vbTab & _
LOCS.Type
Debug.Print "Recordset updatable?: " & LOCS.Updatable


Unassigned = DROPS![Unassigned Pallets]
Capacity = LOCS![Capacity]
In_Use = LOCS![In Use]
Drop_1 = DROPS![Drop Zone Loc 1]
Pallets = DROPS![Pallets]
LOCS_Run = LOCS![Run Number]
DROPS_Run = DROPS![Run Number]
LOCS_UOW = LOCS![UOW]
DROPS_UOW = DROPS![UOW]
LOCS_Location = LOCS![Location]

DROPS.MoveFirst
LOCS.MoveFirst

Do While Not DROPS.EOF
With LOCS
Do While Not .EOF
If Unassigned >= Capacity Then
If In_Use = 0 Then
If Drop_1 = "*" Then
'Record must meet all 3 conditions to call update
ASSIGN_LOC
ASSIGN_DROP
Else: .MoveNext
If .EOF Then Exit Function
End If
Else: .MoveNext
If .EOF Then Exit Function
End If
Else: .MoveNext
If .EOF Then Exit Function
End If
Loop
End With
DROPS.MoveNext
If DROPS.EOF Then Exit Function
Loop
MsgBox ("All done!")
DROPS.Close
Set DROPS = Nothing
LOCS.Close
Set LOCS = Nothing
Click to expand...
Click to expand...
Click to expand...
 
O

ormazd

I just figured out part of the issue. Using the OpenRecordset inside the sub
always reset the table to the first record. Manually walking thru the Debug
routine now shows me the records are using MoveNext correctly. They STILL
aren't updating, though. Grrrrr
I appreciate any more help you can provide!

ormazd said:
Paolo,
The subs should handle that:

Sub ASSIGN_LOC()
Set LOCS = dbMyDB.OpenRecordset("Auto Drop Locs", , dbOpenDynaset)

With LOCS
Debug.Print "EditMode before editing: " & .EditMode
.Edit
In_Use = Capacity
LOCS_Run = DROPS_Run
LOCS_UOW = DROPS_UOW
Debug.Print "EditMode after Edit : " & .EditMode
.Update
Debug.Print "EditMode after updating: " & .EditMode
.MoveNext
End With
End Sub

Sub ASSIGN_DROP()
Set DROPS = dbMyDB.OpenRecordset("Current Auto Drops", , dbOpenDynaset)
With DROPS
.Edit
Unassigned = Pallets - Capacity
Drop_1 = LOCS_Location
.Update
.MoveNext
End With
End Sub

Paolo said:
I noticed that you move the record pointer just if you don't match one of the
three conditions, so you must add a movenext also if you match the three
conditions, otherwise you'll stay forever on the first record that match the
conditions.

Cheers Paolo

ormazd said:
Thanks for the replies.
Paolo,
I've created sub routines to reset the variables after each MoveNext
statement. I'm sure that was part of my problem, but the records still don't
update and move. Any other suggestions?


:

Hi Ormazd,

for you comparisons you are using the values you assign to your variables
outside the loop (and so they keep the same values for all the comparisons in
the loop without regard if you move to another record) and those values are
the ones contained in the first record of your tables.
So that, if you wanna use for the comparisons the values of the record you
are pointing after the movenext you have to reassign the values to your
variables after the movenext.

HTH Paolo

:

I've been scratching my head on this for a while. In theory, this function
will open 2 tables, perform comparisons and update records when 3 conditions
are met. I'm not popping any errors, but it won't advance past the first
record. Any help would be appreciated!

------------------------------------------------------------------------------------------------
Option Compare Database
Option Explicit
Dim dbMyDB As DAO.Database, MyWorkspace As DAO.Workspace
'This routine looks for two files...one with available drop locations, one
with pallets to locate
Dim DROPS As DAO.Recordset
Dim LOCS As DAO.Recordset
Dim Unassigned As Integer
Dim Capacity As Integer
Dim In_Use As Integer
Dim Drop_1 As String
Dim Pallets As Integer
Dim LOCS_Run As String
Dim DROPS_Run As String
Dim LOCS_UOW As String
Dim DROPS_UOW As String
Dim LOCS_Location As String

Function ASSIGN_LOCS()

Set MyWorkspace = DBEngine.Workspaces(0)
Set dbMyDB = CurrentDb
Set DROPS = dbMyDB.OpenRecordset("Current Auto Drops", , dbOpenDynaset)
Debug.Print "Recordset name and type: " & DROPS.Name & vbTab & _
DROPS.Type
Debug.Print "Recordset updatable?: " & DROPS.Updatable

Set LOCS = dbMyDB.OpenRecordset("Auto Drop Locs", , dbOpenDynaset)
Debug.Print "Recordset name and type: " & LOCS.Name & vbTab & _
LOCS.Type
Debug.Print "Recordset updatable?: " & LOCS.Updatable


Unassigned = DROPS![Unassigned Pallets]
Capacity = LOCS![Capacity]
In_Use = LOCS![In Use]
Drop_1 = DROPS![Drop Zone Loc 1]
Pallets = DROPS![Pallets]
LOCS_Run = LOCS![Run Number]
DROPS_Run = DROPS![Run Number]
LOCS_UOW = LOCS![UOW]
DROPS_UOW = DROPS![UOW]
LOCS_Location = LOCS![Location]

DROPS.MoveFirst
LOCS.MoveFirst

Do While Not DROPS.EOF
With LOCS
Do While Not .EOF
If Unassigned >= Capacity Then
If In_Use = 0 Then
If Drop_1 = "*" Then
'Record must meet all 3 conditions to call update
ASSIGN_LOC
ASSIGN_DROP
Else: .MoveNext
If .EOF Then Exit Function
End If
Else: .MoveNext
If .EOF Then Exit Function
End If
Else: .MoveNext
If .EOF Then Exit Function
End If
Loop
End With
DROPS.MoveNext
If DROPS.EOF Then Exit Function
Loop
MsgBox ("All done!")
DROPS.Close
Set DROPS = Nothing
LOCS.Close
Set LOCS = Nothing
Click to expand...
Click to expand...
Click to expand...
 
P

Paolo

In the way you are proceding you make the movenext on the locs recordset that
you've opened in the sub.
For me the best thing you can do is to pass the record id and the new values
to a function so you can open a new recordset with just the record you wanna
update and make your update. Then you do the movenext in the main procedure.
Otherwise do the update in the main procedure. Obvipusly also the movenext.

HTH Paolo

ormazd said:
I just figured out part of the issue. Using the OpenRecordset inside the sub
always reset the table to the first record. Manually walking thru the Debug
routine now shows me the records are using MoveNext correctly. They STILL
aren't updating, though. Grrrrr
I appreciate any more help you can provide!

ormazd said:
Paolo,
The subs should handle that:

Sub ASSIGN_LOC()
Set LOCS = dbMyDB.OpenRecordset("Auto Drop Locs", , dbOpenDynaset)

With LOCS
Debug.Print "EditMode before editing: " & .EditMode
.Edit
In_Use = Capacity
LOCS_Run = DROPS_Run
LOCS_UOW = DROPS_UOW
Debug.Print "EditMode after Edit : " & .EditMode
.Update
Debug.Print "EditMode after updating: " & .EditMode
.MoveNext
End With
End Sub

Sub ASSIGN_DROP()
Set DROPS = dbMyDB.OpenRecordset("Current Auto Drops", , dbOpenDynaset)
With DROPS
.Edit
Unassigned = Pallets - Capacity
Drop_1 = LOCS_Location
.Update
.MoveNext
End With
End Sub

Paolo said:
I noticed that you move the record pointer just if you don't match one of the
three conditions, so you must add a movenext also if you match the three
conditions, otherwise you'll stay forever on the first record that match the
conditions.

Cheers Paolo

:

Thanks for the replies.
Paolo,
I've created sub routines to reset the variables after each MoveNext
statement. I'm sure that was part of my problem, but the records still don't
update and move. Any other suggestions?


:

Hi Ormazd,

for you comparisons you are using the values you assign to your variables
outside the loop (and so they keep the same values for all the comparisons in
the loop without regard if you move to another record) and those values are
the ones contained in the first record of your tables.
So that, if you wanna use for the comparisons the values of the record you
are pointing after the movenext you have to reassign the values to your
variables after the movenext.

HTH Paolo

:

I've been scratching my head on this for a while. In theory, this function
will open 2 tables, perform comparisons and update records when 3 conditions
are met. I'm not popping any errors, but it won't advance past the first
record. Any help would be appreciated!

------------------------------------------------------------------------------------------------
Option Compare Database
Option Explicit
Dim dbMyDB As DAO.Database, MyWorkspace As DAO.Workspace
'This routine looks for two files...one with available drop locations, one
with pallets to locate
Dim DROPS As DAO.Recordset
Dim LOCS As DAO.Recordset
Dim Unassigned As Integer
Dim Capacity As Integer
Dim In_Use As Integer
Dim Drop_1 As String
Dim Pallets As Integer
Dim LOCS_Run As String
Dim DROPS_Run As String
Dim LOCS_UOW As String
Dim DROPS_UOW As String
Dim LOCS_Location As String

Function ASSIGN_LOCS()

Set MyWorkspace = DBEngine.Workspaces(0)
Set dbMyDB = CurrentDb
Set DROPS = dbMyDB.OpenRecordset("Current Auto Drops", , dbOpenDynaset)
Debug.Print "Recordset name and type: " & DROPS.Name & vbTab & _
DROPS.Type
Debug.Print "Recordset updatable?: " & DROPS.Updatable

Set LOCS = dbMyDB.OpenRecordset("Auto Drop Locs", , dbOpenDynaset)
Debug.Print "Recordset name and type: " & LOCS.Name & vbTab & _
LOCS.Type
Debug.Print "Recordset updatable?: " & LOCS.Updatable


Unassigned = DROPS![Unassigned Pallets]
Capacity = LOCS![Capacity]
In_Use = LOCS![In Use]
Drop_1 = DROPS![Drop Zone Loc 1]
Pallets = DROPS![Pallets]
LOCS_Run = LOCS![Run Number]
DROPS_Run = DROPS![Run Number]
LOCS_UOW = LOCS![UOW]
DROPS_UOW = DROPS![UOW]
LOCS_Location = LOCS![Location]

DROPS.MoveFirst
LOCS.MoveFirst

Do While Not DROPS.EOF
With LOCS
Do While Not .EOF
If Unassigned >= Capacity Then
If In_Use = 0 Then
If Drop_1 = "*" Then
'Record must meet all 3 conditions to call update
ASSIGN_LOC
ASSIGN_DROP
Else: .MoveNext
If .EOF Then Exit Function
End If
Else: .MoveNext
If .EOF Then Exit Function
End If
Else: .MoveNext
If .EOF Then Exit Function
End If
Loop
End With
DROPS.MoveNext
If DROPS.EOF Then Exit Function
Loop
MsgBox ("All done!")
DROPS.Close
Set DROPS = Nothing
LOCS.Close
Set LOCS = Nothing
Click to expand...
Click to expand...
Click to expand...
 
O

ormazd

I think I've got it. Thanks for helping an amateur slog through this project!

Paolo said:
In the way you are proceding you make the movenext on the locs recordset that
you've opened in the sub.
For me the best thing you can do is to pass the record id and the new values
to a function so you can open a new recordset with just the record you wanna
update and make your update. Then you do the movenext in the main procedure.
Otherwise do the update in the main procedure. Obvipusly also the movenext.

HTH Paolo

ormazd said:
I just figured out part of the issue. Using the OpenRecordset inside the sub
always reset the table to the first record. Manually walking thru the Debug
routine now shows me the records are using MoveNext correctly. They STILL
aren't updating, though. Grrrrr
I appreciate any more help you can provide!

ormazd said:
Paolo,
The subs should handle that:

Sub ASSIGN_LOC()
Set LOCS = dbMyDB.OpenRecordset("Auto Drop Locs", , dbOpenDynaset)

With LOCS
Debug.Print "EditMode before editing: " & .EditMode
.Edit
In_Use = Capacity
LOCS_Run = DROPS_Run
LOCS_UOW = DROPS_UOW
Debug.Print "EditMode after Edit : " & .EditMode
.Update
Debug.Print "EditMode after updating: " & .EditMode
.MoveNext
End With
End Sub

Sub ASSIGN_DROP()
Set DROPS = dbMyDB.OpenRecordset("Current Auto Drops", , dbOpenDynaset)
With DROPS
.Edit
Unassigned = Pallets - Capacity
Drop_1 = LOCS_Location
.Update
.MoveNext
End With
End Sub

:

I noticed that you move the record pointer just if you don't match one of the
three conditions, so you must add a movenext also if you match the three
conditions, otherwise you'll stay forever on the first record that match the
conditions.

Cheers Paolo

:

Thanks for the replies.
Paolo,
I've created sub routines to reset the variables after each MoveNext
statement. I'm sure that was part of my problem, but the records still don't
update and move. Any other suggestions?


:

Hi Ormazd,

for you comparisons you are using the values you assign to your variables
outside the loop (and so they keep the same values for all the comparisons in
the loop without regard if you move to another record) and those values are
the ones contained in the first record of your tables.
So that, if you wanna use for the comparisons the values of the record you
are pointing after the movenext you have to reassign the values to your
variables after the movenext.

HTH Paolo

:

I've been scratching my head on this for a while. In theory, this function
will open 2 tables, perform comparisons and update records when 3 conditions
are met. I'm not popping any errors, but it won't advance past the first
record. Any help would be appreciated!

------------------------------------------------------------------------------------------------
Option Compare Database
Option Explicit
Dim dbMyDB As DAO.Database, MyWorkspace As DAO.Workspace
'This routine looks for two files...one with available drop locations, one
with pallets to locate
Dim DROPS As DAO.Recordset
Dim LOCS As DAO.Recordset
Dim Unassigned As Integer
Dim Capacity As Integer
Dim In_Use As Integer
Dim Drop_1 As String
Dim Pallets As Integer
Dim LOCS_Run As String
Dim DROPS_Run As String
Dim LOCS_UOW As String
Dim DROPS_UOW As String
Dim LOCS_Location As String

Function ASSIGN_LOCS()

Set MyWorkspace = DBEngine.Workspaces(0)
Set dbMyDB = CurrentDb
Set DROPS = dbMyDB.OpenRecordset("Current Auto Drops", , dbOpenDynaset)
Debug.Print "Recordset name and type: " & DROPS.Name & vbTab & _
DROPS.Type
Debug.Print "Recordset updatable?: " & DROPS.Updatable

Set LOCS = dbMyDB.OpenRecordset("Auto Drop Locs", , dbOpenDynaset)
Debug.Print "Recordset name and type: " & LOCS.Name & vbTab & _
LOCS.Type
Debug.Print "Recordset updatable?: " & LOCS.Updatable


Unassigned = DROPS![Unassigned Pallets]
Capacity = LOCS![Capacity]
In_Use = LOCS![In Use]
Drop_1 = DROPS![Drop Zone Loc 1]
Pallets = DROPS![Pallets]
LOCS_Run = LOCS![Run Number]
DROPS_Run = DROPS![Run Number]
LOCS_UOW = LOCS![UOW]
DROPS_UOW = DROPS![UOW]
LOCS_Location = LOCS![Location]

DROPS.MoveFirst
LOCS.MoveFirst

Do While Not DROPS.EOF
With LOCS
Do While Not .EOF
If Unassigned >= Capacity Then
If In_Use = 0 Then
If Drop_1 = "*" Then
'Record must meet all 3 conditions to call update
ASSIGN_LOC
ASSIGN_DROP
Else: .MoveNext
If .EOF Then Exit Function
End If
Else: .MoveNext
If .EOF Then Exit Function
End If
Else: .MoveNext
If .EOF Then Exit Function
End If
Loop
End With
DROPS.MoveNext
If DROPS.EOF Then Exit Function
Loop
MsgBox ("All done!")
DROPS.Close
Set DROPS = Nothing
LOCS.Close
Set LOCS = Nothing
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Compile Error ?? 2
getting right string in table 5
Creating subroutine with Function using sql recordsets 1
get output of immediate window in report 4
Runtime 3701 on .MoveNext 2
Error Accessing Database 1
Too few parameters? 11
Passing a Concatenated String to a Field on a Report 2

Top