access vba #deleted record on continuous form issue

I have not really used 2010 to much so I cannot be certain.

Is the delete button in the Detail section or in the form footer?  I generally put it in the forms footer section.  

Placing it in the detail section may be preventing the requery from hiding the selected record.

If you put it in the footer section, you should disable the button, and only enable it in the Current event of the form.  Since the current event will fire, and the first record will be selected as soon as the form is opened, I occasionally set a flag in the Form_Load event, then test that flag in the Form_Current event.  If the flag is set (true), then I disable the command button and reset the flag (set it to False).  If the flag is not set (False) then I enable the command button.

Is this temp table a JET based one or is it SQL Server (or something else)?

If SQL Server, add a timestamp column to the table and see if the issue goes away.  You don't need to do anything with the field other then add it.

Jim.

<On the  form_load() I populate the table that this form is bound to. >
Can I ask why you are not using the Open event?
(The Open event can be cancelled, the Load event cannot)

<Displayed on the form I have a label that I alter the caption property based on a value in the record in the detail_paint() . >
can I ask why you are using the OnPaint event?
Try it on the current event

Finally, I am not clear why the temp table can't be the Recordsource for the form.?
In other words, you can make a temp table the recordsource for a form, ...even if the temp tables does not exist (or is not populated, ...in your case).
Your can populate/create the temp table with code, then when the form opens the records are populated.
Thus there is no need to populate the table records on the load event.

In other words, the sample attached here does as you request, with no errors.
Database38.mdb

Thanks, Changing the detail_paint() to Form_current() did the trick.
---> Actually, it did not. ---> see next comment

Thanks for the sample, too. I am basically doing the same thing as in your sample, just not explaining it I guess. The temp table must be populated with data from various tables based on a date that the the form started with (DoCmd.OpenForm ...) but the form is bound to this table.

For some reason I got a run time error when changing the form_load to form_open(), so I went back to form_load(). The RT Error is "The Open_Form command was canceled" and pointed at the DoCmd.OpenForm

Wait, I stand corrected, the Form_Current() did not work after all. It didn't fail when I deleted the record, but now the individual label.captions are incorrect. Changing one changes them all, whereas in the detail_paint() the values were changed individually. Actually, this is a problem in the sample file you provided, as well.
Any other ideas?

OK, ...so is this resolved, or do you need help with the new error?

Jeff

No, not resolved and still need help with original problem.

You know the drill:
Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Unhide and hidden database objects
10. Compile the code. (From the VBA code window, click: Debug-->Compile)
11. Run the compact/Repair utility.
12. Remove any Passwords and/or security.
13. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
14. Post the explicit steps to replicate the issue.
15. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

OK, here it is. Run the frmDailySchedule and you will see a continuous form with 2 records saying Pickup from and Deliver to . To recreate the error, at the end of the form, add a new record by selecting a truck and entering a start time, then Save and Close. Reopen frmDailySchedule and press the X button next to this new record to remove it (from the ScheduleLocal table - the frm is based on a query that selects all of this table.) When the record is deleted, the #deleted shows up in this spot on the form and the run time error occurs in the detail_paint() when it tries to access the (invisible) field ActionType (under the label).

By the way, the expected results is to simply remove the record and refresh the form without the record.
Database3.mdb

<then Save and Close.>
Save and close by doing what exactly?
Your button are all blank?..

In any event, ...when I reopen the form, the record is not saved...

Sorry, but I am lost with your design here.
Are you quite sure this design is optimal for what you are trying to do?

My sample uses a lot less code, and it woks fine.

The buttons have labels to me. Anyway the save and close is the left bottom one.
With all due respect, your code does not work in that the value under the delete button does not show the correct value - it is th same value repeated over and over, which is the same problem I get doing it your way.

Anyone else have any input on this? By the way, it is the Access 2010 database, for those that asked.

I'll try it here in a few minutes.

Jim.

OK I tried your sample and it doesn't work as you outlined.  No matter what I do when entering a new record in the form in trying to save it, when I close and re-open the form it comes back to the original two records.

Looking at the tables, Schedule contains the original two records and ScheduleLocal shows two records with #Deleted.

?

Jim.

Some comments on your OnLoad Code in-line (***JRD).

Jim.

Private Sub form_load()


  Me.AllowAdditions = True

  ***JRD - Use a generic function to return the nth token of a string rather then doing the parsing here.  You may want to pass multiple argument in the openargs and the way this is now, your only looking for the first pipe.   I can give you some examples of this

  ' get the passed in argument if htere is one

  If Len(Me.OpenArgs) > 0 Then
    ' Position of the pipe
    intPos = InStr(Me.OpenArgs, "|")

    If intPos > 0 Then

      ' Retrieve Control Name from the first part of the string
      strControlName = Left$(Me.OpenArgs, intPos - 1)

      ' Retrieve Value to Assign from the end of the string
      strValue = Mid$(Me.OpenArgs, intPos + 1)

      ' Assign the value to the control
      Me(strControlName) = strValue

    End If
  Else
  ***JRD - Harcoded date?  For testing?  If not, bad idea.
    strValue = "09/11/2012"
    Me.txtActivityDate = strValue
  End If

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
   
    Set db = CurrentDb()

***JRD - always use CurrentDB().Execute <SQL> , dbFailOnError
   To execute queries.  SetWarnings off masks any errors you might have and your query may or may not be executing as you think.

    ' clear the ScheduleLocal table
    DoCmd.SetWarnings (WarningsOff)
    DoCmd.RunSQL ("DELETE FROM ScheduleLocal")
    DoCmd.SetWarnings (WarningsOn)

***JRD - Always be explicit as possible - what are you trying to requery here?  If form, then:
    Me.Requery.  If Control, then Me.<somecontrol>.Requery  It's clearer in the code that way.

    DoCmd.Requery
   
    ' populate the ScheduleLocal table with data already in the Schedule table for today
    selQuery = "SELECT * FROM [Schedule] WHERE [ScheduleDate] = #" & strValue & "#" & " ORDER BY [BOLNumber],[StartTime]"
    Set rs = db.OpenRecordset(selQuery)
    If rs.RecordCount = 0 Then
        'MsgBox "The Recordset is Empty"
    Else
        **JRD this is a performance guzzler...no need to move last then first, just loop until rs.EOF
        rs.MoveLast
        rs.MoveFirst
        recCnt = rs.RecordCount
        For rsLoop = 0 To recCnt - 1
            query = "Insert INTO [ScheduleLocal] " & _
                "(ScheduleDate, StartTime, EndTime, TruckNumber, ActionType, Notes, BOLNumber, ScheduleId, Name, Address1, Address2, City, State, ZipCode) " & _
                "VALUES (#" & rs!ScheduleDate & "#,'" & rs!StartTime & "', '" & rs!EndTime & "', '" & rs!TruckNumber & _
                "', '" & rs!ActionType & "', '" & Clean(rs!Notes) & "', " & rs!BOLNumber & ", " & rs!Id & ", '" & Clean(rs!Name) & "'" & ", '" & _
                        Clean(rs!Address1) & "'" & ", '" & Clean(rs!Address2) & "'" & ", '" & Clean(rs!City) & "'" & ", '" & _
                        Clean(rs!State) & "'" & ", '" & Clean(rs!ZipCode) & "');"
Debug.Print query
            ***JRD Again use CurrentDB().execute <SQL>, dbFailOnError so you know when it fails.
            DoCmd.RunSQL query
            rs.MoveNext
        Next rsLoop
    End If
       
    Me.OrderBy = "TruckNumber, StartTime"
    Me.OrderByOn = True
    Me.Requery
    rs.Close
    Set rs = Nothing

    Me.Refresh


 **JRD - No error handling - add it.

Rich,

 Not to get side tracked, but here's a litte more detail on what I meant by that first comment.  An example of one of my OpenArgs that I'm passing to a form:

"ADD;SETCTRLTODATA=txtCustomerID:" & NewData & ";EXITTOFORM=frmLoad"

  I'm using a semi-colon as the main delimiter and a colon as sub-delimiter.  First argment tells my form to jump into add mode, second is that I want to populate one or more controls with some data (like your doing) and the last is that I want focus to move back to a specific form when it's done.

1st Token:  ADD
2nd Token: SETCTRLTODATA=txtCustomerID:" & NewData & "
3rd Token: EXITTOFORM=frmLoad

My form routines look for the tag "SETCTRLTODATA=" when a form opens:

        ' Set any controls required by calling form.
        varOpenArgs = Nz(frm.OpenArgs, "")
        If Not (varOpenArgs = "") Then
          var = glrGetTagFromString(varOpenArgs, "SETCTRLTODATA")
          If Not IsNull(var) Then Call SetControlsToData(frm, Mid$(var, 1))
        End If

glrGetTagFromString() looks for the SETCTRLTODATA tag in all of the tokens and if found, then SetControlsToData() is called:

Sub SetControlsToData(frm As Form, strData As String)

  ' Set controls on frm to values contained in strData.
  ' Delimiter is a ":".  Format of strData is control name: value.
 
  Dim intPairNumber As Integer
  Dim varControlName As Variant
  Dim varData As Variant
  intPairNumber = 1

  Do
    varControlName = dhExtractString(strData, intPairNumber, ":")
    If varControlName = "" Then Exit Do
    varData = dhExtractString(strData, intPairNumber + 1, ":")
    frm(varControlName) = varData
    intPairNumber = intPairNumber + 2
  Loop

End Sub

 Which processes the control/value pairs from that token (everything after SETCTRLTODATA=)  dhExtractString is what is breaking down a passed in string based on a delimiter.

 Easy to setup and very flexible.

HTH,
Jim.

Hi, Jim
Thought I posted this earlier, but don't see it here...
Anyway, thank you very much for the great advise and samples. I am new to Access programming and am used to PHP/MySQL so this is a very different animal for me! Your help is much appreciated?

I think the buttons didn't save well from 2010 to 2003/7 version, so I adjusted the text color to make them more easily read. As far as it not working, the steps for it to work are as follows:
1- Open the frm
2- Add a new item by selecting a truck and a start time.
3- Either move to a new record or press the Update button to save it in the ScheduleLocal table
4- Press the Save and Close button to write it to the Schedule table
5- Close the form
6- Reopen the form and press the X button to delete this record (record changes to #deleted)
7- Press the Save and Close button (run time error occurs here)

The issue comes when I try to reference a value in the recordset in the Detail_Paint routine that is now #deleted, so either I need to check if it is deleted prior to checking or use a different event, though doing it in the Form_Current event Jeff suggested does not work as claimed, so there must be another way.
Database3.mdb

I'm on Vacation now.

I see that Jim has this covered, so I can keep on sunbathing....
;-)

Jeff

Just relaized this got buried in my in-basket; you still working with this?  If so, I'll have a look at it.

Jim.

Hi Jim,
Yes, I am. Would be great if you would take another look!
Rich

Your on paint code is really messing things up.

 OnPaint was added for report viewing on the screen and not really for forms.  Also understand what it is; it fires every time the section is updated graphically, which might happen multiple times as Access goes about its tasks.  The problem with that is that you don't know at what point it's going to fire or how many times (ie. field data may not be available at one point and then available another - this is where your error is coming from on delete).

 Another issue is the continuous form, which in Access is a strange beast indeed; only the current line actually has any controls.  The rest of the records are just painted on the screen and don't actually exist.  This is why when you change an *unbound* control, it changes all lines.

  Why is it this way you might ask?  It's actually goes back to Access's first roots when it was one of the first RDBM's to run under windows 3.1.   Win 3.1 had severe limitations on the number of resources you could have in play at one time and this was the method they choose to do a grid display and yet still live within Windows 3.1.  This is one of Access's chief faults to this day; lack of a proper grid control.

 Other thing with Access that you'll learn is that a lot is built into the product that you as a developer would normally do.  Coming from PHP, your used to doing a lot more.  So with the above said:

1. Remove your OnPaint code.
2. Delete your status label
3.Change the ActionType control's name to txtActionTypeDsiplay
4. Set it's controlsource to:

 =IIf([ActionType]="P","Pickup from","Delivery to")

 And try your form.  

A. All the labels appear correctly
B. You will no longer see #Deleted on the form
C. You won't get the error anymore.

Jim.

That sounds good, but the only issue is that there are really three states: Pickup from, Deliver to and Neither (or blank is fine). Is there a Case statement similar to the IIf that I can put in the macro(?)

and just for reference, if it was more then three conditions, I would write a procedure and call that rather then use IIF()'s.  Too confusing to read with more cases and depending on version, the expression service will evaluate all the conditions even if it doesn't use them, yielding poor performance.

Jim.

Cool! Thank you very much Jim. That makes sense - I guess I should have figured that syntax out myself. Also thank you very much for your explanations of the VBA / Access idiosyncrasies!