Adding a record in a table unsuccessfully

[L'apprentis]

This is the code i am trying to use to add a record in one of my table.
This is not working properly, the data are not added in the table and if I add a value directly in the table I can notice that the autonumber is incremented by the number of time I have pressed the button. It is like all the data are successfully entered and erased as soon as I press the command button.

I was wondering if anybody could see anything wrong in the code?

Private Sub CmdAddLine_Click()

On Error GoTo Err_CmdAddLine_Click

' connection and recordset object variables
Dim cn As Connection
Dim Rs As ADODB.Recordset

' open a connection to the connection object
Set cn = CurrentProject.Connection
' initialise the recordset object
Set Rs = New ADODB.Recordset
' using the recordset object
        With Rs
            .Open "TblLineConfig", cn, adOpenStatic, adLockPessimistic ' open it
            .AddNew 'Prepare to add new record
            .Fields("LineID") = Forms!FrmDrawing!CboDimLine.Column(0)
            .Fields("LineQty") = Forms!FrmDrawing!TxtLineQty
            .Fields("DrawingID") = Forms!FrmDrawing!TxtDrawingID
            .Update 'Update the table
            .Close 'Close the recordset connection
        End With
'update value in the line Subform
Me!SFrmQryLine.Requery
            
Exit_CmdAddLine_Click:
' de-initialise object variables
Set Rs = Nothing
Set cn = Nothing
Exit Sub
Err_CmdAddLine_Click:

End Sub

 

[Jon K]

...and if I add a value directly in the table I can notice that the autonumber is incremented by the number of time I have pressed the button.

There is nothing wrong in the code.

However, it sounds like Access just fails to find the form FrmDrawing in the collection of currently open forms when the button is clicked. Have you checked the spelling of the form name?
.

 

[L'apprentis]

Yep....The form name is correct,
I don't know if it can be any help to solve this problem but here is a snapshop of the table concerned but I doubt it has something to do with it.

 

[Oldsoftboss]

.Fields("LineID") = Forms!FrmDrawing!CboDimLine.Column(0)
.Fields("LineQty") = Forms!FrmDrawing!TxtLineQty
.Fields("DrawingID") = Forms!FrmDrawing!TxtDrawingID

try changing the dots to bombs..

!LineID = Forms!FrmDrawing!CboDimLine.Column(0)
!LineQty = Forms!FrmDrawing!TxtLineQty
!DrawingID = Forms!FrmDrawing!TxtDrawingID

Dave

I use the following to edit/add records to tables:

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblLogTimes", dbOpenDynaset)

'to edit a record
With rst
.FindLast "UserID = " & User.UserID
.Edit
!LoggedOff = Now()
.Update
.Close
End With

'to add a record

With rst
.AddNew
![UserID] = User.UserID
.Update
.Close
End With

HTH

 

[Oldsoftboss]

I can notice that the autonumber is incremented by the number of time I have pressed the button. It is like all the data are successfully entered and erased as soon as I press the command button.

I was wondering if anybody could see anything wrong in the code?

Private Sub CmdAddLine_Click()
On Error GoTo Err_CmdAddLine_Click

Err_CmdAddLine_Click:

End Sub

Notice you dont actually have any error handling. If an error occurs the sub is exited. So what is happening is you are setting the recordset correctly, opening the table, adding a record, but when the data is trying to be added, because the code is incorrect, the code crashes, and exit the sub without updating the table.

Tyr putting
Msgbox Err.Description
in the error handling.

Dave

 

[L'apprentis]

Thanks OldSoftBoss,
I forgot indeed to add the message box for the error handling.
Now that I have added that part of the code I can finally see what's wrong with my form:

You cannot add or change this record, a related record is required in "TblDrawing"

When I fill the form FrmDrawing; a new drawingID Is created but is not saved in the Table Drawing untill the record is saved. I am probably going to have to find a way to requery my form before the user decide to add the line description of the drawing.

 

[Oldsoftboss]

How is the sub called. If from FrmDrawing, then simply place

DoCmd.RunCommand acCmdSaveRecord

before calling the code.


Dave