Solved

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

Posted on 1997-12-08
7
226 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

813 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

13 Experts available now in Live!

Get 1:1 Help Now