Copy last record in subform

[MrBowder]

duh.. new to Access

I have looked for days and I am shocked I cant find the answer..

I have a main form I think called "Transaction Form"
My subform is called "BillingSubform"
subform is in datasheet view
One of the columns is called "DiagCode1"

Simple question is what do I do to copy the last value of a column in my subform into a the same column of a new record??

I would also like to what is the syntax to specify a certain column and row of a subform in datasheet view?

Thanks

 

[shabbaranks]

Did you ever get to the bottom of this? Im trying to achieve thesame thing...

 

[vbaInet]

Why are you needing to do this shabba? And what field is this?

 

[shabbaranks]

hey...

The reason is, if someone is selecting the same entry from a combo box numerous times they get fedup reselecting it. If I added a button then it would copy the previous entry in the subform and paste it into the corrosponding subform field on the next entry.

The field is within a subform, so they are copying from one subform field to another (are these classed as records or recordsets???) - ignore this its just a record/data

Thanks!!

 

[vbaInet]

So set the Default Value property of the control in the After Update event of the combo box.

 

[shabbaranks]

Wouldnt this mean that if the user didnt want this entry they woud then have to delete it?

I was thinking something the lines of having a button available which copies the entry into the clipboard and then they can paste it - the code I currently have is

Private Sub CopyBtn_Click()
Dim clipboard As MSForms.DataObject
Set clipboard = New MSForms.DataObject
clipboard.SetText Forms![PurchaseReqCombEntry]![POSubM].Form![Project].Value
clipboard.PutInClipboard
End Sub

But I get the error User defined type not defined - any ideas what this means?

Thanks

 

[shabbaranks]

Managed to work out this bit - needed to add the reference into Access. For some unknown reason my returned value is null when I try to copy the field in the subform, using the immediate window. But I can see within the form there is a value there - confused.com :S

 

[vbaInet]

Why don't you use the Copy command (i.e. one of the constants) in the DoCmd object?

 

[shabbaranks]

I seem to lack consistancy with my referral to the subform field I currently have

Forms![MainForm]![Subform].Form![record].value

But Im getting Null back when its not - this is triggered from a button within the MainForm - but my context is correct isnt it?

 

[vbaInet]

Forms!MainForm!Subform!Field.Value

The referencing you have is fine too. And if it's returning a value it obviously indicates that your referencing is fine.

 

[shabbaranks]

Any ideas what would cause it to return a null value then? If I reference a record in the main form its fine - its just the subform

Think Ive got it.. The main reason for so many problems withi this DB is that I inherited it, its not setup that well to be honest.

 

[shabbaranks]

Great thanks for sticking by me on this one - Im getting there. Im now trying to use the DoCmd.RunCommand acCMDCopy but I get the error message that this command isnt available right now, why would this be?

 

[vbaInet]

You need to set focus to the control first.

 

[shabbaranks]

Apologies I had that code but still I get the error as above

  Forms![MainFOrm]![Subform].Form![Project].SetFocus
    DoCmd.RunCommand acCmdCopy

 

[vbaInet]

You also need to highlight the text you wish to copy. SelText method will do it.

If you think this is too much hassle you can revert back to using the office clipboard.

 

[shabbaranks]

I decided to revert back to my original method - how do you then use office clipboard to paste into the current subform field? Or is this a pain in the....

 

[vbaInet]

From memory there are about 4 or so methods in the DataObject class so just look for the one that returns the value. I don't remember what it's called exactly but it could be GetText or something like that.