Automaticaly fill in form fields

[Straw]

I am trying to figure out how some fields can automatically be filled in on a form based on an ID number selected from a drop down list.

For instance, let's say that Based on a JobNumber selected from a drop down I would like the SalesRepname, SalesRepcity, SalesReptype automatically filled in. This information is stored in a table that is updated every night from another database.

Table that imports info from another database fields are:

JobNumber
SalesRepname
SalesRepCity
SalesReptype
SaleType
SaleAmount

Then the form:
You select the Job number and SalesRepname,SalesRepcity, SalesReptype are copied over into the form.

However this is not the only information on the form. There are other fields that will be filled in by data entry by the user.


Once this information is automatically into the form, I want to store this information in the underlying table of the form. Therefore it cannot just link to the original table, it must be copied over to the new place.

What is the best way to do this. Query, Macro, write code?? Any help would be greatly appreciated.

Thanks in advance!!

 

[grnzbra]

I believe I'd use code. In the on update event (if it doesn't work just right, try other events) write code that opens a recordset consisting of the desired fields where the field being selected equals the dropdown value. Note: if the dropdown value places a key in a table and displays something else (CityID, City where, let's say, CityID = 23 and City = New York, you see New York in the dropdown, but 23 goes in the underlying table), you may need to specify the text property of the dropdown. In any event, when you've gotten the recordset record you're looking for, just set the form fields equal to the corresponding fields in the reocordset.

 

[Straw]

So this is like a SELECT WHERE statement? Is that right.

Something like this for Sales Rep Name:

SELECT [SalesRepName] from [tblImport] Where [JobNumber]=[Job]
Me![SalesRep]=[SalesRepName]

Am I anywhere close on this. I really do not know how to write VB. Sometimes I can manage to stumble my way through

 

[Spott]

What about the DLookup Function? I have a form where the user selects a step that is being done, the form fills in the workcenter based on the step. Control Source for the text box is:
=DLookUp("[wopr_opr_grp_1]","informix_wipopr"," [wopr_oper]=[Step]&quot

On Step box is a macro "refresh form" that runs "on change"

This works for me because those are fields that are updated from tables linked into this db. If your folks need to be able to edit this information, you'll need a different solution.

 

[Straw]

Spott,
I looked at what you wrote and I think that the Dlookup might work for what I am trying to accomplish. However, I was not quite sure I understood the part that you were talking about how you have the macro set up. Is the Dlookup function in the macro or is it an expression that is attached to the "Step" box on your form.

I did try to set up a Dlookup function before with no success.

Thanks for your help!
Straw