R
Robert Nusz @ DPS
I have a form/Subform combination to be used to read/update a primary table
and secondary table. These tables are IBM DB2 tables, named TST_FR_CASE
RECORDS (primary) and TST_FR_CASE_OTHERS (secondary).
The parent form of this form/subform is named Fr_CR_U. This form used
TST_FR_CASE_RECORDS as record source. The sub form of this combination,
sbFr_Add_Others_U uses TST_FR_CASE_OTHERS as the record source.
I use a form to query the user to enter the primary key of the case being
searched for, then, open the form Fr_CR_U with Data Entry value = No,
filtered on the case number.
This works well to find only the case records I want. My primary form
reflects the primary record, and the sub form reflects all of the secondary
records.
I was using the following in ON FORM CURRENT event....
-------------------------------------------------------------------------------------------------
Private Sub Form_Current()
Me.txt_VEHICLE_CDE.SetFocus
'the following code should prevent the user from scrolling past
'BOF (Beginning-of-file) and EOF (End-of-File)
Dim rst As DAO.Recordset
Dim lngCount As Long
Set rst = Me.RecordsetClone
With rst
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With
If Me.CurrentRecord = lngCount Then
Me.Command75_Next_Record.Enabled = False
Me.Command76_Previous_Record.Enabled = True
MsgBox "There Are No More Records To Display For This Case Number"
ElseIf Me.CurrentRecord = 1 Then
Me.Command75_Next_Record.Enabled = True
Me.Command76_Previous_Record.Enabled = False
Else
Me.Command75_Next_Record.Enabled = True
Me.Command76_Previous_Record.Enabled = True
End If
' End of Special code to prevent BOF/EOF Scrolling
Me.unbtxt_CurRecNum = Me.CurrentRecord
Me.unbtxt_TtlRecNum = lngCount
End Sub
------------------------------------------------------------------------
To allow the user to display all records of the matched data found. I was
using the unbtxt.xxxx fields in the code to reflect previous record
information on the form as the user scrolled forward and backward through the
data. This was working well and even prevented the user from scrolling past
EOF or BOF. I found this code at this site and it works great to turn of
record navigation.
My issue is that I need to be able to change any record of this record set
and save the record with the updates applied. AS WELL AS be able to ADD
additional NEW SECONDARY records to the matching case.
Changes can be made to a record, but when you try to scroll to next record
after chainges were made to the current record, it gives an error "You can't
go to the specified record!". If you exit the form, the changes are saved,
with no error message.
If you open the forms as above, and attempt to add new records with the "ADD
NEW RECORD" command, the form allows the user to add new record, keeping the
correct sequence number intact, but when you try to save the new added
record, it gives you the same "You Can't go to the specified record!" error.
Again, if you exit the form with the RETURN command, it saves the record to
the table.
I need to be able to open the form, reflecting only specific case records,
be able to scroll through existing group of data, be able to keep track of
total number of secondary records that match, and still be able to add new
records.
My question(s) are these:
1) Do I need to use DAO.Recordset or ADOD.Recordset or just rely on
records in direct table.
2) If I use DAO.Recordset and keep the above code, what do I need to do to
be able to add records to existing secondary table without getting the error
above?
3) If I have to use ADOD.Recordset, what would be the equivalent code to the
current "ON CURRENT" code above, is there an example to follow?
4) If I can't use either of them, and rely strictly on existing table(s)
ODBC, what kind of code do I need to use to keep track of total number of
secondary records and current record position functions that the code above
provides.
Can anyone advise on the best way to do what I need to do.
Thanks in advance.
and secondary table. These tables are IBM DB2 tables, named TST_FR_CASE
RECORDS (primary) and TST_FR_CASE_OTHERS (secondary).
The parent form of this form/subform is named Fr_CR_U. This form used
TST_FR_CASE_RECORDS as record source. The sub form of this combination,
sbFr_Add_Others_U uses TST_FR_CASE_OTHERS as the record source.
I use a form to query the user to enter the primary key of the case being
searched for, then, open the form Fr_CR_U with Data Entry value = No,
filtered on the case number.
This works well to find only the case records I want. My primary form
reflects the primary record, and the sub form reflects all of the secondary
records.
I was using the following in ON FORM CURRENT event....
-------------------------------------------------------------------------------------------------
Private Sub Form_Current()
Me.txt_VEHICLE_CDE.SetFocus
'the following code should prevent the user from scrolling past
'BOF (Beginning-of-file) and EOF (End-of-File)
Dim rst As DAO.Recordset
Dim lngCount As Long
Set rst = Me.RecordsetClone
With rst
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With
If Me.CurrentRecord = lngCount Then
Me.Command75_Next_Record.Enabled = False
Me.Command76_Previous_Record.Enabled = True
MsgBox "There Are No More Records To Display For This Case Number"
ElseIf Me.CurrentRecord = 1 Then
Me.Command75_Next_Record.Enabled = True
Me.Command76_Previous_Record.Enabled = False
Else
Me.Command75_Next_Record.Enabled = True
Me.Command76_Previous_Record.Enabled = True
End If
' End of Special code to prevent BOF/EOF Scrolling
Me.unbtxt_CurRecNum = Me.CurrentRecord
Me.unbtxt_TtlRecNum = lngCount
End Sub
------------------------------------------------------------------------
To allow the user to display all records of the matched data found. I was
using the unbtxt.xxxx fields in the code to reflect previous record
information on the form as the user scrolled forward and backward through the
data. This was working well and even prevented the user from scrolling past
EOF or BOF. I found this code at this site and it works great to turn of
record navigation.
My issue is that I need to be able to change any record of this record set
and save the record with the updates applied. AS WELL AS be able to ADD
additional NEW SECONDARY records to the matching case.
Changes can be made to a record, but when you try to scroll to next record
after chainges were made to the current record, it gives an error "You can't
go to the specified record!". If you exit the form, the changes are saved,
with no error message.
If you open the forms as above, and attempt to add new records with the "ADD
NEW RECORD" command, the form allows the user to add new record, keeping the
correct sequence number intact, but when you try to save the new added
record, it gives you the same "You Can't go to the specified record!" error.
Again, if you exit the form with the RETURN command, it saves the record to
the table.
I need to be able to open the form, reflecting only specific case records,
be able to scroll through existing group of data, be able to keep track of
total number of secondary records that match, and still be able to add new
records.
My question(s) are these:
1) Do I need to use DAO.Recordset or ADOD.Recordset or just rely on
records in direct table.
2) If I use DAO.Recordset and keep the above code, what do I need to do to
be able to add records to existing secondary table without getting the error
above?
3) If I have to use ADOD.Recordset, what would be the equivalent code to the
current "ON CURRENT" code above, is there an example to follow?
4) If I can't use either of them, and rely strictly on existing table(s)
ODBC, what kind of code do I need to use to keep track of total number of
secondary records and current record position functions that the code above
provides.
Can anyone advise on the best way to do what I need to do.
Thanks in advance.