Copy record from from to a table (1 Viewer)

theinviter

Registered User.
Local time
Today, 00:50
Joined
Aug 14, 2014
Messages
237
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.

Code:
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 feel free to Remove this Comment
 
Last edited by a moderator:

CJ_London

Super Moderator
Staff member
Local time
Today, 07:50
Joined
Feb 19, 2013
Messages
16,553
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

Registered User.
Local time
Today, 18:50
Joined
Jul 4, 2013
Messages
2,770
Also debug your code to remove the compile errors ie missing With
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:50
Joined
May 7, 2009
Messages
19,169
is Statistics Table the Recordsource of subform [Table1 subform]? because that is where your code is adding record.
 

theinviter

Registered User.
Local time
Today, 00:50
Joined
Aug 14, 2014
Messages
237
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

..forever waiting... waiting for jellybean!
Local time
Today, 15:50
Joined
May 7, 2009
Messages
19,169
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

Registered User.
Local time
Today, 00:50
Joined
Aug 14, 2014
Messages
237
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.
 

Users who are viewing this thread

Top Bottom