Stock query implementation

[mrapoc]

Hi guys/gals

I have managed to work out a query, so when a product is selected for order, it will fetch the necessary stock level for that product into the stock table.

Thing is, I use the order table credential "Phone ID" which is a lookup as a query item, so the query looks for what is selected in the order table.

But I wanted to bring the results from this query back into the order table so it shows stock but apparently i cant do this

Any help is much appreciated

Sam
View attachment Phone Database.accdb

 

[vbaInet]

Bit confused as to what you want to achieve Sam. Could you please elaborate.

 

[mrapoc]

Where the arrows are I want to select talk plan, then the phone model

Upon selecting phone model, the relative stock information should then be fetched for that phone model

I am a complete beginner at this

Here is my query and relationship table

 

[vbaInet]

In your query, you would use an alias which would be implementing a DCount() function. Here's a link on the function:
http://www.techonthenet.com/access/functions/domain/dcount.php

Once, you've read that, you would need an AND logic in your DCount too for Talk_Plan_ID AND Phone_ID

 

[mrapoc]

SO it would be along the lines of???

DCount("Talk Plan Id" And "Phone Id", "Order Id = [Order].[Order Id]")

So it will be bringing the order id value from what is the current order id number?

i think

 

[vbaInet]

Not really. The parameters are incorrect. Here's another link with the AND operator being used as an example:

http://msdn.microsoft.com/en-us/library/aa211471(office.11).aspx

 

[mrapoc]

DCount(Stock Level, Phones, [criteria])

Have I got the first two kinda right

Stock level being the expression of what I want to get the value of, Phones being the table I want to get the expression from?

 

[vbaInet]

Actually, I was just looking at your relationships (because I didn't notice that bit at first ) and trying to understand what's going on there. Why do you have separate Stock Level fields in Phone and Order tables?

Also, from what table do you want to get the Stock Level from?

 

[mrapoc]

Yeh they shouldnt be there really, it was from previous messings around with or something

the phones table should have the stock level - THE actual stock level

the order bit, i just want to fetch the stock level from the phones entity when the respective phone is selected in the order (eg. select sony ericsson, stock level for that phone is shown next to it)

doesnt have to be a complex database really, its just to show i can get a functional one working according to the plan i made

 

[vbaInet]

Alright, I understand your requirements now. See the attached db.

Pay attention to:

1. The use of a form (based on Order table) used as the subform in your Customers form
2. The ControlSource of the UNBOUND text box used to display the Stock Level
3. The one line code in the After_Update event of the Phone_Id control.

 

[mrapoc]

thanks very much works a charm

now you have done it i can see what needed to be done

thanks

 

[vbaInet]

You're welcome.