Total DB Newbie stuck HELP please!

[June7]

Perhaps Barry's PF is an indicator that an Order item can associate with only one invoice - can't double bill. order_item_id and invoice_number should be set as compound unique index to prevent duplicate pairs in Invoice_Line_Items.

However, a job can have multiple invoices (partial billing) until job is completed.

[DBID10T]

When I am adding a product to a list via a form, I can select the Product via a Combo Box, I am trying to display the tblProduct.PriceRetail for the ProductID being added, to record it in a RetailPrice for the ProjectProduct record.


hope that makes sense

DBID10T

[DBID10T]

Perhaps Barry's PF is an indicator that an Order item can associate with only one invoice - can't double bill. order_item_id and invoice_number should be set as compound unique index to prevent duplicate pairs in Invoice_Line_Items.

However, a job can have multiple invoices (partial billing) until job is completed.

With my newbie DB skillset that was how i interpreted that, i think it's pretty similar to how I am trying to use tblProjectProduct in my current design

[DBID10T]

=[tblProject]![PriceRetail] is what I have now and the PriceRetail shows #Name?

[June7]

Oh wait, the PF should not be duplicated at all. Compound index would not prevent associating with another invoice. So PF is set as unique so that ID cannot be used more than once in Invoice_Line_Items. It does not have to be a primary key, just set as unique index. You were right, this is a 1-to-1. Invoice_Line_Items actually appears to duplicate fields from Order_Items. Invoice_Line_Items could probably be eliminated by saving InvoiceID into Order_Items.

You have that where - in textbox? Why do you have table name prefix? Why do you have = sign?

[DBID10T]

It's the text Field where I am trying to store the Retail price (which is listed in tblProduct) in that field for each ProjectPurchase Record

on my Project Purchase Form

Which hopefully should give me a record of the price the unit was sold for at the time of sale

[DBID10T]

ok think I might be getting close,

I Usually use a Combo box and qry to get the key and the display data for a given cell on a form. I created a qryProductRetail and set the PriceRetail to a text box, then set the qryProductRetail as its control source.....but it's not updating when i change to a different product..

[June7]

A query cannot be a ControlSource.

You have a combobox to select product? Its RowSource has ID, product description, product price?

Will require code (macro or VBA) to save price. I use only VBA. Real trick is figuring out what event to put code into. Probably combobox AfterUpdate.

Me!PriceRetail = Me.Comboboxname.Column(2)

[DBID10T]

A query cannot be a ControlSource.

You have a combobox to select product? Its RowSource has ID, product description, product price?

Will require code (macro or VBA) to save price. I use only VBA. Real trick is figuring out what event to put code into. Probably combobox AfterUpdate.

Me!PriceRetail = Me.Comboboxname.Column(2)

Me!PriceRetail = Me.ProductID_FK.Column(4)

hmm

June7 Said "After ComboBox Update"

hmmm

[DBID10T]

June7

So close I figured out where to put that to activate after combobox update but

Me!PriceRetail = Me.ProductID_FK.Column(4)


Did Not change the column read to number4

My qryProduct queries 4 columns ProductID_FK, ProductName, UOMID_FK, PriceRetail

my thought was the bolded portion signified column read but changing from 2 to 4 doesn't seem to change that it's reporting on column1

Thanks for all your tips
DBID10T

[June7]

I actually said "combobox AfterUpdate".

Are you familiar with VBA event coding? Start with http://baldyweb.com/FirstVBA.htm

then https://support.office.com/en-us/art...7-70649e33be4f

Okay, now I see your post.

The number is combobox column index. Index begins with 0. If desired value is in column 4, its index is 3.

[DBID10T]

I actually said "combobox AfterUpdate".

Are you familiar with VBA event coding? Start with http://baldyweb.com/FirstVBA.htm

then https://support.office.com/en-us/art...7-70649e33be4f

Okay, now I see your post.

The number is combobox column index. Index begins with 0. If desired value is in column 4, its index is 3.

Ah ha and i was too tired last night to realize i had another typo, i was trying to pull RetailPrice from tblProject instead of tblProduct, and 3 instead of 4 for the col number,

Thanks as always

[June7]

Shall we call this thread solved?

[DBID10T]

Shall we call this thread solved?

No I call it progressing slowly

If i might query you again

I am Working on my Forms and Such and I am working with this expression

DLookup("CustomerType", "tblCustomerType", "CustomerID_PK = " & [CustomerID_PK])

Should Reference field CustomerType in tblCustomerType when control source CustomerID_PK shows the actual ID number

It doesn't seem to be working quite right

Thanks in Advance,
DBID10T


DLookup("CustomerType", "tblCustomerType", "TxtBoxCustomerID = " & [CustomerID_PK])

Didnt fix it

[June7]

tblCustomerType has a field called CustomerID_PK? or is it CustomerID_FK?

Actually, why would you look at tblCustomerType to determine what type a customer is? Shouldn't the type be a foreign key field in tblCustomers?

Really need to close this thread as the original question has been resolved and you have moved well beyond it. As you encounter new issues, post new questions.