Need to pass a variable to a query based on a selection in a form/table.

You could change it to refer directly to your form:

SELECT  SkpiUpdate.NAMC,  QREVALUE.TagNumber,  QREVALUE.QPRQPINumber,  SkpiUpdate.[Supplier Code],  SkpiUpdate.Date, FROM QREVALUE INNER JOIN SkpiUpdate ON QREVALUE.ScrapRecordTag = SkpiUpdate.ScrapRecordTag WHERE (((QREVALUE.TagNumber)=" & Forms!YourFormName.YourControlName & ") AND ((SkpiUpdate.[Fault Type])="PPM"));

Select allOpen in new window

If TagNumber is a Text field:

SELECT  SkpiUpdate.NAMC,  QREVALUE.TagNumber,  QREVALUE.QPRQPINumber,  SkpiUpdate.[Supplier Code],  SkpiUpdate.Date, FROM QREVALUE INNER JOIN SkpiUpdate ON QREVALUE.ScrapRecordTag = SkpiUpdate.ScrapRecordTag WHERE (((QREVALUE.TagNumber)='" & Forms!YourFormName.YourControlName & "') AND ((SkpiUpdate.[Fault Type])="PPM"));

Select allOpen in new window

How can I test that value?
The error is now gone. However, the query is not pulling the records associated with that TagNumber. Its like it is blank.

Possibly I used the wrong control??

I do have sub-forms. How do I know which form to use?

I'm using

form QREValue

Control is TagNumber which is the name of the field for both the form and table associated with the selection.

Is the value that you're trying to retrieve on the Main form or one of the Subforms?

One good debugging technique is to writeout your query to the Immediate window. You can do that like this:

Dim sql As String

Select allOpen in new window

'/ assign the SELECT query to the string

Select allOpen in new window

sql = "SELECT  SkpiUpdate.NAMC,  QREVALUE.TagNumber,  QREVALUE.QPRQPINumber,  SkpiUpdate.[Supplier Code],  SkpiUpdate.Date, FROM QREVALUE INNER JOIN SkpiUpdate ON QREVALUE.ScrapRecordTag = SkpiUpdate.ScrapRecordTag WHERE (((QREVALUE.TagNumber)=" & Forms!YourFormName.YourControlName & ") AND ((SkpiUpdate.[Fault Type])='PPM'))"

Select allOpen in new window

'/ now print it to the Immediate window

Select allOpen in new window

Debug.Print sql

Select allOpen in new window

This would print your full statement to the Immediate Window.

Since you are dealing with a string value, you'll need to enclose in single quote  characters:

((QREVALUE.TagNumber)='" & Forms!YourFormName.YourControlName & "')

Select allOpen in new window

There are several methods of passing parameters to Access DAO querydef SQL statements:
1.  Reference the control properly in the SQL statement.
2.  Create a basic module function that returns the value and populate the return value any number of ways:

Public Function TagNumber() as string
TagNumber = NZ(Forms!YourFormName.YourControlName,"")
End Function

Select allOpen in new window

'WHERE string will be:

((QREVALUE.TagNumber)=TagNumber())

Select allOpen in new window

Or create the SQL Statement in code (strSQL), complete with values, and put the SQL Statement into the querydef object:
CurrentDB.Querydefs("qryNameHere").SQL=strSQL
Plus several others.

Not sure why Mark is essentially repeating most of my suggestions, but I'll just leave you with him ...

Sorry Scott.  I didn't see most of what I covered in your posts.  Perhaps I read it differently or found it hard to follow.
Sometimes a reader won't get it when expressed one way, but does get it another way.

I've gotten a lot of posts selected as the answer not because I was first with the idea, but because I rewrote a suggestion in a way the reader could understand.  They will usually go with the first solution that makes sense to them and they can use.

An Advanced technique is to write a parameterized query, and fill up the parameters:

Your query:

PARAMETERS SelectedTagVariable Text(255);
SELECT  SkpiUpdate.NAMC,  QREVALUE.TagNumber,  QREVALUE.QPRQPINumber,  SkpiUpdate.[Supplier Code],  SkpiUpdate.Date,
FROM    QREVALUE INNER JOIN SkpiUpdate
            ON QREVALUE.ScrapRecordTag = SkpiUpdate.ScrapRecordTag
WHERE   QREVALUE.TagNumber) = [SelectedTagVariable]
  AND   SkpiUpdate.[Fault Type] = "PPM";

Select allOpen in new window

VBA code:

Dim db As DAO.Database
Set db = CurrentDb


Dim qd As DAO.QueryDef
Set qd = db.QueryDefs("qSaveQREValueReport")
qd.Parameters("SelectedTagVariable") = "whatever value you want"
qd.Execute(dbfailonerror)

Select allOpen in new window

Bonus: It handle Null, quotes (and other irritating weird string symbols) nicely.

Mark,
I have a the below private sub that is not working now. (this is basically for the filter that I used in the sub-form to the bottom right of the picture.
I believe the Public Function for TagNumber has created confusion.

Private Sub Form_Click()
'DoCmd.ApplyFilter "QFQreValueOpen"
Me.Parent.Filter = "[TagNumber]='" & Me.TagNumber & "'"
Me.Parent.FilterOn = True
End Sub

Select allOpen in new window

The filter is another avenue of how a user can select a record that will appear in the same form. However, there are many times that the user may not chose the record via that subform filter.

Guess the SQL engine is confused with the function having the same name as a column.
give the function a different name.

I changed the function name to read.

Public Function SelectedTagNumber() As String
SelectedTagNumber = Nz(Forms!fQREVALUE.TagNumber, "")
End Function

Select allOpen in new window

Now I have the

Private Sub Share_Click()
DoCmd.OpenQuery "qSaveQREValueReport"
End Sub

Select allOpen in new window

qSaveQREValueReport is below

SELECT
 SkpiUpdate.NAMC,
 QREVALUE.TagNumber,
 QREVALUE.QPRQPINumber,
 SkpiUpdate.Date,
 SkpiUpdate.[Part Number],
FROM QREVALUE INNER JOIN SkpiUpdate ON QREVALUE.ScrapRecordTag = SkpiUpdate.ScrapRecordTag
WHERE (((fQREVALUE.TagNumber)=TagNumber()) AND ((SkpiUpdate.[Fault Type])="PPM"));

Select allOpen in new window

Is your function in a standard module ?

If not, move it there.

The SQL engine can call custom function, but these cannot be in a form or class module, as these need to be instanciated first.

I have tried several of the methods mention on this thread. Personally, I think my original method was closet to working.
It prompts me to enter the "SelectedTagVariable". Once, I enter the value then the query works as expected.

Currently it is in the form behind the "On-Click" of the Share control.

Private Sub Share_Click()
DoCmd.OpenQuery "qSaveQREValueReport"
End Sub

Select allOpen in new window

qSaveQREValueReport

SELECT
 SkpiUpdate.NAMC,
 QREVALUE.TagNumber,
 QREVALUE.QPRQPINumber,
 QREVALUE.ProblemDescription,
 QREVALUE.DateCode,
 QREVALUE.InterimAction
FROM QREVALUE INNER JOIN SkpiUpdate ON QREVALUE.ScrapRecordTag = SkpiUpdate.ScrapRecordTag
WHERE (((QREVALUE.TagNumber)=SelectedTagVariable) AND ((SkpiUpdate.[Fault Type])="PPM"));

Select allOpen in new window

I think all I need to do is define the value for "SelectedTagVariable". But I am not sure how to do that.
But if there is a better way then I'd be open that also.

It's all about getting things coordinated and in the right place so they can be seen by the right components.
I see you are trying to apply a filter to a parent form based on a selection in a subform.
However, you state that "However, there are many times that the user may not chose the record via that subform filter."
So you have multiple filtering sources it looks like, so you need a way that works with multiple filtering selection places.  If that's not done right, it can cause confusion when you try to filter from other locations.

Is your public function SelectedTagNumber in a basic module (NOT a form module).  It only works from there.
I'll create a sample database that uses controls to select filter values from multiple places using the global function and attach it in a later posts..

Mark,
Currently I don’t have anything.

I’ve tried it in the form to no avail. 90% of the time the selection will be made via the sub form filter. But there could be cases where it wouldn’t be.

OK.  Here's an example db of how to filter a main form with a subform selection or a main form selector.
I's built on the table MSysObjects and you can filter the main form record by selecting an object Name in the main form combobox, the subform record, or the subform record navigation control.

The secret is in setting the global variable value that feeds the public function in the basic module "Module1".  Once you have that set, you can use it anywhere and set it from anywhere.

Keep in mind that when you set a filter that references a value that can change and the query with that filter requeries, it picks up the new value.
Queries built with static embedded values don't have that problem.  They hold there filters even if the source where they were gotten from changes.

Keep in mind that global variables in memory are not bulletproof if the code state is killed.  (You can use TempVars to help with that, but that's another topic.)

If it doesn't help, let me know.  I may be missing something.
Multiple-Form-Filters.accdb

Before I get into changing my filter.

This is what I am using in my subform to filter. It works perfectly. For what I am doing.

Can I use this TagNumber value also in my new query?

Private Sub Form_Click()
'DoCmd.ApplyFilter "QFQreValueOpen"
Me.Parent.Filter = "[TagNumber]='" & Me.TagNumber & "'"
Me.Parent.FilterOn = True

End Sub

Private Sub Form_Load()
  Dim sSQL As String
  sSQL = "UPDATE [QreValue] set [Select] = false"
  CurrentDb.Execute sSQL
End Sub

Select allOpen in new window

I have no idea because I don't know what you mean by "Can I use this TagNumber value also in my new query?"  
I assume so, but how you use it is up to you.  Hopefully you do it in a way that works so your issue is solved.

Right now I don't know if you still have an issue that doesn't work.  Do you still have a problem somewhere?
What do you plan to do?

I'm a little confused by the new query SQL in the Form_Load event above.  We haven't seen it before and now all of a sudden it's in the mix.  What is it for and what does it do?

I've seen you use two methods of filtering:
1.  Using the form's Filter property.
2.  Using a form recordsource query with a filter in a WHERE clause.

If you are using them both and setting them from different places, then you could really get screwed up.
The form Filter property method is used on forms with multiple record recordsources to reduce the records in the form to one or more records, but not all of them.
The recordsource query filter can do the same.

What's the logic behind what you are trying to do.  Give us the big picture.  You seem to be trying to do many things at once.

I know that I am probably confusing this issue much more than what it needs to be. I appreciate your help. My background and my practice is not any sort of Computer Science or Database design. I am just an automotive engineer looking for an easier way to do my job. Over the last ten years I have created this Database for a team of engineers to use and make their job easier. I could NOT do it with out this group.

Attached is a Power Point that will attempt to explain what I am trying to do.

I will also attach a stripped version of the Database so that you can try it your self.

**Please note**
This is not a final step.

I ultimately want to be able to email a .pdf report to my customer. This procedure will query my data based on a Specific Record selection. My problem now is the selection is not being passed to the query.
Database.pptx

Here is the Database. Please ignore many of the objects. I have deleted tons of things to reduce this to share.

Please see the attached Database
qualitymanagement2020Temp.mdb

OK  Got it.

In your Module1 basic module, put the following code to set up your global SelectedTagVariable() function:

Public g_SelectedTagVariable As String

Public Function SelectedTagVariable() As String
    SelectedTagVariable = g_SelectedTagVariable
End Function

Select allOpen in new window

Then, in your "qSaveQREValueReport" query, change the WHERE clause to use the function:

WHERE (((QREVALUE.TagNumber)=SelectedTagVariable()) AND ((SkpiUpdate.[Fault Type])="PPM"));

Select allOpen in new window

Finally, in your click event on the share button, modify it so it looks like this:

Private Sub Share_Click()
    Module1.g_SelectedTagVariable = Nz(Me.qQreValueTMMKVEHSubform.Form!TagNumber, "")
    DoCmd.OpenQuery "qSaveQREValueReport"
    'DoCmd.OpenReport "qSaveQREValueReport"
End Sub

Select allOpen in new window

Put the code line:

Module1.g_SelectedTagVariable = Nz(Me.qQreValueTMMKVEHSubform.Form!TagNumber, "")

Select allOpen in new window

anywhere you want to set the variable (use the appropriate control name, etc.)
Now when you select a record in the subform and click on the "Share" button, the query pops up with the right TagNumber!
Tip:  If the TagNumber column in the subform is blank, you will filter for TagNumber="", which will result in no record, if that's what you want in that case.

Got it!

Now I've got a compile error and I believe the string declaration in Module 1 has a problem. See the attached.

I figured it out.

I moved "Public g_SelectedTagVariable As String" up to where I had other String variables declared.

Thank you so much. It is working nicely.

Now I'm not getting an error.

However, now I'm not able to change the record stored in the variable.

Even if I select a new records in the form and a new TagNumber is visible. I can only query the TagNumber that is stored in Memory. Also, It looks like it is assigning the TagNumber from the wrong form.

You know what it is you want to do.  Put the code to assign the value (wherever you get it from) to the global variable.  and requery the recordsource to refresh the selected record.  It's that simple.  Look at the db example.

Mark,
I can't get past this compile error. I thought I could change the property name to the control I want as my source. Is this the correct control name? I don't know of anything else it could be.
CompileError.png
qualitymanagement2020Temp.mdb

Hey g:  
The compile error is telling you that it is looking for the control fQREVALUEform, which I believe is the name of your subform control on the form that the code was originally on.  
If you just copy and paste the line somewhere without making the adjustments for control names, etc., then you are going to get a compile error like this one.
Where is the control that has the TagNumber value that you want to assign to the global variable?  Once you change the reference to a control that it can see, then it should work.

The general idea to keep in mind is to put the code that assigns the TagNumber value (to the global variable) anywhere you want to set it.  You can put it on the click event of a textbox that has the tag number.  You can put it on a button that launches your query.  Your choice.  Just make sure that the code can see whatever it is that's holding the value that you want to give the global variable.
Once you've set the global variable value, any code, query, or form using the SelectedTagVariable() function will get the value of the global variable.

What do you want me to do with the .mdb you posted.  Is this a fresh, virgin .mdb?  I don't see any of the variable code.

Previously the global variable was assigned to qQreValueTMMKVEHSubform.Form!TagNumber.

qQreValueTMMKVEHSubform subform was related to the filters that were on the previous .db that I shared. I need the global variable assigned to fQREVALUEform.  qQreValueTMMKVEHSubform was the wrong control for the  variable. It worked for the initial selection but then it would not change the variable value as the selection changed.


However, when I assign it to fQREVALUEform, i get the compile error that I posted. So my concern is that fQREVALUEform is NOT the correct control name to use for assigning the variable. I posted the .db just in case someone wanted to look at the form properties and tell me that was the correct control name or the wrong control name. It does not make sense to me that fQREVALUEform is getting the compile error.

If I am understand this compile error then it is telling me that it doesn't see the control name is setting the variable value.

OK, let me get this straight.  It's your app, your coding? and you don't know how to get the correct control name that's on the form?
I have no idea what the correct control is.  You'll have to determine that.  

The error message is saying that it can't find that subform control on the form with that name - it's not there.

Do you know what control you WANT to pull the TagNumber from on this form?  I don't know your stuff well enough to do it for you.

Sorry Mark.
You are understanding my issue correctly.

I know the form that I want to use. Apparently, I do not know how to get the proper name of that form. But, I honestly do not know what other name it could be. I have been in the properties of this form countless times and can't seem to pull the proper name out.

What exactly does the "NZ" referenced in the Module1 declaration do?

Private Sub Share_Click()
    Module1.g_SelectedTagVariable = Nz(Me.fQREVALUEform.Form![Tag_Number], "")
    DoCmd.OpenQuery "qSaveQREValueReport"
   End Sub

Select allOpen in new window

The NZ() function replaces a Null value in a control with an empty string so that you don't try to put a Null in a string variable, which would cause a datatype mismatch error.

If you are not trying to get a value from a subform on your form, or from a parent form to your form, but from the form that you're button is on, then you don't need to reference the form, just the control.  You can use the "Me"  to refer to the form and you should get an intellisense list of the controls that are on the form when you type the dot (period) on the end of "Me" (Me.).  Your code on your button would look something like:

Private Sub Share_Click()
    Module1.g_SelectedTagVariable = Nz(Me.Tag_Number, "")
    DoCmd.OpenQuery "qSaveQREValueReport"
End Sub

Select allOpen in new window

But you'll need to make sure you are using the right control name and query name.