3422 error when modifying table from form

[smiler2505]

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

 
Do While CurrentProject.AllForms("frmOrd").IsLoaded = True
    DoCmd.Close acForm, "frmOrd"
Loop
 

,
and

  DoCmd.Close acTable, "tblOrd"

But the problem remains

I'm stumped. Any ideas?

[Rabbit]

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.

[smiler2505]

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

[smiler2505]

Sub cmbEnt_NotInList(NewData As String, Response As Integer)
 DoCmd.OpenForm "frmClo"
End Sub

 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 :(

[smiler2505]

 ...  
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())

[Rabbit]

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

[Rabbit]

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.

[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?

[Rabbit]

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.

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
 

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.

[smiler2505]

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

[Rabbit]

What's your code look like now?

[smiler2505]

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

[Rabbit]

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

No problem, good luck.

[smiler2505]

For reference sake:
frmCLOSE:

 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'

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