Book Shop Database Ideas help?

[ilikeshinythings]

Yeah all my forms work and if you select Book_ID the combo box opens and as im using multiple columns it displays the books title and uses that as an alias but the ID is inserted into the database (this was suggested in an earlier post), that works fine.



But I want the book_price box to find the price of the book that is selected in Book_ID combo box.


the record source of the form is Orders tablebut the Book_ID and Book_Price boxes are in the subform which has the record source Order_Details

I couldnt find record source of the Book_ID combo box but it has the control source of Book_ID and the row source of:

SELECT Books.Book_ID, Books.Title FROM Books ORDER BY Books.Title;


And for the Book_Price combo box the row source is:

SELECT retail_Price FROM Books WHERE Book_ID=Forms![Order_Details Subform]!Book_ID;

This is a code I interpretted from another website but it doesn't work at all.

[orange]

Do you really have a combo box for Price?

What is the name of the combobox for Book_Id?

[ilikeshinythings]

Its only a combo box because on some other website i was reading something similar to my problem and they said to put a code in the row source but obviously a normal text box field doesnt have a row source so i tried it with the combo box. But yeah ill change it back to a normal text box if that'd work.

The combo box for Book_ID is just 'Book_ID'

[orange]

Try something like
Forms![Order_Details Subform].Form!Book_ID

What is the recordsource of the subform?

[ilikeshinythings]

The record source for the 'Order_Details Subform' is the 'Order_Details' table.

And I wasn't sure where you meant for me to put the 'Forms![Order_Details Subform].Form!Book_ID' so i tried it in the Row Source of the Book_Price combo box but it returned an error saying 'The record source 'Forms![Order_Details Subform].Form!Book_ID' specified on this form or report does not exist.' But the form is called 'Order_Details Subform' and the book_id box is called Book_ID?

[ilikeshinythings]

Should I replace the combo box with a text box?

[orange]

I was thinking for you to replace

And for the Book_Price combo box the row source is:

SELECT retail_Price FROM Books WHERE Book_ID=Forms![Order_Details Subform]!Book_ID;

[ilikeshinythings]

oh right okay sorry getting late here, I must be losing concentration haha thanks i'll give that a go.

[ilikeshinythings]

Ok I tried 'SELECT retail_Price FROM Books WHERE Book_ID=Forms![Order_Details Subform].Form!Book_ID; ' In the Row Source of the Book_Price combobox but I select a book from the list using Book_ID combobox and then I click the Book_Price combobox and It asks for me to enter a parameter value.

I just want the book_Price box to put the price from the books table for the book_ID which is provided in Book_ID combox box.

Thanks for the help so far, anymore suggestions?

[orange]

Can you post a copy of your mdb? You can remove anything confidential.
Just a few records to show the form with data.
Zip format is good

[ilikeshinythings]

Ok no problem and the tables already should have 15 rows each (if they have more its because I was testing and forgot to delete them)

And also i have plenty of images associated with the .mdb so im not sure how they will link but hopefully it'll work.


The file is 2.1Mb and the forum limit is 2.0Mb so I uploaded it to mediafire

http://www.mediafire.com/download.php?77anc9ah1gq23bk

[June7]

Did you run Compact & Repair before zipping? The project has embedded images? I can't download from that site until I get my home computer fixed.

[ilikeshinythings]

Yeah It has images in it and I didn't compact and repair, should I have? oops

[orange]

I looked at your forms, and was not sure of your intent. I saw continuous forms?

I copied your database to jdatabase.mdb in the zip.

I created 2 forms jOrders and JOrderDetails where jOrderDetails is a subform. The form subform are linked on Order_id.

I created a lookup field Customer Name on the Order form ( It looks up a displays the Customer name for that Customer Id)

I also altered the color and font of the Book_Id and the Price in the subform to show the 2 are linked.

[June7]

Compact & Repair might have made the file small enough to attach.

Design editing causes file size to grow. Need to run C&R periodically, or set to run automatically when project closes.