Solved

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

Posted on 1997-12-08
7
223 Views
Last Modified: 2008-03-03
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
Comment
Question by:guni
  • 4
  • 3
7 Comments
 

Author Comment

by:guni
ID: 1961615
Edited text of question
0
 
LVL 12

Expert Comment

by:Trygve
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

by:Trygve
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:guni
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

by:Trygve
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

by:guni
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

by:
Trygve earned 200 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now