Solved Copy record on a continuous form (1 Viewer)

arnelgp

error reading drive A:
Local time
Today, 16:37
Joined
May 7, 2009
Messages
11,121
if still not working add a function to a Module:
Code:
Public Function genSN(ByVal HealthCardNo As Variant, id As Long) As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i As Integer
    HealthCardNo = HealthCardNo & vbNullString
    If Len(HealthCardNo) = 0 Then
        Exit Function
    End If
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset( _
        "Select HealthInsID " & _
        "From tblHealthInsurance " & _
        "Where HealthCardNo = '" & HealthCardNo & "' " & _
        "Order By IssueDate, HealthInsID;", _
        dbOpenSnapshot, _
        dbReadOnly)
    With rs
        If Not (.BOF And .EOF) Then
            .FindFirst "HealthInsID = " & id
            Do Until .BOF
                i = i + 1
                .MovePrevious
            Loop
        End If
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
    genSN = i
End Function

You're query will look like this:

Code:
SELECT genSN(HealthcardNo, HealthInsID) AS SN,
IssueDate, HealthCardNo, [RelationShip], PolicyNo, [Class],
ExpiredDate, TerminationReasons, Note
FROM tblHealthInsurance
ORDER BY IssueDate, HealthInsID;
 

smtazulislam

Member
Local time
Today, 11:37
Joined
Mar 27, 2020
Messages
271
if still not working add a function to a Module:
Code:
Public Function genSN(ByVal HealthCardNo As Variant, id As Long) As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i As Integer
    HealthCardNo = HealthCardNo & vbNullString
    If Len(HealthCardNo) = 0 Then
        Exit Function
    End If
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset( _
        "Select HealthInsID " & _
        "From tblHealthInsurance " & _
        "Where HealthCardNo = '" & HealthCardNo & "' " & _
        "Order By IssueDate, HealthInsID;", _
        dbOpenSnapshot, _
        dbReadOnly)
    With rs
        If Not (.BOF And .EOF) Then
            .FindFirst "HealthInsID = " & id
            Do Until .BOF
                i = i + 1
                .MovePrevious
            Loop
        End If
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
    genSN = i
End Function

You're query will look like this:

Code:
SELECT genSN(HealthcardNo, HealthInsID) AS SN,
IssueDate, HealthCardNo, [RelationShip], PolicyNo, [Class],
ExpiredDate, TerminationReasons, Note
FROM tblHealthInsurance
ORDER BY IssueDate, HealthInsID;

Its error also.

Optional EDIT/
When I click copy button then copy is successfully insert but not display at that time in the continuous form.
I dont find also AfterUpdate Event so please help this line also.
 

Attachments

  • HealthIns.accdb
    1.4 MB · Views: 21

arnelgp

error reading drive A:
Local time
Today, 16:37
Joined
May 7, 2009
Messages
11,121
check this
 

Attachments

  • HealthIns.zip
    71.6 KB · Views: 20

smtazulislam

Member
Local time
Today, 11:37
Joined
Mar 27, 2020
Messages
271
check this
Thank you so much, I appreciated.

When I click copy button then copy has successfully insert but not display at same time in the continuous form.
Should I need to close form and reopen it.
What I do please ?
 

arnelgp

error reading drive A:
Local time
Today, 16:37
Joined
May 7, 2009
Messages
11,121
you need to Requery after adding the record.
 

Attachments

  • HealthIns.zip
    106.5 KB · Views: 22

smtazulislam

Member
Local time
Today, 11:37
Joined
Mar 27, 2020
Messages
271
you need to Requery after adding the record.
Thank you so much. Before I tried to "Me.Requery". Not work.
I do not call it "Me.Recordset.MoveLast"
Thanks again, you are very shortly answered. Appreciated.
 

smtazulislam

Member
Local time
Today, 11:37
Joined
Mar 27, 2020
Messages
271
you need to Requery after adding the record.

Hi, Please be advice
I want to know, can you put it default user this module,
Any if i call another form that will be work same like that.
can it possible ? thank you.
 

Users who are viewing this thread

Top Bottom