Custom relationship - need help!

[iddqd]

Hello, I am trying to set up a relatively simple relationship between numeric fields in two (already related) tables. The problem is Access doesn't really support this type of relationship... or in fact, doesn't have an interface for coding your own custom relationships (if it had one, this would be ridiculously easy).

But it doesn't. So I have to turn to you.

To explain my problem better, I'll use a visual aid.

My two tables are as follows, the master is called [Raw Materials] and details is called [Raw Materials Details] (the aptly named).

I guess I should rename the [Raw Materials].[Stock] to something else (as this isn't REALLY data redundancy). But anyway, what I want to do is have the [Raw Materials].[Stock] automatically equal to Sum(Stock) FROM [Raw Materials Details].[Stock]. To rephrase, I want the Total Stock field to be automatically equal the sum of corresponding stocks.

Either way, I have no idea how to go about doing this. Can anyone point me in the right direction?

 

[Banana]

Erm, you're not supposed to stored calculated values in a table.

Here's an article...

 

[iddqd]

Erm, you're not supposed to stored calculated values in a table.

Here's an article...

But what if I REALLY want to?

 

[Banana]

You can. Just do it on the form instead of a table. Create a query that pulls the main table and add a Total Stock: Sum([tbl.Detail].[Details]) and bind the form to it. You even can make it a datasheet if you want. That way you get the benefit of auto-updating records and a properly normalized databae.

 

[iddqd]

Is there a guide on how to use automated data entry in a form?

 

[Banana]

There are two ways to do it:

1) If the entry is always same, you simply use default value property for any combobox or textbox. It's in Data tab on property sheet.

2) If you want to copy values that were already entered in a previous record, take a look at duplicating records. There's lot of threads about that in forum, so you can search for it.

If you mean something else, please elaborate.

 

[iddqd]

There are two ways to do it:

1) If the entry is always same, you simply use default value property for any combobox or textbox. It's in Data tab on property sheet.

2) If you want to copy values that were already entered in a previous record, take a look at duplicating records. There's lot of threads about that in forum, so you can search for it.

If you mean something else, please elaborate.

Obviously I can't use the default value .

I want to automatically enter things like current date, access username of user editing entry, or a calculated value, what have you.

 

[Banana]

Current date can be default value. Use Date().

I think there is a function CurrentUser() that will tell you who is using Db now.

In both cases above, you can use default value for that. No coding required.

Calculated value shouldn't be store.

 

[iddqd]

Current date can be default value. Use Date().

I think there is a function CurrentUser() that will tell you who is using Db now.

In both cases above, you can use default value for that. No coding required.

Calculated value shouldn't be store.

yey i <3 you now

 

[The_Doc_Man]

you're not supposed to stored calculated values in a table.

But what if I REALLY want to?

You don't really want to.

Inventory on-hand can start up this simple, but if this can grow into a multi-user database, you really REALLY REALLY don't want this.

Search this forum for articles on Inventory and Stock as keywords.

In brief, inventory should be displayed from a query, not a table. Quantity on hand is the SUM of incoming and outgoing transactions for any item. The sum can be part of a query.

I know you are trying to keep it simple, but the advice of "older, wiser" heads is that this is a HUGE abyss that you are about to step into. This is the kind of sinkhole that swallows people, cars, and houses.

 

[iddqd]

Current date can be default value. Use Date().

I think there is a function CurrentUser() that will tell you who is using Db now.

In both cases above, you can use default value for that. No coding required.

Calculated value shouldn't be store.

Btw, apparently the correct syntax for CurrentUser() is expression.CurrentUser() which doesn't make sense to me... because what object is CurrentUser() a method of???

 

[Adeptus]

Btw, apparently the correct syntax for CurrentUser() is expression.CurrentUser() which doesn't make sense to me... because what object is CurrentUser() a method of???

the Application object

If you open Help from the VB editor in Access, it has an "Applies To" dropdown which tells you this...
if you open Help from the object browser it apparently doesn't... gotta love MicroSloth

 

[iddqd]

hmm, =Application.CurrentUser() gives me a syntax error (Access kidly tells me that MAYBE, just MAYBE I misplaced a comma, never mind that I don't have a single comma anywhere in there).

I guess I'll try to get this to work in a macro first.

 

[iddqd]

it works perfectly in a macro... WTF??

this is the error I've been getting for =Access.CurrentUser() or =Application.CurrentUser()

 

[Adeptus]

hmm... maybe the object.CurrentUser syntax is only for VBA?
Have you tried just CurrentUser() in your query?


btw, you're using a WinXP theme there? nice... what's it called/where can I find it?

 

[iddqd]

I've tried =CurrentUser() first and it doesn't work.

(The theme I use is a WindowBlinds theme called Rivet Rebellion, and you can find it at www.wincustomize.org).