pushing data from existing record into new record..... 2

[lminmei]

I have a regular text box for client name input And have a combo box on the side that gives a lookup of all the clients. <br>
<br>
My dilemma:<br>
<br>
In a new record, as the user selects a name from the combo box, all the information (name, address, phone, etc) will be pushed into the new record's fields.<br>
Is this possible to do?<br>
<br>
Please help!!!

 

[nufather]

I had this same problem when I was creating my helpdesk database. What I did is created 2 tables 1 that I put in all my records in and the other that held all my information specific to that user(ie first , last , user id etc.) I created a relationship between them linking the Employee ID. So I created a form for logging in all the customer calls - when we ask the customer for their Employee ID - it then pulls the information for that user - FIrst, last etc. worked great for me.

 

[DougP]

Yes of course,<br>
In the combo box's AfterUpdate event<br>
Put this<br>
Me![Namefield] = Combo1.col(0)<br>
Do this for each column in your combo box.<br>
Where the (0) is the first column (1) is the second etc.<br>
Me![Namefield] is the name of your &quot;Name&quot; field on your form.<br>
And your form is connected to your table in the forms Properties &quot;Record source&quot;<br>
<br>
<br>

 

[nufather]

DougP I havnt done it that way before. But I am having a problem that I think that this might work for. I am wanting to select a tech from a combo box and when the tech is selected in the email field it automatically puts in the tech's email address. So if I select nufather as the &quot;tech&quot; from the combo box - <A HREF="mailto:nufather@netzero.net">nufather@netzero.net</A> automatically appears in the field &quot;email&quot; - any clues?

 

[DougP]

Yes<br>
Where is the e-mail address? in a table?<br>
<br>
Here is an option. Put the name and e-mail in the combo box.<br>
Then using the same principle as above put the second column in a text box i.e. Me!Email = combo1.col(1)<br>
<br>
Would you like to send that person and e-mail while we are on the subject?<br>
There is a wizard that will do it for you.<br>
Add a new button<br>
click the in &quot;Categories&quot; list on &quot;Report Operations&quot;<br>
Click &quot;Mail report&quot; in the &quot;Actions&quot; list<br>
click Next.<br>
follow prompts<br>
<br>
After its done you can edit the code it creates to make changes to the code like so<br>
<br>
----------------- Sample of what it creates and changes I made<br>
Private Sub Command7_Click()<br>
On Error GoTo Err_Command7_Click<br>
<br>
Dim stDocName As String<br>
<br>
stDocName = &quot;Daily PO's&quot;<br>
DoCmd.SendObject acReport, stDocName<br>
DoCmd.SendObject acSendNoObject, , acformattext, Me!Email, , , &quot;Subject goes here&quot;, &quot;Message here&quot;, False<br>
<br>
Exit_Command7_Click:<br>
Exit Sub<br>
<br>
Err_Command7_Click:<br>
MsgBox Err.Description<br>
Resume Exit_Command7_Click<br>
<br>
End Sub<br>
---------------------------------------<br>
the first docmd line Access created the second one I added.<br>
<br>
<br>

 

[elizabeth]

I just want to warn you that although you may want to display all the fields corresponding to a person's ID on a form, you would *not* want to copy all that data into another table. By duplicating the data you de-normalize your database, violating data integrity rules. This can create all sorts of problems. For example, say a person's phone number changes. If you have the phone number in more than one place, you have to correct or update it in every location. Not only is this a lot more work than updating it in one place; if you accidentally correct it in some but not all cases, you may have no way of knowing which one is correct.<br>
<br>
Instead, store only the foriegn key (ID) in the second table. The underlying query for the form can still *display* all the other data for that person. If you write a query or report or another form you can go to that original table for all the data you need.