How to add a record to a subform recordset?

Topic

New Reply

Hi All,

I have a command that uses the following:

If sbfItems.Form.Recordset.RecordCount > 0 Then

to check for existing records in a subform. I have code to go on and add a record to the underlying table if it = 0 but what I want to do now is be able to add a record to the subform’s recordset and then copy detail down to leave a blank record at the top, which will be then filled in.

As my records are sorted by record ID this will put my “new detail” in the top position.

Once I know how to define the recordset I am pretty sure I know how to read/write the info from each field down 1 record using movelast movenext moveprevious etc unless there is a way to copy then write the entire record at once.

So how do I write the OpenRecordset command? Set rst = CurrentDb.OpenRecordset(sbfItems.Form.Recordset)

Thanks in advance.

Allan

Reply | Quote

Replies

If I understand your situation correctly, you want to add a new record to the underlying table which is being displayed on a subform, but you want to add the new record at the top of the subform, not the bottom. Unfortunately, that is not the way that Microsoft designed forms in general. If it is a continuous form (that is bound), they add at the bottom. If it is a subform that displays one record at a time, then you have to go to the last record, and then to the empty record. Programming around those constraints is a significant challenge, and many different attempts have been made by developers over the years. Most turn out to be less than successful.

The attempts that I’ve seen that were more successful either go to a completely unbound form, or they limit the subform so it cannot be used to add a record, and then have an unbound pop-up form (or unbound controls in the header of the form) that capture the necessary info to use DAO or ADO to insert a new record in the appropriate table. But a fair bit of programming and testing/debugging/bulletproofing is required to do that and do it well. We’ve taken the approach of training users to use the navigation controls to do what needs to be done, and explain the limitation of Access forms.

Reply | Quote

Thanks Wendel for your insight.

My situation is: This is part of my Job card procedure.
A card is raised and items are added to the job using a subform and items table. In a perfect world the user may decide to create a multicard job and so will set an option and enter the first item as a summary line and then the items. What happens is that eg a gearbox comes in and a card is raised to book strip and inspect to, the job is quoted and items of work are added to the job. Depending on the scope, it may turn into a multi card job but may not. If it is, my multicard summary item needs to be the first item.

I realise that any new record will be written to the bottom of the table so this is what I am looking at now after some more browsing.

Write the existing recordset for the subform to a temporary table then add a new record to the underlying table and refresh the recordset.
Open the temp table and .movelast
Open recordset and .movelast
write field data from temp to recordset
.moveprevious on both tables
repeat until reach top record of temp table
.movefirst on recordset and clear fields

top record is now clear for summary

I hope to use the following combined code from examples off the net (adapted)

Code:

Private Sub TestTemp() 
   On Error GoTo ErrorHandler
   Dim strSQL as String
   Dim strTable as String
   Dim RSC as recordset
   Dim RS as recordset

   strTable = “tblTempTest”
   ‘Delete the table if it exists
   DoCmd.DeleteObject  acTable, strTable
strSQL = “Select * INTO ” & strTable & ” FROM tblCustomers ” & _   <<<< How do I write the SQL to be from me.sbfItems.Form.Recordset
"Where CustomerState = 'ILL'"
Currentdb.Execute strSQL

Add a record to the underlying table and requery

set RS = me.sbfItems.Form.Recordset
Set RSC = currentdb.Openrecordset("tblTempTest)
RS.MoveLast
RSC.MoveLast
While Not RSC.BOF
RS.edit
RS![Field1]=RSC![Field1]
RS![Field2]=RSC![Field2]
etc.
RS.Update
RS.MovePrevious
RSC.MovePrevious
WEnd
RS.Close
RSC.Close

Exit Sub
ErrorHandler:
IF Err.Number = 7874 Then
Resume Next 'Tried to delete a non-existing table, resume
End If
End Sub

Is this feasible and/or advisable?

Regards,

Allan

Reply | Quote