VBA question

Printable View

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:

VB Code:

Public Sub InsertParentRecord()
 
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
 
End Sub
 
Public Sub InsertChildRecord()
 
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
 
'IT FAILS HERE BECAUSE PARENT TABLE IS IN EOF.
 
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
 
End Sub

Re: VBA question

Quote:

Originally Posted by briana_banks

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:

VB Code:

Public Sub InsertParentRecord()
 
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
 
End Sub
 
Public Sub InsertChildRecord()
 
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
 
'IT FAILS HERE BECAUSE PARENT TABLE IS IN EOF.
 
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
 
End Sub

what is your question and it is Access or what?
did you insert to parent table or not if not how can the child table get reference of the parent table.

Nov 8th, 2005, 05:52 AM
briana_banks

Re: VBA question
Hi

Using Access...

Quote:

insert to parent table or not if not how can the child table get reference of
the parent table

Please take a look at the code
VB Code:
```
Child.AddNew
Child.Fields("index").Value = CurrentIndex
```
Please take a look at the code .I marked the place where it fails.
Nov 8th, 2005, 06:36 AM
Hack

Re: VBA question

Moved to Office Development.
Nov 8th, 2005, 08:18 AM
briana_banks

Re: VBA question

??

I did it already. :confused: