how to insert new row like Excel functions in MS ACCESS

Question

hi there, I need to find a way to make grid in MS Access & insert new lines to it by code VBA only exactly how excel behave.

Answer 1

Have you looked at continuous forms and datasheets?

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

Answer 2

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