Trying to get AddNew method to work.

  • Thread starter Thread starter Robert Solomon
  • Start date Start date
R

Robert Solomon

Hi. I have a command button that tries to add a new record to a
subform. This is not working as I would like. The main problem is that
the subform does not display the record just added to the detail table.
How do I fix this?

Thanks.

Dim casesrst, casedetailsrst As Recordset
Set casesrst = CurrentDb.OpenRecordset("Cases")
casesrst.MoveLast
Set casedetailsrst = CurrentDb.OpenRecordset("CaseDetails")
With casedetailsrst
.AddNew
!CasesRef = casesrst!CaseNo
!CPTDescription = "Abd Aorta Vascular Family 3rd order"
!CPTCode = 36247
.Update
.Move 0, .LastModified
End With

casedetailsrst.Close
casesrst.Close
Set casesrst = Nothing
Set casedetailsrst = Nothing
 
Add the following code to the end of what you have:

Me!NameOfSubformControl.Form.Requery
 
Instead of adding the record to a completely new recordset, add it to the
subform's recordsetclone.
The new record will be available immediately.
 
Instead of adding the record to a completely new recordset, add it to the
subform's recordsetclone.
The new record will be available immediately.
Click to expand...

Sounds like a very good idea. How do I do that?
 
Assuming that your code is located in the main form:

Dim casesrst As dao.Recordset
Set casesrst = CurrentDb.OpenRecordset("Cases")
casesrst.MoveLast
With Me!yoursubform.Form.RecordsetClone
.AddNew
!CasesRef = casesrst!CaseNo
!CPTDescription = "Abd Aorta Vascular Family 3rd order"
!CPTCode = 36247
.Update
End With

casesrst.Close
Set casesrst = Nothing

BTW in your original code, the line
Dim casesrst, casedetailsrst As Recordset
declares caserst as a variant not a Recordset.

From your recordset names I am guessing that CaseNo might be a field/control
on the main form. If so, and the value you want is for the current record,
you don't need that recordset either. Just use Me!CaseNo instead.

HTH
 
Assuming that your code is located in the main form:

Dim casesrst As dao.Recordset
Set casesrst = CurrentDb.OpenRecordset("Cases")
casesrst.MoveLast
With Me!yoursubform.Form.RecordsetClone
.AddNew
!CasesRef = casesrst!CaseNo
!CPTDescription = "Abd Aorta Vascular Family 3rd order"
!CPTCode = 36247
.Update
End With

casesrst.Close
Set casesrst = Nothing

BTW in your original code, the line
Dim casesrst, casedetailsrst As Recordset
declares caserst as a variant not a Recordset.

From your recordset names I am guessing that CaseNo might be a field/control
on the main form. If so, and the value you want is for the current record,
you don't need that recordset either. Just use Me!CaseNo instead.
Click to expand...

Thanks a lot. That works. And you guessed right about CaseNo.

Now if only I can get the DLookup to work :-)

Rob
 
Back
Top