Update next FROM value with previous TO value

[vipersmind]

2

I have a the following table

tblOxidation_ExprtPoint
HoleID
From
To
Feat

All new HoleID's should begin from 0 metres.
The from should be equal to the prevous To
ie.

[B]hole_id	From	To	feat[/B]
AFC0001	0	38	BOCO
AFC0001	38	73	TOFR
AFC0001	73	100	PRED
AFC0002	0	43	BOCO
AFC0002	43	65	TOFR
AFC0002	65	90	PRED
AFC0003	0	33	BOCO
AFC0003	33	55	WT
AFC0003	55	75	TOFR
AFC0003	75	90	PRED

My problem is that the records come from different tables that don't have both the From and To fileds so I have to fill in the gaps.

But my table is missing most of the From values:

[B]hole_id	From	To	feat[/B]
AFC0001	0	38	BOCO
AFC0001		73	TOFR
AFC0001		100	PRED
AFC0002	0	43	BOCO
AFC0002		65	TOFR
AFC0002		90	PRED
AFC0003	0	33	BOCO
AFC0003		55	WT
AFC0003		75	TOFR
AFC0003		90	PRED

Can anyone help me out with a query or vbCode to update the missing field values.
If you need anymore info just holler.

Thanks

 

[Fizzio]

VB would probably be easier (for me anyway!). I'm sure that there is a function that you could use in a query but I cannot see how it would work at the minute.

From your example, I'm assuming that all the zero values are present.
If so

Public Function FillFrom()
Dim rs as dao.recordset, strPrevTo as string
set rs = CurrentDb.openrecordset(tblOxidation_ExprtPoint)

do until rs.eof

     if rs("From") = 0 then
     else
         rs.edit
         rs("From") = strPrevTo
         rs.update
     end if

strPrevTo = rs("To")
rs.movenext
Loop

If the zero values are occasionally not there, the code will be different.

This should be a one off function so you can run it from the code window or a form event.

 

[vipersmind]

Bit confused

Thanks for the reply Fizzio

Oops sent it early

 

[vipersmind]

Bit confused

Thanks for the reply Fizzio

I understand how the code you gave me works..... but (always a but in there somewhere)

I am not getting any result with it.
I have put code behind a command button, after it runs a couple of other queries to get as far as the table I showed it then calls the function you wrote.

Call FillFrom

I am not really familar with (but wanting to learn more) creating recordset to manipulte data so maybe I am missing something.
Have I put this in right?

Maybe I could merge the original 2 tables using code and fill the missing fileds at that stage?

Two tables are

tblPoint
HoleID
To
Feat

tblLithology
HoleID
From
To
Feat

tblLithology has From field = 0, but only one record for each HoleID
i.e

[B]HOLEID	    FROM   TO   FEAT[/B]
FRC8142	0    5	     TL
FRC8143	0    7	     TL
FRC8144	0    6	     TL
FRC8149	0    1	     TL
FRC8155	0    6	     TL

tblPoint contains more than one record for each HoleID but has no From field.

[B]Hole Id	TO	FEAT[/B]
FRC8142	30	WT
FRC8142	71	BOCO
FRC8143	50	BOCO
FRC8143	84	WT
FRC8144	64	BOCO
FRC8144	84	WT
FRC8149	55	WT
FRC8149	57	BOCO
FRC8149	59	TOFR
FRC8155	31	BOCO
FRC8155	55	WT

I merge these to tables using an append query to get tblOxidation_ExprtPoint

Hope this is a bit of help and spring some ideas.....

Cheers

 

[Fizzio]

I think I should make 2 adjustments to my code - I would probable change it to

Public Sub FillFrom() instead of Function

also

this line
set rs = CurrentDb.openrecordset(tblOxidation_ExprtPoint)

should be
set rs = CurrentDb.openrecordset("tblOxidation_ExprtPoint",dbOpenDynaset)

The fact that you have merged two tables should not matter to the code if the final table is your goal. I'm fairly sure this code will work but get back with any problems.

 

[vipersmind]

Fizzo

I am getting an error message that From cannot be a Zero length string and in most cases it is

Public Sub Oxidation_Click()
On Error GoTo Err_Oxidation

Dim rs As DAO.Recordset, strPrevTo As String
Set rs = CurrentDb.OpenRecordset("tblOxidation_ExprtPoint", dbOpenDynaset)

Do Until rs.EOF

     If rs("From") = 0 Then
     Else
         rs.Edit
         rs("From") = strPrevTo
         rs.Update
     End If

strPrevTo = rs("To")
rs.MoveNext
Loop


Exit_Oxidation:
    Exit Sub
    
Err_Oxidation:
    MsgBox Err.Number & "-" & Err.Description
    Resume Exit_Oxidation
End Sub

Sorry that I am not of more use with this but it's all new to me
Cheers

 

[vipersmind]

Thanks

Thanks Pat and Fizzio

The only other thing I had to do was change

strPrevTo as String

to

strPrevTo as Integer

and it went like a charm

Thanks again

 

[Fizzio]

Good point about the recordset order Pat and the filtering of null values rather than filtering zero's - Glad it eventually worked Viper!