3422 error when modifying table from form

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • smiler2505
    New Member
    • Apr 2007
    • 72

    3422 error when modifying table from form

    Code:
    Sub cmbEnt_NotInList(NewData As String, Response As Integer)
    
     Answer = MsgBox("add?", vbYesNo, "add?")
     
     If Answer = vbYes Then
     
      DoCmd.Close acForm, "frmOrd"
      CurrentDb().TableDefs("tblOrd").Fields("Ent").RowSource = CurrentDb().TableDefs("tblOrd").Fields("Ent").RowSource & ";" & NewData
     
     Else
      
      Response = acDataErrContinue
       
     End If
    
    End Sub
    There's more code but Im only giving the relevant bits. I know I can modify rowsource in the form, but now I'm stuck wondering why when I try and modify the table I get a 3422 error (Cannot modify table structure. Another user has the table open.) after Ive closed the form. There are no other forms/queries/reports/etc open. The only thing I can think of is that the form hasn't disconnected properly from the table; I've tried
    Code:
     
    Do While CurrentProject.AllForms("frmOrd").IsLoaded = True
    	DoCmd.Close acForm, "frmOrd"
    Loop
    ,
    and
    Code:
      DoCmd.Close acTable, "tblOrd"
    But the problem remains

    I'm stumped. Any ideas?
  • Rabbit
    Recognized Expert MVP
    • Jan 2007
    • 12517

    #2
    I'm guessing that this event is being triggered while you're in the form. And while the event is still running, then the form is still open. Have an intermediary form whose On Open event is to close the form and then make the changes and then close itself. See if that works.

    Comment

    • smiler2505
      New Member
      • Apr 2007
      • 72

      #3
      It's obvious now you say it! Ill try that now, thanks

      Comment

      • smiler2505
        New Member
        • Apr 2007
        • 72

        #4
        Code:
        Sub cmbEnt_NotInList(NewData As String, Response As Integer)
         DoCmd.OpenForm "frmClo"
        End Sub
        Code:
         Sub Form_Open(Cancel As Integer) 
        
         Answer = MsgBox("That data inputter is not currently listed.  Would you like to add them now?", vbYesNo, "New data inputter?")
         
         If Answer = vbYes Then
         
          Dim daodb As DAO.Database
          Dim daotdfOrd As DAO.TableDef
          Dim daofldOrd As DAO.Field
          
          Response = acDataErrAdded
          DoCmd.Close acForm, "frmOrd"
          
          Do While CurrentProject.AllForms("frmOrd").IsLoaded = True
        	DoCmd.Close acForm, "frmOrd"
          Loop
          
          CurrentDb().TableDefs("tblOrd").Fields("Ent").ValidationRule = CurrentDb().TableDefs("tblOrd").Fields("Ent").ValidationRule & """ & NewData & """
          daofldEnt.RowSource = daofldEnt.RowSource & ";" & NewData
         
         Else
          
          Response = acDataErrContinue
           
         End If
        End Sub
        results in same error :(

        Comment

        • smiler2505
          New Member
          • Apr 2007
          • 72

          #5
          Code:
           ...  
          Loop
          MsgBox (SysCmd(acSysCmdGetObjectState, acTable, "tblOrd"))
            CurrentDb().TableDefs("tblOrd").Fields("Ent").ValidationRule = CurrentDb().TableDefs("tblOrd").Fields("Ent").ValidationRule & """ & NewData & """
          ...
          Gives 0, ie, the table is closed. So I assume it's something to do with the code (it gives an error at the line starting CurrentDb())

          Comment

          • Rabbit
            Recognized Expert MVP
            • Jan 2007
            • 12517

            #6
            Let me do some testing and I'll get back to you.

            Comment

            • Rabbit
              Recognized Expert MVP
              • Jan 2007
              • 12517

              #7
              Ok. You're going to have to break the chain of events.
              Put the code in the timer event. Set the timer interval to 1000. Close the form in an event other than the timer event.

              A note of caution though, I ran into trouble with quotation marks. I could run the code once but on the second time around there was an error. I'm still trying to work that part out.

              Comment

              • smiler2505
                New Member
                • Apr 2007
                • 72

                #8
                When you say put the close event in another event, do you mean call it from timer?

                Also, the rowsource has the newdata (from NotInList event) added to it. How can I pass this onto the timer event?

                Comment

                • Rabbit
                  Recognized Expert MVP
                  • Jan 2007
                  • 12517

                  #9
                  Originally posted by smiler2505
                  When you say put the close event in another event, do you mean call it from timer?

                  Also, the rowsource has the newdata (from NotInList event) added to it. How can I pass this onto the timer event?
                  What I mean was to just keep the Close event in the Open Form event or the original event you had it in. And then in the Timer event you modify the structure.

                  You can pass the value of the new data through the OpenArgs parameter of the OpenForm.

                  [Code=vb]
                  Form1
                  Private Sub but1_Click()
                  DoCmd.OpenForm "Form2", , , , , , Me.NewData
                  DoCmd.Close acForm, "Form1"
                  End Sub

                  Form2
                  Private Sub Form_Timer()
                  MsgBox Me.OpenArgs
                  End Sub
                  [/Code]

                  I'm still having problems with the quotes. So even after you get through to modifying the table structure, you might run into trouble with the quotes.

                  Comment

                  • smiler2505
                    New Member
                    • Apr 2007
                    • 72

                    #10
                    How do I delete messages lol?! This comment is no longer applicable

                    Comment

                    • Rabbit
                      Recognized Expert MVP
                      • Jan 2007
                      • 12517

                      #11
                      What's your code look like now?

                      Comment

                      • smiler2505
                        New Member
                        • Apr 2007
                        • 72

                        #12
                        Its fixed! I just have to find the right name for the row source property, but thanks for your help!

                        Comment

                        • Rabbit
                          Recognized Expert MVP
                          • Jan 2007
                          • 12517

                          #13
                          Originally posted by smiler2505
                          Its fixed! I just have to find the right name for the row source property, but thanks for your help!
                          No problem, good luck.

                          Comment

                          • smiler2505
                            New Member
                            • Apr 2007
                            • 72

                            #14
                            For reference sake:
                            frmCLOSE:
                            Code:
                             Sub Form_Open(Cancel As Integer)
                            DoCmd.Close acForm, "frmOrd", acSaveYes
                            End Sub
                            Sub Form_Timer()
                            On Error GoTo ErrorsoExit
                             
                            NewData = Left(OpenArgs, InStr(OpenArgs, ";") - 2)
                            Store = Mid(OpenArgs, InStr(OpenArgs, ";") + 2)
                            CurrentDb().TableDefs("tblOrd").Fields("Ent").ValidationRule = CurrentDb().TableDefs("tblOrd").Fields("Ent").ValidationRule & " OR """ & NewData & """"
                            CurrentDb().TableDefs("tblOrd").Fields("Ent").Properties("RowSource") = CurrentDb().TableDefs("tblOrd").Fields("Ent").Properties("RowSource") & ";" & NewData
                            If Store = "" Then DoCmd.OpenForm "frmOrd" Else DoCmd.OpenForm "frmOrd", , , , , , Me.Bookmark = Store
                            DoCmd.Close acForm, "frmCLOSE", acSaveNo
                            ErrorsoExit:
                            End Sub
                            'frm1'
                            Code:
                            Sub cmbEnt_NotInList(NewData As String, Response As Integer)
                             
                            Response = acDataErrContinue
                            'Question repeats when form is closed unless this line is added
                            Me.cmbEnt.Undo
                             
                            Answer = MsgBox("Question?", vbYesNo, "Title?")
                             
                            If Answer = vbYes Then
                             
                            Dim Store As String
                             
                            If Me.NewRecord = False Then Store = Me.Bookmark
                             
                            DoCmd.OpenForm "frmCLOSE", , , , , acHidden, NewData & " ; " & Store
                             
                            End If
                             
                            End Sub

                            Thanks again to Rabbit

                            Comment

                            Working...