Setting a field's default value as an value from another table


A

ashour.rehana

I have two tables:

One called Invoice Details and the other called Parts. They are both
related via the PartID field (the autonumber key field).

In the Invoice Details table I have a field called UnitPrice and in
the Parts table, I have a field called Price.

Question: How can I set the default value of the UnitPrice field in
the Invoice Details table as the value in the Price field in the Parts
table?

I'm assuming I can use a query for this, but I have no idea how to
write it.

I'm familiar with Access, but have very limited coding experience.
I'm using Access 2003.

Thank you,

AR
 
Ad

Advertisements

L

Lord Kelvan

sounds like you want a trigger but access dose not support triggers.
If the price of your products never change then you dont need to do
that but if they do then what you have done is correct.

i do supose you are using a form to enter data you can have an
afterupdate event on a form to where you enter invoice details to
automatically insert that value into that field woudl be the best way.

Regards
Kelvan
 
K

Ken Sheridan

You can't do it in the table definition, but you'll find an example in the
Orders Subform in the sample Northwind database, where the AfterUpdate event
procedure of the ProductID control looks up the value of the UnitPrice column
for the selected product from the Products table and assigns it to a control
bound to the UnitPrice column in the OrderDetails table, using the following
code:

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID

' Look up product's unit price and assign it to UnitPrice control.
Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter)

Ken Sheridan
Stafford, England
 
A

ashour.rehana

You can't do it in the table definition, but you'll find an example in the
Orders Subform in the sample Northwind database, where the AfterUpdate event
procedure of the ProductID control looks up the value of the UnitPrice column
for the selected product from the Products table and assigns it to a control
bound to the UnitPrice column in the OrderDetails table, using the following
code:

    Dim strFilter As String

    ' Evaluate filter before it's passed to DLookup function.
    strFilter = "ProductID = " & Me!ProductID

    ' Look up product's unit price and assign it to UnitPrice control..
    Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter)

Ken Sheridan
Stafford, England

Thank you Ken

That is exactly what I was looking for. A few modifications to the
code and it worked like a charm.

Regards,
Ashour
 
Ad

Advertisements

J

John W. Vinson

Dude I have questions in access, just wondrin if u can help me

Yes, but only if you ask the question.

Do note that this newsgroup was abandoned by Microsoft several years ago. Only
a very few of us ghosts still hang around it. There's a lot more traffic on
the Web forums listed below in my sig.

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://answers.microsoft.com/en-us/office/forum/access?tab=QnA
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top