Problem with .Addnew method

[johndoomed]

Hi, I've searched and read this forum for a couple of hours now, but I cant seem to find the solution on how to add a new record in a subform that does not allow additions.

(I'm using a Norwegian version of Access 2000)

Because I need the subform to not allow additions, I have tried to add a record directly to the table. I found this code on this forum:

Private Sub Kommando13_Click()

On Error GoTo Err_Kommando13_Click

Dim db As Database
Dim rst As Recordset

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

'to add a record

With rst
.AddNew
![Person] = Forms![Hovedmeny]![Person ID].Value
![Oppgave nr] = Forms![Oppgave]![Oppgave nr].Value
.Update
.Close
End With

Err_Kommando13_Click:
    MsgBox Err.Description
End Sub

I get this errormessage:

Compile error:

User-defined type not defined

Hope you can help me with this. One of the last pieces of my project...

 

[gemma-the-husky]

might be the .value

lose the .value, since the contents of the text boxes is implied/returned anyway - that might be it

otherwise put a breakpoint at the with, and step through a line at a time to see which bit is failing

 

[ChrisO]

You are probably missing a reference to DAO but you really don’t need it. Also, your code will run straight into the error handler even if there is no error.

Private Sub Kommando13_Click()

    On Error GoTo ErrorHandler
    
    With CurrentDb.OpenRecordset("Oppgave_handling")
        .AddNew
        ![Person] = Forms![Hovedmeny]![Person ID]
        ![Oppgave nr] = Forms![Oppgave]![Oppgave nr]
        .Update
        .Close
    End With
    
ExitProcedure:
    Exit Sub
    
ErrorHandler:
    MsgBox Err.Description
    Resume ExitProcedure
        
End Sub

Spaces in field names should be avoided but hope that helps anyway. (Code not tested.)

Regards,
Chris.

 

[johndoomed]

Thanks to you both!

Chris, the code worked perfectly!

 

[johndoomed]

Hi again! I'm expanding this code in order to insert multiple records with one click. This is part of a new "project" part og my CRM db. Adding checklist items to the project.

This is my code. As you can see, 3 of the values are the same (VBA'speaking), but I need to set the type each time.

Are there an easier way of doing this? Or is the code good for that matter

    With CurrentDb.OpenRecordset("Prosjekt_checkitem")
        .AddNew
        ![Prosjekt_id] = Me.Prosjekt_id.Value
        ![Checkitemtype_id] = 1
        ![Opprettet_date] = Now()
        ![Opprettet_av] = Forms![Hovedmeny]![Person ID].Value
        .Update
        .AddNew
        ![Prosjekt_id] = Me.Prosjekt_id.Value
        ![Checkitemtype_id] = 2
        ![Opprettet_date] = Now()
        ![Opprettet_av] = Forms![Hovedmeny]![Person ID].Value
        .Update
        .Close
    End With