Hello,



I am trying to accomplish a seemingly simple thing in Access, but I can?t

figure out how to do it:



In a data entry form, I would like to be able to enter a value in a control

(probably a combo box), have that value look up a record in another table,

and copy the values of other fields in that record into fields on the data

entry form.  I don?t just want to SEE the values of these fields; I 

actually

want to copy them into my form?s table, so that I can store them 

independent

of the source table.



For example, I am constructing an INVOICE form used to create invoices 

which

has an INVOICE LINE ITEM subform for entering individual products to be

included on the invoice.  When working in the INVOICE LINE ITEM subform, 

the

user enters a PRODUCT CODE (or uses a combo box to find it).  The PRODUCT

CODE performs a lookup on a PRODUCT table which contains the PRODUCT CODE,

PRODUCT DESCRIPTION, and PRODUCT PRICE.  I would like to be able to copy 

the

PRODUCT DESCRIPTION and PRODUCT PRICE into my INVOICE LINE ITEM table so

that I can make adjustments to the price and description.



As far as I can tell I can?t use the DLOOKUP function because that simply

displays the data from the other table, where I need to copy the data into

new fields.



On doing some research I have come across the AfterUpdate Event Facility, 

but I am not entirely sure how to use this part of access. 



Can Anyone Help?



Thanx In Advance!

Hi Aaron



with events you're getting into VB and that's the way that I would do it to

get data on a form, modify that data then add it to another table.



I have something similar in my own database where I choose a packing

specification and the system offers me the breakdown of packaging materials

it thinks I should use.



I then change whichever ones I want to before accepting the data.  The

amended(or not) values are added to the main table.



To do this I have a form which is not based on a table but has text boxes

for each of the fields I want to see/add.

A combo box on the form allows me to choose from the packspec table and the

afterupdate event for the combo box then triggers a set of code to populate

the text boxes on the form from the packspec table.



I make any changes that I want then click on the OK button and that has VB

attached to its "On Click" event.



The code in both cases involves recordsets:_



dim rst1 as adodb.recordset

set rst1 = new adodb.recordset

rst1.open "select * from tblpackspecs", currentproject.connection etc

		(this opens a recordset based on the packspec table but only

showing the record chosen in the combo box.



with rst

.find "packspeckid ='" & cmbpackspec & "'"

me!txt1 = !field1

me!txt2 = !field2

		(populate the fields from the recordset)



.close

end with

set rst1 = nothing





Then I make the changes on the form and click on OK



I use another  recordset so



rst1.open "select * from tblorders"  etc......



with rst1

.addnew

!field1 = me!txt1

!field2 = me!txt2



etc....

.update

.close

end with

set rst1 = nothing





Hope this makes sense



Regards



Richard Gibson

IT Manager

Walkerpack Ltd

01604 760529



-----Original Message-----

From: Aaron Dennis [mailto:aarondennis@s...]

Sent: 21 February 2002 17:06

To: Access

Subject: [access] Lookup Multiple Field Values





Hello,



I am trying to accomplish a seemingly simple thing in Access, but I can't

figure out how to do it:



In a data entry form, I would like to be able to enter a value in a control

(probably a combo box), have that value look up a record in another table,

and copy the values of other fields in that record into fields on the data

entry form.  I don't just want to SEE the values of these fields; I 

actually

want to copy them into my form's table, so that I can store them 

independent

of the source table.



For example, I am constructing an INVOICE form used to create invoices 

which

has an INVOICE LINE ITEM subform for entering individual products to be

included on the invoice.  When working in the INVOICE LINE ITEM subform, 

the

user enters a PRODUCT CODE (or uses a combo box to find it).  The PRODUCT

CODE performs a lookup on a PRODUCT table which contains the PRODUCT CODE,

PRODUCT DESCRIPTION, and PRODUCT PRICE.  I would like to be able to copy 

the

PRODUCT DESCRIPTION and PRODUCT PRICE into my INVOICE LINE ITEM table so

that I can make adjustments to the price and description.



As far as I can tell I can't use the DLOOKUP function because that simply

displays the data from the other table, where I need to copy the data into

new fields.



On doing some research I have come across the AfterUpdate Event Facility, 

but I am not entirely sure how to use this part of access. 



Can Anyone Help?



Thanx In Advance!