Updating field when inputting into another????

[joolsUK0575]

Hi there

I have a database that has two tables.

Table 1 is called applications and table 2 is called contacts.

Table 1 has two fields that relate (not through the relationships) to table 2.

These fields are named thus: Officer and ContactTelephone

Through a form I wish to be able to do the following.

When I choose the Officer from the drop down box (combo box tied into Table2) it will then automatically put in to ContactTelephone the necesary number.

It MUST drag the info from Table 2 and place in to Table 1.

Any ideas

Thanks

Jools

 

[BLeslie88]

jools.

officer is in both tables...?

So when they select an application an officer ties to it.?

What exactly are you trying to acheive.?

At any rate after selecting the officer in a combobox. you can run a search on the after update event and update any controls on the form.

How would you know if more then one officer had the same name.?

You may have conflicts, unless you have a OfficerID field... ?

 

[suepowell]

Why do you need to have the number in table one, is the number likely to change for that officer, and you would still need to know which number you called on a particular occasion.

If not then you probably don't need to store the number in table 1, just relate the two tables in any forms or reports where you need the phone number in addition to the datt in table one.

Sue

 

[joolsUK0575]

Hi Folks

OK, I found a nice little piece of code that would appear to do what I want. Trouble is I don't know what bit of the code relates to a form control, column etc

The code is

Sub Zip_OnExit(Cancel As Integer)
Dim varState, varCity As Variant
varState = DLookup("State", "tblZipCode", "ZipCode =[Zip] ")
varCity = DLookup("City", "tblZipCode", "ZipCode =[Zip] ")
If (Not IsNull(varState)) Then Me![State] = varState
If (Not IsNull(varCity)) Then Me![City] = varCity

How would I change this for my own benefit. Could someone explain what is what in the code?

I think from looking at it that the varState = DLookup("State", "tblZipCode", "ZipCode =[Zip] ") part relates to the table

Does the If (Not IsNull(varState)) Then Me![State] = varState relate to what the boxes are named on the form



Help!

Thanks

Jools

 

[joolsUK0575]

Thanks for that.

BUT

How in the heck does this all work. Obviously no code would be desirable. The method described as "Good" looks very nice but I couldn't work out what was going on.

Sorry to be so thick on this

Jools