Creating a Form for End User Info?

M

Michael M

Hello all, I am a “newer user†to the Access World and would like to know if
I’m going down the right path with this software for what I want it to do.

I currently have a table with the fields “Placeâ€, â€Panelâ€, â€Sublocationâ€,
“Circuit IDâ€
I also have a query built with the same information. I have a form that
displays the Place and Panel (which I would like to be the primary filters
for the search). Below that I have a graphical (squares) for each
sublocation and below that I have what would be the output field which is the
Circuit ID.

In the Form I would like to be able to enter in the Place and Panel like a
search, than be able to click the square “sublocation†and have the Circuit
ID associated to all 3 be populated in the Circuit ID output box. If this is
possible with Access I hate to say it but if you could hold my hand through
it I would greatly appreciate it!

Mike
 
T

tina

my guess is that you have multiple Places, and each place may have one or
more Panels, and each Panel may have one or more Sublocations, and each
Sublocation may have one or more Circuit IDs. so that a specific CircuitID
will be found at one specific Sublocation on one specific Panel at one
specific Place. is that correct?

if not, please explain how the data are related.

hth
 
R

roadie.girl

of course it can be done ... everything minus cloning sheep can be done
with access (well .. pretty much anyway)
from what i understand you want to do ..

you'll want to create your form first.
then you'll want to create 4 combo boxes (drop down boxes)
the first combo box you'll want to set the recordsource to be SELECT
DISTINCT Place from myTable
the second .. SELECT DISTINCT Panel from myTable
the thrid .. SELECT CircuitID from myTable where Panel = " &
mycombobox2 & " AND Place = " & mycombobox1

ok .. so that what i think you're trying to do .. if it's not, i hope
this puts you in the right direction.
 
M

Michael M

That's correct Tina.. I have an excel spreadsheet that's linked to a table.
Multiple Places/Panels/Sublocations
 
M

Michael M

Still haven't figured this part out "where Panel = " &
mycombobox2 & " AND Place = " & mycombobox1"

Where does this go?

I would like to be able to select Place than select Panel
than click and image that would represent a sublocation (like Jack 1-28 of
panel #)
and get the output of the Circuit ID...
 
T

tina

okay. since you're working from an external data source, i see no particular
reason to fuss about normalization issues in this situation. to create a
dynamic "grid" of Sublocations is probably do-able, but would require using
VBA code to populate the grid and then retrieve the one-or-more CircuitIDs
that are found at a specific Place/Panel/Sublocation, when a particular grid
position is "clicked". it would take time and effort to write and test such
code. (unless your post happens to be read by somebody who has already
created such a solution in the past, and shares it.) unless you're familiar
with VBA, or very experienced in a similar language like VB, it's probably
beyond your present skill level.

how invested are you in the concept of a grid? a much simpler solution would
be to employ a series of combo box controls, or listbox controls, or a
mixture of both, in a "cascade". for example: create a form (i'll call it
frmExcelSheet). the first combo box (i'll call it cboPlaces) lists all the
Places in the table (which i'll call tblExcelSheet), with a RowSource of

SELECT DISTINCT Place FROM tblExcelSheet ORDER BY Place;

second combo box (i'll call it cboPanels) lists all the Panels for the Place
selected in cboPlaces, with a RowSource of

SELECT DISTINCT Panel FROM tblExcelSheet WHERE
Place=[Forms]![frmExcelsheet]![cboPlaces] ORDER BY Panel;

you also need to add code to cboPanels' Enter event procedure, to requery
the combo box, as

Me!cboPanels.Requery

if you don't know how to create an event procedure, see the "Create a VBA
event procedure" instructions at
http://home.att.net/~california.db/instructions.html.

third combo box (i'll call it cboSublocations) lists all the Sublocations
for the Panel selected in cboPanels, with a RowSource of

SELECT DISTINCT Sublocation FROM tblExcelSheet WHERE
Panel=[Forms]![frmExcelsheet]![cboPanels] ORDER BY Sublocation;

and again, adding a Requery action to cboSublocations' Enter event
procedure, as

Me!cboSublocations.Requery

since (presumably) all the CircuitIDs at a given combination of
Place/Panel/Sublocation are unique, your last combo box (i'll call it
cboCircuitIDs) will have a slightly different RowSource, as

SELECT CircuitID FROM tblExcelSheet WHERE
Sublocation=[Forms]![frmExcelsheet]![cboSublocations] ORDER BY CircuitID;

you'll need to use a Requery action to update this control, as well. if
you're using a combo box control, you can requery on the Enter event, same
as the previous controls. but if you're using a listbox, you might want to
requery on cboSublocations' AfterUpdate event instead (though the code will
be the same regardless of which event you run it from). that way, as soon as
you choose a Sublocation, the CircuitIDs listbox values will be updated
appropriately.

remember that where i described combo box controls above, you can substitute
a listbox control instead. the RowSource is the same for either type of
control.

hth
 
M

Michael M

Thanks Tina you and Roadie.girl got my main problem out of the way.

Just wish I could jazz it up with what I would consider a graphic boost.

If anyone would happen to have that kind of VBA code "grid" or making a
button set up to pull the information down to a form I would appreciate it.



tina said:
okay. since you're working from an external data source, i see no particular
reason to fuss about normalization issues in this situation. to create a
dynamic "grid" of Sublocations is probably do-able, but would require using
VBA code to populate the grid and then retrieve the one-or-more CircuitIDs
that are found at a specific Place/Panel/Sublocation, when a particular grid
position is "clicked". it would take time and effort to write and test such
code. (unless your post happens to be read by somebody who has already
created such a solution in the past, and shares it.) unless you're familiar
with VBA, or very experienced in a similar language like VB, it's probably
beyond your present skill level.

how invested are you in the concept of a grid? a much simpler solution would
be to employ a series of combo box controls, or listbox controls, or a
mixture of both, in a "cascade". for example: create a form (i'll call it
frmExcelSheet). the first combo box (i'll call it cboPlaces) lists all the
Places in the table (which i'll call tblExcelSheet), with a RowSource of

SELECT DISTINCT Place FROM tblExcelSheet ORDER BY Place;

second combo box (i'll call it cboPanels) lists all the Panels for the Place
selected in cboPlaces, with a RowSource of

SELECT DISTINCT Panel FROM tblExcelSheet WHERE
Place=[Forms]![frmExcelsheet]![cboPlaces] ORDER BY Panel;

you also need to add code to cboPanels' Enter event procedure, to requery
the combo box, as

Me!cboPanels.Requery

if you don't know how to create an event procedure, see the "Create a VBA
event procedure" instructions at
http://home.att.net/~california.db/instructions.html.

third combo box (i'll call it cboSublocations) lists all the Sublocations
for the Panel selected in cboPanels, with a RowSource of

SELECT DISTINCT Sublocation FROM tblExcelSheet WHERE
Panel=[Forms]![frmExcelsheet]![cboPanels] ORDER BY Sublocation;

and again, adding a Requery action to cboSublocations' Enter event
procedure, as

Me!cboSublocations.Requery

since (presumably) all the CircuitIDs at a given combination of
Place/Panel/Sublocation are unique, your last combo box (i'll call it
cboCircuitIDs) will have a slightly different RowSource, as

SELECT CircuitID FROM tblExcelSheet WHERE
Sublocation=[Forms]![frmExcelsheet]![cboSublocations] ORDER BY CircuitID;

you'll need to use a Requery action to update this control, as well. if
you're using a combo box control, you can requery on the Enter event, same
as the previous controls. but if you're using a listbox, you might want to
requery on cboSublocations' AfterUpdate event instead (though the code will
be the same regardless of which event you run it from). that way, as soon as
you choose a Sublocation, the CircuitIDs listbox values will be updated
appropriately.

remember that where i described combo box controls above, you can substitute
a listbox control instead. the RowSource is the same for either type of
control.

hth


Michael M said:
That's correct Tina.. I have an excel spreadsheet that's linked to a table.
Multiple Places/Panels/Sublocations
Click to expand...
Click to expand...
 
Top