Adding a record to a table without opening it

[shaunacol]

After updating a certain field I need to check if that value exists in a separate table/field. If it does I need to do nothing, but if it doesn't I would like to create a new record and add in this value.

At the moment I have the below - which doesnt work. I am clutching at straws as I guess I need to set up a recordset - but I do not know how.


Private Sub FileNo_AfterUpdate()


Dim rst As Recordset
Dim Box As String

If IsNull(DLookup("HSBCBoxNo", "Tbl_convert", "HSBCBoxNo = Forms!FRM_InputRecord!FRM_InputRecordSubform!FileNo&quot) Then
Forms!FRM_InputRecord!Frm_InputRecordSubform!FileNo = Box

With rst
.AddNew ' Add new record.
!FileNo = Box ' Add data.
.Update ' Save changes.
End With

Else
End If
End Sub

 

[Petemush]

Private Sub FileNo_AfterUpdate()

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim Box As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Tbl_Convert&quot

If IsNull(DLookup("HSBCBoxNo", "Tbl_convert", "HSBCBoxNo = Forms!FRM_InputRecord!FRM_InputRecordSubform!FileNo&quot) Then

Forms!FRM_InputRecord!Frm_InputRecordSubform!FileNo = Box

With rst
.AddNew ' Add new record.
!FileNo = Box ' Add data.
.Update ' Save changes.
End With
End If
End Sub

That should do the trick,

Cheers,

Pete

 

[ninash]

Hi Shaunacol

Listed below is the code I have just written to add a new record to a table. Feel free to change it to suit your needs.

Dim rstUpdate, rstCurrent As Recordset

Set rstAddRec = CurrentDb.OpenRecordset("select * from ReportAddressInfoWithKey&quot
'the above being the transfer to the new table
'Add New Record Here
With rstAddRec
.AddNew
rstAddRec![ReportKey] = Me![ReportKey]
rstAddRec![Winton Ref] = Me![Winton Ref]
rstAddRec![Site Name] = Me![Site Name]
.Update
End With

Tony

 

[shaunacol]

Thank you both - I am now spolit for choice!

 

[Petemush]

One thing I forgot to mention, the above code will work if you're using Access97 or 2000 but make sure your references include DAO if you're using 2000.

Cheers,

Pete

 

[Rolliee]

If all you wish to do is add a record, do it with an

INSERT INTO....

This is a play form I set up to do just that!

Private Sub cmdDoer_Click()
Dim SQL As String, x1 As String, x2 As String, x3 As String, okay As Boolean

okay = IsNull(Me.txtOne)
okay = okay Or IsNull(Me.txtTwo)
okay = okay Or IsNull(Me.txtThree)


If okay Then Exit Sub

x1 = Chr(34) & Me.txtOne & Chr(34)
x2 = Chr(34) & Me.txtTwo & Chr(34)
x3 = Chr(34) & Me.txtThree & Chr(34)

SQL = "INSERT into Tabl1 ( one, two, three)"
SQL = SQL & "VALUES ( " & x1 & "," & x2 & "," & x3 & ""


' MsgBox SQL

DoCmd.RunSQL SQL

Me.txtOne = Null
Me.txtTwo = Null
Me.txtThree = Null

cmdSee_Click

End Sub