Basic Basic Query (At least I think so)

[beef4dinner]

Hi,

I am very new to the world of access and i'm having to learn the hard way, but pulling apart old databases and tinkering with things until I understand.

I have what I think is a very simple query. I have a text box that has a piece of data displayed that arose from a filter. I want to double click on this text box, opening up a new form and displaying data from a table derived from the entry in the first text box. How do I achieve this?

Let me know if you need a bit more detail as that must be very brief, but I have been trying to figure out things like SetValue, but I think I am barking down the tree with something like that.

Look forward to your input!

Tom

 

[ajetrumpet]

I have a text box that has a piece of data displayed that arose from a filter. I want to double click on this text box, opening up a new form and displaying data from a table derived from the entry in the first text box. How do I achieve this?

You achieve it by writing a DoCmd.OpenForm command on the DoubleClick event of the textbox. Then, if the appropriate syntax section of the command, specify the filter. If you look this up in the help menu, it will show you which portion of the syntax is just like the WHERE clause in a query. That's the section you need to use to specify the textbox value as the criteria. Make sure too, that the VIEW section of the command is set to datasheet.

Obviously, the form will have to based on whatever table you want to see too...

I have been trying to figure out things like SetValue, but I think I am barking down the tree with something like that.

Yes you have, but I believe it's called "barking UP a tree".

 

[beef4dinner]

Thanks Adam, I thought barking down the tree sounded off (and wrong!)!

I think venturing into VB territory which I am trying to avoid.

Say I have a form which is displaying bits and pieces from a filter. If I clicked on someones name, for example, it would open up a new form and display info on that person from another table. I had a brainwave and thought I would do a query that would display all the info in the second form, but how do I get the query to run off the name I double clicked on?

 

[neileg]

Why not use a sub form to show the data instead of a second popup. Perhaps not as neat, but you dont need any VBA, the subform wizard syncronises the forms.

 

[ajetrumpet]

I had a brainwave and thought I would do a query that would display all the info in the second form, but how do I get the query to run off the name I double clicked on?

You can't. The query doesn't have this capability. It can only pull a value from a control, not a property. BUT, if the control you are double clicking on already has a value in it, then just write...

DoCmd.OpenQuery "QueryName"

on the double click event. You have to use this event regardless, because double clicking on a control doesn't do anything by default. If the control is not going to have a value in it when you click it, you have to write some lines of VBA, sorry! If you have to do this, reading the last post should be a good guide.

BTW, Neil's scenario is ideal for doing something like this. The reason I posted additional information for you now is because I have seen more than one post where someone specifically wanted to open a form this way, and wanted nothing to do with the subform linking method.

 

[beef4dinner]

Thanks Adam and Neil. I will have a good butchers at this tonight, and see what I can come up with.

I have been having a lot of trouble trying to get a query to work off two criteria, the query works fine, but when I add it to a button it tells me to Enter Parameter Value, despite the fact the value is already in the query (God I sound like a novice...I am).

Imagine a query is...filter by Name [The name that is in Combo1] and then by JOBID "2" (All this data is in the same table). Everything works fine when you open the query, but trying to run it by clicking on a button causes that Enter Parameter Value box to come up, and whatever I type into it, it still just displays everything under Name, rather than Name and JOBID.

Hope that makes sense.

Tom

 

[beef4dinner]

By the way, it is asking me to Enter Paramater Value for JOBID...

 

[ajetrumpet]

the query works fine, but when I add it to a button it tells me to Enter Parameter Value, despite the fact the value is already in the query (God I sound like a novice...I am).

If anyone is a novice here, it's probably me!

trying to run it by clicking on a button causes that Enter Parameter Value box to come up, and whatever I type into it, it still just displays everything under Name, rather than Name and JOBID.

What's the code with the button say?

Getting a popup like this is often caused by a small syntax error or two.

 

[beef4dinner]

Sorted the problem, I added a text box with the control source being the JOBID, that seemed to solve the problem at long last! Didn't want the extra text box, but it is not a problem if it gets rid of that pop up.

Now just need to get my head round a few more bits and I will be there!