472,352 Members | 1,504 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,352 software developers and data experts.

can link one drop-down list to another?

Is it possible to link one drop-down box to another? For example, if a
name is chosen from a drop-down list, can another drop-down list then
automatically display the person's office as a result of the choice
made in the first drop-down list? If so, how can that be done? I am
working with 'Teach Yourself Microsoft Access' and I didn't see it
discussed in that book (if it's even possible to do). Or would I just
have to create linked tables in the database layout instead?

Also, is there a way to create a 'last updated' field automatically
populated by the date at which a record was last updated?

Finally, how do I specify in which database field a value for a
checkbox should be stored?

Thanks in advance for any help on these issues!

Nov 13 '05 #1
2 9210
On 19 May 2005 13:12:32 -0700, km*******@hotmail.com wrote:
Is it possible to link one drop-down box to another? For example, if a
name is chosen from a drop-down list, can another drop-down list then
automatically display the person's office as a result of the choice
made in the first drop-down list? If so, how can that be done? I am
working with 'Teach Yourself Microsoft Access' and I didn't see it
discussed in that book (if it's even possible to do). Or would I just
have to create linked tables in the database layout instead?

Also, is there a way to create a 'last updated' field automatically
populated by the date at which a record was last updated?

Finally, how do I specify in which database field a value for a
checkbox should be stored?

Thanks in advance for any help on these issues!


1) The person can have more than 1 office?
Leave the Rowsource of the second combo box blank.
Code the first Combo Box AfterUpdate event:
ComboTwo.Rowsource = "Select [FieldName] From TableName Where
[NameField] = '" & Me!ComboName "';"

Each person has only one office?
Instead of the second combo box, use an unbound text control.
Include the office as a 2nd column in the first combo box.
then code the combo AfterUpdate event:
TextControlName = Me!ComboName.Column(1)
where the column number is the number of the column that displays the
office. The Column number is zero based, so Column(1) is the 2nd
column.

2) Add a DateTime datatype field to your table
Name it [LastUpdated].

If the table is the form's recordsource, add that field to the form
from the Field List tool button.
If a query is the form's record source, add the field to the query
first, then add it to the form from the form's Field List tool button.

Code the Form's BeforeUpdate event:
If Me.Dirty = True then
Me![LastUpdated] = Now
End If

3) Add a Check box field to the table.
If the table is the form's recordsource, add the Check Box to the Form
from the form's Field List tool button.
If a query is the form's record source, add the field to the query
first, then add it to the form from the form's Field List tool button.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Nov 13 '05 #2
km*******@hotmail.com wrote:
Is it possible to link one drop-down box to another? For example, if a
name is chosen from a drop-down list, can another drop-down list then
automatically display the person's office as a result of the choice
made in the first drop-down list? If so, how can that be done? I am
working with 'Teach Yourself Microsoft Access' and I didn't see it
discussed in that book (if it's even possible to do). Or would I just
have to create linked tables in the database layout instead?


A simple example.

Given two tables:

TBL_PERSONNEL
PER_PK PER_FIRST_NAME PER_LAST_NAME
1 Joe Blow
2 Sally Silly
3 DP Gumby

TBL_OFFICE_ASSIGNMENT
OFF_PK OFF_PER_FK OFF_NUMBER
1 3 A-1000
2 3 A-2201
3 3 S-3456
4 1 A-2013
5 2 J-2341
6 2 B-1204A

In reality, you'd probably have a many to many relationship here with
three tables with office details listed once per office with one office
per record in a table of offices. The TBL_OFFFICE_ASSIGNMENT would have
a PK reference instead of the office number.

Anyway, make your first combo box based on the tbl_personnel table. Use
the builder to construct a row source (combo box properties window,
beside the "row source" property) that brings in the PER_PK and maybe
the combined name, PER_LAST_NAME & ", " & PER_FIRST_NAME. Do this by
constructing query when in the builder (it looks like a query design
window anyway) and bring down PER_PK as the first field and then make a
calculated field like this in the field cell:

Worker Name: PER_LAST_NAME & ", " & PER_FIRST_NAME

Close the query builder and say yes you want to make this the row source.

In the combo box properties window, go to format and indicate:

Column Count: 2 (this will be for the per_pk and worker name field)

Column Widths: 0; 2"

The 0 makes the first column (PER_PK) hidden - the user only sees the
work name.

So, you have PER_PK (which is hidden) and then the name.

Go to the "data" tab and find the "Bound column" property. Make sure it
is 1 (by default). It is the bound column that represents the value of
this combo box when a selection is made. In this case it will be
whatever the value of PER_PK is. For instance, if you choose Silly,
Sally, the value of the combo box will be 2.

Go to the name property of the properties window (click on the "Other"
tab) and give this combo box a name, say, cboName.

Now you're done the first combo box.

Next, create a second combo box. Use the builder to create a row source
(as described above). Use the table TBL_OFFICE_ASSIGNMENT.

Bring down OFF_NUMBER into the query design. Next bring down
OFF_PER_FK. this latter field is the "look up number" for the personnel
occupying the office.

Now, in the "show" check box beneath the OFF_PER_FK field, turn the
check mark off - you don't want anyone seeing this field, you just want
to show the office numbers the person selected in the first combo box.

Next, go to the criteria field immediately below the OFF_PER_FK field
and type in:

[Forms]![FrmYourFormName]![cboName]

This tells the row source of this second combo box to only show values
for the name selected in the combo box, cboName.

Name the second combo box cboOffices

You might think you're done, but there is one thing. Each time you make
a selection in the name combo box, cboName, you have to tell Access to
reevaluate what is showing in the second combo box. Some people do this
in the after-Update event of the first combo box, I prefer to do it in
the On Got Focus event of the second combo box.

In the properties window of the second combo box, cboOffices, click on
the "Event" tab. Find the "On Got Focus" event and click the builder
and choose code builder.

In the VBA screen, under "Private Sub cboOffices_GotFocus()" (Access
writes this for you), type in:

me.cboOffices.requery

This will tell the cboOffices combo box to reevaluate itself each time
you click on it.

Now you're done.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Peter Shankey | last post by:
Assuming I have a db link that an application uses once in a great while. How can it be coded so that the link is close when not in use and only...
9
by: Wang, Jay | last post by:
Hello, all, I would like to enable some text between <SPAN url="http://www.testserver.com/">WORD TO BE DRAGGED </SPAN>. I put some javascript and...
8
by: WindAndWaves | last post by:
Hi everyone, Has anyone got any experience with drop and drag in Access? I would like to make a calendar style form where my users can drop and...
4
by: kemal asad | last post by:
how can i create a vb application where i can drag and drop ( with a move caracteristic) email from outlook, into my application( email viewer)....
10
by: Geoff Jones | last post by:
Hi I'm trying to drop a table by using: Dim cmd As New OleDbCommand("DROP TABLE IF EXISTS books", myconnection) cmd.ExecuteNonQuery() but...
1
by: Just Me | last post by:
I noticed I could drag a filename from windowsexplorer onto a richtextbox and drop it as a Link and the result is a link on the box that open the...
1
by: Staal | last post by:
My C# application supports drag and drop of files, folders, shortcuts and links from other applications into mine. This all works pretty nicely,...
5
by: empiresolutions | last post by:
Hello Fellow Developers, I am using the awesome drag and drop script found at http://script.aculo.us/. I have also added a modification that...
5
by: Romulo NF | last post by:
Greetings, Im back here to show the new version of the drag & drop table columns (original script ). Ive found some issues with the old script,...
3
by: cold80 | last post by:
Hi guys, maybe it's a silly question, but I'm quite stuck with it... I've tried to implement a drag and drop operation from inside my c# program....
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
2
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
0
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.