Solved: Delete used rows in worksheet(sounds easy)

[PeterNZ]

Hi guys

Have excel 2003 workbook with form I created to enter data and once all data entries done the click of a command button will send all that data to an access database, all good so far.

Now I want to have a msgbox with" all records successfully sent to DB" worksheet data can now be deleted Yes/No User clicks yes & the data is deleted except for the header row.

Reason delete needed is if they add more data to sheet and send again without deletion then duplicates will be in DB.

So checking for duplicates code may otherwise be needed.(or pehaps preferred to remove human error)

Please help O wise ones.

[lucas]

Why not use cells and clear contents of the entire sheet?

Then add your header row back in using an array?

[VBA]Option Explicit
Sub Sheet_Fill_Column_Headings()
Sheets("Sheet1").Cells.ClearContents
Dim myarray As Variant
myarray = Array("Heading 1", "Heading 2", "Heading 3", "Heading 4", "Heading 5")
Sheets("Sheet1").Range("a1:e1").Value = myarray
End Sub[/VBA]

[Bob Phillips]

Why do you even need headers, a db is field defined, not worksheet headers.

[austenr]

Bob, could be that he wants the headers for other people to do data entry in. Why not just make Lucas's solution a workbook open/close event.

[Bob Phillips]

Bob, could be that he wants the headers for other people to do data entry in. Why not just make Lucas's solution a workbook open/close event.

He said he used a form Austen.

[austenr]

My bad. Must be my ADD kicking in.

[PeterNZ]

Great response fellas, I had best explain that the form is launched by clicking a command button on the top header area of the worksheet, It's used to enter one row (record) at a time to a worksheet , it closes after a record is entered. To enter another record they simply click the button again.
When the user finishes all entries for the week maybe 20 or 30 diferent records He can check the worksheet for accuracy and completeness then Click a second command button also located on worksheet header area which will send all to the Access DB.
So I realy do need the header area to remain untouched by delete code.
hope this helps clarify for you. Thanks
PS: Thinking maybe use a named range to delete, as entries will always be within a fairly small range of maybe 20 or 30 rows A to N starting at row 3. (row 1 has header for sheet and cmd buttons , row 2 has colomn headers only)
just never done delete code before, deletion seems so final

[georgiboy]

Maybe something like...
[VBA] Ans = MsgBox("All records successfully sent to DB" & vbNewLine & vbNewLine & _
"Do you wish to delete worksheet data?", vbYesNo, "Input needed...")

Select Case Ans
Case vbYes: Range("A3:N30").ClearContents
Case vbNo: MsgBox "You have chosen not to delete the sheet data."
End Select[/VBA]
Hope this helps

[PeterNZ]

That looks like what the doctor ordered

Thanks georgieboy

[Bob Phillips]

Two things.

You are not deleting the data as you save it in the database. You should think of the worksheet not as the data, but as a report OF THE DATA, the worksheet only serves to display what is on the database. Therefore deleting it from the worksheet is fine, it can always be retrieved.

Having the user click the button every time that they want to enter a record, then closing the form is not a god user experience IMO. Better to load the form, have Add and Quit buttons, and after they enter the record and hit Add then you clear the form, BUT leave it up. They remove the form with Quit.

[PeterNZ]

Thanks XLD good advice.

I have one Question though I have noticed that if the access db is currently open when the ado code goes to write to access no data is written to access. Why would that be?. Because if db is closed all data gets written to access db no prob.


PS:I have just spent couple hunded $ on two new books on Access and VBA arrived yesterday so have alot of study ahead of me.
should help me though as will need to develop queries and reports for the boss . (They always want more lol)

[mikerickson]

[VBA]If MsgBox("Delete?", vbYesNo) = vbYes Then
ActiveSheet.UsedRange.Offset(1,0).ClearContents
End If[/VBA]

[Bob Phillips]

Thanks XLD good advice.

I have one Question though I have noticed that if the access db is currently open when the ado code goes to write to access no data is written to access. Why would that be?. Because if db is closed all data gets written to access db no prob.

If Access has the table locked, such as in design mode, you will fail to update it, but you should get an error raised. If you are just looking at data that should be no problem.

PS:I have just spent couple hunded $ on two new books on Access and VBA arrived yesterday so have alot of study ahead of me.
should help me though as will need to develop queries and reports for the boss . (They always want more lol)

A couple of hundred $? Do they include a solid gold bookmark at that price? Be careful though, Access has tons of functionality that you don't need if you use Access purely as a database and Excel as the client with an ADO interface. That is the way I tend to use it, I don't like Access as a product so I don't use its reporting capability and so on.

[PeterNZ]

Ok I'm not seeing error so must need error handler code to even see an error in the first place? . Here is my module that sends to access db. What error code should be added to it to know and handle why won't write to db if db is open at time of trying to export to db.

Sub ADOFromExcelToAccess()


Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' set the path & connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=K:\ENGSHARE\AA- IDCON ENSHARE\Planning & Scheduling\Callout Log Data & Forms\Logs\BreakinWorkDontOpenThis.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "DontOpenThis", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("CorB") = Range("A" & r).Value
.Fields("WorkOrder") = Range("B" & r).Value
.Fields("5W1H") = Range("C" & r).Value
.Fields("Challenge") = Range("D" & r).Value
.Fields("DateDone") = Range("E" & r).Value
.Fields("Time") = Range("F" & r).Value
.Fields("YourName") = Range("G" & r).Value
.Fields("Asset") = Range("H" & r).Value
.Fields("Area") = Range("I" & r).Value
.Fields("Superviser") = Range("J" & r).Value
.Fields("Item") = Range("K" & r).Value
.Fields("Type") = Range("L" & r).Value
.Fields("Cause1") = Range("M" & r).Value
.Fields("Cause2") = Range("N" & r).Value
.Fields("ActionTaken") = Range("O" & r).Value
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

Thanks Guys

[Bob Phillips]

Adding error handling code won't show an error, it just handles it. If the error isn't being raised then error handler makes no dfference.

I have this proble with AddNew, it doesn't give any feedback, so what I do is to read the record after inserting it, if it is there I will find the record.

[PeterNZ]

OK thanks XLD.