query results in a subform

[rookie user]

Hello everyone,

I am very new to Access so I definetely need help. I have a form with two combo boxes (outbound): 1.- City Names and 2.- Postal Codes

These combo boxes are based on a Table which I called SD and contains five fields: City Names, Postal Codes, Province, Terminal and Carrier.

On the form I have created a command button that search for values entered in the combo boxes based on a query named Routing. Basically looks at the city names and postal codes in the combo boxes and returns the city, postal code, province, terminal and carrier. I got this working fine.

Now, the issue I am having is that I want the results from the query to be displayed as a subform and I can get this to work. I have created a sub-form based on the query.routing and all it does is to show all the results.

Any suggestions are welcome. Thanks..!

[NeoPa]

I suspect that (Example Filtering on a Form.) could have been written especially for you ;)

[rookie user]

I suspect that (Example Filtering on a Form.) could have been written especially for you ;)

Hi NeoPa,

I just got this workng fine by changing the command button from running a query to refreshing form data. However now I am getting the message: The expression OnEnter you entered as the event property setting produced the following error: The object doesn't contain the automation object 'searchForm' when I hit enter....It works fine if I click with the mouse on the button.

Any idea why this is happenning and how I can solve this...

Thanks,

[Rabbit]

Can you post the code so we can see where it went wrong?

[rookie user]

Can you post the code so we can see where it went wrong?

Hi Rabbit,

Here is the code;

Private Sub Test_Click()
On Error GoTo Err_Test_Click
 
 
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
 
Exit_Test_Click:
    Exit Sub
 
Err_Test_Click:
    MsgBox Err.Description
    Resume Exit_Test_Click
 
End Sub
 

I tried using the same code and just changing Click for enter but it didn't work either....Hope you can help me

[Rabbit]

The only possible place where the code can go wrong is in your DoMenuItem. Are you sure 5 is the correct option?

I don't know what item 5 is so you'll have to tell me.

[rookie user]

The only possible place where the code can go wrong is in your DoMenuItem. Are you sure 5 is the correct option?

I don't know what item 5 is so you'll have to tell me.

Hi Rabbit,

How do I know which Item 5 is? Access is using this code when I created the command button for refreshing the data in the subform

[Rabbit]

You want to refresh? Replace 5 with acRefresh

[rookie user]

You want to refresh? Replace 5 with acRefresh

Hi Rabbit,

I could get rid of the error by going to the subform properties and deleting the OnEnter which was SearchForm.Requery. Now seems to work fine, however when I enterered a value in the postal code (unbound combo box) the cursor goes to the subform intead of the command button...do you know how I can fix this?...Thanks

[Rabbit]

If you right-click anywhere in the form and select Tab Order, you can change the order in which controls get focus after the user Tabs or presses Enter.

[rookie user]

If you right-click anywhere in the form and select Tab Order, you can change the order in which controls get focus after the user Tabs or presses Enter.

Thanks a lot Rabbit...!! It's working fine now.

I have the form as a pop up, Do you know how I can protect it so the user just see the form and not the queries or tables

[Rabbit]

From the menu Tools > Startup, you can select the startup options when someone opens the database. You can select what form opens and what menus are available, among other things.

To bypass the startup, when you open the database, hold Shift until it opens. This will allow you full access. If you need it more secure and you and the user are using MS Access 2003, create a backup of the .mdb file and convert one to a .mde file. But make sure you make a backup because you can't make any changes to a .mde file.

[rookie user]

From the menu Tools > Startup, you can select the startup options when someone opens the database. You can select what form opens and what menus are available, among other things.

To bypass the startup, when you open the database, hold Shift until it opens. This will allow you full access. If you need it more secure and you and the user are using MS Access 2003, create a backup of the .mdb file and convert one to a .mde file. But make sure you make a backup because you can't make any changes to a .mde file.

Thanks rabbit....How do reverse the startup options?

[rookie user]

Thanks rabbit....How do reverse the startup options?

I got it..!...Thanks for your patience.

[Rabbit]

Not a problem.

[rookie user]

Not a problem.

Hi Rabbit,

I have a couple of questions. If I want to set up a password, access asks me to have the database open for exclusive use, How do I do this? also How do I convert the file to a .mde file? I went to the file and change it (rename it), however I still can go on the table and make changes....

Thanks,

[NeoPa]

I think that it will allow you to make design changes like this when opened normally, as long as no-one else has the database open at the same time. It will convert the open-mode to exclusive on-the-fly.
However, should you want to open it explicitly as exclusive (and there must be no-one else with the database open for this to work), then :

• Click on the Open button.
• Select the database you want to open.
• Click to drop-down the list on the Open button.
• Select Open Exclusive.

.

NB. This is in Access 2003. I imagine it is similar in other versions. If not, then look for some form of Options button within the Open dialogue box for similar functionality.

[NeoPa]

How do I convert the file to a .mde file?

Some points to remember about MDE files.

• The design of the database is not modifiable once it's been converted to an MDE file. It is absolutely critical that you keep a master copy of the database (MDB) file somewhere safe before converting it to an MDE.
• The design of the database is not modifiable once it's been converted to an MDE file. It is absolutely critical that you keep a master copy of the database (MDB) file somewhere safe before converting it to an MDE.

Well, it was a very important point.
To convert a Database (MDB) file to an Executable (MDE) file (Access 2003 but other versions will be similar) :

1. Select Tools / Database Utilities / Make MDE File...
2. Select the database to convert.
3. Click on Make MDE.
4. Enter (or select) a filename for your new MDE file.