Using Parameter queries in forms

[Mountaineer]

I am trying to build a history log of changes to projects.

I have a table (t_Program budgets) with “Request number†(autonumber and
primary key), “Company†(text field), “Project number†(text field), “Project
Title†(text field), “Status†(text field), etc. to store the historical data.

I have a second table (t_Project) listing information for Company (text
field), Project Number (number field), and Project Title (I have to use ODBC
to get this table). I am using this table as “Row Sourceâ€.

I am using a form for data entry. I am using combo boxes: cboCompany,
cboProjectNumber, and cboProjectTitle. When the Company and Project number
are selected, I want ProjectTitle to be automatically filled in (note:note
it requires the combination of company and project number).

There can also be occasions where a project number has not been determined
yet but the impending change needs to be captured. In this case, Company
would be selected but Project number would be blank (not selected). For this
condition I want the Project Title to be “TBDâ€.

I wrote a parameter query using t_Project to find the Project Title where
“Company†and “Project Number†comes from the forms’ cboCompany and
cboProjectNumber fields.

I have tried writing macros on the “after Update†property of the
cboProjectNumber field to setvalues based on the different conditions. I’ve
tried to write macros to openquery, etc. I have had no luck. Any help would
be appreciated.

 

[Allen Browne]

I think this is a display issue. If the value for [Project Title] has not
yet been determined, it should be stored as a Null. You can then display it
as "TBD" if that helps people understand what the null means.

One way to do that is place another text box on top of the [Project Title]
one on your form. Set its Control Source to:
=IIf([Project Title] Is Null, "TBD", [Project Title])
Then use the Enter event of this new text box to SetFocus to the real one,
so it goes blank when the user enters the box, ready to accept a valid
value.

You will need to set the Tab Order so the new text box becomes the active
one in the right place as the user tabs through the form. Set the Tab Stop
of [Project Title] to No, so it doesn't mess up the tab order.

 

[Mountaineer]

Sorry. I'm still not getting it.

What I currently have:

cboCompany
Control Source:Company
Row Source Type:Value List


cboProjectNumber
Control Sourceroject Number
Row Source:SELECT t_Projects.Project_No FROM t_Projects;


cboProjectTitle
Control Sourceroject Title
Row Source:SELECT [q_Project Title].Title FROM [q_Project Title];

new Text box txtProjectTitle
Control Source:=IIf([cboProjectTitle] Is Null,"TBD",[cboProjectTitle])
On Enter event:
Private Sub txtProjectTitle_Enter()

cboProjectTitle.SetFocus

End Sub



When I select Company and Project Number, I can see the Project Title from
the cboProjectTitle dropdown box. But the value never gets selected. The
txtProjectTitle stays "TBD". When I check "t_Program Budgets", value is
still blank so the value is not getting transferred.

Any help would be appreciated.

 

[John W. Vinson]

Sorry. I'm still not getting it.

What I currently have:

cboCompany
Control Source:Company
Row Source Type:Value List


cboProjectNumber
Control Sourceroject Number
Row Source:SELECT t_Projects.Project_No FROM t_Projects;


cboProjectTitle
Control Sourceroject Title
Row Source:SELECT [q_Project Title].Title FROM [q_Project Title];

new Text box txtProjectTitle
Control Source:=IIf([cboProjectTitle] Is Null,"TBD",[cboProjectTitle])
On Enter event:
Private Sub txtProjectTitle_Enter()

cboProjectTitle.SetFocus

End Sub



When I select Company and Project Number, I can see the Project Title from
the cboProjectTitle dropdown box. But the value never gets selected. The
txtProjectTitle stays "TBD". When I check "t_Program Budgets", value is
still blank so the value is not getting transferred.

Any help would be appreciated.

The Enter event is probably inappropriate here: it doesn't fire when a value
is *selected* from the control but when you *enter* the control. Since this
control source is based on an expression, it won't let you type in it... I
suspect you want to requery txtProjectTitle in the AfterUpdate event of
cboProjectTitle. I'm not sure I see the point of having the two controls -
can't you just display the project title in the combo? Where are you trying to
"transfer" the value, and why?

 

[Mountaineer]

Hi,

We work with projects for multiple companies. We have an internal system
which assigns project numbers, etc. This system contains the company,
project number, Project Title, etc. I use ODBC to connect to this
information.

Management wanted an additional tracking/communication tool in conjunction
to our internal system. This tool is to track changes/requests/ approvals to
projects. I use a form for data entry. I want the person to select the
company and project number. But I wanted the form to automatically fill-in
the Project Title.

In addition, management wanted to know of impending new projects. These are
projects which "could" surface but a formal number has not been assigned.
Therefore, outside our internal system. In this example, we would know which
company could be affected but no project number has been assigned. In this
case I wanted a default "TBD" as the project title.

t_Program Budgets is my master table of all information to be stored
t_Projects is my ODBC connected table to our internal system
q_Project Title-I wrote a parameter query to find the Project Title based on
the company and project number selected on the form.

Why did I add a second control (the text box)? I did this based on the
suggestion of Allen Browne. However, I am extremely novice and I may have
missed the point.

When I looked in my master table (t_Program Budgets) I found when I selected
the company and project numbers on the form, this information "transferred"
to the master table. But the "Project Title" field did not.
--
Thank you!!

Mountaineer

Sorry. I'm still not getting it.

What I currently have:

cboCompany
Control Source:Company
Row Source Type:Value List


cboProjectNumber
Control Sourceroject Number
Row Source:SELECT t_Projects.Project_No FROM t_Projects;


cboProjectTitle
Control Sourceroject Title
Row Source:SELECT [q_Project Title].Title FROM [q_Project Title];

new Text box txtProjectTitle
Control Source:=IIf([cboProjectTitle] Is Null,"TBD",[cboProjectTitle])
On Enter event:
Private Sub txtProjectTitle_Enter()

cboProjectTitle.SetFocus

End Sub



When I select Company and Project Number, I can see the Project Title from
the cboProjectTitle dropdown box. But the value never gets selected. The
txtProjectTitle stays "TBD". When I check "t_Program Budgets", value is
still blank so the value is not getting transferred.

Any help would be appreciated.

The Enter event is probably inappropriate here: it doesn't fire when a value
is *selected* from the control but when you *enter* the control. Since this
control source is based on an expression, it won't let you type in it... I
suspect you want to requery txtProjectTitle in the AfterUpdate event of
cboProjectTitle. I'm not sure I see the point of having the two controls -
can't you just display the project title in the combo? Where are you trying to
"transfer" the value, and why?

 

[Mountaineer]

I got it!!!!

I found examples for some forms on:

www.mvps.org/access/forms/frm0028.htm
www.mpvs.org/access/forms/frm0058.htm

I found these through other discussion threads.

Speaking as an extremely novice person, I would like to say how much I
appreciate all the people who take the time and effort to help people like
me.

-
Thank you!!

Mountaineer

Hi,

We work with projects for multiple companies. We have an internal system
which assigns project numbers, etc. This system contains the company,
project number, Project Title, etc. I use ODBC to connect to this
information.

Management wanted an additional tracking/communication tool in conjunction
to our internal system. This tool is to track changes/requests/ approvals to
projects. I use a form for data entry. I want the person to select the
company and project number. But I wanted the form to automatically fill-in
the Project Title.

In addition, management wanted to know of impending new projects. These are
projects which "could" surface but a formal number has not been assigned.
Therefore, outside our internal system. In this example, we would know which
company could be affected but no project number has been assigned. In this
case I wanted a default "TBD" as the project title.

t_Program Budgets is my master table of all information to be stored
t_Projects is my ODBC connected table to our internal system
q_Project Title-I wrote a parameter query to find the Project Title based on
the company and project number selected on the form.

Why did I add a second control (the text box)? I did this based on the
suggestion of Allen Browne. However, I am extremely novice and I may have
missed the point.

When I looked in my master table (t_Program Budgets) I found when I selected
the company and project numbers on the form, this information "transferred"
to the master table. But the "Project Title" field did not.
--
Thank you!!

Mountaineer

Sorry. I'm still not getting it.

What I currently have:

cboCompany
Control Source:Company
Row Source Type:Value List


cboProjectNumber
Control Sourceroject Number
Row Source:SELECT t_Projects.Project_No FROM t_Projects;


cboProjectTitle
Control Sourceroject Title
Row Source:SELECT [q_Project Title].Title FROM [q_Project Title];

new Text box txtProjectTitle
Control Source:=IIf([cboProjectTitle] Is Null,"TBD",[cboProjectTitle])
On Enter event:
Private Sub txtProjectTitle_Enter()

cboProjectTitle.SetFocus

End Sub



When I select Company and Project Number, I can see the Project Title from
the cboProjectTitle dropdown box. But the value never gets selected. The
txtProjectTitle stays "TBD". When I check "t_Program Budgets", value is
still blank so the value is not getting transferred.

Any help would be appreciated.

The Enter event is probably inappropriate here: it doesn't fire when a value
is *selected* from the control but when you *enter* the control. Since this
control source is based on an expression, it won't let you type in it... I
suspect you want to requery txtProjectTitle in the AfterUpdate event of
cboProjectTitle. I'm not sure I see the point of having the two controls -
can't you just display the project title in the combo? Where are you trying to
"transfer" the value, and why?