Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

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

Posted on 1997-12-08
Medium Priority
234 Views
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.
0
Question by:guni
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3

Author Comment

ID: 1961615
Edited text of question
0

LVL 12

Expert Comment

ID: 1961616
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

LVL 12

Expert Comment

ID: 1961617
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

Author Comment

ID: 1961618
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

LVL 12

Expert Comment

ID: 1961619
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

Author Comment

ID: 1961620
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

LVL 12

Accepted Solution

Trygve earned 800 total points
ID: 1961621
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
###### Suggested Courses
Course of the Month8 days, 18 hours left to enroll