VBA question

Hi All


I have 2 tables -parent table and child table.
first i call the parent function and after that i call
child function but IT FAILS BECAUSE PARENT TABLE IS IN EOF:
[HIGHLIGHT]
Public Sub InsertParentRecord()
[/HIGHLIGHT]
parent.CursorType = adOpenKeyset

parent.LockType = adLockOptimistic

parent.ActiveConnection = cn

parent.Open ("SELECT * FROM parent")

parent.AddNew

LocalDate = Date
LocalTime = Time
Date_Time = Date & " " & Time
parent.Fields("Date_Time").Value = Date_Time

parent.Fields("StartTime").Value = f1

parent.Fields("BatchNo").Value = f2

parent.Fields("Operator").Value = f3

parent.Update
parent.Close
[HIGHLIGHT]
End Sub

Public Sub InsertChildRecord()
[/HIGHLIGHT]
parent.CursorType = adOpenKeyset

parent.LockType = adLockOptimistic

parent.ActiveConnection = cn

parent.Open ("SELECT * FROM parent")
Child.CursorType = adOpenKeyset

Child.LockType = adLockOptimistic

Child.ActiveConnection = cn

Child.Open ("SELECT * FROM Child")

If parent.EOF = True Then 'MoveLast is not supported.
parent.MoveFirst
While parent.EOF = False
CurrentIndex = parent.Fields(0).Value
parent.MoveNext
Wend
End If

While parent.EOF = False
CurrentIndex = parent.Fields(0).Value
parent.MoveNext
Wend

Child.AddNew

Child.Fields("index").Value = CurrentIndex

f1 = StartHour & ":" & StartMin
f2 = StopHour & ":" & StopMin

Child.Fields("StartTime").Value = f1
Child.Fields("StopTime").Value = f2

If Step_no = 1 Then
[HIGHLIGHT]
'IT FAILS HERE BECAUSE PARENT TABLE IS IN EOF.
[/HIGHLIGHT]
parent.Fields("StartTime").Value = f1
End If
If Step_no = 3 Then
parent.Fields("StopTime").Value = f2
End If
parent.Update

Child.Update
Child.Close
parent.Close
[HIGHLIGHT]
End Sub
[/HIGHLIGHT]

briana banks said:

Hi All


If Step_no = 1 Then
[HIGHLIGHT]
'IT FAILS HERE BECAUSE PARENT TABLE IS IN EOF.
[/HIGHLIGHT]
parent.Fields("StartTime").Value = f1

Click to expand...

Before the parent.fields() I don't see parent.AddNew() is that not needed?

Hi

I want to update the last(existing) record in the parent
table.

It is failing, because in your search for the last 'index', your search condition terminates (a while) on Parent At EOF.

Before updating the parent record, you need to either scroll back through (movefirst/search) or just requery your Parent recordset looking for the found Index.