Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    How to turn off edit mode (Access 2003 xp sp3)

    I have a code block that clears an underlying table and then requerys the form to clear it. (form is in datasheet mode)

    <font face="Comic Sans MS"> Private Sub cmdClear_Click()
    Dim rst As DAO.Recordset
    On Error GoTo cmdClear_Error

    Set rst = CurrentDb.OpenRecordset("tblContTimeCards")
    With rst
    If Not rst.BOF Then
    .MoveFirst
    Do Until .EOF
    .Delete
    .MoveNext
    Loop
    End If
    End With

    rst.Close
    Set rst = Nothing

    sbfContTimeInput.Requery

    cmdProcess.SetFocus

    Exit Sub

    cmdClear_Error:
    MsgBox ("There is an input line in edit mode. Press 'Esc' to get rid of the little pencil in the left column and try again.")
    End Sub </font face=comic>

    but it leaves the form in edit mode, with the little pencil showing. What command do I need so that the form is not left in edit mode?
    "Heading for the deep end"

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: How to turn off edit mode (Access 2003 xp sp3)

    The pencil just means that there is an unsaved record. Is there any data on the line next to the pencil?

    I pasted your code into a button behind a form,and I did not get the pencil, so I can't tell why you get it.

    Do you have any other code running that could be interfering?
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: How to turn off edit mode (Access 2003 xp sp3)

    Yes I do.

    in reference to the attached picture:

    EmpCode: is a required value and has the following code
    <font face="Comic Sans MS">Private Sub Employees_Enter()
    If Me.NewRecord = True Then Employees.Value = EmpNo
    End Sub

    Private Sub Employees_Exit(Cancel As Integer)
    On Error GoTo here
    If IsNull([Employees]) Then Exit Sub
    EmpNo = [Employees]
    Exit Sub
    here:
    End Sub</font face=comic>

    Jobcode: has
    <font face="Comic Sans MS">Private Sub Jobcode_BeforeUpdate(Cancel As Integer)
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim invdate As Date
    Set rst = CurrentDb.OpenRecordset("Invoices")
    With rst
    .MoveFirst 'Find the first matching record (assuming it's a string value)
    .FindFirst "[Job#]=" & [Jobcode] 'If NoMatch is true, there is no matching record so Not NoMatch means you found one
    If Not .NoMatch Then
    invdate = !Date
    If DateDiff("d", invdate, WorkDate) > 3 Then
    If MsgBox("This Job Invoiced on " & invdate & ", Continue?", vbYesNo) = vbNo Then Cancel = True
    End If
    End If
    End With
    Set rst = Nothing

    If [Jobcode] > DMax("[Job #]", "Original") Then
    MsgBox ("This Job Number is not valid. Please Check")
    Cancel = True
    End If

    End Sub

    Private Sub Jobcode_Exit(Cancel As Integer)
    Dim strSQL As String
    Dim rst As DAO.Recordset
    Dim who As String

    If Jobcode < 50000 Then
    Me.Parent!lblCustomer.Caption = ""
    Exit Sub
    End If

    strSQL = "SELECT Original.[Job #], Customer.Customer " & _
    "FROM Customer INNER JOIN Original ON Customer.AccNo = Original.AccNo " & _
    "WHERE (((Original.[Job #])=" & Jobcode & "))"

    Set rst = CurrentDb.OpenRecordset(strSQL)

    With rst
    who = !Customer
    End With

    rst.Close
    Set rst = Nothing

    Me.Parent!lblCustomer.Caption = who

    End Sub</font face=comic>

    Dept: has no code

    PTime: has no code

    WorkDate: is a required value and has the following code
    <font face="Comic Sans MS">Private Sub WorkDate_Enter()
    WorkDate = Me.Parent!JobDate
    End Sub
    </font face=comic>

    What I want to do is cancel the unsaved record with code rather than having the operator remember to press Esc to clear the pencil.
    Attached Images Attached Images
    "Heading for the deep end"

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to turn off edit mode (Access 2003 xp sp3)

    You might add a line

    Me.Undo

    at the beginning of the cmdClear_Click event procedure.

    BTW, you can replace the code

    Set rst = CurrentDb.OpenRecordset("tblContTimeCards")
    With rst
    If Not rst.BOF Then
    .MoveFirst
    Do Until .EOF
    .Delete
    .MoveNext
    Loop
    End If
    End With

    rst.Close
    Set rst = Nothing

    with the single line

    CurrentDb.Execute "DELETE * FROM tblContTimeCards"

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: How to turn off edit mode (Access 2003 xp sp3)

    Further to Hans' reply

    This code is the cause. Whenever you start a new record, you immediately change it.

    Private Sub Employees_Enter()
    If Me.NewRecord = True Then Employees.Value = EmpNo
    End Sub

    If you put the EmpNo in either as a Default Value, or as Child Master Link fields (if this is a subform) then you would not get the pencil.
    Regards
    John



  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: How to turn off edit mode (Access 2003 xp sp3)

    Hans, A slight diversion in reference to your post of using

    CurrentDb.Execute "DELETE * FROM tblContTimeCards"

    Is the syntax right to replace a query with the following SQL

    DELETE tblOT.*, tblOT.jdate
    FROM tblOT
    WHERE (((tblOT.jdate)=DMax("tblOT.jdate","tblOT")));

    with

    CurrentDb.Execute "DELETE * FROM tblOT WHERE (((tblOT.jdate)=DMax('tblOT.jdate','tblOT')))" ?

    I am delighted with the above code that you showed me as I have been attempting to streamline my DB and being able to replace a few queries with 1-liners is great!
    "Heading for the deep end"

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to turn off edit mode (Access 2003 xp sp3)

    Yes, you can even simplify it to
    <code>
    CurrentDb.Execute "DELETE * FROM tblOT WHERE jdate=DMax('jdate', 'tblOT')"
    </code>
    (Access generates lots of superfluous elements in its SQL to make it easier to edit and expand the query later on)

  8. #8
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: How to turn off edit mode (Access 2003 xp sp3)

    John, I see what you mean. I'll try putting the emp number as a default value
    "Heading for the deep end"

  9. #9
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: How to turn off edit mode (Access 2003 xp sp3)

    Thank you Hans
    "Heading for the deep end"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •