I am sorry...I can not upload the database

Adam has no specific price

charlie has a specific price
by scrolling to differrent customer ,the list in the productid field displaying the specific price of charlie
if I open the query it displays the price correctly

opening query for adam,no special price

that is norman has a specific price
the query is okay but the productid field in subform (I use PRODID in my database for productid) displaying wrongly.
I use the query as the rowsource.

the specific price form

hopefully anybody could hel me

Hi Liem,

OK, I think that the first question you had is why your subform doesn't update pricing when you change the customer in the custname field in the order form and/or why, when the form loads, why it doesn't show the pricing information for that customer.

This is how forms load in Access. The subform loads before the form, so if you start with a new record, it will use the default values in the form before you change the customer is selected so you have to have some code or macro in the main form to update the subform as you select a customer.

If starting with an existing record that already has a customer selected and it isn't showing the correct pricing, then that means your subform isn't set to respond to those changes.

Can you show us a screen shot of the subform in design view with the prodid combo box selected with it's property dialog visible showing the rowsource of that combo box so we can see the full content of it. If necessary, highlight the rowsource and press shift+F2 to zoom. If it just points directly to a query, then we will need to see that query in design view as well.

On OrderF, in design view, select the CustID combo box (representing customer name). In the property window, go to the Events tab. Do you have anything listed anywhere there?

If so, which event(s) is/are used? Are they macros or VBA?

Basically, without looking at every single object and property in your database, I'm thinking you may just need to add an event that requeries the subform when you select a customer.

At the moment the event proerty is still empty

Iam sorry I am not good at vba
I know how to change the new price if the price gets higher or lower

Ok, not a problem, it's one line of code :)

In the events properties, click on the "After Update" event. Next to it will be blank but you should see a drop down followed by 3 dots (ellipses). Click the Ellipses and if prompted, choose "Code Builder". When the VBA Editor opens, it will already have some code entered:

VBA Editor BEFORE adding code

Private Sub CustID_AfterUpdate()

End Sub
Copy

Next, between the first line and the "End Sub" line, add the requery line so it will look something like:

VBA Editor AFTER Editing code

Private Sub CustID_AfterUpdate()

     SUBFORMCONTROLNAME.Requery

End Sub
Copy

1) Click on the code blocks above to expand/copy
2) In the last block, replace "SUBFORMCONTROLNAME" with the actual name of the subform control in OrderF which may or may not be the same as the name of the form it holds.

Save you form and code, close it, reopen it and see if it works as you need it to.

Is it just to write REQUERY on afterupdate event or which event
Thanks

Sorry

is it okay,the rowsource of the productname

the control name

If you click on the grey boxes in my previous post, it will show what you need in vba, or, if you'd rather use a macro, choose macro builder instead of code builder.

When the Macro Builder opens, in the drop down next to the "+" choose Requery and press tab
In the Control Name box, type the name of the SubForm control in OrderF and press tab.
Click Save and Close the Macro Builder.
Save your form and close it. Reopen your form and see if it works.

no...I'd rather use vba
is my vba correct

In the following pictures, I will walk you step by step thru the Macro version as it may be a little less confusing:

1) Click the button with the 3 dots

2) Choose Macro Builder

3) In the dropdown, Select or Type "Requery"

4) In the Control Name box, enter the name of your sub form control (Replace "YOURSUBFORMCONTROLNAME" with your control's name)

5) Click Save (sorry, it's marked as "1" here)
6) Click Close (sorry, it's makred as "2" here)
7) Save your "OrderF" form and close it
8) Reopen your "OrderF" form and see if it is working.

Sorry if I went too fast or threw too much at you earlier.

is it right the controlname "PRODUCTNAMECOMBO"

The control name you need is the name of the sub form control itself in the OrderF. See below

oooo....I am sorry

The subform control is the control that is selected with the orange border around it in the image above of Richard's TechHelp Invoicing Template/Order Form. Whatever the actual name of your subform control is, that's what you put in the control name field

oooo....sorry

Oooo...sorry

actually adam has no special price

charlie has one

Sorry,Iwas not careful to read Your explanation....I am very sorry

the custid field (orderf) name is custnamecombo and I have to requery the orderdatailf (subform)

is there anything wrong with my relationship

The first image you've shown would be described as:
Each customer could have many orders
Each order could have many order details
Each line item in the order detail could have exactly one product

The second image shown would be described as:
Each customer could have many product price points
Each product could be used in many customers' price points

So what is the problem you are trying to resolve with these images?

Below I've listed a few Seminars/Tech-Help Videos on relationships and joins for you to review which may help to clear things up for you as well.

Access Relationship Seminar
Relationships
Outer Joins

Oooo..one product only one order detail ya

after reviewing the invoicing video of RICHARDS
I notice that linking tables only in the query,let me know why we use outer joint between ordert and customert and orderid in ordert with the foreign key in query
isn't it one to many?

Could you help me why using outer joins.
We know that orders should have one or more ordersin order detailf snd every customer has. Orders (I think there is no customer with no ordering)

I am used to using outer joins on color.
There are several materials I receive fabrics ( using color) and dacron (no color needed)

Hi Liem, sorry I haven't gotten back to you sooner. Haven't been feeling well last couple days and dealing with a family issue on top of that. If no one else answers before I get home from work, I will review this thread and respond. It will likely be after 6pm EDT U.S.

Again, sorry for the delay.
Don

That's OKAy.
you had answered truly kind
Iam very grateful

later...give the correct way to solve the problem
THANKS ALOT

IAM looking forward to waiting for your help

Was the relationship I had correct
Why I could not make the customerprice in orderdetailf correctly
Thank you

To answer your question, outer joins typically represent one to many relationships. In the Query Design view, the one side of the relationship is identified by a dot while the many side is indicated by the arrow head pointed to it.

Sometimes, it may show a "1" by the table that represents the "one" side of the relationship and "" next to the many side. This happens when referential integrity is set at the table level.

So, if you have a fabrics table that has 3 fabrics: Cotton, Wool, Polyester and a Colors Table that has the colors: Red, Blue, Orange, Green, Pinstripe, etc. And you only have cotton in red and orange, Wool in blue and green and pinstripe:

Inner Join 1 Fabric to Many Color Will show:
Cotton: Red, Orange
Wool: Blue, Green, Pinstripe
Polyester: NO RECORDS SHOWN

Outer Join 1 Fabric to Many Color Will show:
Cotton: Red, Orange
Wool: Blue, Green, Pinstripe
Polyester: Displayed with no colors

Inner Join or Outer Join, 1 Color to Many Fabrics Will Show:
Red: Cotton
Blue: Wool
Orange: Cotton
Green: Wool
Pinstripe: Wool
and Polyester would never be listed

Ya sir DONALD , I did use this outer joins...
I meant that I know how to use it, in customer pricing of RICHARDS database template , the orderlist is using outer joins, I need to know why he used outer joins.
To my opinion every customer does have orders

Ahh, OK, sorry.

Every customer you currently have may have orders. What if you get a new customer? They won't have any orders initially. If you use an inner join, you won't see them to be able to add an order for them.

Ooo ...I see
Now ...could you show the correct way making
My customer pricing especially in the order
Detailf displaying the correct price (specific price as the query, I used this query a rowsource)

Ooo ...I see
Now ...could you show the correct way making
My customer pricing especially in the order
Detailf displaying the correct price (specific price as the query, I used this query as the rowsource)
Thank you

Sorry ...doubled

Here are two options thru VBA:

1) This is the VBA Option I gave you earlier with your fieldnames used:
Place the following code in your OrderF VBA module

Update the pricing when the Customer Name is changed

Private Sub custnamecombo_AfterUpdate()

     ' Requery the Product combo box
     Detailf.Form!productnamecombo.Requery

End Sub
Copy

2) The following code should ensure it has the proper values every time you view the Product combo:
Place the following code in your Detailf VBA module (if that is the name of form in your subform)

Update the Pricing when the Product Combo gets Focus

Private Sub Productnamecombo_GotFocus()

     ' Make sure the data in OrderF is current
     if Parent.Form.Dirty Then Parent.Form.Dirty = False

     ' Requery the Product Combo
     productnamecombo.Requery

End Sub
Copy

ya SIR DON it works
thank you very much
I am very poor in vba

later sir DON,trademarkcasing ...
several customer want to use their own or differrent trademark case from default (the trademarkcase I use)
could you help me
thenk you

Give it time :)
Just keep working through Richard's courses and you'll develop a better understanding of the database itself which will make understanding VBA that much easier :)

THANKs alot sir DON

Donald I just wanted to give you a huge thanks. You went above and beyond with helping Liem here, and it's greatly appreciated by him, I'm sure, and also by me. Thank you very much.

thank you RICHARDS

RICHARDS,may I know if A juction table with 3 primary keys as composite key,is it okay.

I will use the customerpricingt with 3 foreign keys (Customerid-productid-tradmarkcasingid) as the junction table
thank you

I have problem on this trademarkcasing,especially for qty on hand
1.several customers need a product with different trademarkcasing
2.a product with 2 different trademarkcasings (for every customers)
hopefully you could help me
thank you

I want to add the trademarkcasingid field

the junction table

I am confused how to do it
thank you

I change to this as the junction table and I use it as subform of the productf

I want to know the remaining tradecasing
is it okay to separate the casing name in a different table

customer specific price

it is confusing to put the casingname
I will put in the custprodt,can we display the casingname as we determine the new price

the customerpricing works very well
can we do the same thing to display the casing name as to get the NEWPRICE

can we do that way too
thank you very much

Hi Liem ,

I have to work all 3 jobs today so won't be able to dig into this before tomorrow night.

However, since you've seen how toresolve the pricing, it should work similarly for casings.

My suggestion would be to make a backup of your database and then, as Richard suggests, try to get it working, you may surprise yourself.

We also use dlookup in the query of customer pricing , I meant we have 2 dlookups in the same query

And to display the casingname , we use the vba
Me.casingname= me.productnamecombo.coloumn(2)

This thread is now getting extremely long, and I'm actually noticing it's slowing down loading, so I'm gonna close this one. Liem, if you need additional help, start a new thread.

Continued here.