Solved Copy record on a continuous form (1 Viewer)

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:07
Joined
May 7, 2009
Messages
19,169
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, 17:07
Joined
Mar 27, 2020
Messages
806
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: 120

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:07
Joined
May 7, 2009
Messages
19,169
check this
 

Attachments

  • HealthIns.zip
    71.6 KB · Views: 132

smtazulislam

Member
Local time
Today, 17:07
Joined
Mar 27, 2020
Messages
806
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

..forever waiting... waiting for jellybean!
Local time
Today, 22:07
Joined
May 7, 2009
Messages
19,169
you need to Requery after adding the record.
 

Attachments

  • HealthIns.zip
    106.5 KB · Views: 119

smtazulislam

Member
Local time
Today, 17:07
Joined
Mar 27, 2020
Messages
806
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, 17:07
Joined
Mar 27, 2020
Messages
806
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.
 

smtazulislam

Member
Local time
Today, 17:07
Joined
Mar 27, 2020
Messages
806
Hello @arnelgp Good day !
I would like to asking you if I want to adding this function to the report Page. Can work it ?
This is your code what you provide me before.


Code:
'Coder : arnelgp
'Code Source :  access-programmers.co.uk
Public Function IqExSN(ByVal EmployeeID As Variant, ID As Long) As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i As Integer
    
    EmployeeID = EmployeeID & vbNullString
    If Len(EmployeeID) = 0 Then
        Exit Function
    End If
    
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset( _
        "Select EmployeeID " & _
        "From tblEmployee " & _
        "Where EmployeeID = " & EmployeeID & " " & _
        "Order By ExpiredDateAr, EmployeeID;", _
        dbOpenSnapshot, _
        dbReadOnly)
    With rs
        If Not (.BOF And .EOF) Then
            .FindFirst "EmployeeID = " & ID
                Do Until .BOF
                    i = i + 1
                    .MovePrevious
                Loop
        End If
        .Close
    End With
    
    Set rs = Nothing
    Set db = Nothing
    IqExSN = i
    
End Function

Report Query here :
Code:
SELECT IqExSN([Reports]![rptEmployeeIqamaList]![EmployeeID],[EmployeeID]) AS SN, tblEmployee.EmployeeID, tblEmployee.EmployeeName, LtblCountryLookup.Country, LtblDesignationsLookup.Designation, LtblDepartmentsLookup.DeptName, tblEmployee.IdentityType, tblEmployee.IdentityNumbers, tblEmployee.ExpiredDateAr, tblEmployee.ExpiredDateEn, tblEmployee.StatusID
FROM LtblDesignationsLookup RIGHT JOIN (LtblCountryLookup RIGHT JOIN (LtblDepartmentsLookup RIGHT JOIN tblEmployee ON LtblDepartmentsLookup.DeptID = tblEmployee.DeptID) ON LtblCountryLookup.CountryID = tblEmployee.CountryID) ON LtblDesignationsLookup.DesgID = tblEmployee.DesgID
WHERE (((tblEmployee.IdentityType)="Iqama") AND ((tblEmployee.StatusID)=1))
ORDER BY tblEmployee.ExpiredDateAr;
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:07
Joined
May 7, 2009
Messages
19,169
you need to test it if it will work.
 

Users who are viewing this thread

Top Bottom