Adding a new record using an unbound form

[sjg0003]

I am trying to add a record using a form that is not bound to any tables.

Here is what I am working with.

Table1: customer_info

field1 = cust_snn (primary key)
field2 = cust_fname
field3 = cust_lname
...
...
...


Table2: customer_transactions
field1 = id (autonumber)
field2 = trans_ssn (fkey)
field3 = fee
field4 = deposit

I have a form that is not bound to these tables. I am trying to add a record into table2. Right now all I am trying to add into table2 is a trans_ssn. But not sure how to go about this.

I found this in a previous post:

Dim db as DAO.Database
Dim rs as DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tablename", dbOpenDynaset)
rs.FindFirst "[ID] = " & Me![ID_control]
rs.AddNew
rs("fieldname1") = Me![field1_control]
. . .
rs.Update
rs.close
db.close

However I am not sure about the rs.FindFirst part of this code. Since my pkey in table2 is an autonumber I do not think I need this but I do not know what to replace it with.

I modified the above to this:

Dim cn As DAO.Database
Dim rs As DAO.Recordset

Set cn = CurrentDb
Set rs = cn.OpenRecordset("customer_transactons", dbOpenDynaset)
rs.AddNew
rs("trans_ssn") = Me![txtTranNum]

rs.Update
rs.Close
cn.Close

When I run this I get a type mismatch error. I am sure it has to do with the rs.AddNew statement. But I am not sure??

Any suggestions?

 

[scriverb]

You final code here looks jus fine. You have the right setup to add the new record, assign the values, and then update the recordset. You need to add all of the lines of code for all the fields in the table. You are trying to run this with just one field assignment, unless this is just an example.

What the error is telling you is the field may be numeric and you are assigning a text value to it.

Look over your field types and make sure they are matching.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]