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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jimbo213 - 30 May 2008 16:39 GMT

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

Reply to this Message

PJFry - 30 May 2008 17:11 GMT

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"
[quoted text clipped - 10 lines]
> Thanks for your reply & assistance.
> Jimbo213

Reply to this Message

Jimbo213 - 30 May 2008 17:21 GMT

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

> Can you post the code?
>
[quoted text clipped - 12 lines]
> > Thanks for your reply & assistance.
> > Jimbo213

Reply to this Message

PJFry - 30 May 2008 17:59 GMT

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

> Here is SQL behind the Query:
> SELECT [D7-MASTER].[Owning Project], Project.Name, Project.ProjectIsECS
[quoted text clipped - 29 lines]
> > > Thanks for your reply & assistance.
> > > Jimbo213

Reply to this Message

Jimbo213 - 30 May 2008 18:19 GMT

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?

Signature

Thanks for your reply & assistance.
Jimbo213

> Couple of possibilites:
>
[quoted text clipped - 47 lines]
> > > > Thanks for your reply & assistance.
> > > > Jimbo213

Reply to this Message

Jimbo213 - 30 May 2008 18:21 GMT

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.

Signature

Thanks for your reply & assistance.
Jimbo213

> Couple of possibilites:
>
[quoted text clipped - 47 lines]
> > > > Thanks for your reply & assistance.
> > > > Jimbo213

Reply to this Message