Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

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.
0
guni
Asked:
guni
  • 4
  • 3
1 Solution
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now