copying previous data (cloning)

[vanny]

Hi,

i need help regarding copying previous data in a table (as a cloning method).
i have a form with continous records. near each record, a button was created that opens in another form which contains various fields.

in order to facilitate time, i created a button on the second form to be able to copy previous data for the respective records on the first form.

but the problem happens if there are 3 fields all of them have to be filled in to be able to perform the copy operation.

for example:

port = rs!port
vessel1= rs!vessel1
vessel2 = rs!vessel2
rs.movenext
rs.edit

all fields must be filled that is port, vessel1, vessel2...and if only port and vessel1 field is filled, this cannot be copied for the other records.

can anyone give me suggestions about how can i make it in a way that it copies data irrelevant of what fields have been entered.

any help will be much appreciated.

thanks a lot.

 

[rborob]

If isNull(vessel1) then
rs!vessel1 = 0
else vessel1 = rs!vessel1

 

[vanny]

Copying Previous data (cloning)

hi,
thanks for your help, but still it is not working, and i can't figure out where is the mistake.

if isnull(port) then
rs!port=0
else port = rs! port
if isnull (vessel1) then
rs!vessel1=0
else vessel1 = rs! vessel1

rs.movenext
rs.edit

rs!Port= Port
rs!Vessel1 = Vessel1
rs.Update
rs.Close
Me.Requery

end if
end if

can someone please check my code

thanks for the help

 

[rborob]

if isnull(port) then
rs!port=0
'END
else port = rs! port

you need the end statement to break out if its null

 

[vanny]

hi still it is not working, i am missing out a point.
can you please check this code underneath..thanks

if isnull(port) then
rs!port=0
end
else port = rs! port
end if

if isnull (vessel1) then
rs!vessel1=0
end
else vessel1 = rs! vessel1
end if

rs.movenext
rs.edit

rs!Port= Port
rs!Vessel1 = Vessel1

rs.Update
rs.Close
Me.Requery

 

[rborob]

how about
rs.addnew
rs("Port") = Me.Port
rs("Vessel1") = Me.vessel1

im assuming "port" is the field name into which you are inserting and Port is the textbox or whatever it is your copying from

 

[vanny]

i rearranged the code as you indicated, but still it is not working

exactly Port is the name of the field that i am copying from and pasting in the next record.

if isnull(port) then
rs!port=0
end
else port = rs! port
end if

if isnull (vessel1) then
rs!vessel1=0
end
else vessel1 = rs! vessel1
end if

rs.movenext
rs.addnew

rs("Port") = Me.Port
rs ("Vessel1") = Me.Vessel1

rs.Update
rs.Close
Me.Requery

 

[rborob]

can you post an example?

 

[vanny]

hi, i just uploaded a fake example just to see the concept of it.

vesselform is the main form (continous records) that when transhipment button is clicked it opens in vessel (form). in the second form vessel on the first record the user enters data such as the port, vessel 1 leaving for example vessel 2 field empty.

if the user goes on the second record (on the first form) and wants to copy exactly the same data it only presses the Copy previous button that will copy all the records, even if not all of them are filled.

 

[rborob]

you didnt explain that very well

 

[vanny]

Hi,
First of all sorry for not explaining well, hope by these print screens you can understand better the idea.

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 (please see jpeg attached). Form 1 and Form 2 use the same table for storage that is CLRsShipped Table (see jpeg attached). 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.

Hope this explains a bit better. Thanks for your patience and help.

 

[vanny]

URGENT HELP NEEDED - Copying previos data

hi can someone check a bit the code i posted, cause i believe that there is a small error somewhere, as it is copying data, but not pasting the data in the table...


thanks a lot