Update recordset problem and general question

[ST4RCUTTER]

I have a main table used to track project details. Related to this table is a table called, "tbl_dates" which houses all project dates. The dates table looks like this:

DateID | DateRefID | DateEntered | WorkOrderID

These four fields store
Field 1 - (PK) DateID: which is an Auto-number for this table
Field 2 - DateRefID: which is a reference ID for another table which identifies the type of date. Example: 33 which is the ID for the datetype "Project Start Date".
Field 3 - DateEntered: Which is whatever date that gets entered (4/1/2008).
Field 4 - WorkOrderID: Which is a FK that corresponds to the PK of the main table.
I am using the following code within the "On Insert" event of the main table to add a "Project Start Date" record (DateRefID=33) to tbl_dates. In this way, when a project is added on the main table it automatically adds a Project start date to tbl_dates for the current date.

Private Sub Form_AfterInsert()

Dim rst As DAO.Recordset
Dim CRdate As Date

CRdate = Date
Set rst = CurrentDb().OpenRecordset("tbl_dates", dbOpenDynaset)
rst.AddNew
rst.DateRefID = 33
rst.DateEntered = CRdate
rst.WorkOrderID = [Forms]![frm_Main].[WorkOrderID]
rst.Update
rst.Close

Forms!frm_Main.frm_sub_milestones2.Form.Requery
Forms!frm_Main.frm_sub_missdates.Form.Requery

End Sub

You will also notice some code in there to requery two forms which can be disregarded. Now when I insert a record into the main table I receive the following error: "Compile Error: Method or data member not found". The code that is highlighted is the rst.DateRefID = 33. I have verified all the names for the fields and they are correct. Any ideas?

On a second note, I wanted to prevent multiple users from having any issues with this auto-insert to tbl_dates. How can I lock in such a way that multiple users are not permitted to select the same auto-number primary key on table? How can I prevent the table from being locked while this AddNew is being done?

 

[boblarson]

That should be:

rst!DateRefID = 33

or

rst("DateRefID") = 33

or

rst.Fields("DateRefID") = 33

 

[ST4RCUTTER]

Thank you again Bob! That worked like a charm. Now let me ask you, is it safer (in a multi-user environment) to open a query recordset as opposed to a table recordset and then insert my record? When opening the table would I not also be locking the table from use by others? Will that cause other queries against tbl_dates to fail if run concurrently?

 

[boblarson]

You shouldn't need to open a recordset to insert a record. Just use an append or update query, or a bound form.