Using a function to set a defalut value in a field in a table

I want to set a default value in a field in a table, lets call it table A. The default value has to come from another table, lets call it table B. The value from table B should be from a row corresponding to a foreign key in table A. Example: In table A there is a field call Stock#, this is a foreing key to table B, where stock# is primary key. In table B there are all the informations about the stock, that is quantity, price etc. When I key values in table A, and keys a value in the Stock# field, I want the price from table B showing for this Stock#. Perhaps the question looks easy, but I have tried many things without succeding yet. Can you help me as how to write in table design for table A the function to use to set the default value from table B? Thank you.
guniAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

guniAuthor Commented:
Edited text of question
0
TrygveCommented:
To make this happen on table level in access is close to impossible as far as I know.

What you can do is to make your registration form make a dlookup at table B to get the price for the stock registered.

At the After Update event for the Stock field insert something like this.

if not isnull(me!Stock) then
  me!Price=dlookup("[Price]","B","[Stock]='" & me!Stock & "'")
endif


If you realy want this to happen at table level you will probably have to move your data to SQL Server or equal and make triggers that insert the stock price when you have updated the stock number.

Hope this helps!
Trygve
0
TrygveCommented:
You could also just leave the price in the B table, and join in this table in your queries when you need the price for calculations.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

guniAuthor Commented:
I'll try to explain it better.
I think you are right about the problem about doing it at the table level.
I have a form named Ordrelinie based on a table named ORDRELINIE. I have another table named VARER. When I key in data in form Ordrelinie, I want a value from VARER (field: Listepris) inserted in table ORDRELINIE in a field named Salgspris (this field Salgspris is shown on the form Ordrelinie). The value should be shown when I key in Varenr in form Ordrelinie and proceed to the next field.
The field Varenr is a field both in table ORDRELINIE (foreign key) and in table VARER (primary key).
The value is only a default value (proposed value) as I want to be able to correct the value with another value keyed in form Ordrelinie in field Salgspris. Salgspris is a bounded object to field Salgspris in table ORDRELINIE. To clarify: I want a proposed value inserted in the field Salgspris in the form Ordrelinie. But I also want to be able to correct this proposed value.
0
TrygveCommented:
My answer gave you the solution to this:

if not isnull(me!VareNr) then
  me!SalgsPris=dlookup("[Salgspris]","varer","[vareNr]='" & me!VareNr & "'")
endif


This is put in the After Update Event for the VareNr control. It will put the value in the Salgspris field, and when you move to this field you will be able to change to any value you like.

You could also expand it to check if there is already something in the salgspris field, and then skip if there is

if not isnull(me!VareNr) then
  if not isnumeric(me!Salgspris) then
    me!SalgsPris=dlookup("[Salgspris]","varer","[vareNr]='" & me!VareNr & "'")
  End If
End If

I have assumed that the field VareNr is a text field.

Try it out.

If you need help on how to insert it into the After Update Event, please let me now. But please not reject the answer before you have tried it.

Good Luck
Trygve
0
guniAuthor Commented:
Trygve, I don't reject your answer, but I cannot get it working. The field Varenr is a numeric filed without decimals. Can you correct the code and tell me how to put it in? Thank you, Gudmund.
NB: I'll give you all the points when YOU give me the solution, thank you.
0
TrygveCommented:
OK, since the VareNr is numeric we have to it slightly different

If Not IsSull(me!VareNr) Then
  me!SalgsPris=dlookup("[Salgspris]","varer","[vareNr]=" & me!VareNr)
End If


Put it in:
On the form, select the VareNr control, view the properties and go to the line marked AfterUpdate. If you have not already assigned any macro or function to this event select the box (with 3 small dots in it) to the right of the line. If you have something assigned please let me know and I will give you hints on how to deal with it.
OK, having selected the box with the three dots, you will get a box from which you select the Code Builder.

You will the get somthing like this:
Sub VareNr_AfterUpdate ()

End Sub

Copy the code from above and paste it between the Sub... line and the End Sub ... line.

Compile the code and see if you get any errors.
If not have a go; if all field references are correct it should work
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.