# Solved: Access DB -Autopopulate a field from a secondary table.



## StumpedTechy (Jul 7, 2004)

Okay I hope this will be easy.

I have a form that links to a Table. We input information into this form it then dumps the information into the table. Lets call this table Table 1. We also have a second table that has all of our facilities information (I.E. Site name, location, phone number, and whatnot). Lets call this table Table 2.

Now on Table 1 there is a field that has a listbox of the Site Names you click on it get the drop down and pick the location you want. What it DOESN'T do is the following.

I have a phone number field in Table 2 that I want to auto-populate the phone number field of Table 1 depending on the site picked in the listbox where this listbox reffences the same table 2 I want the phone number from. 

EG Table 2 has Site A in the first column and hone number A in the second column, Site B phone number B, Site C and phone number C and so fourth. I want to pick on Table 1 the Site A and have Phone number A auto populate from Table 2's Phone number A and so fourth.

Can this be done fairly easily?


----------



## cristobal03 (Aug 5, 2005)

You should really be using forms for this kind of thing. Data entry, I mean. Putting data directly into a table is begging for fat fingers and other kinds of data corruption. Moreover--as you've discovered--there is no way to programmatically enhance a table. So the answer is no, you cannot do this kind of thing with a table, but it would be fairly simple with a form.

I wonder if your database has normalization issues, however. There should be no reason to duplicate data. You can use queries to pull significant data from multiple sources if necessary; you do not need to store the same value more than once.

chris.


----------



## StumpedTechy (Jul 7, 2004)

Um as I said we have a form. I am just directly in the table right now because I have over 5 months of inputs I have to do and using the Table vrs the form saves me MANY steps. I.E. the clicking on the new form button,clicking on the save button, and the brief pauses for it to propogate the information from the form to the DB.

Can you tell me WHERE in the form you make the change for the form to get the information from Table that has our facilities are on and then can dump the same phone number into the form as the facility we choose in the facility listbox?

The reason this has ot be duplicated into a second table instead of linked is this is an autopopulation field that can then be modified by us if the call back number is NOT the number of the facility.

I.E. I have a person at Site B but is calling about Site A. If I pick Site A it will auto populate with Site A's phone number (as most calls will have that call back number) but I need to be able to modify it to what I need for the record.

This is why I can't do a 1 for 1 link but I just need some kind of auto-population.

If this can be done in a form format can you tell me how?


----------



## cristobal03 (Aug 5, 2005)

Well, you'd have a really simple form with a listbox of the facilities and a combobox or textbox set to display phone numbers. Then, in the listbox's *AfterUpdate* event, you can either set the combobox's *RowSource* property to query a list of phone numbers for that facility from your *Table 1*, or pull a single phone number (if only one exists per facility). Then you might have a "Save" command button that inserts the value of the combobox or textbox into a record in your *Table 2*.

That's just one way to do it.

chris.


----------



## StumpedTechy (Jul 7, 2004)

> or pull a single phone number (if only one exists per facility)


How do I do this?

I only have the expression builder, code builder, and whatever option and I don't know how to use any of the 3.

I can't make the phone linst a list box or a combo box as it has to be inputable in case the number is not another site or is not one we currently have in the DB.


----------



## OBP (Mar 8, 2005)

The combo box list is based on the phone numbers in the Table 2 and yo put the selection in the Phone field where you want to store the phone number in table 1 using VBA. If the number is not on the list you an still type the number in to the Phone field.


----------



## cristobal03 (Aug 5, 2005)

Hmm. Well, the pull method you asked about requires some intermediate VBA. You'd create a recordset object of the proper record from *Table 1* and insert the value of the queried phone number into the textbox control. It might look something like this airware:


```
Private Sub lstFacilities_AfterUpdate()
  Dim rst As DAO.Recordset

  Set rst = Access.Application.CurrentDb.OpenRecordset( _
            "SELECT PhoneNumber " & _
            "FROM [Table 1] " & _
            "WHERE FacilityName = '" & Me.lstFacilities.Value & "'")

  With rst
    If .EOF = False Then
      Me.txtPhoneNumber.Value = !PhoneNumber
    Else
      MsgBox "No phone number exists for the selected facility!", _
             vbCritical + vbOkOnly, _
             "Error"
    End If
  End With

  Set rst = Nothing
End Sub
```
chris.


----------



## StumpedTechy (Jul 7, 2004)

Ouch making my head hurt.

Here is a copy of the DB. I stripped out all the data but left the functionality just as it was originally designed.

I want the requestor phone to be tied to the family center but also to be manually inputable in case of a number change not in the DB or if the user is at another site.

I think if I applied myself to this I could eventually get it.... bnut right now it changed from what I was hoping was easy to "blah blah blah"

The funny thing is I can VB script for PC access very well... You would think this would be right along the same lines but it looks entirely different.

Thanks again for the help

The form is Order Entry
The table that the information reside in is Centers
The table I want the information to be stored in is Order Entry


----------



## OBP (Mar 8, 2005)

Ok, I have added a simple query to list the centers and their phone numbers. This supplies the new combo box on your form with it's data. If you select one of the phone numbers it puts it in the Requestors phone number field. It just uses some very simple VB in the Combo box's "After Update" event procedure. You can still overwrite the data in the Requestors phone field or just type the data straight in.


----------



## StumpedTechy (Jul 7, 2004)

Hrm it looks good until I want to save then it barks at me about updating a primary key? Also why can't this just link to the family center selection instead of adding a Select Requestor phone field?


----------



## OBP (Mar 8, 2005)

When you say link to the family center selection, the new combo effectively replaces your Center selection combo as it is no longer required. You can modify your combo if you like by adding a second column which holds the phone number and use the VB from my combo, but you will have to change the column(2) in the code to Column(1).
I do not know what the error message is except there isn't a value going in to the "Request ID " field.


----------



## StumpedTechy (Jul 7, 2004)

Okay that was the problem I didn't give you the Autoincrement version this was added to another revision of this DB. I'll look into modifying the current DB with what you gave me. Tks.


----------



## StumpedTechy (Jul 7, 2004)

Just an FYI update -

I copied the query but took out the address field. Changed the VB code to point to the Family_Center instead of Combo48 and linked it to the Family_Center combo box instead of the one you made lastly I changed the column to 1 instead of 2 (since there was no longer an address field in the query) I modified the shown fields to 2. On my original DB and it all works fine now... doesn't look as pretty with the side scroll bar in the sites field.... but it does the job. Thanks for the help.


----------



## OBP (Mar 8, 2005)

Great work.
Can you mark this thread as "Solved" using the thread tools at the top of the page?


----------

