I am a Newbee to Access, and I want to enter data via a drop down lookup in a form cell Linked to a Table. When I do this the Number entered I want to have Text Stating the location of that numbered item show up in the Location Cell on the fill in form so that the Name of that Location does not have to be typed in. I am familiar with VlookUp in Excel but have NO idea how to do this in access.
Scooter172
See if this helps:
25 Cool Control Tricks
Boyd Trimmell aka HiTechCoach
Microsoft Access MVP -2010-2015
Programming: Nine different ways to do it right, a thousand ways to do it wrong.
Binary--it's as easy as 1-10-11
Sending a download of a 1997 Version of access workbook via a ;ink isnt much help when I am using Version 2007. And as stated in previous post "I AM A NEWBEE". Here is a simpler request, I open form A to enter data. I enter a Part Number, Upon doing so the next cell that would be filled in or tabbed through would auto populate with the location for that part number, based on the table that lists the part number in [tblParts] column "PartNum" and a second column named "Location". listing the location for said part number. The Primary key is the part number for [tblParts]. The question is... Is VBA required or is there an easier way to make this happen? If so can it be relayed in this forum rather than having me download something from another web sight via a link.
Do a search for DLookup.
In the text box make the value =DLookup("[LocationFieldName]", "[RelatedTable]", "PartID = '" & partID & "'"
Someday I'll understand everything...
Why is it not much help?Originally Posted by Scooter172
TIP: Just because you are using Acess 2007 does not mean you can not use examples create in previous versions. Just convert the database to your format.
You can use most example from Access 97 and later without issues in 2007 or 2010.
The reason a lot of example are in 97 or 2000 format is so that more people can use the same example.
I only post an example in 2007 or 2010 format (accdb) if there are feature that only work in the newer version. Otherwise I leave it in the lowest version that it will run it. You will find that this is very common.
There are lots of great examples for Access that were posted back in 2000 that still work great today with Access 2007 or 2010. Most of the VBA commands have not really changed much since the first version of Access.
I still use a lot of VBA code I wrote back in Access 2.0 with Access 2010!
Boyd Trimmell aka HiTechCoach
Microsoft Access MVP -2010-2015
Programming: Nine different ways to do it right, a thousand ways to do it wrong.
Binary--it's as easy as 1-10-11
will this work for a combo box, and do you make it as a expression? I tryed to sync combo boxs but the second box does not fill. The first cbo box has the correct drop down info but the second bos sits empty. any way here is the code I am using for the combo box. the idea is to drop down the etel number in comboo and this will auto fill the location for that phone. [vba]Private Sub cboLocation_AfterUpdate()
Me.cboLocation.RowSource = "SELECT Location FROM" & " Locations WHERE Number = " & Me.cbonumbers & " ORDER BY Location"
Me.cboLocation = Me.cboLocation.ItemData(0)
End Sub
[/vba]
Last edited by Scooter172; 10-23-2010 at 06:11 PM. Reason: adding attachment
Hi-Tech Coach, as a flight instructor I make no assumption when someone tells me they have flown a plane that they have the same ability that I as an instructor have. When I stated I am a NEWBEE it meant I AM BUILDING MY VERY FIRST ACCESS DATA BASE. Clerity is a wonderful thing.
Your flight instruction analogy is intriguing. Please consider you're attempting to pilot Access with little or no training. You can remedy that by studying Access Basics by Crystal at http://www.accessmvp.com/strive4peace/
You code is assumogn that the vaule in the cbonumbers contol is a numeric data type. Is that corect?
if not then try this:
[vba]Private Sub cboLocation_AfterUpdate()
Me.cboLocation.RowSource = "SELECT Locations.[Location] FROM Locations WHERE Locations.[Number] = " & Chr(34) & Me.cbonumbers & Chr(34) & " ORDER BY Locations.[Location];"
Me.cboLocation = Me.cboLocation.ItemData(0)
End Sub
[/vba]
Boyd Trimmell aka HiTechCoach
Microsoft Access MVP -2010-2015
Programming: Nine different ways to do it right, a thousand ways to do it wrong.
Binary--it's as easy as 1-10-11
Let me ask everyone here... When I submit a code sample are you more interested in sending links to your web sights outside of this forum, Or is this forum to help me understand the possible code error/s?
[VBA]Private Sub cbonumber_AfterUpdate()
Me.cboLocations.RowSource = "SELECT Location FROM" & " Locations WHERE Number = " & Me.cbonumber & " ORDER BY Location"
Me.cboLocations = Me.cboLocations.ItemData(0)
End Sub[/VBA]
This code was taken from a working access data base (Training Sample from Microsoft), I created tables with the information I wanted to query, all I did was change the name of the combo box and the tables to reflect the names of my tables, and Set the property settings to reflect those of the training data base for each combo box. Here is original code. [VBA]Private Sub cboCategories_AfterUpdate()
Me.cboProducts.RowSource = "SELECT ProductName FROM" & " Products WHERE CategoryID = " & Me.cboCategories & " ORDER BY ProductName"
Me.cboProducts = Me.cboProducts.ItemData(0)
End Sub
[/VBA]
All Kidding aside, I prefer only responses that test if my changes are in error, and if so where. If you want to send me to a link to download other Test Books please do not respond,, I am in a training course, but most Training courses show you only how to build tables and create forms using a wizard,,, they teach nothing about VBA or how to enter it, test it and find errors. VBA books I have read such as Power programming by John Walkenbach are very good and have taught me alot about Xcel VBA which I build and use daily. Books such as Learning Access visually have nice pictures, again on how to create a table or form using a wizard. Currently reading Access 2007 Inside Out by Microsoft. You see it is not a book/ or a link to a book I need, it is help to test and find errors, Unlike Excel errors in Access are less easy to spot. Hence coming to this sight for advice. Some one should write a book on VBA Writing and Error Locating for Access.
what do the Chr(34) mean... and yes the Numbers data is Numaric with no decamil
Over the past 7 years I have posted over 20,000 replies to help others. I have only asked TRHEE (3) questions in all that time.
After answering the same questions many times, I will write up an answer.
News groups do not allow attachments. Not all forums allow attachments. I needed a place to post attachments.
So I created a site where I can blog my answers to common questions and also have a place to download stuff.
There are some advantages to linking to external sites:
1) posting links to articles uses less space in the forum which has advantages
2) providing a link to an article that is being updated, you will always have the latest info when you go back to the post.
3) newsgroups and forums have very limited capabilities for presenting information.
Finally linking pages together is what the web is all about. It would not be much of a web if it did not link pages together.
If someone has already explained the topic very well then why try to rehash it or plagiarize it. I would just link to it.
Last edited by HiTechCoach; 10-24-2010 at 10:43 AM.
Boyd Trimmell aka HiTechCoach
Microsoft Access MVP -2010-2015
Programming: Nine different ways to do it right, a thousand ways to do it wrong.
Binary--it's as easy as 1-10-11
I am just really used to people wanting to see working examples. That is why I gave you the original link. I will avoid giving you any more links.
Note: It is very time consuming and length to explain Access form designs with just text.
Back to your original question:
I am confused by what you really want.
The code you are posting is for cascading combo boxes. Is that what you want?
I thnk you really just want to show a vaule related the a selection in the cbonumber control? Is this is correct then in the control source of a text box use a Dlookup() as previously suggested. You can also display the data from another column in the cbonumber combo box box by setting the control source to =cbonumber.column(X) where X is the index value for the column with the Location.
TIP: In the VBA editor, highlight a command then press F1 to get help on that command. (linked to VBA debugging removed)
Boyd Trimmell aka HiTechCoach
Microsoft Access MVP -2010-2015
Programming: Nine different ways to do it right, a thousand ways to do it wrong.
Binary--it's as easy as 1-10-11
Chr(34) is the Ascii for a single quote as the DLookup function will usually error out if you use a string variable without single quotes surrounding it.
So you can resolve the error by including the quotes in the code, or including the Chr(34) in the variable.
[VBA]DLookup("[Field]", "[Table]", "FieldName = '" & Variable & "'")
'Or
DLookup("[Field]", "[Table]", "FieldName = " & Chr(34) & Variable & Chr(34))[/VBA]
Scooter am I correct in assuming that for each number there will be only 1 location? If so:
[VBA]
- Set the combo box for the number field to 2 columns
- Set the width of your columns so that the 2nd column is 0 (i.e. hidden)
- Change the data source for your combo box so that it contains 2 fields, the number and it's corresponding location
- Create an after update event on the combo box to something like the following:
Me.txtLocation = Me.cboNumber.Columns(1)
[/VBA]
You don't need a 2nd combo box if only a single value can be returned anyway.
We are what we repeatedly do. Excellence, therefore, is not an act but a habit.
Aristotle
Posting Permissions
Reply With Quote
