Selecting column entries based on data matching

  • Thread starter Thread starter Brutalius
  • Start date Start date
B

Brutalius

Hello,

I have a customer table with customer name and phone number. I also have an
order table which has customer name and phone number columns. I have created
a drop down selection for the customer name column in the order table, which
references the customer table. Is there a way for the phone number column in
the order table to automatically select the appropriate phone number from the
customer table, based on the customer name i selected?

Thanks!
 
Yes, there is an easy way to do this, but you really shouldn't.

One of the top rules in database design says "Thou shall not enter duplicate
data"

For example, if there is a phone number stored in the customer table, you
should never enter that phone number in any other table. Instead, use a form
to display the data, and lookup the phone number from the Customers table to
populate an unbound control on your Order form.

Reason being, if that phone number ever changes, then you have may have no
idea how many different places you stored that number, and updating the phone
number accurately in all places becomes a major issue. The major concept
behind relational database design is to avoid this.

Here is how you would handle the scenario:

Create a form based on your Orders table.

Put an unbound control on it.

If the Customer Primary Key is a String (text) datatype
Set the ControlSource property of the control to this:

=DLookup("fldPhoneNumber", "tblCustomers", "CustomerID = '" & Me.ctlCustomer
& "'")

If it is a Number datatype,
Set the ControlSource property of the control to this:

=DLookup("fldPhoneNumber", "tblCustomers", "CustomerID = " & Me.ctlCustomer)

This assumes that your Customers table is named "tblCustomers", your Phone
Number fields is named "fldPhoneNumbers", and the control on your Orders form
that contains the company is called "ctlCustomer"


That way, you only have one actual peice of data, that you can use anywhere,
and when you update it, it updates everywhere else (because you are only
referencing it).

The DLookup function looks up a value in a field where a certain criteria is
met. In your case, looks up "PhoneNumber" from "Customers" where the
Customer ID is equal to the Customer ID on your Orders form.

If you need further help implementing this, many of us here are willing to
help.


hth
-jack
 
Hi Jack,

You've recommended a much better approach. I didn't realize forms could use
control sources from more than one table. Thank you.

I've tried what you recommended but i'm getting a "#name" error in the
textbox. Here's what I did:

In the expression builder window that pops up under the control source
property for my textbox which will display the phone number, i put:

=DLookUp("ContactPhone","Contacts","ContactName = '" & [Me].[cmbContact] &
"'")

Thus, the name of the table is called 'Contacts', the field with phone
number is 'ContactPhone' and the field with the contact name is called
'ContactName'. ContactName is the primary key.

Any idea why the error occurs?
 
I just tried playin around with it on a form and couldnt get it using the
control source property (i usually do it through code and have to leave a
minute), but if you want to do it in code try this:

In the form's OnCurrent event:

Private Sub Form_Current
Me.ctlPhoneNum = DLookup("ContactPhone", "Contacts", "ContactName = "' &
Me.cbmContact & "'")
End Sub

I forget the Me keyword may not work in a controlsource.

Unfortunately I need to leave, didn't want to leave you hanging though.


--
Jack Leach
Tri-State Machine

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away."

www.tristatemachine.com


Brutalius said:
Hi Jack,

You've recommended a much better approach. I didn't realize forms could use
control sources from more than one table. Thank you.

I've tried what you recommended but i'm getting a "#name" error in the
textbox. Here's what I did:

In the expression builder window that pops up under the control source
property for my textbox which will display the phone number, i put:

=DLookUp("ContactPhone","Contacts","ContactName = '" & [Me].[cmbContact] &
"'")

Thus, the name of the table is called 'Contacts', the field with phone
number is 'ContactPhone' and the field with the contact name is called
'ContactName'. ContactName is the primary key.

Any idea why the error occurs?

Dymondjack said:
Yes, there is an easy way to do this, but you really shouldn't.

One of the top rules in database design says "Thou shall not enter duplicate
data"

For example, if there is a phone number stored in the customer table, you
should never enter that phone number in any other table. Instead, use a form
to display the data, and lookup the phone number from the Customers table to
populate an unbound control on your Order form.

Reason being, if that phone number ever changes, then you have may have no
idea how many different places you stored that number, and updating the phone
number accurately in all places becomes a major issue. The major concept
behind relational database design is to avoid this.

Here is how you would handle the scenario:

Create a form based on your Orders table.

Put an unbound control on it.

If the Customer Primary Key is a String (text) datatype
Set the ControlSource property of the control to this:

=DLookup("fldPhoneNumber", "tblCustomers", "CustomerID = '" & Me.ctlCustomer
& "'")

If it is a Number datatype,
Set the ControlSource property of the control to this:

=DLookup("fldPhoneNumber", "tblCustomers", "CustomerID = " & Me.ctlCustomer)

This assumes that your Customers table is named "tblCustomers", your Phone
Number fields is named "fldPhoneNumbers", and the control on your Orders form
that contains the company is called "ctlCustomer"


That way, you only have one actual peice of data, that you can use anywhere,
and when you update it, it updates everywhere else (because you are only
referencing it).

The DLookup function looks up a value in a field where a certain criteria is
met. In your case, looks up "PhoneNumber" from "Customers" where the
Customer ID is equal to the Customer ID on your Orders form.

If you need further help implementing this, many of us here are willing to
help.


hth
-jack
Click to expand...
Click to expand...
 
You the man Jack! It works great.

dymondjack said:
I just tried playin around with it on a form and couldnt get it using the
control source property (i usually do it through code and have to leave a
minute), but if you want to do it in code try this:

In the form's OnCurrent event:

Private Sub Form_Current
Me.ctlPhoneNum = DLookup("ContactPhone", "Contacts", "ContactName = "' &
Me.cbmContact & "'")
End Sub

I forget the Me keyword may not work in a controlsource.

Unfortunately I need to leave, didn't want to leave you hanging though.


--
Jack Leach
Tri-State Machine

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away."

www.tristatemachine.com


Brutalius said:
Hi Jack,

You've recommended a much better approach. I didn't realize forms could use
control sources from more than one table. Thank you.

I've tried what you recommended but i'm getting a "#name" error in the
textbox. Here's what I did:

In the expression builder window that pops up under the control source
property for my textbox which will display the phone number, i put:

=DLookUp("ContactPhone","Contacts","ContactName = '" & [Me].[cmbContact] &
"'")

Thus, the name of the table is called 'Contacts', the field with phone
number is 'ContactPhone' and the field with the contact name is called
'ContactName'. ContactName is the primary key.

Any idea why the error occurs?

Dymondjack said:
Yes, there is an easy way to do this, but you really shouldn't.

One of the top rules in database design says "Thou shall not enter duplicate
data"

For example, if there is a phone number stored in the customer table, you
should never enter that phone number in any other table. Instead, use a form
to display the data, and lookup the phone number from the Customers table to
populate an unbound control on your Order form.

Reason being, if that phone number ever changes, then you have may have no
idea how many different places you stored that number, and updating the phone
number accurately in all places becomes a major issue. The major concept
behind relational database design is to avoid this.

Here is how you would handle the scenario:

Create a form based on your Orders table.

Put an unbound control on it.

If the Customer Primary Key is a String (text) datatype
Set the ControlSource property of the control to this:

=DLookup("fldPhoneNumber", "tblCustomers", "CustomerID = '" & Me.ctlCustomer
& "'")

If it is a Number datatype,
Set the ControlSource property of the control to this:

=DLookup("fldPhoneNumber", "tblCustomers", "CustomerID = " & Me.ctlCustomer)

This assumes that your Customers table is named "tblCustomers", your Phone
Number fields is named "fldPhoneNumbers", and the control on your Orders form
that contains the company is called "ctlCustomer"


That way, you only have one actual peice of data, that you can use anywhere,
and when you update it, it updates everywhere else (because you are only
referencing it).

The DLookup function looks up a value in a field where a certain criteria is
met. In your case, looks up "PhoneNumber" from "Customers" where the
Customer ID is equal to the Customer ID on your Orders form.

If you need further help implementing this, many of us here are willing to
help.


hth
-jack

:

Hello,

I have a customer table with customer name and phone number. I also have an
order table which has customer name and phone number columns. I have created
a drop down selection for the customer name column in the order table, which
references the customer table. Is there a way for the phone number column in
the order table to automatically select the appropriate phone number from the
customer table, based on the customer name i selected?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Jack, if I can ask you one more question:

When I select a Contact from the drop down list on my order form, the phone
number pops up no problem thanks to your code.

Now I want to allow the user to enter a new contact via the same form. I
have attached code to the contact name control on the form to do this and it
works fine. The new name appears in my contact table. When the user enters
the new phone number, I also want this to go into the contact table. But I
can't figure this out. I tried adding the following code to the AfterUpdate
event for the Phone number control:

SQL = "UPDATE Contacts SET ContactPhone= '" & Me.cmbContactPhone & "' WHERE
ContactName = '" & Me.cmbContactName & "'"

I don't get any errors, but the phone number isn't written to the contact
table next to the new contact name. Any ideas what is going wrong?


Brutalius said:
You the man Jack! It works great.

dymondjack said:
I just tried playin around with it on a form and couldnt get it using the
control source property (i usually do it through code and have to leave a
minute), but if you want to do it in code try this:

In the form's OnCurrent event:

Private Sub Form_Current
Me.ctlPhoneNum = DLookup("ContactPhone", "Contacts", "ContactName = "' &
Me.cbmContact & "'")
End Sub

I forget the Me keyword may not work in a controlsource.

Unfortunately I need to leave, didn't want to leave you hanging though.


--
Jack Leach
Tri-State Machine

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away."

www.tristatemachine.com


Brutalius said:
Hi Jack,

You've recommended a much better approach. I didn't realize forms could use
control sources from more than one table. Thank you.

I've tried what you recommended but i'm getting a "#name" error in the
textbox. Here's what I did:

In the expression builder window that pops up under the control source
property for my textbox which will display the phone number, i put:

=DLookUp("ContactPhone","Contacts","ContactName = '" & [Me].[cmbContact] &
"'")

Thus, the name of the table is called 'Contacts', the field with phone
number is 'ContactPhone' and the field with the contact name is called
'ContactName'. ContactName is the primary key.

Any idea why the error occurs?

:

Yes, there is an easy way to do this, but you really shouldn't.

One of the top rules in database design says "Thou shall not enter duplicate
data"

For example, if there is a phone number stored in the customer table, you
should never enter that phone number in any other table. Instead, use a form
to display the data, and lookup the phone number from the Customers table to
populate an unbound control on your Order form.

Reason being, if that phone number ever changes, then you have may have no
idea how many different places you stored that number, and updating the phone
number accurately in all places becomes a major issue. The major concept
behind relational database design is to avoid this.

Here is how you would handle the scenario:

Create a form based on your Orders table.

Put an unbound control on it.

If the Customer Primary Key is a String (text) datatype
Set the ControlSource property of the control to this:

=DLookup("fldPhoneNumber", "tblCustomers", "CustomerID = '" & Me.ctlCustomer
& "'")

If it is a Number datatype,
Set the ControlSource property of the control to this:

=DLookup("fldPhoneNumber", "tblCustomers", "CustomerID = " & Me.ctlCustomer)

This assumes that your Customers table is named "tblCustomers", your Phone
Number fields is named "fldPhoneNumbers", and the control on your Orders form
that contains the company is called "ctlCustomer"


That way, you only have one actual peice of data, that you can use anywhere,
and when you update it, it updates everywhere else (because you are only
referencing it).

The DLookup function looks up a value in a field where a certain criteria is
met. In your case, looks up "PhoneNumber" from "Customers" where the
Customer ID is equal to the Customer ID on your Orders form.

If you need further help implementing this, many of us here are willing to
help.


hth
-jack

:

Hello,

I have a customer table with customer name and phone number. I also have an
order table which has customer name and phone number columns. I have created
a drop down selection for the customer name column in the order table, which
references the customer table. Is there a way for the phone number column in
the order table to automatically select the appropriate phone number from the
customer table, based on the customer name i selected?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Let me know what tables you are using for:

Customers
Orders
Contacts

These should be all seperate tables, with the following relationships:

Customers to Orders (one-to-many, based on CustomerID primary key in the
Customers and CustomerID forgein key in the Orders table)

and the same for Customers to Contacts (one customer-many contacts)
The new name appears in my contact table.
Click to expand...
Also, please post the code you have behind the control for ContactName

I'll need a better understanding of what is going on to advise any further.

--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery
 
Hi Jack,

Sorry for the confusion. There are only Contacts and Order tables. I
referred to customers by accident at the beginning, but I meant contacts. I
have a one-to-many relationship established between the contacts and orders
tables.

When I enter a new name into the ContactName combo box of the form, I use
the NotInList property to add that contact to the contact table. I found
this code below and it works fine:

---
Dim strSQL As String
Dim i As Integer
Dim Msg As String

Msg = "'" & NewData & "' is not a contact listed in the database." &
vbCr & vbCr
Msg = Msg & "Do you want to add this person as a new contact?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Project Number")
If i = vbYes Then
SQL = "INSERT INTO Contacts (ContactName) VALUES(""" & NewData & """)"
DBEngine(0)(0).Execute SQL, dbFailOnError
Response = acDataErrAdded
MsgBox "Please fill in contact phone number in appropriate field."
Else
Response = acDataErrContinue
End If
---

Also, for the ContactName combo box, I added the following code to the
AfterUpdate event such to display the appropriate phone number in the phone
number text box on the form.

---

Me.txtPhone = DLookup("ContactPhone", "Contacts", "ContactName = '" &
Me.cmbContactName & "'")

---

So, what i'd like to do now is allow the user to enter a new phone number in
the phoneNumber textbox on the form when a new contact is added.

Thanks again,

Mike
 
I am not very familiar with update queries, so I won't try and advise how to
do it using one of them, and there may be better way's that what I'm
suggesting below, but this example you can use to enter any values into any
particular table (or query) that you want. I would put it in the
BeforeUpdate event of the form, but you can play with that as well... the
code should work regardless of where it is located, as long as you have the
right values and the procedure is called from somewhere.

This example with open the table and add a new record using the DAO
Recordset object.

Private Sub Form_BeforeUpdate

Dim rs as DAO.Recordset
Set rs = CurrentDB.OpenRecordset("tablename")
With rs
.AddNew
.Fields("Field1") = Value 1
.Fields("Field2") = Value 2
.Fields("Field3") = Value 3 'etc, etc,
.Update
End With
rs.Close
Set rs = Nothing

End Sub


Each Field name is a column in your table (e.g. ID, Name, Number, whatever)
and each value is whatever you want to set that particular record value to.
You can use something like:
rs.Fields("Contacts") = Me.cboContacts

If you do this, ALWAYS close the recordset (rs.Close) and remove the
reference to rs (Set rs = Nothing). Failure to do so may cause some bugs
that can be very difficult to trace.


--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery
 
Back
Top