Help please to get value of a TextBox into the Criteria of a Query.

[ZaphodBBB]

Hi
O.S. = Windows XP Pro all Service Packs
Access = 2003

I have a form with 2 tabbed pages. On the second page I have a subform which populates in Datasheet view with a list of items. One Column has the title Job Number.
When I click on one of the record selectors (little boxes to left of the first used column) the Job Number is written into a Global Variable. Have checked and this works fine.
I have a Command Button on the form below the subform, that after clicking on a job(in the subform) I want to open a new form based on a query that takes the Value of the Job Number selected and populates the new form with all the fields and data accociated with the Job Number selected.
The final few words of the query look something like this (have not got work PC in front of me) ..WHERE ((JobNumber)=([txtJobNumber.Value])) or I can change it to WHERE (JobNumber = (lngJobNumber)).
The problem is that when I push the command button instead of the new form opening and populating, a small box pops up on screen asking me to enter a value for txtJobNumber.Value or lngJobNumber(This is the Variable I have used that holds the Value of what was in the TextBox txtJobNumber.Value.)

Can I get the query to somehow just accept the value direct from the TextBox or Variable?

Any help much appreciated I have seen another couple of similiar questions on this site, one that is close seemed to suggest writing a function that would pass the value? I'll try that today.

[NeoPa]

I have posted a function earlier in another thread (Use selected record in Form B (acDialog Form) as go to criteria in Form A) to give this facility.
However, I think there is a more valid way of doing this with forms and subforms.
Obviously you're welcome to use the solution posted there but I think it's a bit of a kludge for your situation.
We have a bit of a form / subform expert on here, give her a chance to post here as I think she can give you the 'proper' answer.
If not of course, use the kludge.

[ZaphodBBB]

Thanks NeoPar

I'll have a look at the Kludge as you call it now.

Just for a bit more info, I tried this afternoon to make up a .bas module with a function in it and have the Query obtain the required parameter from that function but still no go. I have done this previously and I'm sure it worked. Makes me wonder if the Query does not know how to look at the subform on the tabbed page of the form. The subform is not linked but gets the data that populates it from another query when it is opened. This works fine.

The Main Form is called frmFieldServiceDatabase. it consists of 2 tabbed pages. The second of which contains the unlinked subform.

Thanks again I'll go have a look now at the Kludge!!

[NeoPa]

I'm no expert on sub-forms, but I do know that referring to an element on a sub-form has to include the name of the Tab it's on, rather than the name of the sub-form itself in the reference.
This may help your code.
BTW Our main forms expert expects to be away for a short while, so please bear with us.
Others may very well step in mind you.
Another BTW, How do you get BBB frm BeeBleBrox? That can't be right surely ;).

[ADezii]

Hi
O.S. = Windows XP Pro all Service Packs
Access = 2003

I have a form with 2 tabbed pages. On the second page I have a subform which populates in Datasheet view with a list of items. One Column has the title Job Number.
When I click on one of the record selectors (little boxes to left of the first used column) the Job Number is written into a Global Variable. Have checked and this works fine.
I have a Command Button on the form below the subform, that after clicking on a job(in the subform) I want to open a new form based on a query that takes the Value of the Job Number selected and populates the new form with all the fields and data accociated with the Job Number selected.
The final few words of the query look something like this (have not got work PC in front of me) ..WHERE ((JobNumber)=([txtJobNumber.Value])) or I can change it to WHERE (JobNumber = (lngJobNumber)).
The problem is that when I push the command button instead of the new form opening and populating, a small box pops up on screen asking me to enter a value for txtJobNumber.Value or lngJobNumber(This is the Variable I have used that holds the Value of what was in the TextBox txtJobNumber.Value.)

Can I get the query to somehow just accept the value direct from the TextBox or Variable?

Any help much appreciated I have seen another couple of similiar questions on this site, one that is close seemed to suggest writing a function that would pass the value? I'll try that today.

The correct syntax for referring to a Field Value on a Sub-Form from the Main
Form is as follows:

Forms![frmMainForm].[subfSubFormName].Form![FieldName]

P.S. There should be no need to refer to the specific Page on the Tabbed Dialog
Control

[NeoPa]

The correct syntax for referring to a Field Value on a Sub-Form from the Main
Form is as follows:

Forms![frmMainForm].[SubFormName].Form![FieldName]

P.S. There should be no need to refer to the specific Page on the Tabbed Dialog
Control

Thanks, that's right.
Bear in mind though, (not you ADezii - I'm sure you know already) that the SubFormName refers to the name of a container type item on the main form and not the name of the form within. Also, the Form![FieldName] is actually spelt 'Form' and is not intended to represent the name of the actual form used either.
I'm not sure how subforms are handled on tabbed forms ATM but I don't even want to go there just now ...Because life's complicated enough!

[ZaphodBBB]

Hi ADezii and NeoPar

Many thanks for the format for getting the value into the criteria. It worked fine once I got the correct names of the forms into the expression.

One thing I find difficult to follow it that in my Forms List the subform is named 'frmReviewJobsSubform,' however if I click on the subform itself in Design View it says 'qryReviewJobs subform' and when I entered the last version into the Function it worked... i.e. FindJob = Forms![frmFieldServiceDatabase]![qryReviewJobs subform].Form![txtJobNumber].

IF you have a moment could you please explain the syntax of the statement to me... i.e. Why use . between Forms and why have the final..... '].Form![txtJobNumber] why do you need the word form there??

Anyway much appreciate your time and help.
Kind Regards
Mike

[NeoPa]

'.' means the following is the name of a property of the parent.
In collections though, you have something similar to an array of like properties.
Take the Forms() collection, for instance. This is the collection of open forms.
Normal reference usage is Forms(Name of form), but VBA has provided another, more convenient way of writing that which is Forms![Name of Form] ([] only necessary with chars like spaces which would confuse the compiler otherwise).
So, Forms("MyForm") ==> Forms!MyForm.

So, the '!' is used to identify an element in a collection whereas the '.' identifies a defined property of an object. Where '!' is used and is not required, the compiler will treat it as a '.'.
Forms() is a collection of Form objects.

Clear as mud yet?

[NeoPa]

FindJob = Forms![frmFieldServiceDatabase]![qryReviewJobs subform].Form![txtJobNumber].

IF you have a moment could you please explain the syntax of the statement to me... i.e. Why use . between Forms and why have the final..... '].Form![txtJobNumber] why do you need the word form there??
Mike

In this case, it could have been written as (someone correct me if I'm wrong but I tried it out on my test forms and it worked ok)

FindJob = Forms!frmFieldServiceDatabase.[qryReviewJobs subform].Form.txtJobNumber
or
FindJob = Forms("frmFieldServiceDatabase").[qryReviewJobs subform].Form.txtJobNumber

Notice the [] around 'qryReviewJobs subform' as it has an embedded space.
I still don't seem to have answered the question!
[qryReviewJobs subform] is actually a containing object property of the main form.
One of the properties of this type of object is .Form, which refers to the subform itself.
I don't know why it's structured this way - and it certainly confuses lots of people - but that's how it is :S.

Final addendum - The container part doesn't necessarily have the same name as the actual form that is contained in it.
I'm not sure it can, but it certainly needn't.

[ZaphodBBB]

Hi Neopa

Thanks for the explanation...dont know about clear as mud but its getting there!!
Just for interest sake I cant view this site very well from my work laptop. In a lot of the threads/questions the text in lines runs into the line above or below.

Anyway much appreciate your assistance. Thanks!

[MMcCarthy]

FindJob = Forms("frmFieldServiceDatabase").[qryReviewJobs subform].Form.txtJobNumber
 

This is not quite right Ade

Forms("FormName").Controls("ControlName")
 

Is the syntax. I don't know if there is anyway to reference a control on a subform using this syntax.

In this case, it could have been written as (someone correct me if I'm wrong but I tried it out on my test forms and it worked ok)

FindJob = Forms!frmFieldServiceDatabase.[qryReviewJobs subform].Form.txtJobNumber
or
FindJob = Forms("frmFieldServiceDatabase").[qryReviewJobs subform].Form.txtJobNumber

Notice the [] around 'qryReviewJobs subform' as it has an embedded space.
I still don't seem to have answered the question!
[qryReviewJobs subform] is actually a containing object property of the main form.
One of the properties of this type of object is .Form, which refers to the subform itself.
I don't know why it's structured this way - and it certainly confuses lots of people - but that's how it is :S.

Final addendum - The container part doesn't necessarily have the same name as the actual form that is contained in it.
I'm not sure it can, but it certainly needn't.

[MMcCarthy]

FindJob = Forms("frmFieldServiceDatabase").[qryReviewJobs subform].Form.txtJobNumber
 

This is not quite right Ade

Forms("FormName").Controls("ControlName")
 

Is the syntax. I don't know if there is anyway to reference a control on a subform using this syntax.

You could try...

Forms("FormName").Controls("SubFormName").Form.TextBoxName
 

[NeoPa]

This is not quite right Ade

Yes it is. It's perfect.
What else would you expect ;)

-Ade.

[ADezii]

Hi ADezii and NeoPar

Many thanks for the format for getting the value into the criteria. It worked fine once I got the correct names of the forms into the expression.

One thing I find difficult to follow it that in my Forms List the subform is named 'frmReviewJobsSubform,' however if I click on the subform itself in Design View it says 'qryReviewJobs subform' and when I entered the last version into the Function it worked... i.e. FindJob = Forms![frmFieldServiceDatabase]![qryReviewJobs subform].Form![txtJobNumber].

IF you have a moment could you please explain the syntax of the statement to me... i.e. Why use . between Forms and why have the final..... '].Form![txtJobNumber] why do you need the word form there??

Anyway much appreciate your time and help.
Kind Regards
Mike

'Keep in mind that you will rarely, if ever, actually refer to the name of the Form
'comprising a Sub-Form. You will actually be referring to the name of the
'Sub-Form control. The actual Sub-Form itself is derived from the Form property
'of the Sub-Form Control. As it applies to your individual case, this would be:
Forms![frmFieldServiceDatabase]![qryReviewJobs subform].Form

NOTE: I sincerely apologize if I have made matters more confusing - there
seems to be no easy way to explain this syntax.

[NeoPa]

As ADezii so clearly says, this is not a simple concept to understand.
(Thank you again ADezii - another good, helpful post).
I will just copy in here something I set up and saved, to help clarify this particular issue.
It doesn't contradict anything ADezii has already said but hopefully shows from a slightly different angle.

The external syntax (code running from outside the form itself or one of its subforms) for this is :

Forms![FormName].[SubFormName].Form.[ControlName]
or
Forms("FormName").[SubFormName].Form.[ControlName]

Where :
FormName is the name of the main form.
SubFormName is the name of the Sub-form control on the main form in which the actual sub-form is contained.
ControlName is the name of the control that you want to refer to.

You will often see (!) used instead of (.). In most cases it's not a problem as Access will interpret it as a (.) if object it refines is not a collection.

Example to refer to the subform's txtJobNumber control :

Forms!frmFieldServiceDatabase.[qryReviewJobs subform].Form.txtJobNumber
or
Forms("frmFieldServiceDatabase").[qryReviewJobs subform].Form.txtJobNumber