Combobox Example, using Listbox to also populate field, Mainform/Subform
---
Hi Luis,
you can do what you are asking by using code to generate SQL to append
records ... but you will also have to use code to show items selected on
the listbox if a previous order is pulled.
what I would recommend instead is a subform using a combobox to
drop-down and select just one item at a time. If you prefer to pick
things with a listbox (since it shows more), you may wish to put a
listbox in the (sub)form header or footer to populate the current record.
here is a generic example of how you can show columns from a combobox in
other controls:
Combobox Example
Store names in just one place. For instance, if you have a People
table, define a PID (or PeopleID) autonumber field. You will also have
fields for Firstname, Lastname, etc. Then, in other tables, when you
want to identify a person, you can use the key field. One way to do this…
Create an autonumber field in the People table -->
PID, autonumber
then, in the other tables...
PID, long, DefaultValue = Null
Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.
~~~~~
from the design view of a form, add a combobox control and give it a
logical name
Name --> PID
ControlSource --> PID
RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname
(you can click in the RowSource property, click on the Builder Button
(...) to the right and use a tool that works like the one you are
probably familar with for queries if SQL is not your strong suit)
BoundColumn --> 1
ColumnCount --> 3
columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)
ListWidth --> 3
(should add up to the sum of the column widths)
if you have a listbox, sometimes you need to make the width .01 more
than the sum of the columns to prevent the horizontal scrollbar from
appearing.
PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.
If you want to show other information from your combobox in other
controls, you can use calculated fields. For instance
textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)
The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access.
~~~~
if you would like to learn more about properties and SQL, email me and
request my 30-page Access Basics document -- be sure to put "Access
Basics" in the subject or I may miss your message
~~~~
now, since prices may change, you may wish to actually store the price
instead of just displaying it. For this, you can use the AfterUpdate
event of the combobox (and listbox if you go that route).
me.ItemPrice_controlname = me.combobox_controlname.column(#)
~~~
even though you may use a listbox, make the multi-select property = None
and make the Controlsource=ItemID (assuming your Items have an ID
associated with them as they should). Each listbox will just show one
item selected for each record, but you will have the list of what is
ordered because you will have multuiple records. The listbox would just
be an alternative way to pick items.
~~~
each form or subform should be based on just one table. to create a
subform, put a subform control on you mainform using the toolbox (Cancel
the wizard if it pops up and fill properties manually)
Then, from the design view of the main form
1. turn on the properties window
(r-click anywhere and choose Properties from the shortcut menu)
2. click ONE time on the subform control
3. click on the DATA tab of the Properties window
LinkMasterFields --> MainID
LinkChildFields --> MainID
If you have multiple fields, delimit the list with semi-colon
LinkMasterFields --> MainID;maincontrolname
LinkChildFields --> MainID;childcontrolname
where MainID is an autonumber field (usually) in the parent table and a
Long Integer field in the child table
the link field properties actually use controls, not fields -- so the
controls you reference must be ON each of the respective forms and the
NAME property is what you need to reference -- if a control is bound, I
usually make the Name of the control the same as the ControlSource (what
is in it)
It is common to set the Visible property to No for the control(s) used
in LinkChildFields
*** Difference between Subform Control and Subform ***
The first click on a subform control puts handles* around the subform
object.
*black squares in the corners and the middle of each size -- resizing
handles
The subform object has properties such as
Name
SourceObject
LinkMasterFields
LinkChildFields
Visible
Locked
Left
Top
Width
Height
the subform control is just a container for the subform.
the subform itself is an independent form -- you can open it directly
from the database window and it has the same properties of the main
form. It is only called a subform because of the way it is being used.
To summarize, when you are in the design view of the main form, the
first click on the subform is the subform control -- you will see the
handles around the edges -- and the second click gets you INTO it -- you
will see a black square where the rulers intersect in the upper left of
the "form" you are "in" (and this is the same as if you went to the
design directly)
me.subform.controlname --> the subform control
me.subform.controlname.form --> the form inside the subform control
Personally, I edit subforms directly, not from inside the main form -- I
have had trouble with Access putting property changes in the wrong place
for RowSources and RecordSource. Since it happens there occassionally,
for major changes, I go to the design view of the "sub"form directly
from the databse window when the main form is closed.
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
