Query result shows on form but not in VBA code Me.ControlName

  • Thread starter Thread starter Jimbo213
  • Start date Start date
J

Jimbo213

I have a query that Joins MasterTable.OwningProject and ProjectTable.Project
Query returns the 3rd column Project.ECS_YN value of "Yes" or "No"

The "Yes" "No" shows on the form in a combobox Named DisplayECSprojYN.
The control source for that combo box is OwningProject

In VBA code I call out Me.DisplayECSprojYN and I get the OwningProject value
instead of either "Yes" or "No" that shows on the form.

1) what is correct VBA code to retrieve the Yes/No value
2) why doesn't Me.DisplayECSprojYN work?

Thanks for your reply & assistance.
Jimbo213
 
Here is SQL behind the Query:
SELECT [D7-MASTER].[Owning Project], Project.Name, Project.ProjectIsECS
FROM [D7-MASTER] INNER JOIN Project ON [D7-MASTER].[Owning Project] =
Project.Project;

Result DOES display correctly on FORM in field DisplayECSprojYN as "Yes"
when Owning Project = R12345 on record1 of D7-MASTER.

However this code incorrectly evaluates Me.DisplayECSprojYN as "R12345"

If Me.DisplayECSprojYN = "Yes" And Me.Use_Case <> "" Then

and it should evaluate as "Yes"

Thanks PJFry for your reply & assistance.
Jimbo213
 
Couple of possibilites:

If DisplayECSprojYN is a true Yes/No field, you may wish to use -1 instead
of "Yes".

With Use_Case, what are you trying to evaluate? Is it a null value or and
empty string? If you want to evaluate a null value, try this:

If Me.DisplayECSprojYN = "Yes" And IsNull(Me.Use_Case) Then

This is straight air code, so you may need to tweak it a bit. I would try
the If statement using only the Use_Case to evaluate which method works, ""
or IsNull.

Try those solutions and see if you get any more traction.

PJ

Jimbo213 said:
Here is SQL behind the Query:
SELECT [D7-MASTER].[Owning Project], Project.Name, Project.ProjectIsECS
FROM [D7-MASTER] INNER JOIN Project ON [D7-MASTER].[Owning Project] =
Project.Project;

Result DOES display correctly on FORM in field DisplayECSprojYN as "Yes"
when Owning Project = R12345 on record1 of D7-MASTER.

However this code incorrectly evaluates Me.DisplayECSprojYN as "R12345"

If Me.DisplayECSprojYN = "Yes" And Me.Use_Case <> "" Then

and it should evaluate as "Yes"

Thanks PJFry for your reply & assistance.
Jimbo213


PJFry said:
Can you post the code?
Click to expand...
Click to expand...
 
Your idea to use -1 in the If statement won't work. Maybe I didn't explain
the problem correctly PJ.

The reason I know Me.DisplayECSprojYN is evaluating incorrectly in VBA was
to set the code breakpoint and F8 down to the problem statement.

I then hover over Me.DisplayECSprojYN in the IF-Statement and it shows
"R12345" which is the control source for that combo box [Owning Project] and
NOT the combo boxes value that displays so nicely on the Form.

That's my problem.

Me.DisplayECSprojYN shows "Yes" on the form but I can't seem to get either
-1 or "yes" in the Visual Basic Editor.

Does this help you generate any other possible things to try?

--
Thanks for your reply & assistance.
Jimbo213


PJFry said:
Couple of possibilites:

If DisplayECSprojYN is a true Yes/No field, you may wish to use -1 instead
of "Yes".

With Use_Case, what are you trying to evaluate? Is it a null value or and
empty string? If you want to evaluate a null value, try this:

If Me.DisplayECSprojYN = "Yes" And IsNull(Me.Use_Case) Then

This is straight air code, so you may need to tweak it a bit. I would try
the If statement using only the Use_Case to evaluate which method works, ""
or IsNull.

Try those solutions and see if you get any more traction.

PJ

Jimbo213 said:
Here is SQL behind the Query:
SELECT [D7-MASTER].[Owning Project], Project.Name, Project.ProjectIsECS
FROM [D7-MASTER] INNER JOIN Project ON [D7-MASTER].[Owning Project] =
Project.Project;

Result DOES display correctly on FORM in field DisplayECSprojYN as "Yes"
when Owning Project = R12345 on record1 of D7-MASTER.

However this code incorrectly evaluates Me.DisplayECSprojYN as "R12345"

If Me.DisplayECSprojYN = "Yes" And Me.Use_Case <> "" Then

and it should evaluate as "Yes"

Thanks PJFry for your reply & assistance.
Jimbo213


PJFry said:
Can you post the code?

:


I have a query that Joins MasterTable.OwningProject and ProjectTable.Project
Query returns the 3rd column Project.ECS_YN value of "Yes" or "No"

The "Yes" "No" shows on the form in a combobox Named DisplayECSprojYN.
The control source for that combo box is OwningProject

In VBA code I call out Me.DisplayECSprojYN and I get the OwningProject value
instead of either "Yes" or "No" that shows on the form.

1) what is correct VBA code to retrieve the Yes/No value
2) why doesn't Me.DisplayECSprojYN work?

Thanks for your reply & assistance.
Jimbo213
Click to expand...
Click to expand...
Click to expand...
 
PJ - is it possible to execute the SQL code from within the VBA code?

Please be specific ... include any DIM statements.
Here is the SQL code from the Query

SELECT [D7-MASTER].[Owning Project], Project.Name, Project.ProjectIsECS
FROM [D7-MASTER] INNER JOIN Project ON [D7-MASTER].[Owning Project] =
Project.Project;

thanks.

--
Thanks for your reply & assistance.
Jimbo213


PJFry said:
Couple of possibilites:

If DisplayECSprojYN is a true Yes/No field, you may wish to use -1 instead
of "Yes".

With Use_Case, what are you trying to evaluate? Is it a null value or and
empty string? If you want to evaluate a null value, try this:

If Me.DisplayECSprojYN = "Yes" And IsNull(Me.Use_Case) Then

This is straight air code, so you may need to tweak it a bit. I would try
the If statement using only the Use_Case to evaluate which method works, ""
or IsNull.

Try those solutions and see if you get any more traction.

PJ

Jimbo213 said:
Here is SQL behind the Query:
SELECT [D7-MASTER].[Owning Project], Project.Name, Project.ProjectIsECS
FROM [D7-MASTER] INNER JOIN Project ON [D7-MASTER].[Owning Project] =
Project.Project;

Result DOES display correctly on FORM in field DisplayECSprojYN as "Yes"
when Owning Project = R12345 on record1 of D7-MASTER.

However this code incorrectly evaluates Me.DisplayECSprojYN as "R12345"

If Me.DisplayECSprojYN = "Yes" And Me.Use_Case <> "" Then

and it should evaluate as "Yes"

Thanks PJFry for your reply & assistance.
Jimbo213


PJFry said:
Can you post the code?

:


I have a query that Joins MasterTable.OwningProject and ProjectTable.Project
Query returns the 3rd column Project.ECS_YN value of "Yes" or "No"

The "Yes" "No" shows on the form in a combobox Named DisplayECSprojYN.
The control source for that combo box is OwningProject

In VBA code I call out Me.DisplayECSprojYN and I get the OwningProject value
instead of either "Yes" or "No" that shows on the form.

1) what is correct VBA code to retrieve the Yes/No value
2) why doesn't Me.DisplayECSprojYN work?

Thanks for your reply & assistance.
Jimbo213
Click to expand...
Click to expand...
Click to expand...
 
Back
Top