Populate a field automatically

[ccoffelt]

I have a Access 2003 database that includes the following fields:

Drawing Type
Equip. #
Equip. Name
Drawing #
Revision #
Title
Location

When I enter the Equip. # I want it to automatically put in the Equip. Name. Example: I type in OPR-001 and it automatically puts in the name Sensor Heating Oven in the Equip. Name column. Any suggestions?

 

[jedraw]

Here is a reference to relational database design info.
I think this would be a good place to start.

Your question relates to forms and lookup tables.

http://r937.com/relational.html

 

[ProgramError]

if you use a form to enter your data you could use a lookup table to find opr-001 in one field and returns sensor heating oven as a result from a field on the same record.
take a look at dlookup or use a combobox with multiple columns and use the columns(number) metod to retreieve the data.


Ian Mayor (UK)
Program Error
Your lack of planning is not my emergency!

 

[Remou]

There are dedicated fora, for example, forum705. It is best to ask there.

 

[ccoffelt]

Thanks ProgramError. I took your advice. I can't get the dlookup function to work though. This access stuff is new to me so I'm not sure if I am doing it right or not. Can you give me a hand?

tbl_Equipment
EquipmentID
EquipmentNumber
EquipmentName

On the form (called frm_Drawing) I put a combo box in for EquipmentNumber.

Then I made an unbound text field on the form for EquipmentName. In the Control Source I entered the following:

=DLookUp("EquipmentName","tbl_Equipment","EquipmentNumber=" & Forms![frm_Drawing]!EquipmentNumber)

 

[ProgramError]

Forget Dlookup in this instance. It would normally be used where tables have no relationships and your table should have relationships. [red]I suggest you do take note of jedraw's advice and learn about relational databases, the way you are doing it isn't the norm.[/red]

But if you still want to persue it this, here's a way.

I'm assuming your combo box source is based on a table or query and contains multiple columns
ie EquipmentID,EquipmentNumber,EquipmentName
1 o123 heater
2 p231 tap
3 xyz another item

you can use the matching statement from the list below to fill the textboxes.

Me.NameOfTheComboBox.coloumn(0) will return the equipmentID
Me.NameOfTheComboBox.coloumn(1) will return the equipmentnumber
and
Me.NameOfTheComboBox.coloumn(2) will return the equipment name

put these as the source of the unbound textboxes

You will also have to create an afterupdate routine to re-populate the textboxes when a different equipmentId is selected in the comboBox.

It would be far easier to work on and more managable if you could sort out your databases relationships.
There are forums on this site dedicated to this subject.


Ian Mayor (UK)
Program Error
Your lack of planning is not my emergency!

 

[ccoffelt]

I figured it out. Thanks