how to insert new row like Excel functions in MS ACCESS

I need to find a way to开发者_Python百科 make grid in MS Access & insert new lines to it by code VBA only exactly how excel behave.

Have you looked at continuous forms and datasheets?

It is very rarely a good idea to replicate spreadsheet behaviour in a database

Code InsertRows : insert row in between tow row

first Create A temporarilytable And then deal with this table like the main table

and the code is Work with me and here the code :

 Sub InsertRows()
    On Error GoTo ErrorNu
    Dim SQLP As String
    Dim Con As New ADODB.Connection
    Dim Conx As New ADODB.Connection
    Dim Rst As New ADODB.Recordset
    Dim Rs As New ADODB.Recordset
    Dim Rsx As New ADODB.Recordset
    Dim Rn As New ADODB.Recordset
    Dim Rd As New ADODB.Recordset
    Dim Num As Long
    Dim intRows
    Dim arrEmployees As Variant
    Dim x As Integer, Y As Integer

    Set Con = CurrentProject.Connection
Con.BeginTrans

sqlo = " select max(AutoRec)as maxa from Note_Custom "
Set Rn = Con.Execute(sqlo)


SQLP = " SELECT AutoRec, TextCOspoId, OuerM,Note"
SQLP = SQLP & " , TextBillId,NuCOspoId,dateTybe FROM Note_Custom ORDER BY AutoRec"

Rs.Open SQLP, Con, adUseClient, adOpenStatic, adCmdText

sqlo = " UPDATE Note_Custom SET TextBillId = ''"
sqlo = sqlo & " WHERE  AutoRec > " & 0
Con.Execute (sqlo)


intRows = Val(Rn!maxa)
Num = 1
arrEmployees = Rs.GetRows(intRows)
Y = 0
    For x = 0 To intRows - 1
If x = Val(SelTop - 1) Then
Y = 1
Rs.AddNew
Rs![AutoRec] = arrEmployees(0, x)
Rs![TextBillId] = 1
Rs.Update
End If
Rs.AddNew
Rs![AutoRec] = arrEmployees(0, x) + Y
Rs![TextCOspoId] = arrEmployees(1, x)
Rs![OuerM] = arrEmployees(2, x)
Rs![Note] = arrEmployees(3, x)
Rs![NuCOspoId] = arrEmployees(5, x)
Rs![dateTybe] = arrEmployees(6, x)


Rs![TextBillId] = 1
Rs.Update
    Next x
    sqlo = "DELETE * FROM Note_Custom where TextBillId = """""
Con.Execute (sqlo)
Con.CommitTrans
SelFiled = Me.SelTop
Me.Requery
sqlo = "SELECT Last(AutoRec) AS LastAuto,First(AutoRec) AS FirstAuto,Count(AutoRec) AS CountAuto FROM Note_Custom"
    Set Rd = Con.Execute(sqlo)
If Me.SelTop <> AutoRec Or Rd!LastAuto <> Rd!CountAuto Then
Refix
End If
DoCmd.GoToRecord , , acGoTo, SelFiled
'Me.SelTop = SelFiled
If RecType = False Then
Forms![Ncustom]!Edite.Enabled = True
Forms![Ncustom]!Viewer1.Enabled = False
Forms![Ncustom]!DELETE.Enabled = False
End If
arrEmployees = Empty
Rs.Close
Con.Close
Set Rs = Nothing
Set Con = Nothing
Exit Sub
ErrorNu:
SelFiled = Me.SelTop
Me.Requery
Me.SelTop = SelFiled
End Sub