By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,761 Members | 1,879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,761 IT Pros & Developers. It's quick & easy.

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

P: 4
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSaveRecord_Click()
  2.   'Make sure date is filled out
  3.     If Nz(txtActivityDate, "") = "" Then
  4.       MsgBox "The Date Field is Required"
  5.       txtActivityDate.SetFocus
  6.       Exit Sub
  7.     End If
  9.   ' open connection and recordset
  10.     Dim cnConnection As ADODB.Connection
  11.     Set cnConnection = New ADODB.Connection
  12.     Dim strConnection As String
  13.     strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  14.                     "Data Source=" & CurrentProject.Path & "\metrix_front.mdb;"
  15.     cnConnection.Open strConnection
  17.     Dim rsRecordset As ADODB.Recordset
  18.     Set rsRecordset = New ADODB.Recordset
  19.       rsRecordset.CursorType = adOpenDynamic
  20.       rsRecordset.LockType = adLockPessimistic
  21.     rsRecordset.Open "SELECT * FROM tblDailyPlan", cnConnection
  23.   'write the new record data to table
  24.     DoCmd.Hourglass True
  25.     rsRecordset.AddNew
  26.       rsRecordset!ActivityDate = txtActivityDate
  27.       rsRecordset!ShipClass_ID = txtShipClass
  28.       rsRecordset!Activities_ID = txtActivity
  29.       If IsNull(Me.txtInputs) Then
  30.         rsRecordset!Inputs = 0
  31.       Else
  32.         rsRecordset!Inputs = txtInputs
  33.       End If
  34.       If IsNull(Me.txtCompletedActions) Then
  35.         rsRecordset!CompletedAct = 0
  36.       Else
  37.         rsRecordset!CompletedAct = txtCompletedActions
  38.       End If
  39.       rsRecordset!ActualTime = txtActualTime
  40.       rsRecordset!Comments = txtComments
  41.       rsRecordset!Employee_ID = [Forms]![frmLogonAssist]![Employee_ID]
  42.     rsRecordset.Update
  44.   'clear form and close connections
  45.     Dim ctl As Control
  46.     On Error Resume Next
  47.     For Each ctl In Me.Controls
  48.       ctl.Value = Null
  49.     Next ctl
  50.   'Cleanup
  51.     rsRecordset.Close
  52.     cnConnection.Close
  53.     Set rsRecordset = Nothing
  54.     Set cnConnection = Nothing
  56.     txtInputs.Enabled = True 'reactivates control in case "Admin" or "Leave" was selected
  57.     txtCompletedActions.Enabled = True 'reactivates control in case "Admin" or "Leave" was selected
  58.     txtShipClass.Enabled = True 'reactivates control in case "Admin" or "Leave" was selected
  60.   ' dont like this but it works - delay is so that recordset can be successfully writen to table before requery runs
  61.     TWait = Time
  62.     TWait = DateAdd("s", 2, TWait)
  63.     Do Until TNow >= TWait
  64.       TNow = Time
  65.     Loop
  66.     DoCmd.Hourglass False
  67.     Forms![frmDailyPlan]![subfrmDailyPlanRange].Form.Requery
  68.     Me.[txtActivityDate].SetFocus
  69. End Sub
Apr 25 '12 #1
Share this Question
Share on Google+
8 Replies

Expert Mod 15k+
P: 31,489
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.
Apr 25 '12 #2

P: 4
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.
Apr 25 '12 #3

Expert Mod 15k+
P: 31,489
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.
Apr 25 '12 #4

Expert Mod 100+
P: 2,321
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?
Apr 26 '12 #5

P: 4
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 qryDailyPlanRange. 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.
Apr 26 '12 #6

Expert Mod 100+
P: 2,321
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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Private bSaveClicked as boolean
  5. Private btn_Save_Click()
  6.   bSaveClicked=True
  7.   On error resume next 'In case user cancels the save
  8.   Docmd.Runcommand accmdSaveRecord
  9.   bSaveClicked=false
  10. End Sub
  13. Private Sub Form_BeforeUpdate(Cancel as integer)
  14.   'You can put form validation here if you want
  18.   If not bSaveClicked then
  19.     dim intReply as vbMsgBoxResult
  20.     intReply =Msgbox("Do you wish to save this record?",vbyesnoCancel+vbquestion)
  22.     Select Case intReply
  23.       Case vbCancel
  24.         Cancel=True
  25.         Me.Undo
  26.       Case vbYes
  27.         Cancel=false
  28.       Case vbNo
  29.         Cancel=true
  30.     End Select  
  31.   Else
  32.    'User clicked save, so don't ask
  33.     Cancel=false
  34.   End if
  35. End Sub
This is just some quick code, which can be refined quite a bit depending on your needs.
Apr 26 '12 #7

P: 4
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.
Apr 26 '12 #8

Expert Mod 15k+
P: 31,489
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 :-)
Apr 27 '12 #9

Post your reply

Sign in to post your reply or Sign up for a free account.