04-25-12, 21:58 #1Registered User
- Join Date
- Apr 2012
Unanswered: Unbound Form and Bound Form working together?!
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 written to the table with out being checked out first. Plus I am trying to learn more about unbound forms. 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 automatically 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.
Private Sub btnSaveRecord_Click()
' make sure date is filled out
If Nz(txtActivityDate, "") = "" Then
MsgBox "The Date Field is Required"
' 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;"
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
rsRecordset!ActivityDate = txtActivityDate
rsRecordset!ShipClass_ID = txtShipClass
rsRecordset!Activities_ID = txtActivity
If IsNull(Me.txtInputs) Then
rsRecordset!Inputs = 0
rsRecordset!Inputs = txtInputs
If IsNull(Me.txtCompletedActions) Then
rsRecordset!CompletedAct = 0
rsRecordset!CompletedAct = txtCompletedActions
rsRecordset!ActualTime = txtActualTime
rsRecordset!Comments = txtComments
rsRecordset!Employee_ID = [Forms]![frmLogonAssist]![Employee_ID]
'clear form and close connections
Dim ctl As Control
On Error Resume Next
For Each ctl In Me.Controls
ctl.Value = Null
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
04-25-12, 22:58 #2Moderator
Provided Answers: 19
- Join Date
- Jun 2005
- Richmond, Virginia USA
The belief that Unbound forms are necessary in order to do data validation before "committing" data to a Table is simply not true!
Any "checks" you need to do before saving the data can be done using the Form_BeforeUpdate event of a bound form and Cancel = True to stop the saving of the record until missing/erroneous data is dealt with!
A big part of the reason to use Access for database development is the speed with which it can be created, using Bound Forms. Several developers I know, experienced in both Visual Basic database development and Access development, estimate that development using Unbound Forms by experienced developers takes two to three times as long as it does when using Access and Bound Forms.
If you insist on using unbound forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end.
- You can create an EXE file which gives total protection to your code/design
- You can distribute the db to PCs without a copy of Access being on board
- Your data security is far, far better than anything you can do in Access
Linq ;0)>Hope this helps!
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007