Where clause

[redFred]

Main form has subform, which in turn has a subform with a combo box that I
want to use to return jobs only for the client ID of the main form (I also
have a Client ID text box on the that subform).

My row source is:
SELECT [tbl 2 Job].InvNum, [tbl 2 Job].EnterDate, [tbl 2 Job].StartDate,
[tbl 2 Job].EndDate FROM [tbl 2 Job] WHERE ((([tbl 2
Job].ClientID)=[Forms]![frm1 Client]![txtClientID])) ORDER BY [tbl 2
Job].StartDate DESC;

The combo box returns "Name#". However, pulling down the list the records
associated with the first client record in the table appear for all clients.
I am confident my issue is the Where Clause is not correct.

Can anyone see what is wrong with my Where Clause?

BTW, on my main form ClientID is contained in text box txtClientID; on the
second subform it is contained in text box txtClientID with a control source
of "=[Forms]![frm1 Client]![txtClientID]".


Thanks.

 

[Douglas J. Steele]

Are you saying that [frm1 Client] is the name of your subform?

Subforms aren't actually opened as part of the Forms collection: you need to
refer to them via their parent form, something like:

[Forms]![ParentFormName]![frm1 Client].Form![txtClientID]

Actually, you need to ensure that the name of the subform control on the
parent form is [frm1 Client]: depending on how the form was added as a
subform, the name of the subform control may be different than the name of
the form being used as a subform. You need to use the control name.

 

[redFred]

Thanks for responding, Doug.

My main form: [frm1 Client]
Subform1: [Reservationfrm]
Subform2: [ReceiptsEntryfrm]

The latter two are subform container names, not the actual form name.
Focus is on Subform2, for entry.

Try-first, using ClientID from the subform in focus:
[Forms]![frm1 Client]![Reservationfrm]![ReceiptsEntryfrm].[Form]![txtClientID]

Try-second, using ClientID from the main form:
[Forms]![frm1 Client].[Form]![txtClientID]

Behavior is the same.

HOWEVER, it appears that the data returned is correct for the first client
attempted. I mean, whatever client I try after first launching will be
correct. All that follow before re-launching again will contain that same
data.

My conclusion is my Where Clause is, in fact, correct. This is a data entry
form -- could that be causing issue?

Thanks for the help!

Are you saying that [frm1 Client] is the name of your subform?

Subforms aren't actually opened as part of the Forms collection: you need to
refer to them via their parent form, something like:

[Forms]![ParentFormName]![frm1 Client].Form![txtClientID]

Actually, you need to ensure that the name of the subform control on the
parent form is [frm1 Client]: depending on how the form was added as a
subform, the name of the subform control may be different than the name of
the form being used as a subform. You need to use the control name.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Main form has subform, which in turn has a subform with a combo box that I
want to use to return jobs only for the client ID of the main form (I also
have a Client ID text box on the that subform).

My row source is:
SELECT [tbl 2 Job].InvNum, [tbl 2 Job].EnterDate, [tbl 2 Job].StartDate,
[tbl 2 Job].EndDate FROM [tbl 2 Job] WHERE ((([tbl 2
Job].ClientID)=[Forms]![frm1 Client]![txtClientID])) ORDER BY [tbl 2
Job].StartDate DESC;

The combo box returns "Name#". However, pulling down the list the records
associated with the first client record in the table appear for all
clients.
I am confident my issue is the Where Clause is not correct.

Can anyone see what is wrong with my Where Clause?

BTW, on my main form ClientID is contained in text box txtClientID; on the
second subform it is contained in text box txtClientID with a control
source
of "=[Forms]![frm1 Client]![txtClientID]".


Thanks.

 

[Douglas J. Steele]

I'm having a hard time following your description.

Do you have two subforms on [frm1 Client], or is [ReceiptsEntryfrm] a
subform of the form contained in [Reservationfrm]?

If both are subforms of the parent form, you need only refer to
[Forms]![frm1 Client]![Reservationfrm].Form![txtClientID] or [Forms]![frm1
Client]![ReceiptsEntryfrm].Form![txtClientID], depending on which subform
contains txtClientID.

If you've got a subform on a subform, you need

[Forms]![frm1
Client]![Reservationfrm].Form![ReceiptsEntryfrm].Form![txtClientID]

For more information, see http://www.mvps.org/access/forms/frm0031.htm at
"The Access Web".

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for responding, Doug.

My main form: [frm1 Client]
Subform1: [Reservationfrm]
Subform2: [ReceiptsEntryfrm]

The latter two are subform container names, not the actual form name.
Focus is on Subform2, for entry.

Try-first, using ClientID from the subform in focus:
[Forms]![frm1
Client]![Reservationfrm]![ReceiptsEntryfrm].[Form]![txtClientID]

Try-second, using ClientID from the main form:
[Forms]![frm1 Client].[Form]![txtClientID]

Behavior is the same.

HOWEVER, it appears that the data returned is correct for the first client
attempted. I mean, whatever client I try after first launching will be
correct. All that follow before re-launching again will contain that same
data.

My conclusion is my Where Clause is, in fact, correct. This is a data
entry
form -- could that be causing issue?

Thanks for the help!

Are you saying that [frm1 Client] is the name of your subform?

Subforms aren't actually opened as part of the Forms collection: you need
to
refer to them via their parent form, something like:

[Forms]![ParentFormName]![frm1 Client].Form![txtClientID]

Actually, you need to ensure that the name of the subform control on the
parent form is [frm1 Client]: depending on how the form was added as a
subform, the name of the subform control may be different than the name
of
the form being used as a subform. You need to use the control name.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Main form has subform, which in turn has a subform with a combo box
that I
want to use to return jobs only for the client ID of the main form (I
also
have a Client ID text box on the that subform).

My row source is:
SELECT [tbl 2 Job].InvNum, [tbl 2 Job].EnterDate, [tbl 2
Job].StartDate,
[tbl 2 Job].EndDate FROM [tbl 2 Job] WHERE ((([tbl 2
Job].ClientID)=[Forms]![frm1 Client]![txtClientID])) ORDER BY [tbl 2
Job].StartDate DESC;

The combo box returns "Name#". However, pulling down the list the
records
associated with the first client record in the table appear for all
clients.
I am confident my issue is the Where Clause is not correct.

Can anyone see what is wrong with my Where Clause?

BTW, on my main form ClientID is contained in text box txtClientID; on
the
second subform it is contained in text box txtClientID with a control
source
of "=[Forms]![frm1 Client]![txtClientID]".


Thanks.

 

[redFred]

Is [ReceiptsEntryfrm] a
subform of the form contained in [Reservationfrm]?

Yes.

I tried it as you suggested. I had left out one "form". It is still acting
the same.

On a clean startup...I select a client....get good information.
Go to another client....get the same information -- that of the first client.
Go to a third client...get the same return -- that of the first client.

When I start up clean again and select any client, that client's information
is what's returned for every client until I restart clean.

Like I said, the clause syntax is ok.

Does this form being set to DE have any bearing? Why would code that worked
once not work every change of client?

Thanks, again!

I'm having a hard time following your description.

Do you have two subforms on [frm1 Client], or is [ReceiptsEntryfrm] a
subform of the form contained in [Reservationfrm]?

If both are subforms of the parent form, you need only refer to
[Forms]![frm1 Client]![Reservationfrm].Form![txtClientID] or [Forms]![frm1
Client]![ReceiptsEntryfrm].Form![txtClientID], depending on which subform
contains txtClientID.

If you've got a subform on a subform, you need

[Forms]![frm1
Client]![Reservationfrm].Form![ReceiptsEntryfrm].Form![txtClientID]

For more information, see http://www.mvps.org/access/forms/frm0031.htm at
"The Access Web".

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for responding, Doug.

My main form: [frm1 Client]
Subform1: [Reservationfrm]
Subform2: [ReceiptsEntryfrm]

The latter two are subform container names, not the actual form name.
Focus is on Subform2, for entry.

Try-first, using ClientID from the subform in focus:
[Forms]![frm1
Client]![Reservationfrm]![ReceiptsEntryfrm].[Form]![txtClientID]

Try-second, using ClientID from the main form:
[Forms]![frm1 Client].[Form]![txtClientID]

Behavior is the same.

HOWEVER, it appears that the data returned is correct for the first client
attempted. I mean, whatever client I try after first launching will be
correct. All that follow before re-launching again will contain that same
data.

My conclusion is my Where Clause is, in fact, correct. This is a data
entry
form -- could that be causing issue?

Thanks for the help!

Are you saying that [frm1 Client] is the name of your subform?

Subforms aren't actually opened as part of the Forms collection: you need
to
refer to them via their parent form, something like:

[Forms]![ParentFormName]![frm1 Client].Form![txtClientID]

Actually, you need to ensure that the name of the subform control on the
parent form is [frm1 Client]: depending on how the form was added as a
subform, the name of the subform control may be different than the name
of
the form being used as a subform. You need to use the control name.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Main form has subform, which in turn has a subform with a combo box
that I
want to use to return jobs only for the client ID of the main form (I
also
have a Client ID text box on the that subform).

My row source is:
SELECT [tbl 2 Job].InvNum, [tbl 2 Job].EnterDate, [tbl 2
Job].StartDate,
[tbl 2 Job].EndDate FROM [tbl 2 Job] WHERE ((([tbl 2
Job].ClientID)=[Forms]![frm1 Client]![txtClientID])) ORDER BY [tbl 2
Job].StartDate DESC;

The combo box returns "Name#". However, pulling down the list the
records
associated with the first client record in the table appear for all
clients.
I am confident my issue is the Where Clause is not correct.

Can anyone see what is wrong with my Where Clause?

BTW, on my main form ClientID is contained in text box txtClientID; on
the
second subform it is contained in text box txtClientID with a control
source
of "=[Forms]![frm1 Client]![txtClientID]".


Thanks.