Trying to get AddNew method to work.

[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

 

[Larry Daugherty]

Hi Robert,

Check out Requery in Help and then give that a shot.

HTH

 

[Robert Solomon]

Hi Robert,

Check out Requery in Help and then give that a shot.

HTH

I'm getting an error: Operation not supported for this type of object

 

[PC Datasheet]

Add the following code to the end of what you have:

Me!NameOfSubformControl.Form.Requery

 

[Alphonse Giambrone]

Instead of adding the record to a completely new recordset, add it to the
subform's recordsetclone.
The new record will be available immediately.

 

[Rob Solomon]

Instead of adding the record to a completely new recordset, add it to the
subform's recordsetclone.
The new record will be available immediately.

Sounds like a very good idea. How do I do that?

 

[Alphonse Giambrone]

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

 

[Rob Solomon]

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.

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

Now if only I can get the DLookup to work

Rob