Linking a table/form to a Query/Subform (1 Viewer)

[ggodwin]

Hey folks,

I have a form that has 11 fields that are linked to a table called "Qrevalue".

I also have a query that is called "Qrevalue_open". This query simply reads one field in the table "Qrevalue" and displays all records that have a NULL value. This query is displayed as a datasheet in a subform on my main form that has the 11 fields listed above.

What the query does is shows Engineers what items are open/new and need more infotmation added. I can get the query to display in the subform that is not a problem. But, What I would like to do is allow the engineer to select a record that is displayed in the Open query subform and when the selection is made that field is automatically selected in the main form and the engineer and enter the additional information.

This step will take a "find" step out of the work flow and make it much easier for the user.

Here is a print screan of the database. If you notice the record that is selected on the right has "R0030170" however, the main form has "754306" in the Tag Number field.

How can I link these two based on a user click of he record?

 

[ggodwin]

I Just wanted to bump this item and see if I could get a hint of how to do this? It may require VBA?? I am not sure.

if anyone has any ideas I would greatly appreciate it. I actually need to design three more forms to do a similar task but I want to get it working first.

thanks!!

 

[lala]

something like this

if that's a query that you have displayed with the empty records, then make it into a form, datasheet view (it will look exactly how it looks now, but it will let you add code to it)

in the OnClick event of the subform have something like forms!your_main_form!tag_number_field=me.tag_number_field_on_the_subform

i'm not near a computer, probably made a bunch of mistakes

but it will get you going

 

[ggodwin]

That is exactly what I have now. It is a form of a query placed on a "subform" and it is datasheet view.

If anyone has an example of the code behind that would be great!

 

[lala]

I gave you the code
On the onclick event of the subform put that line
Forms!your main form!tagnumber=me.tagnumber

Again, I'm on my cellphone, so the syntax is probably wrong, or maybe someone has a better idea

But this should at least get you going

 

[ggodwin]

If anyones knows the exact syntax for this I would appreciate it. I can't get this to work for anything.

 

[lala]

can you attach your db?
it's much easier for me to do it for you, than to try to figure out why yours doesn't work

 

[ggodwin]

Yes, thanks for the help.
I stripped this down pretty good. It is on form QRE VALUE

Thank you!

 

[lala]

ok, here you go

and also, i apologize, the solution i gave was wrong, the code i mean
that code doesn't look anything up, it sets a field to tagnumber


anyhow, just disregard the solution and here's the database

let me know if this is what you wanted

 

[ggodwin]

Lala,
thanks for the help. However, I am not sure I see what you did?
I see you made the QF1 and QFAll Queries. However, I don't see how they link on the form "QRE Value".

My original quest was to try and find a way to select any of the records that appear in the subform/query window (right side) on the QRE VALUE form. When a user selects a record I would like the On click event to "select" that same record in the QRE Value form/table so that the user can enter information. Otherwsise the user will need to manually "find" the record listed in the open query window (on the right) before he/she can enter the information. Which will remove it from the query of open items.

I may be completely missing what you did and I also may not be explaining the idea of what I am trying to do.

 

[lala]

I did
Click on that form on the right

If you click in any field, the same tag number comes up on the main form
Both tabs of the subform after clicking once anywhere take you to the record that you clicked on

 

[ggodwin]

YES!!!! That is exactly what I need!!

Do those queries you added do anything?
Where is the code behind the form?

??

 

[lala]

One more thing
If you want this to happen when the user selects the record by clicking the arrow on the left, then delete the code from the onclick events from all the 3 textboxes and put it in the onclick event of the subform

 

[lala]

Yes, they're the filters
if you look at the onclick event of any of the textboxes on the subform, you will see those queries there

The reason there's 2 of them is because I did the same thing for the second tab too

So if you click on a record on the second tab, it will also appear on the left

 

[ggodwin]

Is there any reason the form on the left will not navigate to the records that populate the table it is linked to?

It seem all I can see is what is connected to with the subform?

Actually, I can navigate to the records until after my first "on click".
and I then I cant navigate to the others.

1 other question. Do I just need to adopt the qf1 type queries and apply the on click code?

 

[lala]

Yes, the reason you can't go back is because now you have to make a button that will clear the filter
I'm on my phone again, the code is something like docmd.applyfilter showallrecords

Probably messed it up
Will try when I get to work

And your second question about adopting the queries I didn't understand

 

[ggodwin]

The question about adopting the queries is that the db I gave to you was stripped down almost raw. So for me to transfer this method to my main db, I am wondering if all I need to do is create the select QF queries like you used in the sample db and then apply to code behind the "on click"

On my first attemp to make this work on my db I am getting a request to enter a parameter value for QREValue.tagnumber. If I hit OK (with out entering anything) it then ask for another value for "Forms!qre value!QreValue_TMMK-VEH!tagnumber".

Is there something I may be leaving out?

 

[lala]

yes, i renamed the textbox on your main form to tagnumber
it used to be text95

so either change my code to text95 or rename the textbox to tagnumber

if you decided to rename the textbox, make sure you fix it in all your queries or code that uses this field

if you rename my code, then rename it in both queries and i think in the onclick event in both places


and yes, all you need is the 2 queries and the onclick event code to move to your db

 

[ggodwin]

Yes, the reason you can't go back is because now you have to make a button that will clear the filter
I'm on my phone again, the code is something like docmd.applyfilter showallrecords

OK, I tried this above filter and got a compile error. "Expected function or Variable". I am assuming that the .showallrecords may be the culprit.

I have another small issue. Just wondering. Is there a reason that the I must make two selections on the "on-click" event before it navigates the selection on the main form? This only occurs on the first selection, after than it only requires one selection to make the change.

 

[lala]

OK, I tried this above filter and got a compile error. "Expected function or Variable". I am assuming that the .showallrecords may be the culprit.

I have another small issue. Just wondering. Is there a reason that the I must make two selections on the "on-click" event before it navigates the selection on the main form? This only occurs on the first selection, after than it only requires one selection to make the change.

i noticed that, i thought maybe because you have some code some where
i usually don't have that problem

as far as the all records, the code is DoCmd.ShowAllRecords
try that