Updating fields using previous records details

[rnutts]

Hi

Have a form which has various fields. the key fields for this question are

Sample Number -- Autonumber
BatchNumber --Combo Box based upon query

I have a command button which adds a new record and I want it to create a new record but to use the batch number from the previously viewed record

Help please

Thanks

Richard

 

[Dennisk]

one way is to use a variable to store the current primary key and update it in the forms on current event if the current record is not null. Then in the new button use the stored key to retreive the record.

 

[rnutts]

Thanks

I am fairly new to this and am struggling with the most of what you said, could you expand on you reply

Thanks

Richard

 

[rnutts]

Would this work

On the click event of the command button there is code to open a new record.
Could I put a line in to record the batch number in the current view and then after the new record creation put a line to set the value in the batch number in the new record

Code below for command button, this was done with a access wizard

Private Sub Sample_in_same_Batch_Click()
On Error GoTo Err_Sample_in_same_Batch_Click


DoCmd.GoToRecord , , acNewRec

Exit_Sample_in_same_Batch_Click:
Exit Sub

Err_Sample_in_same_Batch_Click:
MsgBox Err.Description
Resume Exit_Sample_in_same_Batch_Click

End Sub

 

[Dennisk]

At the top of your form code module declare a variable after
Option Compare Database
Option Explicit

dim intPrimaryKey as long

in the forms on current event

if not me.NewRecord Then
intPrimaryKey=YourPrimarykey
endif

in the new button retreive the data

' This is the ado version

dim rst as ADO.Recordset

set rst=Currentdb.Openrecordset ("Select YourlistofColumns FROM YourTable WHERE [YourPrimarykey ='" & intPrimaryKey)

if not rst.eof then
' Load the controls on the form from the recordset
' This example is for one control
me!YourControlName = rst("YourColumnName")
endif

set rst = Nothing