Creating Record in Form Click Event

[Tiggster]

Hello everyone,

I am trying to write a new record to a database table within my current
Access 2003 project and am receiving an error message saying the "operation
not allowed in this context." I am using the following code within my event
handler:

Private Sub cmdAdd_Click()
Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Set conn = CurrentProject.Connection

rst.Open "tblAccommodations", conn, adOpenKeyset, adLockOptimistic

rst.AddNew
rst.Fields(1).Value = "Steve"
rst.Fields(2).Value = "My description"
rst.Close

Set rst = Nothing
Set conn = Nothing
End Sub

Is this action really not permitted or am I doing something wrong? I'm an
experienced VB.NET and ASP.NET developer, but am unfamiliar with VBScript, so
I'm a little lost here. Is there a better approach to accomplish the same
task?

Thanks for any help or suggestions.

Steve

 

[Allen Browne]

Steve, is this a bound form?
Is it bound to tblAccommodations?
If so, you can create the new record with an AddNew to the RecordsetClone of
the form, instead of having to open another recordset to the table.

Before you do that, it would be important to make sure there is no partially
entered record in the form, so:
If Me.Dirty Then Me.Dirty = False

If you are not using bound forms, you have a real treat waiting for you as
you discover how easy they are in Access, and the power of the events it
exposes (e.g. Form_BeforeUpdate for validation.)

 

[Tiggster]

I am actually attempting to create a record in another table in the database.
The table attached to the form contains information regarding students, and I
need to be able to write records into a separate table that assigns
attributes to students based on an academic advisor's analysis. The form is
linked to tblStudents, but the record needs to be written to
tblAccommodations.

Does that make any sense? Thanks for your assistance.

 

[Allen Browne]

Okay, someone else who uses the old ADO may be able to help.

Personally I use DAO for data stored in JET tables, since it is the native
library. The DAO code would be like this:

Private Sub cmdAdd_Click()
Dim rs As DAO.Recordset
set rs = dbEngine(0)(0).OpenRecordset("tblAccommodations", _
dbOpenDynaset, dbAppendOnly)
rs.AddNew
rs.Fields(1).Value = "Steve"
rs.Fields(2).Value = "My description"
rs.Update
rs.Close
Set rs = Nothing
End Sub

If that gives an error, indicate the error number, error message, and the
line that gives the error.

I presume you realize that the Fields collection is zero-based.

And presumably you don't have any other forms/reports/recordsets using
tblAccommodations at the time.

 

[RoyVidar]

Hello everyone,

I am trying to write a new record to a database table within my
current Access 2003 project and am receiving an error message saying
the "operation not allowed in this context." I am using the
following code within my event handler:

Private Sub cmdAdd_Click()
Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Set conn = CurrentProject.Connection

rst.Open "tblAccommodations", conn, adOpenKeyset,
adLockOptimistic

rst.AddNew
rst.Fields(1).Value = "Steve"
rst.Fields(2).Value = "My description"
rst.Close

Set rst = Nothing
Set conn = Nothing
End Sub

Is this action really not permitted or am I doing something wrong?
I'm an experienced VB.NET and ASP.NET developer, but am unfamiliar
with VBScript, so I'm a little lost here. Is there a better approach
to accomplish the same task?

Thanks for any help or suggestions.

Steve

I think the error is because you try to close the recordset with
pending
changes. I e, rs.EditMode = adEditAdd. For ADO to save the record,
you'd need to either specificy issue the .Update method, or in the
case you'd be updating more, you'd fire .AddNew, or do one of the
..Move<direction> method, which would also explicitly save.

So to use a recrordset, issue .Update before .Close.

I think I would rather use a query - ie for ADO, something like this

with currentproject.connection
.execute "INSERT INTO myTable (field1, field2) " & _
"VALUES ('Steve', 'My description')", , _
adcmdtext + adexecutenorecords
end with

DAO

With currentdb
.execute <same SQL>, dbfailonerror
end with

 

[Tiggster]

I attempted to use the DAO approach and the following code in my button click
event handler:

Private Sub cmdAdd_Click()

Dim strSSN As String
Dim strID As String

Me.SSN.SetFocus
strSSN = Me.SSN.Text

Me.Combo3.SetFocus
strID = Me.Combo3.Value

With CurrentDb
.Execute "INSERT INTO tblStudentAccommodations (SSN, ID) VALUES ('" & _
strSSN & "', '" & strID & "')", dbFailOnError
End With

Me.Refresh

End Sub

When the button is clicked I receive the following error:

"The expression On Click you entered as the event property setting produced
the following error: A problem occurred while Microsoft Office Access was
communicating with the OLE server or ActiveX control."

The code worked perfectly initially, but once I added a subform to my main
form which displays the "many" records in my relationship, the error occurs.
The query powering the subform data by necessity must access the data in the
tblStudentAccommodations linking table. Everything worked fine until a query
was used that accessed the destination form of the INSERT SQL statement, so
it seems that accessing the table via a query while simultaneously trying to
execute an insert statement is where I'm having the issue. Is there any way
to work around this?

Thanks for all your help.