Solved Copy record on a continuous form (1 Viewer)

smtazulislam

Member
Local time
Today, 19:29
Joined
Mar 27, 2020
Messages
806
I am looking to copy a record on a continuous form and insert this copy as a new record. and in this table name filed like; HealthInsID, EmployeeID, HealthCardNo, Relationship, PolicyNo, class, SponsorID, IssueDate, ExpiredDate, TerminationDate, TerminationReasons, Note.
Please check the design view .
Capture1.JPG


HealthCardNo is in index "No Duplicate"

This is the Contentious Form

Capture.JPG


Needs to copy RED MARK Fields where is code..
Code:
Private Sub cmdCopy_Click()
Dim strSQL As String

     strSQL = "INSERT INTO tblHealthInsurance (HealthInsID, EmployeeID, HealthCardNo, Relationship, PolicyNo, class, SponsorID, IssueDate, ExpiredDate, TerminationDate, TerminationReasons, note) " & _
              "VALUES(#" & Me.IssueDate & "#, '" & Me.Relationship & "','" & Me.PolicyNo & "', '" & Me.Class & "', '" & Me.SponsorID & "', #" & Me.ExpiredDate & "#);"
     CrrrentDb.Execute strSQL, dbFailOnError

End Sub

But database not execute, Error.
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:29
Joined
Sep 21, 2011
Messages
14,044
I say this all the time, but I will say it yet again. :(
Build the SQl statement into a string variable and Debug.Print the string to ensure the syntax is correct. Then just execute that string when you do get the syntax correct.?

So many people slap some code together and hope for the best. Sadly that normally fails. :)
 

smtazulislam

Member
Local time
Today, 19:29
Joined
Mar 27, 2020
Messages
806
I say this all the time, but I will say it yet again. :(
Build the SQl statement into a string variable and Debug.Print the string to ensure the syntax is correct. Then just execute that string when you do get the syntax correct.?

Yes, String Variable problems. There is message me "Variable not defined".
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:29
Joined
Sep 21, 2011
Messages
14,044
No, I mean declare a straing variable, let's call it strSQL and put you SQl attempt into that variable.
Then Debug.Print strSQL to see what is produces, and mend it until it is correct.

THEN execute it.

Put you toe in the water first. Don't just jump in. :)

Edit: I would have thought SponsorID would be numeric not a string?
 

smtazulislam

Member
Local time
Today, 19:29
Joined
Mar 27, 2020
Messages
806
Okay I have to delete from VALUES SponsorID field.
/Edit
Code:
Dim strSQL As String

     strSQL = "INSERT INTO tblHealthInsurance (HealthInsID, EmployeeID, HealthCardNo, Relationship, PolicyNo, class, SponsorID, IssueDate, ExpiredDate, TerminationDate, TerminationReasons, note) " & _
              "VALUES(#" & Me.IssueDate & "#, '" & Me.Relationship & "','" & Me.PolicyNo & "', '" & Me.Class & "', #" & Me.ExpiredDate & "#);"
    Debug.Print strSQL

End Sub
Now command stop. Not run....
Then I also try to requery form.
Code:
Forms!frmEmployeeEdit.Forms!frmHealthInsuranceSubform.Requery
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:29
Joined
Sep 21, 2011
Messages
14,044
No need to delete the SponsorID value, but get it correct re quotes or not?
The fields in the INSERT statement must match the number of fields in the VALUES statement?

Copy and Paste the result of the debug.print back here.
Correct the number of fields first. They *MUST* be the same number/type?
You have 12 fields and 4 values? :-(

Edit: You had that all along as well.?
 

smtazulislam

Member
Local time
Today, 19:29
Joined
Mar 27, 2020
Messages
806
Now I try 5 fields and 5 Values. but not run !
Code:
im strSQL As String

     strSQL = "INSERT INTO tblHealthInsurance (IssueDate, Relationship, PolicyNo, Class, ExpiredDate) " & _
              "VALUES(#" & Me.IssueDate & "#, '" & Me.Relationship & "','" & Me.PolicyNo & "', '" & Me.Class & "', #" & Me.ExpiredDate & "#);"
    Debug.Print strSQL

End Sub
After coding, I try also F8 to debugging in VBA and it was okay.
I doubt it have date format problem ? what do you think ?
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:29
Joined
Sep 21, 2011
Messages
14,044
Copy an paste the result of the debug.print

However note, you do not have the Execute statement there anymore?
 

Gasman

Enthusiastic Amateur
Local time
Today, 16:29
Joined
Sep 21, 2011
Messages
14,044
You still have not shown the result of the debug.print, despite being asked several times? :(

Also Policy number is defined as numeric and yet you are treating it as text?
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:29
Joined
May 7, 2009
Messages
19,169
Code:
Dim strSQL As String
Dim db As DAO.Database
Set db = CurrentDb
    Set db = CurrentDb
     strSQL = "INSERT INTO tblHealthInsurance (IssueDate, Relationship, PolicyNo, Class, ExpiredDate) " & _
              "SELECT p0, p1, p2, p3, p4;"
              
    With db.CreateQueryDef(vbNullString, strSQL)
        .Parameters(0) = Me!IssueDate
        .Parameters(1) = Me!Relashionship
        .Parameters(2) = Me!PolicyNo
        .Parameters(3) = Me!Class
        .Parameters(4) = Me!ExpireDate
        .Execute dbFailOnError
    End With
    Set db = Nothing
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:29
Joined
May 7, 2009
Messages
19,169
you may also try using the recordsetclone:
Code:
    Dim clone As DAO.Recordset
    Dim rst As DAO.Recordset
    Dim f As DAO.Field ' iterate through Fields
    
    Set rst = Me.RecordsetClone
    rst.Bookmark = Me.Bookmark
    Set clone = rst.clone
    rst.AddNew
    With clone
        For Each f In .Fields
            If (f.Attributes And dbAutoIncrField) Then ' always skip the AutoNumber Primary Key
            Else
                If f.Name Like "TerminationReason*" Then
                Else
                    rst.Fields(f.Name) = f
                End If
            End If
        Next f
    End With
    rst.Update ' update the main table with the new record
    Me.Bookmark = rst.LastModified
    Set clone = Nothing
    Set rst = Nothing
    Set f = Nothing
 

smtazulislam

Member
Local time
Today, 19:29
Joined
Mar 27, 2020
Messages
806
Code:
Dim strSQL As String
Dim db As DAO.Database
Set db = CurrentDb
    Set db = CurrentDb
     strSQL = "INSERT INTO tblHealthInsurance (IssueDate, Relationship, PolicyNo, Class, ExpiredDate) " & _
              "SELECT p0, p1, p2, p3, p4;"
           
    With db.CreateQueryDef(vbNullString, strSQL)
        .Parameters(0) = Me!IssueDate
        .Parameters(1) = Me!Relashionship
        .Parameters(2) = Me!PolicyNo
        .Parameters(3) = Me!Class
        .Parameters(4) = Me!ExpireDate
        .Execute dbFailOnError
    End With
    Set db = Nothing

I apologize for late reply. And thank you so much for help. I appreciated
It has been copy. but problems is not insert same fields.
Please see the attached screenshot.

Capture.JPG


Note: This subform Relationship with tblEmployee & tblHealthInsurance into EmployeeID To EmployeeID.

EDIT/

Actual Health Card No is 886209291, but every click change numbers and add +1.
Please see the image red oval.
And I needs a message when copy has been insert. Thank you
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:29
Joined
May 7, 2009
Messages
19,169
add healthcard and issuedate to your insert query.
HealthInsID is Autonumber.
 

smtazulislam

Member
Local time
Today, 19:29
Joined
Mar 27, 2020
Messages
806
Its work perfectly. Thank you so much.
another question, I have HealthInsID Value AutoNumber ,

Capture1.JPG


See the attached image as like serial numbers 1, 2, 3, 4, ........ I want to put by the date ascending.
I appreciate for help..
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:29
Joined
May 7, 2009
Messages
19,169
you create a Query from your table

select DCount("1", "tblHealthInsurance", "IssueDate & HealthInsID ='" [IssueDate] & HealthInsID] & "'") As SN,
IssueDate, HealthCardNo, [RelationShip], PolicyNo, [Class], ExpiredDate, TerminationReasons Note
From tblHealthInsurance
ORDER BY IssueDate, HealthInsID;

save the query and used as Recordsource of your subform
 

smtazulislam

Member
Local time
Today, 19:29
Joined
Mar 27, 2020
Messages
806
you create a Query from your table

select DCount("1", "tblHealthInsurance", "IssueDate & HealthInsID ='" [IssueDate] & HealthInsID] & "'") As SN,
IssueDate, HealthCardNo, [RelationShip], PolicyNo, [Class], ExpiredDate, TerminationReasons Note
From tblHealthInsurance
ORDER BY IssueDate, HealthInsID;

save the query and used as Recordsource of your subform
It is great work I learn. Thank you so much,
There is export serial Number SN = 1 only.
Not continuous 1, 2, 3, 4, 5....

EDIT/
This is sql in the Record Source in the subform:

Code:
SELECT DCount("1","tblHealthInsurance","IssueDate & HealthInsID = '" & [IssueDate] & [HealthInsID] & "' ") AS SN, tblHealthInsurance.IssueDate, tblHealthInsurance.HealthCardNo, tblHealthInsurance.RelationShip, tblHealthInsurance.PolicyNo, tblHealthInsurance.Class, tblHealthInsurance.ExpireDate, tblHealthInsurance.TerminationReasons, tblHealthInsurance.Note, tblHealthInsurance.HealthInsID, tblHealthInsurance.EmployeeID
FROM tblHealthInsurance
ORDER BY tblHealthInsurance.IssueDate, tblHealthInsurance.HealthInsID;
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:29
Joined
May 7, 2009
Messages
19,169
use Less than Or Equal (<=):

select DCount("1", "tblHealthInsurance", "IssueDate & HealthInsID <='" & [IssueDate] & [HealthInsID] & "'") As SN,
IssueDate, HealthCardNo, [RelationShip], PolicyNo, [Class], ExpiredDate, TerminationReasons Note
From tblHealthInsurance
ORDER BY IssueDate, HealthInsID;
 

smtazulislam

Member
Local time
Today, 19:29
Joined
Mar 27, 2020
Messages
806
use Less than Or Equal (<=):

select DCount("1", "tblHealthInsurance", "IssueDate & HealthInsID <='" & [IssueDate] & [HealthInsID] & "'") As SN,
IssueDate, HealthCardNo, [RelationShip], PolicyNo, [Class], ExpiredDate, TerminationReasons Note
From tblHealthInsurance
ORDER BY IssueDate, HealthInsID;

Now export numbers LIKE : 1 ; 501; 683;
SN = 1
SN = 501
SN = 683
as number of column HealthInsID.

Edit/
Not continuous 1, 2, 3, 4....
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:29
Joined
May 7, 2009
Messages
19,169
hmm..
add the HealthCardNo:

select DCount("1", "tblHealthInsurance", "HealthCardNo & IssueDate & HealthInsID <='" & [HealthCardNo] & [IssueDate] & [HealthInsID] & "'")
 

Users who are viewing this thread

Top Bottom