How do I close an open form?

[sueb]

I have a little form that I want to:

1. accept a new primary key (chart number) (this is the only field on the form),
2. add a new record with this primary key,
3. open the main form, and
4. close itself

So far, it does 1 through 3, but doesn't do 4, and also gives me an error, saying that there's an operator missing on line 12 of the following code:

Private Sub ChartNum_Exit(Cancel As Integer)
    Dim stDocName As String
    Dim stMainForm As String
    Dim stLinkCriteria As String
 
    stMainForm = "Patient_IUR_Overview"
    stDocName = "Patients: Add"
 
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    'DoCmd.Close stDocName
 
    stLinkCriteria = "[Patients.Patient Index]=" & Me![Patient Index]
    DoCmd.OpenForm stMainForm, , , stLinkCriteria
 
Exit_ChartNum_Exit:
    Exit Sub
 
Err_ChartNum_Exit:
    MsgBox Err.Description
    Resume Exit_ChartNum_Exit
 
End Sub
 

I'm pretty sure that it has something to do with the index, but I don't know how to fix that line (I cribbed it from another module that behaves similarly).

Thanks in advance, because I know someone will have posted the answer almost before I can come back to check for it!

[hype261]

It looks like you commented out closing the form on line 10. Was this on purpose?

[sueb]

I got a "type mismatch" on that line, and I wasn't even sure what this "Close" command was going to attempt to close.

[hype261]

Here is the MSDN link on the DoCmd.Close method. You got the type mismatch because you are sending a string where it expects an AcObjectType.

http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

[sueb]

Thanks for that link, but here is the line I'm using, and it gives me a run-time error of 2585, saying: "This action can't be carried out while processing a form or report event."

DoCmd.Close acForm, stDocName, acSaveYes

What's wrong with this line?

[Rabbit]

Your form is named "Patients: Add"? Bad choice of name. Does "[Patients: Add]" work?

[sueb]

So I changed the two lines of code to read:

    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Close , stDocName
 

and here's what happens:

1. I add the new chart number, and the correct window opens.
2. I close that window manually, and find that the original "add" window is still there.
3. When I click the window's "X" button, I get: "Syntax error (missing operator) in query expression '[Patients.Patient Index]='."

How can it be opening the right window if it's having a problem with that particular line? It's actually adding a record for the chart number I entered, and it opens the correct window to look at all the fields for that record, so what's going on? And why does this error only show up when I manually try to close the "add" window?

[eneyardi]

on deactivate event macro,put type close on action

[sueb]

Do what, where, now?

[Rabbit]

[Patients.Patient Index] is the name of the field? If it's not, then that syntax is wrong. Just use [Patient Index] or [Patients].[Patient Index].

[sueb]

Neither of those syntaxes work. Just to refresh things, what happens is:

- the add window pops up
- I enter a new number (The only action that causes the patient window to come up is TAB; RETURN just clears the field. However, if I manually open the patient form, and search for the new number, it's been added.
- the patient window opens
- the add window remains open (behind the patient window), and if I close it using the "X" button in the upper-right-hand corner, I get the error message "Run-time error 3075: Syntax error (missing operator) in query expression '[Patient Index]]='."

Here's the module as it stands:

Private Sub ChartNum_Exit(Cancel As Integer)
    Dim stDocName As String
    Dim stMainForm As String
    Dim stLinkCriteria As String
 
    stMainForm = "Patient_IUR_Overview"
    stDocName = "Patients: Add"
 
    DoCmd.RunCommand acCmdSaveRecord
 
    stLinkCriteria = "[Patient Index]=" & Me![Patient Index]
    'stLinkCriteria = "[Patients].[Patient Index]=" & Me![Patient Index]
    DoCmd.OpenForm stMainForm, , , stLinkCriteria
 
    DoCmd.Close , stDocName
 
Exit_ChartNum_Exit:
    Exit Sub
 
Err_ChartNum_Exit:
    MsgBox Err.Description
    Resume Exit_ChartNum_Exit
 
End Sub
 

[Rabbit]

Can you upload your database?

[john garvey]

From your code the docmd needs an oject to work with, if your code is attached to the form then try DoCmd.Close acForm, Me.Name

[sueb]

Rabbit, here's my database.

And, john garvey, I think I'm already trying that on line 15. Or is that line wrong? or something?

[john garvey]

You need to tell Access what to close and what its name is

[Rabbit]

Turns out you can't save or close a form while the form is processing a form event. You can do it on a button click, but not during a form event. The reason you're getting the patient id error is because there is no patient id on a new record.

[sueb]

Rabbit, so line 9 (in post # 11) doesn't write the record?

Edit: But wait, it does write the record, because the supplemental form does come up appropriately (with the just-added medical record number in it). Only the closing of the "add" window gets an error.

[Rabbit]

I get an error on the save when I run it from my computer.

But you can't close the form during the processing of a form event. That's what the error message says.

[sueb]

Hmmm... You must be getting a different error message from me. Mine says:

"Run-time error 3075: Syntax error (missing operator) in query expression '[Patient Index]]='."

And I get this even when I comment out the line that would try to close the "add" form.

[Rabbit]

That's not because of the close form line. It's because of the open form line. When you trigger that function while it's in a new record, Patient Index has yet to be filled in, therefore it is either null or blank and the Form is unable to use that where statement.

[sueb]

Yes, that makes sense, but it leaves me wondering how this function is managing to work correctly. As you'll notice in both my original question and in post # 11, after I enter a new ID in the "add" window, the correct patient record window opens (displaying the new ID), and the record has already been entered into the database (I can access that new record, containing only the new ID, from other forms, users, etc.).

It's only when I try to close the "add" window that I get the error, and I get the same error whether or not the close command is commented out.

It's crazy--I'm tempted to just leave it alone since, if you don't try to close the "add" window, but allow it to be re-used, you only get the error when you finally shut down the database at the end of the day.

[Rabbit]

You only get the error when you try to close the window (I assume by clicking on the x in the upper right of the form) because it then triggers the function and attempts to open the form using the null Patient ID. If you comment out the open form line, you will not get the error. But when you comment out the open form line, you will get the other error I was talking about.

[sueb]

But then how do I get the new record window to open? (Which it does, by the way, and is exactly what I want to have happen.)

[Rabbit]

Wait.. are you trying to exit the form without putting in a new chart number? That's when you get the error right? Only if you don't enter in a new chart number.

Try moving the code to the AfterInsert event of the form instead. That way, it should only trigger when a new record is added.

[sueb]

No. As I specified in my descriptions, I enter the new number, press <TAB> to exit the field, and the new window opens, in which I can finish entering the rest of the data. I only get the error when I try to close the "add" window AFTER successfully inserting my new record.

Edit: Well, actually, I ALWAYS get the error when I close the "add" window, regardless of what's gone before.

[Rabbit]

Oh.. you're tabbing? Since the chart number is the only control on there, when you tab, it will trigger the event. Which will open the form for the one you just added. Then it will move on to a new record. When you try to close that form, the event is triggered again and it fails because you are now on a new record that has no patient ID.

Also, your form isn't closing because you didn't specify what type of object you're trying to close. You need to specify that it's an acForm object you're closing. But when you do though, you will get the error that says you can't do that while processing a form event. Therefore, you should move the code into the AfterInsert event.

[sueb]

Okay, that worked to eliminate the error. Do you think there's any way that I can make the "add" window close automatically?

[Rabbit]

Yeah, you just need to specify the type of object you're closing.

DoCmd.Close acForm, "frmName"

[sueb]

Ah, that's it now! So the whole problem was that I had my code in the wrong event.

Thanks, Rabbit!

Would you consider summing up the advice you've given me here in a single post so that I can put the "best answer" on that one, making it easier for those who come to this thread later?

[Rabbit]

The reason the form doesn't close is because you didn't specify what type of object you're closing.

DoCmd.Close acForm, "FormName"

However, you can not close the form while it is processing a form event. You will need to move the code out of that event into a different one.

I suggest that you move the code to the after insert event of the form because that will also solve your other problem. That problem being that you only want the code to trigger when a Patient ID is available and it will only be available after the record is inserted into the table.