Form/Subform for Data Entry/View/Update

[Guest]

Working on an application that has several hundred projects. Some projects have change orders. There is a total of several hundred change orders. Relationship is 1 project to many COs. I need a data entry/view/update form that addresses 1 project and all of its change orders at one time. I want to have the project info in a form and the CO info in a subform. I want to use a combo box or list box to select a project from the list of all projects and use the selected project to populate the form with the project info and put all of the COs for the selected project into the subform. How do I get the selected project number to automatically go to the right record in the project table and select the related COs from the CO table?

 

[tina]

the easy way to get started is to select your projects table in the database
window (don't open it). then click New Object: Auto Form on the toolbar and,
*if your table relationships have been defined correctly in the
Relationships window*, Access will build the form/subform for you.
next open the form in design view. click View, Form Header/Footer from the
menu bar. click the Control Wizards button on the "Toolbox" toolbar, to
select (depress) it. next, click the Combo Box button on the same toolbar
and then click anywhere in the header section of the form. when the wizard
dialog opens, select the option "Find a record on my form based on the
value I selected in my combo box.". the wizard will walk you thru the
process pretty painlessly.
there you go, a basic user interface that fits your stated requirements.
you'll want to test it out and tweak it until you have exactly what you
need.

hth

Working on an application that has several hundred projects. Some projects

have change orders. There is a total of several hundred change orders.
Relationship is 1 project to many COs. I need a data entry/view/update form
that addresses 1 project and all of its change orders at one time. I want to
have the project info in a form and the CO info in a subform. I want to use
a combo box or list box to select a project from the list of all projects
and use the selected project to populate the form with the project info and
put all of the COs for the selected project into the subform. How do I get
the selected project number to automatically go to the right record in the
project table and select the related COs from the CO table?

 

[Van T. Dinh]

Use the ComboBox Wizard to create a ComboBox to find Record, preferably in
the FormHeader section.

In the first ComboBox Wizard Dialog, select the 3rd option "Find a record on
my form based on the value I slected in the ComboBox".

The Wizard will create the necessary code for you.

If you want to use the ListBox, try to understand the code created by the
Wizard and modify it for the ListBox.

--
HTH
Van T. Dinh
MVP (Access)

Working on an application that has several hundred projects. Some projects

have change orders. There is a total of several hundred change orders.
Relationship is 1 project to many COs. I need a data entry/view/update form
that addresses 1 project and all of its change orders at one time. I want to
have the project info in a form and the CO info in a subform. I want to use
a combo box or list box to select a project from the list of all projects
and use the selected project to populate the form with the project info and
put all of the COs for the selected project into the subform. How do I get
the selected project number to automatically go to the right record in the
project table and select the related COs from the CO table?

 

[Guest]

Thanks Tina. I followed your instructions and the form built fine. It put the combo box in the header and did not build the normal record navigator at the bottom of the form. But when I run it and select a project from the combo box, it does not display the project/CO info for the selected project. The data displayed is for record 1. The only way to move beyond record 1 seems to be to click in the column to the left of the detail part of the form, then I can use the arrow keys to go to the next/previous record.

Any suggestions

Thanks

Tony

 

[tina]

did you use the wizard to build the combo box as i suggested, and did you
select the option "Find a record on my form based on the value I selected
in my combo box."?
if not, try that. if so, post the code that runs from the combo box so we
can see it.

Thanks Tina. I followed your instructions and the form built fine. It put

the combo box in the header and did not build the normal record navigator at
the bottom of the form. But when I run it and select a project from the
combo box, it does not display the project/CO info for the selected project.
The data displayed is for record 1. The only way to move beyond record 1
seems to be to click in the column to the left of the detail part of the
form, then I can use the arrow keys to go to the next/previous record.

 

[tina]

does the bound column in the combo box correspond to [Project Number]? and
is the project number a Number data type (in the table), or a Text data
type?

 

[Guest]

project number is a text data type
Don't know if the bound column in the combo box corresponds to [Project Number].
When I open the form in design view, "unbound" is in the combo box. The value
for bound column in the data tab is 1(I changed it to 0 and nothing different
seemed to happen)
The query pulls project number from the table and when I run the form, the combo
box lists the project numbers from the project table but the only thing that
happens when I select a specific number from the list is that the list goes away,
the selected number is left in the combo box and it is highlighted. There is no
change to the rest of the form.

 

[tina]

well, it sounds like it should be working, especially since you built it
with the wizard. i don't know what else to check "remotely". if your db is
A97 or higher, i can look at it and try to fix the problem, if you want.
*make a copy of the db*, remove proprietary data (but enter some
representative "dummy" data), zip to less than one MB (i have Winzip at this
end to unzip). reference the newsgroups in the email Subject line, copy the
posted thread - all of it, please - into the body and send to
ttacc_kill_allspam_ess1 at yahoo dot com, removing the underscores
and everything *between* them.

 

[Van T. Dinh]

--
HTH
Van T. Dinh
MVP (Access)

 

[Van T. Dinh]

Post the SQL String for the ROwSource of the ComboBox. Probably the
BoundColumn is incorrectly set.

 

[Guest]

Here is the SQL statement for the row source
SELECT [JobsTableMay02-04].[Project Number
FROM [JobsTableMay02-04];

 

[tina]

tony sent me his db and i had a look; though you might be interested in the
outcome. i found that he had changed his Bound Column setting to 0, but when
i changed it back to 1, the recordsetclone Find still didn't work. i
eventually changed the reference from

rs.FindFirst "[Project Number] = '" & Me![Combo29] & "'"

to

rs.FindFirst "[Project Number] = '" & Me![Combo29].Column(0) & "'"

now it works fine.
i've never had to refer to the bound column that way before, seemed strange
to me. but then he has unusual data in the primary key (text) field:
ann--nnnn. when i ran a break on the original code, the value showing for
Me![Combo29] looked correct, but just absolutely *would not* find the
matching record in the table. go figure.

Post the SQL String for the ROwSource of the ComboBox. Probably the
BoundColumn is incorrectly set.

--
HTH
Van T. Dinh
MVP (Access)

project number is a text data type.
Don't know if the bound column in the combo box corresponds to [Project Number].
When I open the form in design view, "unbound" is in the combo box. The value
for bound column in the data tab is 1(I changed it to 0 and nothing different
seemed to happen).
The query pulls project number from the table and when I run the form,

the

combo
box lists the project numbers from the project table but the only thing that
happens when I select a specific number from the list is that the list goes away,
the selected number is left in the combo box and it is highlighted.

There
is no

change to the rest of the form.

 

[Van T. Dinh]

That sounds strange but you obviously knew what you were doing & tried
different ways already.

Thanks for letting us know.