2:confused:

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.
hole_id From To feat
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:
hole_id From To feat
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
:D

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.

Thanks for the reply Fizzio

Oops sent it early

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
HOLEID FROM TO FEAT
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.
Hole Id TO FEAT
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

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.

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

The If needs to be changed to:

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

You only want to update the records with null values in the From field.

There is one other problem with the code though. The recordset will not be in a predictable order. You MUST use a query that is ordered by a unique field (or set of fields) to ensure that your records are being properly populated. If you don't use a query, you could end up with the code using the "To" value of record 496 to populate the "From" value of record 2.

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
:p

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