Replies

The code I gave you in my first reply works fine as long as you don’t have multiple instances of a form.
From your originally description I understood that you would like to close all other forms but the one currently selected.
This code makes sure NOT to close the form using/calling the code. Unlike other methods there is no reason to refresh form count variables and do multiple loops since my loop starts examining the highest numbered form.
And since we are using the Forms collection (open forms only), there is no reason to check if a form is open.
I have changed the code a little to make it easy for you to insert it in a module (not class module).
It’s a function, so you don’t even have to call it from VBA code in a class module unless you have other things to do in the event chosen to call the function.
In the events section of a form’s property definitions choose the Load or Open event and type directly: =CloseAllOtherOpenForms([Form])
Be sure to type it eactly like that, i.e. don’t change [Form] to anything else.
If you do have other VBA code for the chosen event include this line: Call CloseAllOtherOpenForms(Me)

Public Function CloseAllOtherOpenForms(frm As Form) As Boolean
Dim intI As Integer
‘ Close all open forms except the calling form
For intI = Forms.Count – 1 To 0 Step -1
If Forms(intI).Name frm.Name Then ‘ Do not close the calling form
DoCmd.Close acForm, Forms(intI).Name
End If
Next intI
End Function

You may use the following code to close all open forms except the one having the code.

Dim intI As Integer

‘ Close all open forms except Me
For intI = Forms.Count – 1 To 0 Step -1
If Forms(intI).Name Me.Name Then ‘ Do not close Me
DoCmd.Close acForm, Forms(intI).Name
End If
Next

Note to my previous reply:
What I wrote about using a certain US format (like jan-17-2002) was not about defining a format. It was about typing input in that format.

I thought you were referring to display formats only (see my previous reply).
As for input you you can perfectly well use the US format: jan 17 2002 or jan-17-2002.
Be carefull not to use the US input format: 1/17/2002 (2002-01-17). It will be allright for that date but for 1/10/2002 it won’t work. The latter will be taken as a european date (2002-10-01) when your International settings in the Windows Controlpage are UK settings.
If you choose to input your dates as jan 17 2002, or jan-17-2002 or 2002-01-17 you’ll be allright.

In tables, forms and reports use the format property to specify your date format (e.g. mmm dd yyyy).
In code use Format(yourdateandtime,”mmm dd yyyy hh:nn:ss”).

For information about international settings use Access help. Type this in the Office assistant: international dateformats

Except for a missing “)” in your first Kill stmt your code seems OK. Check the spelling of the path names in your consts defining the dbs.

Sorry to disagree Tom,

There is no reason to look up the ClassID. It should be the bound column of the Class combobox.. Take a look at my sample db, and I’am sure you’ll agree.

Are you missing a space before and after the & ????

Try this: SET [IQC Proficiency].Sample = “Sample ” & mid((IQC Proficiency].Sample,8))

The name of your report is hopefully in stDocname. Look at the code preceeding the DoCmd.

Replace ECR Report by the name of your form inclosed in [] if it contains spaces.
If your form is name ECR Form the stmt should look like this:

DoCmd.OpenReport stDocName, acPreview, , “[ECR_Number]=forms![ECR Form]!ECR_Number “

Hi Randall,

It seems you are trying to assign a string (the SELECT stmt) to a Long (ClassID).

I had some spare time. So, to help you, I’ve prepared and attached a small sample database for you. It shows you how you can control a class/student registration job using a combobox for class selection and two listboxes displaying students. One listbox has students selected for the class, the other one has students you may select for inclusion in the first list.
Select a class and the listbox at your right hand can be used for adding students to a class and the listbox at your left hand will display students already selected. I’ve included a button to remove students selected, in case you need that.
The sample database has 3 tables (tblClass, tblStudent, and tblClassRegistration) with selfexplaning names. Take a look at the relations.
There are 2 queries both of which depend on the form frmClassRegistration being open. One query has students selected for the class selected on the form, and the other one has students not selected for the class on the form.
I am using multiselect lists.
To select one student: doubleclick the student.
To do multisection of students: 1. press left mouse button while selecting multiple consecutive rows or 2. click ctrl and left mouse button on several individual rows. And then click the register students button.

I hope you will find my sample application (Office 97) helpfull.

In your query type in the criteria for Dept on consecutive rows like this:
“Admin”
“Accounts”
In(“Marketing”,”Sales”)
In(“HR”,”Maintenance”)
Like “*”

In a new column of your query type:
Combo:Forms![frmMenuForm]![Dept]

In Combo’s criteria type (consecutive rows):
“Admin”
“Accounts”
“Marketing”
“HR”
“”

That’s all. Hope you find this helpfull.

Harry,
Yes, this is pretty annoying, isn’t it?
You will have to open the subform in design view and change the names of the controls.
Right click on a control and choose properties. Change the value of the property called Name to what you have typed in the Caption of the corresponding field in your table when you defined it.

Sorry Mark,

I forgot to tell you that the name frmKunde should be replaced by the name of your form.
If you always want your query to return one record only, then use Chris’ suggestion.
If you want the ability to print all records using the same query as well, then use my suggestion.

Multiple users can use a non-split db. You split the db for maintenance and/or performance reasons.
1. You have to establish an easy way (for the end user) to re-establish links from the tables in the back-end db (on the server) to the front-db (normally on the user’s PC) . This is needed when a new version of the front-end is installed and when/if the back-end db is moved. Code in the front-end db should check the links and initiate proper relinking action if neccesary (Check each time the user logs on the front-end db).
2. In addition I like to check that the version of the back-end is OK for the front-end. You might have to change the table structure in the back-end for some reason (after you have realeased it for use). Results may be strange if a user tries to use a new front-end with an old back-end (or vice versa).
3. Linked tables may not be opened as tables in VB ( i.e. you cannot use Seek etc.). Look for help on OpenRecordset.
4. There are special concerns when using db password. Look for help on linked databases and db passwd.

Have fun.

Open your form in design mode and use the toolbox to put a button on the form. When the guide asks you for the action to perform choose report.
When the button has been created show the program code and correct the DoCmd.OpenReport stmt to something like this:

DoCmd.OpenReport stDocName, acPreview, , “[YourID]=forms!frmKunde!YourID ”

where YourID is the name of the primary key of the record displayed on the form.

If you have done updates on the form, make sure the record is saved first.