Update Text Field

Ringers

Registered User.
Local time
Today, 15:32
Joined
Mar 4, 2003
Messages
54
I am attempting to populate text box Contact_Name. The contact name should be selected from the choices made in two combo boxes, cboBranch_Name and cboBranch_City.There are many different cities in a branch_name, but each city has one branch contact. All these fields are in one table, Branch.

I have been trying to populate the text box using a Dlookup. Is their another way to do it? Is this even a good way to? At the moment the text field is displaying a wildcard error. I have entered the Dlookup script in the rowsource section of the text box.The Dlookup script is as follows.

=Dlookup("[Branch_Contact]","Branch","[Branch_City]=" & [Forms]![MainForm]![cboBranch_City] & "And [Branch_Name]=" & Forms!MainForm!cboBranch_Name)

Any help with this would be great, as this has been driving me around the bend.
:)
 
I recommend you change your table structure a little but.

Here's an example - should all become clear from exploring this. No need for DLookup at all.
 

Attachments

This would work but what the other fields, address, postcode, state etc.. That mean would putting everyfield in an different table, in think thats bad design. Also there should be a way to retrieve data within an table to a text field. It shouldn't be this diffcult to do something so basic.
 
Mile's been doing quite awhile for this. Listen to him I'd do. And having seperate tables this type of data in is db good design.
 
Ringers said:
That mean would putting everyfield in an different table, in think thats bad design.

Each table should contain only one item of data. That is, everything in the table should be dependant upon the table's primary key.

As an example: Employees

If we had for example this table: *

tblEmployees
PayrollNumber
Forename
Surname
Grade
Department


Looking at this, fair enough, you have all the information relating to an employee but, on closer inspection, there are two fields that are not dependant upon the primary key (in this case, PayrollNumber.)

You can't associate grades or a department to the PayrollNumber so you would split these out into tables of their own.

Our new structure would become:

tblEmployees
PayrollNumber
Forename
Surname
GradeID
DepartmentID

tblGrades
GradeID

tblGrades
DepartmentID

You then relate the GradeID between the tblGrades and tblEmployees and between tblDepartments and tblEmployees. In both these cases the relationship would be a one-to-many relationship as one grade can relate to many employees in the same way that one department can have many employees.

This is taking a database to 2nd Normal Form. If you can get it to this level then you're database will already be in 1st Normal Form (or should be!)

The benefit to using the relationship model, also, is that, in my example, if the powers that be decided that Fish Sales wasn't an adequate name for the department as it didn't just sell fish but other seafood, they could change the name of the department in tblDepartments to Aquaculture Sales then the change would be reflected in all records that relate to tblDepartments so Joe Bloggs and his pals would become employees within the Aquaculture Sales and this negates the need to manually find and replace the department for all records in the database.



* Where the key is:

Table Name
Primary Key
Dependant Field
Foreign Key
 
The branch table is as follows, as far as i can tell all the fields are dependent on the PK. But if you think their is something wrong, let me know.

Table Branch

PK Branch_Id
Branch_Name
Branch_Phone
Branch_Contact
Branch_Address
Branch_City
Branch_State
FK Client_Id

A Client can have many different Branch e.g wal mart has many different locations around the states.

But each branch has one contact. The Branch_name can be different also, e.g kmart or Wal-Mart and within that the city must be chosen.

The selections are made in the combo boxes, which work but don't update the relevant text field.

Thanks for your advice with this.:p
 
The "no coding" method is to use a query with a join to the lookup table as the recordsource for the form. This type of query is referred to in Access help as an "autolookup query" if you need more of an explaination. When you choose a branchID from the combo, the rest of the fieles will "auto fill" without any coding required.
 
I already tried using an lookup.

=DLookUp("[Branch_Contact]","Branch","[Branch_City]=" & [ Forms]!MainForm!cboBranch_City & " And [Branch_Name]=" & Forms!MainForm!cboBranch_Name)

This just returns a wild card error. So i am really frustrated with this.
 
=Dlookup("[Branch_Contact]","Branch","[Branch_City]=[Forms]![MainForm]![cboBranch_City] And [Branch_Name]=Forms![MainForm]![cboBranch_Name])
 

Users who are viewing this thread

Back
Top Bottom