Copy record from from to a table

[theinviter]

Dear Guys;

i need your help,

I have a form name navigation panel and show the total number of record in field Text0 and Text12.
so i want on click of button it copy the value of specified field above (Text0 and Text12) in Statistics Table .
i tried the code below but it work if there is a subform in the main form.

so please guide me how to do this on button click copy the record in new record in Statistics Table.

Private Sub Command28_Click()
Dim bolWithBlank As Boolean
Dim bm As Variant
On Error GoTo new_Err

  
    Me![Table1 subform].Form.RecordsetClone
        If Not (.BOF And .EOF) Then
            .MoveFirst
            Do While Not .EOF
                If Len(Trim(!Prep.Value & "")) = 0 Then
                    bolWithBlank = True
                    bm = .Bookmark
                    Exit Do
                End If
                .MoveNext
            Loop
            If bolWithBlank Then
                .Bookmark = bm
                .Edit
            Else
                .AddNew
            End If
            !Prep = Me.Text0.Value
            !Prep1 = Me.Text12.Value
            '!Record_ID = Me.ID_Drug.Value
            .Update
        Else
               
             .AddNew
            !Prep = Me.Text0.Value
            !Prep1 = Me.Text12.Value
            '!Record_ID = Me.ID_Drug.Value
            .Update
        End If
    End With
new_Err:
    MsgBox "Updated"
End Sub

Code Tags Added by UG
Please use Code Tags when posting VBA Code
Please read this for further information:-

Please use Code Tags when posting VBA Code

To make your code easier to read, please use the Code tag around your code, this will ensure that any code you copy and paste from your DB retains it's formatting making it easier to read and follow; Firstly click on the Code button, that's the button at the top of the posting window with the...

www.access-programmers.co.uk

Please feel free to Remove this Comment

 

[CJ_London]

please use the code tags to make you code readable by preserving indentation. (copy your code from your module, click on the </> button and paste into the new window that opens)

 

[Cronk]

Also debug your code to remove the compile errors ie missing With

 

[arnelgp]

is Statistics Table the Recordsource of subform [Table1 subform]? because that is where your code is adding record.

 

[theinviter]

I corrected the debug with with. But Instead of copy the value in subform. I need to copy the value in statistics table.
Actually I don't want a subform in main form.

 

[arnelgp]

you need another Field (pk) so you can identify the record you are saving.
you can add code to the Click event of a button to add the record using SQL insert:

private sub button_click()
currentdb.execute "insert into [statistic table] (pkfieldName, field1, field2) " & _
"select " & thePKValue & "," & Nz(Me!Text0, "Null") & ", " & Nz(Me!Text12, "Null") & ";"
end sub

 

[theinviter]

you need another Field (pk) so you can identify the record you are saving.
you can add code to the Click event of a button to add the record using SQL insert:

private sub button_click()
currentdb.execute "insert into [statistic table] (pkfieldName, field1, field2) " & _
"select " & thePKValue & "," & Nz(Me!Text0, "Null") & ", " & Nz(Me!Text12, "Null") & ";"
end sub

Thank you that work . but this code also add a blank record if no data there in the field.
so my question can we modify the code and if blank will not add to record and show a message that there must be a data to enter.