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
8 2016 NeoPa 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB 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.
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?
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.
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.
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 32,556
Expert Mod 16PB 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 :-)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Nicola Larosa |
last post by:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I find the following excerpt fairly sad... (right before the screenshot):
"The first version of Scribus used Python, with the Python bindings...
|
by: karenmiddleol |
last post by:
The following code works fine I can connect to a SQL database and list
all the records in the Orders table onto a web page.
Now our users want me to modify it so that each row displayed as a...
|
by: DavidGeorge |
last post by:
I have 2 subforms, neither of which is linked to the main form. Of the
left of my form is a subform with a list of organisations and on the
right is a subform with details of the organisation...
|
by: joshc |
last post by:
I've got two bits of code that I would like some more experienced folks
to check for conformance to the Standard. I've tried my best to read
the standard and search around and I think and hope this...
|
by: Paul H |
last post by:
I am using the code below to force a user to confirm changes to a form. This
works fine unless the form has a subform. Here is what happens:
1. If I put the code in the both the main form and the...
|
by: Mikus Sleiners |
last post by:
I'm trying to write new stream from string and i can't figure out why my
memory stream
instance is null after i have writen to it with stream writer.
Here is an example.
MemoryStream stream =...
|
by: paquer |
last post by:
Im trying to sort out all subform records except for the new record
created via a cmd on the main form.
The command creates the new record, autofills some fields, then the PK
value is...
|
by: miha.valencic |
last post by:
Is there a way (int ASP.NET 1.1 or 2.0) to check which "Cache-Control"
headers have been output or set before the page is written to output
stream?
There is Response.Headers property in .NET...
|
by: thirunavukarasukm |
last post by:
Hai....
"i want used confirm in javascript in code behind"
i am used confirm button ,,,in code behind.
if the confirm button worked
the function is return the true...
|
by: gviteri |
last post by:
This is my first coding attempt at VBA (6.3) and I'm very ill-rehearsed. I was hoping that someone with more experience would be able to help me out here. This is what I have:
Sub Macro1()
On...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |