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

  • Thread starter Thread starter ashour.rehana
  • Start date Start date
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
 
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
 
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
 
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
Click to expand...

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
 
I am using in the form the folowing code:
Private Sub Store_AfterUpdate()
Me![Store].DefaultValue = Me![Store].Value
Me![Manager] = DLookup("Manager", "Actual Managers", "Store = " & Me![Store])
End Sub
Click to expand...
I need the number of the store to be automaticly updated after the last record i entered, then the manager name looked up after the store number.

This works like a charm only when i type the store number... when i use the last record store number (it is completed automaticly as I wanted, I just hit Enter), the dlookup doesn't work anymore...
 
Last edited:
Dude I have questions in access, just wondrin if u can help me
Click to expand...

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
 
Back
Top