By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,899 Members | 1,066 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,899 IT Pros & Developers. It's quick & easy.

query results in a subform

P: 9
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..!
Feb 12 '07 #1
Share this Question
Share on Google+
17 Replies


NeoPa
Expert Mod 15k+
P: 31,471
I suspect that (Example Filtering on a Form.) could have been written especially for you ;)
Feb 12 '07 #2

P: 9
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,
Feb 14 '07 #3

Rabbit
Expert Mod 10K+
P: 12,364
Can you post the code so we can see where it went wrong?
Feb 14 '07 #4

P: 9
Can you post the code so we can see where it went wrong?
Hi Rabbit,

Here is the code;

Expand|Select|Wrap|Line Numbers
  1. Private Sub Test_Click()
  2. On Error GoTo Err_Test_Click
  3.  
  4.  
  5.     DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
  6.  
  7. Exit_Test_Click:
  8.     Exit Sub
  9.  
  10. Err_Test_Click:
  11.     MsgBox Err.Description
  12.     Resume Exit_Test_Click
  13.  
  14. End Sub
  15.  
I tried using the same code and just changing Click for enter but it didn't work either....Hope you can help me
Feb 14 '07 #5

Rabbit
Expert Mod 10K+
P: 12,364
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.
Feb 14 '07 #6

P: 9
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
Feb 14 '07 #7

Rabbit
Expert Mod 10K+
P: 12,364
You want to refresh? Replace 5 with acRefresh
Feb 14 '07 #8

P: 9
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
Feb 14 '07 #9

Rabbit
Expert Mod 10K+
P: 12,364
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.
Feb 14 '07 #10

P: 9
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
Feb 14 '07 #11

Rabbit
Expert Mod 10K+
P: 12,364
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.
Feb 14 '07 #12

P: 9
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?
Feb 14 '07 #13

P: 9
Thanks rabbit....How do reverse the startup options?
I got it..!...Thanks for your patience.
Feb 14 '07 #14

Rabbit
Expert Mod 10K+
P: 12,364
Not a problem.
Feb 14 '07 #15

P: 9
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,
Feb 22 '07 #16

NeoPa
Expert Mod 15k+
P: 31,471
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.
Feb 22 '07 #17

NeoPa
Expert Mod 15k+
P: 31,471
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.
Feb 22 '07 #18

Post your reply

Sign in to post your reply or Sign up for a free account.