copying previous data (cloning) 1

[Angelique21]

Hi, hope some one can help me as i am still a beginner.

Form 1 is a continuous form where it can handle a list of containers. Near this list there is a button called Transit Details that when clicked it opens in another Form that is Form 2 .Form 1 and Form 2 use the same table for storage that is CLRsShipped Table . The data in Form 2 is usually the same for each container listed in Form1 and so to avoid each time writing transit data in Form 2, I’ve created a button called Copy Previous that will eventually copy data from the previous record.

This is the code under ONCLICK Copy previous button:

Private Sub CopyPrev_Click()

'uses the SortID to ensure correct record sequence according to data entry sequence regardless of when procedure is run

On Error GoTo CopyPrevErr

Dim dbc As DAO.Database, qdf As DAO.QueryDef, rs As DAO.Recordset
Dim sqlstr, PrevCLR, TPort1, Vessel2, ETST1, ETAT1, TPort2, Vessel3, ETST2, ETAF, TT, ProductsRef, FComments As String
Set dbc = CurrentDb

DoCmd.RunCommand acCmdSaveRecord

sqlstr = "SELECT CLRsShipped.CLR, CLRsShipped.TPort1, CLRsShipped.Vessel2, CLRsShipped.ETST1, CLRsShipped.ETAT1," & _
" CLRsShipped.TPort2, CLRsShipped.Vessel3, CLRsShipped.ETST2, CLRsShipped.ETAF, CLRsShipped.TT, CLRsShipped.ProductsRef, CLRsShipped.FComments" & _
" FROM CLRsShipped" & _
" WHERE CLRsShipped.ShipID = " & [Forms]![ShipBook]![ShipID] & "" & _
" ORDER BY CLRsShipped.SortID;"

Set qdf = dbc.CreateQueryDef("", sqlstr)
Set rs = qdf.OpenRecordset()

rs.FindFirst "[CLR] = """ & Forms!ShipBook!ShipBook1.Form!CLR & """"
rs.MovePrevious

TPort1 = rs!TPort1
Vessel2 = rs!Vessel2
ETST1 = rs!ETST1
ETAT1 = rs!ETAT1
TPort2 = rs!TPort2
Vessel3 = rs!Vessel3
ETST2 = rs!ETST2
ETAF = rs!ETAF
TT = rs!TT
ProductsRef = rs!ProductsRef
FComments = rs!FComments

rs.MoveNext
rs.Edit

rs!TPort1 = TPort1
rs!Vessel2 = Vessel2
rs!ETST1 = ETST1
rs!ETAT1 = ETAT1
rs!TPort2 = TPort2
rs!Vessel3 = Vessel3
rs!ETST2 = ETST2
rs!ETAF = ETAF
rs!TT = TT
rs!ProductsRef = ProductsRef
rs!FComments = FComments

rs.Update
rs.Close
Me.Requery

Exit Sub

CopyPrevErr:
errNo = Err.Number

Select Case errNo

Case 3021
MsgBox "Nothing to copy." & Chr(13) & "You are on the first record.", vbCritical + vbOKOnly, "Copy what?"

Case 94
MsgBox "Previous record does not have" & Chr(13) & "any transhipment details to copy.", vbCritical + vbOKOnly, "Nothing to copy"

With this code the copy previous button works only if all the fields are entered. What I wish to achieve is to be able to copy records irrelevant of which fields are entered.. so for example if only TPort1 and Vessel2 are entered as Transit Details in the first record, these can still be copied for the second record, without the user having to manually input them..So when some of the fields are null, these still should be copied as null.
(if isnull then and the code) but i am a bit confused here..
Any help will be much appreciated..Thanks a lot

 

[CindyK]

Change the variable type to a variant for the fields:

Dim sqlstr as String
Dim PrevCLR, TPort1, Vessel2, ETST1, ETAT1, TPort2, Vessel3, ETST2, ETAF, TT, ProductsRef, FComments As Variant

This will allow you to copy the null values. As it stands now, it's trying to put a null into a string.. no can do!

Hope this helps.

 

[PHV]

Actually ONLY FComments was typed as String in this line ...

 

[CindyK]

Sure was.... glad you saw that. I always do the declarations on a separate line - should have checked this before responding!

Angelique - my apologies for jumping in with what I thought was an answer. I'm not sure why it's not working, since as PHV points out, they ARE variants... and therefore should be able to be copied just fine. I just copied the code into a sample form and it worked fine regardless of whether the fields were all filled in.

What error message are you getting?

 

[Angelique21]

Thanks a lot for your help CindyK, as soon as I changed the fields into a Variant, it worked successfully.

Thanks a lot i really appreciate your help