Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2007
    Posts
    74

    Question Unanswered: Force write to table while form still open

    Hi All,

    I have a Call Observation Tracking database that I was asked to put together for one of our call center management groups. There are 33 Questions with a Yes, No, or N/A Checkbox. Each question is in it's own option group.

    When the observer completes a call observation and clicks the submit button I ask using a message box if they want to close the form or not. If they want to do more observations they click no. In that case, I want to write the record and then reset each of the fields except for the site name for the site they are observing. It is a client requirement that they be able to keep the form open.

    I noticed that the record does not get written to the table until the form is closed. So I am trying to force the write while the form is still open and before I reset the fields. I am trying to use
    Code:
     
    "DoCmd.GoToRecord , , acNewRec"
    to do this but I get an error when the line is executed. Oddly enough the errror says "You can't use the GoToRecord action or method on an object in design view." Obviously, I am not in design view so I do not understand the error.

    Am I going about this the right way? Is there another way to force the write without closing the form?

    I have posted the code below and would truly appreciate any guidance....

    Code:
     
    Private Sub cmdSUBMIT_Click()
    On Error GoTo Err_cmdSUBMIT_Click
    '*******************************************************************
    'This module writes all data from the userform to the OBDATA table.*
    '*******************************************************************
        '***Variable Declaration
        Dim response As String
        Dim strSITENAME As String
        
        '***Get the sitename
        strSITENAME = Nz(DLookup("[SITENAME]", "[SITES]", "[ID] =" & Forms![Observation Tracker 2]!cbSITE), "")
        
        '***Get the data & write it to the table
        Me![SITENAME] = strSITENAME
        Me![REPNAME] = Nz(Me!txtREP.Value, "")
        Me![MTN] = Nz(Me!TXTBTN.Value, "")
        Me![BTN] = Nz(Me!txtMTN.Value, "")
        Me![11] = Nz(Me!og1.Value, "")
        Me![12] = Nz(Me!og2.Value, "")
        Me![13] = Nz(Me!og3.Value, "")
        Me![14] = Nz(Me!og4.Value, "")
        Me![15] = Nz(Me!og5.Value, "")
        Me![16] = Nz(Me!og6.Value, "")
        Me![21] = Nz(Me!og7.Value, "")
        Me![22] = Nz(Me!og8.Value, "")
        Me![23] = Nz(Me!og9.Value, "")
        Me![24] = Nz(Me!og10.Value, "")
        Me![25] = Nz(Me!og11.Value, "")
        Me![31] = Nz(Me!og12.Value, "")
        Me![32] = Nz(Me!og13.Value, "")
        Me![33] = Nz(Me!og14.Value, "")
        Me![34] = Nz(Me!og15.Value, "")
        Me![35] = Nz(Me!og16.Value, "")
        Me![36] = Nz(Me!og17.Value, "")
        Me![37] = Nz(Me!og18.Value, "")
        Me![38] = Nz(Me!og19.Value, "")
        Me![41] = Nz(Me!og20.Value, "")
        Me![42] = Nz(Me!og21.Value, "")
        Me![43] = Nz(Me!og22.Value, "")
        Me![44] = Nz(Me!og23.Value, "")
        Me![45] = Nz(Me!og24.Value, "")
        Me![46] = Nz(Me!og25.Value, "")
        Me![51] = Nz(Me!og26.Value, "")
        Me![52] = Nz(Me!og27.Value, "")
        Me![53] = Nz(Me!og28.Value, "")
        Me![54] = Nz(Me!og29.Value, "")
        Me![55] = Nz(Me!og30.Value, "")
        Me![56] = Nz(Me!og31.Value, "")
        Me![57] = Nz(Me!og32.Value, "")
        Me![58] = Nz(Me!og33.Value, "")
        Me![COMMENT] = Nz(Me!txtComment.Value, "")
        
        
        '***Is the user done
            response = MsgBox("Would you like to close the form?", vbYesNo + vbQuestion, "Close Form?")
            
            Select Case response
                Case Is = vbYes
                    'The user is done so close the form
                    DoCmd.Close acForm, "Observation Tracker"
                
                Case Is = vbNo
                    'The user is not done so write the record and reset the fields
                    DoCmd.GoToRecord , , acNewRec
                    Me.txtREP = ""
                    Me.TXTBTN = ""
                    Me.txtMTN = ""
                    Me.txtComment = ""
                    Me.og1 = 3
                    Me.og2 = 3
                    Me.og3 = 3
                    Me.og4 = 3
                    Me.og5 = 3
                    Me.og6 = 3
                    Me.og7 = 3
                    Me.og8 = 3
                    Me.og9 = 3
                    Me.og10 = 3
                    Me.og11 = 3
                    Me.og12 = 3
                    Me.og13 = 3
                    Me.og14 = 3
                    Me.og15 = 3
                    Me.og16 = 3
                    Me.og17 = 3
                    Me.og18 = 3
                    Me.og19 = 3
                    Me.og20 = 3
                    Me.og21 = 3
                    Me.og22 = 3
                    Me.og23 = 3
                    Me.og24 = 3
                    Me.og25 = 3
                    Me.og26 = 3
                    Me.og27 = 3
                    Me.og28 = 3
                    Me.og29 = 3
                    Me.og30 = 3
                    Me.og31 = 3
                    Me.og32 = 3
                    Me.og33 = 3
            End Select
        
        
    Exit_cmdSUBMIT_Click:
        Exit Sub
        
    Err_cmdSUBMIT_Click:
        MsgBox Err.Description
        Resume Exit_cmdSUBMIT_Click
        
    End Sub
    Thanks in advance!
    Brent Blevins

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Have you tried using DoCmd.RunCommand accmdSaveRecord before your goto new record?

    Just another thought... you might like to consider adding the record via a recordset (DAO/ADO) rather than through the form's record source.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jun 2007
    Posts
    74
    SaveRecord says it is not available..

    I was writing the data (form's record source) in that way because once I am done I will be passing this to someone that does not know SQL. So I did not want to have to deal with their Syntax Errors later if they try to add new questions to the observation form. It seemed like the most simple way for a more novice user to be able to modify. At this point though I am not sure if I can make it work. So, I may have to write the data using SQL.

    Thank you for the reply!
    Brent

  4. #4
    Join Date
    Jun 2007
    Posts
    74

    Talking Conformity is the Key

    OK, So I have to demo this database in 5 hours....

    I gave up on my previous method of writing to the table. I used an Insert Into statement instead Code Below:

    Code:
     
    Private Sub cmdSUBMIT_Click()
    On Error GoTo Err_cmdSUBMIT_Click
    '*******************************************************************
    'This module writes all data from the userform to the OBDATA table.*
    '*******************************************************************
        '***Variable Declaration
        Dim intID As Long
        Dim response As String
        Dim OBDATE As Date
        Dim SITENAME As String, REPNAME As String, BTN As String, MTN As String, COMMENT As String
        Dim S1Q1 As Integer, S1Q2 As Integer, S1Q3 As Integer, S1Q4 As Integer, S1Q5 As Integer, S1Q6 As Integer, _
        S2Q1 As Integer, S2Q2 As Integer, S2Q3 As Integer, S2Q4 As Integer, S2Q5 As Integer, _
        S3Q1 As Integer, S3Q2 As Integer, S3Q3 As Integer, S3Q4 As Integer, S3Q5 As Integer, S3Q6 As Integer, S3Q7 As Integer, S3Q8 As Integer, _
        S4Q1 As Integer, S4Q2 As Integer, S4Q3 As Integer, S4Q4 As Integer, S4Q5 As Integer, S4Q6 As Integer, _
        S5Q1 As Integer, S5Q2 As Integer, S5Q3 As Integer, S5Q4 As Integer, S5Q5 As Integer, S5Q6 As Integer, S5Q7 As Integer, S5Q8 As Integer
        Dim strSQL As String
        Dim db As DAO.Database
        '***Get the Sitename from the Sites Table
        SITENAME = Nz(DLookup("[SITENAME]", "[SITES]", "[ID] =" & Forms![Observation Tracker]!cbSITE), "")
        
        '***Get the rest of the data from the form
        REPNAME = Me.txtREP
        BTN = Me.TXTBTN
        MTN = Me.txtMTN
        S1Q1 = Me.og1
        S1Q2 = Me.og2
        S1Q3 = Me.og3
        S1Q4 = Me.og4
        S1Q5 = Me.og5
        S1Q6 = Me.og6
        S2Q1 = Me.og7
        S2Q2 = Me.og8
        S2Q3 = Me.og9
        S2Q4 = Me.og10
        S2Q5 = Me.og11
        S3Q1 = Me.og12
        S3Q2 = Me.og13
        S3Q3 = Me.og14
        S3Q4 = Me.og15
        S3Q5 = Me.og16
        S3Q6 = Me.og17
        S3Q7 = Me.og18
        S3Q8 = Me.og19
        S4Q1 = Me.og20
        S4Q2 = Me.og21
        S4Q3 = Me.og22
        S4Q4 = Me.og23
        S4Q5 = Me.og24
        S4Q6 = Me.og25
        S5Q1 = Me.og26
        S5Q2 = Me.og27
        S5Q3 = Me.og28
        S5Q4 = Me.og29
        S5Q5 = Me.og30
        S5Q6 = Me.og31
        S5Q7 = Me.og32
        S5Q8 = Me.og33
        COMMENT = Me.txtComment
        OBDATE = Date
        
        
        '***Insert the data into the table
        strSQL = "INSERT INTO OBDATA (SITENAME, REPNAME, BTN, MTN, 11, 12, 13, 14, 15, 16, 21, 22, 23, 24, 25, 31, 32, 33, 34, 35, 36, 37, 38, 41, 42, 43, 44, 45, 46, 51, 52, 53, 54, 55, 56, 57, 58, COMMENT, OBDATE) Values ('" _
        & SITENAME & "','" & REPNAME & "','" & BTN & "','" & MTN & "','" & S1Q1 & "','" & S1Q2 & "','" & S1Q3 & "','" & S1Q4 & "','" & S1Q5 & "','" & S1Q6 & "','" _
        & S2Q1 & "','" & S2Q2 & "','" & S2Q3 & "','" & S2Q4 & "','" & S2Q5 & "','" _
        & S3Q1 & "','" & S3Q2 & "','" & S3Q3 & "','" & S3Q4 & "','" & S3Q5 & "','" & S3Q6 & "','" & S3Q7 & "','" & S3Q8 & "','" _
        & S4Q1 & "','" & S4Q2 & "','" & S4Q3 & "','" & S4Q4 & "','" & S4Q5 & "','" & S4Q6 & "','" _
        & S3Q1 & "','" & S3Q2 & "','" & S3Q3 & "','" & S3Q4 & "','" & S3Q5 & "','" & S3Q6 & "','" & S3Q7 & "','" & S3Q8 & "', '" & COMMENT & "', '" & OBDATE & "');"
        
        
        Set db = CurrentDb
        db.Execute strSQL, dbFailOnError
        
            
        '***Test to see if the row was written
        If db.RecordsAffected = 1 Then
            'Tell them it was successful
            response = MsgBox("Your observation was submitted successfully would you like to close the form?", vbYesNo + vbQuestion, "Success")
                    
        ElseIf db.RecordsAffected <> 1 Then
            MsgBox "Serious Error, the row was not written to the DB"
        End If
            
        
        '***Is the user done
        Select Case response
            Case Is = vbYes
                'The user is done so close the form
                DoCmd.Close acForm, "Observation Tracker"
            
            Case Is = vbNo
                'The user is not done so reset the fields
                Me.txtREP = ""
                Me.TXTBTN = ""
                Me.txtMTN = ""
                Me.txtComment = ""
                Me.og1 = 3
                Me.og2 = 3
                Me.og3 = 3
                Me.og4 = 3
                Me.og5 = 3
                Me.og6 = 3
                Me.og7 = 3
                Me.og8 = 3
                Me.og9 = 3
                Me.og10 = 3
                Me.og11 = 3
                Me.og12 = 3
                Me.og13 = 3
                Me.og14 = 3
                Me.og15 = 3
                Me.og16 = 3
                Me.og17 = 3
                Me.og18 = 3
                Me.og19 = 3
                Me.og20 = 3
                Me.og21 = 3
                Me.og22 = 3
                Me.og23 = 3
                Me.og24 = 3
                Me.og25 = 3
                Me.og26 = 3
                Me.og27 = 3
                Me.og28 = 3
                Me.og29 = 3
                Me.og30 = 3
                Me.og31 = 3
                Me.og32 = 3
                Me.og33 = 3
                
                
        End Select
        
        
    Exit_cmdSUBMIT_Click:
        Exit Sub
        
    Err_cmdSUBMIT_Click:
        MsgBox Err.Description
        Resume Exit_cmdSUBMIT_Click
        
    End Sub
    Thanks again for your response!!

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're most welcome

    Glad you got it sorted in time for your demo!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    I just posted code to retrieve and write data for situations just like this - If you have control over the design of the application and using that many fileds that are so much alike - try naming the fields in the table and the the controls on the form the same names and use this code - saves many keystrikes


    Public Sub FillUnboundForm()
    On error resume next
    Dim db As Database
    Dim rst As Recordset
    Dim rfld As DAO.Field
    On Error Resume Next
    Set db = CurrentDb
    Set rst = db.OpenRecordset("select * from mytable", dbOpenDynaset, dbSeeChanges)

    With rst
    If .EOF Then
    Else
    For Each rfld In rst.Fields
    Me(rfld.Name) = rfld
    Next
    End If
    End With

    End Sub

    Public Sub saveUnboundForm()
    On error resume next
    Dim db As Database
    Dim rst As Recordset
    Dim rfld As DAO.Field, myfield
    On Error Resume Next
    Set db = CurrentDb
    Set rst = db.OpenRecordset("select * from mytable",dbOpenDynaset, dbSeeChanges)

    With rst
    If .EOF Then
    Else
    For Each rfld In rst.Fields
    rst.Edit
    rfld = Me(rfld.Name)
    rst(Me(rfld.Name)) = Me(rfld.Name).Value
    rst.update
    Next
    End If
    End With

    End Sub
    Dale Houston, TX

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ^ Yes, that's basically what I was suggesting in post 2
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    I believe setting the Dirty Property to False in a bound form will force the record to be saved as well.

    C

  9. #9
    Join Date
    Oct 2010
    Posts
    3

    Does setting Dirty = True Accomplish this?

    Boy, I'd like to use something simple like this for my similar problem when a record isn't *really* added or updated yet in the underlying table. My form's datasource points to the table in question, so does this qualify as a bound form? In the form's AfterUpdate event I put the code, Me.Dirty = True. Nothing changed -- still no write to the table intil I move to a new record or close the form. Am I doing something wrong here? Any comments appreciated. Tris

  10. #10
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I might be a little late but the acCmdSaveRecord is only available if the Menu option Records/Save Record is not greyed out. Do you have full menus or are you limiting them? Another thought... when you open the form and change something is the Save Record menu available?

    If not there might be something with your form that is preventing it from working. I had a similar problem but I don't recall what the problem was.

  11. #11
    Join Date
    Oct 2010
    Posts
    3

    Forcing write to table while form open

    Thank you for reminding me I hadn't tried DoCmd****nCommand acCmdSaveRecord yet. I tried it and it solved my problem, leaving me with the current record displayed in the form, but all it's changes written to the table -- exactly what I needed. As for putting dirty = true in the form's after update procedure, it didn't work for me, so I've abandoned the idea. I'm learning so much from reading this forum! Thanks to everyone who posts.
    Tris

  12. #12
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    You need to set Dirty = False for it to force the save.

    C

  13. #13
    Join Date
    Oct 2010
    Posts
    3

    Dirty = False not in the right event?

    Just for fun, I removed the acCmdSaveRecord code from my Update button click event (which had solved my problem) and then at the form level put in:
    Private Sub Form_AfterUpdate()
    Me.Dirty = False
    End Sub
    I keyed some changes and clicked my Update button. Once again, keyed changes continued to display in the form but were not written to the table. Maybe, changing the data in one or more fields and then doing a DoCmd.Requery in an Update button click event isn't enough to trigger the after update event for the form?

    Anyhow the "DoCmd****nCommand acCmdSaveRecord" was the solution for me. Thanks for your comments. Tris

  14. #14
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Sounds like you need to set Dirty = False on the click event of the update button and not in the form update event.

    But if the other solution works then go with that.

    C

Posting Permissions

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