This is a multi-part message in MIME format.



------=_NextPart_000_000B_01C1A27A.E8FB07C0

Content-Type: text/plain;

	charset="iso-8859-1"

Content-Transfer-Encoding: quoted-printable



Hi



I have a  yes/no field which I need automatically updating when a new 

record is added. So the field of the previous record is altered when a 

new record is added. I am currently usign this code but it is a bit 

temperamental in that it does not always work. is there a more full 

proff way of achieving this.



Dim cnn1 As ADODB.Connection

Dim rst1 As ADODB.Recordset

Dim strSQL As String

Set cnn1 =3D CurrentProject.Connection

Dim str1 As String

Dim str2 As String

Dim str3 As String



str1 =3D Forms!frmStatus_promote![req Main ID]



strSQL =3D "SELECT * FROM Request_Status WHERE " & _

    "Request_Status.MainIDFK =3D " & str1 & " " & _

    "ORDER BY Request_Status.Date "

   

Set rst1 =3D New ADODB.Recordset

rst1.ActiveConnection =3D cnn1

rst1.Open strSQL, , adOpenKeyset, adLockOptimistic



      

 rst1.MoveLast

 rst1!Status_Mode.Value =3D 1

 rst1.AddNew

 Forms!frmStatus_promote.Requery



'Cleanup objects

    cnn1.Close



    Set cnn1 =3D Nothing

    Set rst1 =3D Nothing



Thanks in advance



Andrew

This is a multi-part message in MIME format.



------=_NextPart_000_0002_01C1A1D0.AA96BEC0

Content-Type: text/plain;

	charset="iso-8859-1"

Content-Transfer-Encoding: 7bit



If you want the field to automatically be yes in the new record when it is created why not just set the Default value to

yes in the table design screen.  Then when a new record is created it will automatically be yes without any code.

  -----Original Message-----

  From: AAustin [mailto:plant-tech@x...]

  Sent: Sunday, January 20, 2002 3:56 PM

  To: Access

  Subject: [access] yes/no fields set to true when





  Hi



  I have a  yes/no field which I need automatically updating when a new record is added. So the field of the previous

record is altered when a new record is added. I am currently usign this code but it is a bit temperamental in that it

does not always work. is there a more full proff way of achieving this.



  Dim cnn1 As ADODB.Connection

  Dim rst1 As ADODB.Recordset

  Dim strSQL As String

  Set cnn1 = CurrentProject.Connection

  Dim str1 As String

  Dim str2 As String

  Dim str3 As String



  str1 = Forms!frmStatus_promote![req Main ID]



  strSQL = "SELECT * FROM Request_Status WHERE " & _

      "Request_Status.MainIDFK = " & str1 & " " & _

      "ORDER BY Request_Status.Date "



  Set rst1 = New ADODB.Recordset

  rst1.ActiveConnection = cnn1

  rst1.Open strSQL, , adOpenKeyset, adLockOptimistic





   rst1.MoveLast

   rst1!Status_Mode.Value = 1

   rst1.AddNew

   Forms!frmStatus_promote.Requery



  'Cleanup objects

      cnn1.Close



      Set cnn1 = Nothing

      Set rst1 = Nothing



  Thanks in advance



  Andrew