'Bookmarking' recordsets ?

[liamfitz]

I hoped the following code, would select and then edit/update, a particular record in my recordset. Maybe I have the theory/syntax wrong.

Set rst = Me.RecordsetClone
strCriteria = "[Referral_ID] = '" & Me.Recordset!Referral_ID & "'"
rst.FindFirst strCriteria
Me.Bookmark = rst.Bookmark
If Not IsNull(Me.Parent!txtClient_ID.Value) Then
    dt = Me.Referral_Date.Value
    With Me.Parent
        refid = Left(!txtForename.Value, 1) & Left(!txtSurname.Value, 1) & Format(dt, "ddmmyy")
        cid = !txtClient_ID.Value
        n = !Forename & " " & !Surname
    End With
    With Me.Bookmark
        .Edit
        !Client_ID = cid
        !Referral_ID = refid
        !CalcClient_Name = n
        .Update

When it gets to the .Edit command, I get an error, suggesting I've got the 'With Bookmark' idea wrong ?? You can see what I'm trying to do - any help much appreciated. Thanks.

 

[Beetle]

So you're trying to programmatically take the clients first and last name from the parent table and redundantly write those values to the child table, along with a Referral_ID which is also concatenated from other existing values?

First question: Why?

If you have ClientID in the sub form (child table), which would normally be handled by the Master/Child link, then that's all you need to display (not store) the clients name in the sub form if you need to.. As far as the the Referral_ID, it's generally not a good idea to use concatenated values like this for PK/FK fields (not sure if Referral_ID is a key field or not). Again, just display the value wherever you need it based on existing data.

 

[liamfitz]

I see it's not entirely clear beetle. Apologies. I am using Client details from Parent FORM, to create a concatenated string value ( Referral_ID ). The issue is when I create a new record in the subform, it 'skips' to another 'existing record in the subform/recordset and changes THAT data ( while still creating a new record with just the field/control I'm creating and the P.K. number - I'd like it to update this newly created record with the fields I supply. .MoveLast used to work before I .Edit and .Update, but doesn't seem to anymore !