0

I'm teaching myself Database design, using MS Access (2007). This question may have a basic answer, but remember, I'm just a learner and it's not so obvious to me.
I would like to select a Company either by 'Name' or 'ID' or 'Account Number' in a Form and have all that Company's details such as Address and Phone Numbers change and appear too.
I have tried to do it in a Table using a Look-up list but only the company name changes .
Thanks in advance

3
Contributors
5
Replies
6
Views
8 Years
Discussion Span
Last Post by touchngo
0

I may be misreading what you are seeking to do, but if you want to a) search for an entry in a table and b) return selected information about that entry, it sounds to me like you are seeking to perform what Access calls a Query. There should be a Query wizard in the tool bar that you can play with, or if you're familiar with SQL, you can write your own. If the Query is indeed what you'd like to be able to do, I can give you more information to help you out. =)

0

Thanks for your reply,
I have been using a Query but I still can't find a way to select a Company name and have the address change and show at the same time when I use a Form. I'm using a form based on a Query to create an Invoice.
For example...
If a supplier wants to bill a customer, the supplier would select that Customers name at the top of an Invoice (Form), this would then change and show all the Address details too, meaning they wouldn't have to type the Address too.
Sorry, I'm not sure how else to explain this, however I am sure a Query is the answer.

0

The way I've done this in the past is by using a dropdown box and then VBA to code what happens when a name is selected from the dropdown box. Here is the VBA:

Private Sub Combo1_Change()
  Dim s1 As String
  If ((Me!Combo1.Column(0) <> "") And Not IsNull(Me!Combo1.Column(0))) Then
    s1 = "Id = " & Me!Combo1.Column(0)
    Me.Recordset.FindFirst s1
  End If
End Sub

In this example, the row source of the combo box has to have the ID of the record as the first field. You can hide the first field and add the name as the second field; this will result in the user only seeing the name and searching by name, but the ID is available for coding purposes. Also, the ID in the combobox would refer to the ID of the record source of the form.

I'm not sure how familiar you are with VBA, so let me know if you need any more help.

0

Sorry, this is way beyond me, I'm good but not that good and certainly not as good as you. I'm afraid VBA and SQL are beyond my knowledge at the moment although I know they are important to effectively design a database. I'm still stuck, but thank you very much for all your efforts

0

Having difficulty getting my reply through, but I'm sorry VBA is way beyond me as is SQL. I had hoped there was a simpler way to link Fields and have them all show in a Form when one of the Fields was selected. I think my understanding is at the infancy stage, but I'll get there, I always do. So I'm still stuck, but thank you very muck for your help.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.