vba table update question??

  • Thread starter Thread starter Norman Fritag
  • Start date Start date
N

Norman Fritag

Hi there

I have there a situation whereas I want to evaluate behind a form, what the
change for a range was.
The Range is set as txtRangeFrom and to txtRangeTo. Maximum range is 0 to
12.

Now the use wants to make all kinds of changes to the range, which can vary
from eg: 7 - 9, 0 - 6, 0 - 6.
I have 3 default ranges 0 - 12, 7 - 12, 0 - 6.
The Ranges are related to a the table Issues as one to many relation ship,
on issues has a range category from to, which is set on this form. I would
have to check as well if for the desired range changes request, data against
this record were entered or not and than flag appropriate actions. ( the
later part is resolved)

the answer I looking for is to know how I could specifically tell, what
changes were made (is resolved ) and what parts / items/data of the range, I
would have to add or delete from the control table?

eg1: if the range was 0 - 11 >>> 0,1,2,3,4,5,6,7,8,9,10,11
New range required 7 - 9, meaning that 0 - 6 related and 10 - 11
record from the control table would need to be deleted . New range =
7,8,9
eg2: if the range was 7 - 9 >>> 7,8,9
new range required 0 - 7, Meaning 0 - 6 would need to be added,
8 - 9 to be deleted from the control table.
New range is 0,1,2,3,4,5,6,7
eg3: if the range was 0 - 7 >>> 0,1,2,3,4,5,6,7
new range required 4 - 9, Meaning 8 - 9 would need to be added,
0 - 3 would need to be deleted from the control table. New range is
4,5,6,7,8,9

the scenarios could go on and on....

I guess the answer that I am looking for is more about, what the program has
to do, after it found out,
what the differences between the old and the new range is. Whether it should
delete rows that fall into the range 0 - 6 (meaning all rows of the control
table, that fall into this range)
and or add rows that are not there if required and leave those parts of the
range that are unchanged.

the control table has this columns: Issue, range (= range form and to) and
etc....
1 , 0
1 , 1 til range 6

That is what I am looking for to resolve.


I was thinking of reading the range into and array and than compare the 2
range with the result to say what numbers are there to keep, what numbers
are not there to add and those number that are to much to delete, ( I words)
but is have very few experience working with arrays.

eg:
'to delete from controle table
For intx = Me.txtRangefrom.OldValue To Me.txtrangeto.OldValue

strOldRange = strOldRange & intx & ", "

Next intx
For intx = Me.txtRangefrom To Me.txtRangesto

StrNewRange = strNewRange & intx & ", "

Next intx
'array evaluation goes here
??
intfrom = Int(StrNewRangefrom) '
intNewto = Int(StrNewRangeto)
' followed by a delete query where the range is eg : 0 - 6

'to add to controle table
For intx = Me.txtRangefrom.OldValue To Me.txtrangeto.OldValue

strOldRange = strOldRange & intx & ", "

Next intx
For intx = Me.txtRangefrom To Me.txtRangesto

StrNewRange = strNewRange & intx & ", "

Next intx
'array evaluation goes here

??
intfrom = Int(StrNewRangefrom) '
intNewto = Int(StrNewRangeto)
' followed by a append query where the range is eg: 8 - 10


where the end result is to leave range range 7 remains, 0 - 6 get delete,
8- 10 get added to the control table!??

any hint or help is very much appreciated.

Regards
Norman
 
This sounds complicated and maybe not what you would use a data table for
but the way I would tackle it would be to itterate through the possible
range and make changes to the table as required, something like:
(don't forget to set a reference to DAO

Private Sub SetItems
Dim intSt as Integer, intEnd as integer, intN as integer
Dim stSQL as String, intItem as Integer '(assuming your Item field is an
integer)
Dim db As DAO.Database, rs as DAO.Recordset

intSt=0 'possible start
intEnd =12 'possible end
stSQL= "SELECT Item FROM tblYourTable Order By Item;"
set db=currentdb()
set rs=db.openrecordset(stSQL, dbopendynaset)
if rs not .eof then
with rs
.Movefirst
do while !Item<txtRangeFrom
.Delete
.Movenext
loop

For intN=txtRangeFrom to intEnd
.FindFirst ("Item = " & intN)
if .NoMatch and intN <= txtRangeTo then
.AddNew
!Item=intN
.Update
else
if intN>txtRangeTo Then
.Delete
end if
end if
Next intN
end with
set rs=nothing
set db=nothing
exit sub

You might want to add some error handling.
 
Doug,
thanks. I appreciate your comments!

What you have written had triggered some thoughts. I think that it could
work. I will test it out and let you know if it works, if you are
interested.

regards
Norman
 
Back
Top