A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Drop Down List



 
 
Thread Tools Display Modes
  #1  
Old December 8th, 2008, 02:46 AM posted to microsoft.public.access.tablesdbdesign
CATaylor
external usenet poster
 
Posts: 1
Default Drop Down List

I am designing a database to trace bid request info. I have created a
(table)"project list" containing project name & project location, and I have
created a (table)"general contractor list" containing gc name & other info.
I have then created a (table) "bid request list" in which I have been able to
show drop downs in the project name & gc name fields, but it will not let me
select this info. It either skips right over it, leaving the field blank, or
resorts to auto entering the 1st one alphabetically even tho it's not what
I've selected. What am I doing wrong? I know it's something in the design.
Need some help. Is it a query?
  #2  
Old December 8th, 2008, 03:36 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Drop Down List

On Sun, 7 Dec 2008 17:46:10 -0800, CATaylor
wrote:

I am designing a database to trace bid request info. I have created a
(table)"project list" containing project name & project location, and I have
created a (table)"general contractor list" containing gc name & other info.
I have then created a (table) "bid request list" in which I have been able to
show drop downs in the project name & gc name fields, but it will not let me
select this info. It either skips right over it, leaving the field blank, or
resorts to auto entering the 1st one alphabetically even tho it's not what
I've selected. What am I doing wrong? I know it's something in the design.
Need some help. Is it a query?


First off... don't use Table datasheets for data entry. They are VERY limited,
even when you use Microsoft's misdesigned, misleading, and again very limited
Lookup Field misfeature (http://www.mvps.org/access/lookupfields.htm).

Instead, use a Form based on your table. Put combo boxes on the *form*, not in
the table.

There's no way to tell from your post what you did wrong, but look at the
combo's Row Source (the query *from which it gets its data*), Bound Column
(which field in that query is the value of the combo box), and COntrol Source
(the field into which the selected value will be stored).
--

John W. Vinson [MVP]
  #3  
Old December 8th, 2008, 03:41 AM posted to microsoft.public.access.tablesdbdesign
tina
external usenet poster
 
Posts: 1,997
Default Drop Down List

two things you need to fix right off the bat:

1) get rid of the Lookup fields in the linking table "bid request list". to
do that, open the table in Design view, select each field in turn and select
the Lookup tab in the bottom portion of the window, then change the
DisplayControl setting to Textbox. for more information on *why* you should
do this, see http://www.mvps.org/access/lookupfields.htm.

1.5) make sure one field in the table is the same data type as the *primary
key field* of tblProjectList (NOT the project name field), and make sure the
other field is the same data type as the *primary key field* of
tblGeneralContractorList (again, NOT the gc name field). these two fields
are "foreign key fields" linking back to those primary key fields in their
respective "parent" tables.

2) don't do the data entry directly in tables; *forms* are the correct venue
for adding/editing/reviewing/deleting records. build a form with
tblBidRequestList as the RecordSource. add a combobox control, set its'
ControlSource to the foreign key field that links to tblProjectList. set
its' RowSource to tblProjectList. you want the combobox control to *show*
the project names, but *store* the project primary key value in
tblBidRequestList. so read up on the following properties of the combobox
control, so you'll understand how it works, and come back with specific
questions if you run into difficulties there.

once you have the first combobox control set up and working correctly, add a
second combobox control to the form, with ControlSource set to the foreign
key field that links to tblGeneralContractorList, and RowSource set to
tblGeneralContractorList. set it up like the other one, to *show* the gc
names, but *store* the primary key value.

and finally, if talk of primary and foreign keys is unfamiliar to you, then
recommend you read up on relational design principles, so you'll understand
how to make Access work for you, and find it easier to understand the
answers you get here in the ngs. for more information, see
http://home.att.net/~california.db/tips.html#aTip1.

hth


"CATaylor" wrote in message
news
I am designing a database to trace bid request info. I have created a
(table)"project list" containing project name & project location, and I

have
created a (table)"general contractor list" containing gc name & other

info.
I have then created a (table) "bid request list" in which I have been able

to
show drop downs in the project name & gc name fields, but it will not let

me
select this info. It either skips right over it, leaving the field blank,

or
resorts to auto entering the 1st one alphabetically even tho it's not what
I've selected. What am I doing wrong? I know it's something in the

design.
Need some help. Is it a query?



  #4  
Old December 8th, 2008, 04:26 AM posted to microsoft.public.access.tablesdbdesign
tina
external usenet poster
 
Posts: 1,997
Default Drop Down List

so read up on the following properties of the combobox
control


oops! those would be:

RowSourceType
RowSource
ColumnCount
ColumnHeads*
ColumnWidths
BoundColumn*
ListRows*
ListWidth
LimitToList
AutoExpand*

the asterisk (*) indicates the properties you'll probably most often leave
at the default setting.

hth


"tina" wrote in message
...
two things you need to fix right off the bat:

1) get rid of the Lookup fields in the linking table "bid request list".

to
do that, open the table in Design view, select each field in turn and

select
the Lookup tab in the bottom portion of the window, then change the
DisplayControl setting to Textbox. for more information on *why* you

should
do this, see http://www.mvps.org/access/lookupfields.htm.

1.5) make sure one field in the table is the same data type as the

*primary
key field* of tblProjectList (NOT the project name field), and make sure

the
other field is the same data type as the *primary key field* of
tblGeneralContractorList (again, NOT the gc name field). these two fields
are "foreign key fields" linking back to those primary key fields in their
respective "parent" tables.

2) don't do the data entry directly in tables; *forms* are the correct

venue
for adding/editing/reviewing/deleting records. build a form with
tblBidRequestList as the RecordSource. add a combobox control, set its'
ControlSource to the foreign key field that links to tblProjectList. set
its' RowSource to tblProjectList. you want the combobox control to *show*
the project names, but *store* the project primary key value in
tblBidRequestList. so read up on the following properties of the combobox
control, so you'll understand how it works, and come back with specific
questions if you run into difficulties there.

once you have the first combobox control set up and working correctly, add

a
second combobox control to the form, with ControlSource set to the foreign
key field that links to tblGeneralContractorList, and RowSource set to
tblGeneralContractorList. set it up like the other one, to *show* the gc
names, but *store* the primary key value.

and finally, if talk of primary and foreign keys is unfamiliar to you,

then
recommend you read up on relational design principles, so you'll

understand
how to make Access work for you, and find it easier to understand the
answers you get here in the ngs. for more information, see
http://home.att.net/~california.db/tips.html#aTip1.

hth


"CATaylor" wrote in message
news
I am designing a database to trace bid request info. I have created a
(table)"project list" containing project name & project location, and I

have
created a (table)"general contractor list" containing gc name & other

info.
I have then created a (table) "bid request list" in which I have been

able
to
show drop downs in the project name & gc name fields, but it will not

let
me
select this info. It either skips right over it, leaving the field

blank,
or
resorts to auto entering the 1st one alphabetically even tho it's not

what
I've selected. What am I doing wrong? I know it's something in the

design.
Need some help. Is it a query?





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:41 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.