How to confirm a record has been written before the code moves on

[Kaw4Life]

Currently I have a 3 second loop in place and sometimes that works and sometimes it does not. Here is the problem. The main form is unbound. The reason for this is that I do not want anything writen to the table with out being checked out first. No data is written until the use clicks the 'Save Record' button. The subform is bound to a query that limits the data to 'that' user only. The subform reflects the entries being made by the input on the main form. I have a requery the works 50% of the time. If the user clicks the 'Save Record' it shows up automaticly for them. I want 'EVERY' entry to show up with out them having to hit F5 or something else to get the newest data to show in the subform.

Here is the code.

Private Sub btnSaveRecord_Click()
  'Make sure date is filled out
    If Nz(txtActivityDate, "") = "" Then
      MsgBox "The Date Field is Required"
      txtActivityDate.SetFocus
      Exit Sub
    End If

  ' open connection and recordset
    Dim cnConnection As ADODB.Connection
    Set cnConnection = New ADODB.Connection
    Dim strConnection As String
    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & CurrentProject.Path & "\metrix_front.mdb;"
    cnConnection.Open strConnection

    Dim rsRecordset As ADODB.Recordset
    Set rsRecordset = New ADODB.Recordset
      rsRecordset.CursorType = adOpenDynamic
      rsRecordset.LockType = adLockPessimistic
    rsRecordset.Open "SELECT * FROM tblDailyPlan", cnConnection

  'write the new record data to table
    DoCmd.Hourglass True
    rsRecordset.AddNew
      rsRecordset!ActivityDate = txtActivityDate
      rsRecordset!ShipClass_ID = txtShipClass
      rsRecordset!Activities_ID = txtActivity
      If IsNull(Me.txtInputs) Then
        rsRecordset!Inputs = 0
      Else
        rsRecordset!Inputs = txtInputs
      End If
      If IsNull(Me.txtCompletedActions) Then
        rsRecordset!CompletedAct = 0
      Else
        rsRecordset!CompletedAct = txtCompletedActions
      End If
      rsRecordset!ActualTime = txtActualTime
      rsRecordset!Comments = txtComments
      rsRecordset!Employee_ID = [Forms]![frmLogonAssist]![Employee_ID]
    rsRecordset.Update

  'clear form and close connections
    Dim ctl As Control
    On Error Resume Next
    For Each ctl In Me.Controls
      ctl.Value = Null
    Next ctl
  'Cleanup
    rsRecordset.Close
    cnConnection.Close
    Set rsRecordset = Nothing
    Set cnConnection = Nothing

    txtInputs.Enabled = True 'reactivates control in case "Admin" or "Leave" was selected
    txtCompletedActions.Enabled = True 'reactivates control in case "Admin" or "Leave" was selected
    txtShipClass.Enabled = True 'reactivates control in case "Admin" or "Leave" was selected

  ' dont like this but it works - delay is so that recordset can be successfully writen to table before requery runs
    TWait = Time
    TWait = DateAdd("s", 2, TWait)
    Do Until TNow >= TWait
      TNow = Time
    Loop
    DoCmd.Hourglass False
    Forms![frmDailyPlan]![subfrmDailyPlanRange].Form.Requery
    Me.[txtActivityDate].SetFocus
End Sub

[NeoPa]

Bound forms don't give you less control. You just need to use them properly. Unbound forms, on the other hand, are clearly less easy to work with and require you to be very precise about everything you try to do. They're also, generally, less efficient, but that rarely becomes an issue to be fair.

I suggest you take a step back and do it properly with the bound forms approach.

[Kaw4Life]

There are arguments on both sides as to the pros and cons to unbound forms. What I have is what I have. Starting over is not an option for me. This goes in to production Monday. The lag is not a show stopper just a minor glitch.

[NeoPa]

Kaw4Life:
There are arguments on both sides as to the pros and cons to unbound forms.

I'd be interested to see some. I've been in the game for a long while now and never heard anyone with any understanding of the issues recommend unbound forms for managing data. I've seen plenty of it about, but only ever from people who had little idea of the options.

As you say though, this is your project and you have the absolute right to choose your own course. My advice is simply offered as advice. No more.

[TheSmileyCoder]

I have not before encountered such an issue. As far as I know the code will not run beyond the update point until AFTER the update has finished. I don't know if this could be related to using ADODB type recordsets since I have almost exclusively worked with DAO recordsets which I believe to be preferable when connecting to Access databases.

The only thing I can suspect as a casue could be if your forms recordset is based on a query, which contains a second query, and that Access uses a cached version of the second query. What is the recordsource property of your subform? Or is your subform also unbound?

[Kaw4Life]

I did not do the best of jobs describing the enviorment. I am new to this Access stuff, I grew up as a Network type, routers, firewall and stuff like that. I agree that a bound for is far easier to get put together. I was reading Denise Gosnell's book and thought she was saying that unbound is faster and fasilatated multi users better. I have since rebuilt the form in a test copy that is bound. Everthing works well. The only thing that I DO NOT LIKE is that an entry is made to the table just but completing the last field in the form. I am sure I can come up with something the will give the user a more concreat indication that the entry has been made but for now it just happens with little indication it was completed sucessfully. I know I can do a MsgBox but again that slows the process of multipul entries.

To answer your question SmileyCoder the subform is based on a query with no second query. The subform is bound to qryDailyPlanRan ge. Even when the front and back end are on the same machine I get mixed results when the user makes and entry with the parent unbound form. About 50% of the time the new entry shows up on the subform.

[TheSmileyCoder]

I must say it sounds weird to me.

If your interested in doing the debugging work, you could try to use a DAO recordset instead and see if that changes anything.


Now when it comes to saving/not saving the record, I personally prefer to write a routine in the forms BeforeUpdate event and add a save button. Quick example:

Option Compare Database
Option Explicit
Private bSaveClicked as boolean

Private btn_Save_Click()
  bSaveClicked=True
  On error resume next 'In case user cancels the save
  Docmd.Runcommand accmdSaveRecord
  bSaveClicked=false
End Sub


Private Sub Form_BeforeUpdate(Cancel as integer)
  'You can put form validation here if you want



  If not bSaveClicked then
    dim intReply as vbMsgBoxResult
    intReply =Msgbox("Do you wish to save this record?",vbyesnoCancel+vbquestion)

    Select Case intReply
      Case vbCancel
        Cancel=True
        Me.Undo
      Case vbYes
        Cancel=false
      Case vbNo
        Cancel=true
    End Select  
  Else
   'User clicked save, so don't ask
    Cancel=false
  End if
End Sub

This is just some quick code, which can be refined quite a bit depending on your needs.

[Kaw4Life]

One of the things I want is to have the record NOT saved unless they hit 'Save Record'.

I will look at your suggestion and see if I can make that happen.

[NeoPa]

Kaw4Life:
One of the things I want is to have the record NOT saved unless they hit 'Save Record'.

Indeed. I was going to write some code to illustrate how that can be achieved, but it seems Smiley has done that already. My code would only have been marginally different from that in effect. Somewhat different style maybe, but very similar effect. I'm sure you'll find it helps you to get it working exactly as you wish :-)