Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2012
    Posts
    9

    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"
    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
    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

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Kaw4Life View Post

    ...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...
    The Main Form/Subform construct is only intended to be used with Bound Forms. Ignoring this rule is why you're having to go through all of this song and dance and still only getting the desired results 50% of the time.

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •