Adding to tables DAO recordset (1 Viewer)

[sam1fletcher]

I have been trying to add records to table from a form and got a runtime error item not found in this collection. The bale is linked to another table like this: tlbSchool"one------->"many" tblContacts(table im inserting data) and bothe have a feild school and that is the line flagged up for error in this code

   Dim Contacts As DAO.Database
   Dim Cont As DAO.Recordset
    Set Contacts = CurrentDb
    Set Cont = Contacts.OpenRecordset("tblContacts")
   Cont.AddNew
   Cont("ContactFirstName").Value = Me.FirstName.Value
   Cont("ContactSurname").Value = Me.Surname.Value
   Cont("School").Value = Me.SchoolName.Value
   Cont("JobTitle").Value = Me.FirstName.Value
   Cont("Email").Value = Me.EmailAddress.Value
   Cont("Sport1Involved").Value = Me.SportInolved.Value
   Cont("Padsis").Value = Me.Padsis.Value
   Cont.Update

Any ideas how to get around this

 

[Rx_]

Create a query that has both tables. In the query - test that it is possible too update one record.
In place of the tblContacts - replace the query either by name if it is saved in the query collection, or the actual SQL code.

Also: look at using dbOpenDynaset, dbSeeChanges (shown in 2nd example)

Dim rs As DAO.Recordset
Dim strSQL As String
Dim intFieldIndex As Integer
' Basics of using a SQL statement
strSQL = "SELECT Field1, Field2, Field3 FROM TableName"

Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.Recordcount <> 0 Then
 Debug.Print rs(intFieldIndex)
 Debug.Print rs.Fields(2)
End If
rs.Close
Set rs = Nothing

Example: Pass in a SQL String with a parameter

Public Function GetTrainerId(strTrainerName As String) As Integer 
Dim query As QueryDef 
Dim rs As Recordset 

    Set query = CurrentDb.QueryDefs("FindTrainer") 
    query.Parameters("pName") = strTrainerName 
     
    Set rs = query.OpenRecordset(dbOpenDynaset, dbSeeChanges) 
     
    If rs.RecordCount = 0 Then  ' does it already exist?
        rs.AddNew 
        rs("Name") = strTrainerName 
        rs.Update 
        rs.MoveLast 
        rs.MoveFirst 
    End If 
     
    GetTrainerId = rs("ID") 
     
    Set rs = Nothing 
    Set query = Nothing 
     
End Function

There are some decent code samples at:
http://msdn.microsoft.com/en-us/library/office/ff820966(v=office.14).aspx

This sites SAMPLE DATABASES have some examples too.
It might be worth searching the Sample Databases to find an existing working example or two. It has served me well to discover additional ideas. Example: adding error trapping.