473,554 Members | 2,841 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 9452
On 19 May 2005 13:12:32 -0700, km*******@hotma il.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.Rowsou rce = "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.Co lumn(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*******@hotma il.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_ASSI GNMENT
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_ASS IGNMENT 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_ASSI GNMENT.

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_GotF ocus()" (Access
writes this for you), type in:

me.cboOffices.r equery

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
3787
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 openned when it is needed? Thanks
9
6793
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 it will extract http://www.testserver.com/ from the the span element when I select the whole text in the SPAN and drag it. However, I want to drag it without have to select the words between the...
8
20821
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 drag appointments.... I am using Access 2003 and I have access to .net and the like, but I would really like to keep it simple.
4
2055
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). thanks, any help is welcome, samples a great way of learning. Kemal
10
22724
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 I get a syntax error: "Syntax error in DROP TABLE or DROP INDEX"
1
1424
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 file if I click it. Is that the whole story. Are links only for opening files into their app? I also wonder why the effect All includes Scroll, Copy, and Move but does not include Link?
1
3270
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, except that I am trying to get a little more information when a web link is dropped onto my application from Internet Explorer. When you drag the link in the Address bar of Internet Explorer to...
5
2256
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 interacts to a db for reordering upon release of a dragable item. Within each dragable is a input checkbox. This checkbox holds a DB id value that is sent on submit. This process works fine in IE, but in...
5
13746
by: Romulo NF | last post by:
Greetings, I´m back here to show the new version of the drag & drop table columns (original script ). I´ve found some issues with the old script, specially when trying to use 2 tables with drag&drop on the same page (which was not possible). Now i´ve a new concept of the script, more object oriented. I´ve also commented the whole code so you...
3
2733
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. What I would like to do is allow to copy a link to an Outlook message from my application. So I use: button1.DoDragDrop("http://www.google.com", DragDropEffects.Copy); The text string is copied...
0
7600
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7521
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8042
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7889
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5436
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3560
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3548
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2020
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1134
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.