DLOOKUP help please

[goddo2305]

I'm trying to work out how to make a DLookup work on my Access 2010.
Currently I have 3 tables (tblBooks, tblCustomers, tblOrders). For the time being we will only refer to tblBooks. So tblBooks has 6 fields = ID, BookTitle, Author, CostPrice, SalePrice and Genre.
I have made a form that is replicating an order form. Here is a look at the form as it is now –http://i.imgur.com/daDOiBx.jpg
Basically I have made a dropdown box that shows the ID and the booktitle but only puts the ID in the box. I want that to then get the SalePrice from tblBooks and put it in the Cost box. So far all I get is "#Error"
I was using this dlookup command =
=DLookUp("SalePrice","tblBooks","ID=" & [bookdropdown])


*Note that "bookdropdown" is the BookID dropdown box above the Cost textbox in the form*
Hopefully that's all the information that will help you to help me Once this one is fixed I'm sure it's self explanatory to slightly edit the command for other uses within the database.

I'm not sure if the drop down box has anything to do with it, but the "row source" is SELECT [tblBooks].[ID], tblBooks.[BookTitle] FROM tblBooks ORDER BY [ID], [BookTitle];



Thanks

[lfpm062010]

If the [bookdropdown] is from a textbox then the syntax should be

=DLookUp("SalePrice","tblBooks","ID=" & me.[bookdropdown])

If the "ID" is a text data type, then you will have to put quotes around me.[bookdropdown].

=DLookUp("SalePrice","tblBooks","ID='" & me.[bookdropdown] & "'")

[ranman256]

or forms!frmMAin!bookdropdown

[amrut]

Double check your field names and control names.Make sure the combo box ( dropdown box ) has its bound column to ID i.e.column 1. One more option is change the column count to 3, modify the rowsource to SELECT [tblBooks].[ID], tblBooks.[BookTitle]. tblBooks.[SalePrice] FROM tblBooks ORDER BY [ID], [BookTitle]; and modify the Cost text box's control source to
=[bookdropdown].Column(2).

[goddo2305]

If the [bookdropdown] is from a textbox then the syntax should be

=DLookUp("SalePrice","tblBooks","ID=" & me.[bookdropdown])

If the "ID" is a text data type, then you will have to put quotes around me.[bookdropdown].

=DLookUp("SalePrice","tblBooks","ID='" & me.[bookdropdown] & "'")

Thanks for the reply! I tried this and instead of "#Error" I got "#Name?"

=DLookUp("SalePrice","tblBooks","ID=" & me.[bookdropdown])

Hopefully we are on the right track now! Thanks

[Geo21]

The easiest thing is to create a query to join tblBooks with tblOrders. So, the Price will be adopted automatically depending on the bookid. If this is not convenient, and you must use dlookup, where are you using the code? In the default value? Or in the "AfterUpdate" event? Perhaps, depending on what you are doing, you'll need to use a Form/subForm. By the way, I assume that Form tblOrders is bound to Table tblOrders, right?

To make a long story short, try the below and see if it helps:

Click [Event Procedure] in the "After Update" Property of "Bookdropdown" and type in the code below.

Me.Cost = DLookup("SalePrice", "tblBooks", "[bookID] = " & Me.bookdropdown)