Combo Box on a Subform

[LearnersPermit]

I have 2 problems with a subform:

I have an order form which contains information regarding clients wishing to purchase books, this form includes name, address, etc. On this form I have a subform which includes information about the specific book(s) to be ordered.

1. The subform includes a Catalogue No., Title, Language, Quantity, UnitPrice, Total (quantity*UnitPrice). When I select a catalogue no. it automatically populates the title and UnitPrice, once the quantity is input the total is calculated for each book order. Everything works EXCEPT the language. I have a table called tblBooks which includes all of the book information and has an autonumber called Book_ID and is linked to tblanguage by Language_ID, there is also tblLanguageLink which includes Book_ID and Language_ID. On the subform Language is a combo box since I have three languages available - English, French and Bilingual.
I am attempting to have a drop down for languages on the subform that will be populated when I select the catalogue no. Any suggestions?

2. I also have a problem with my subform in that it adds records to the tblBooks. This table should only provide the information for the subform and all information should be written to tblOrder_details. What is happening is that both tables are written to. Any suggestions?

Thanks for any assistance.

 

[Zeroanarchy]

If I am on the correct wave length this should be the code you are after:

Private Sub Suburbid_BeforeUpdate(Cancel As Integer)

Dim strFilter As String

'Evaluate filter before it's passed to DLookup function.
strFilter = "suburbID = " & Me!SuburbID

Me!Serviceman = DLookup("Serviceman", "Suburb",strFilter)
Me!Postcode = DLookup("Postcode", "Suburb", strFilter)
Me!Melwaysref = DLookup("melwaysref", "Suburb", strFilter)
Me!Pacsuburb = DLookup("pacsuburb", "Suburb", strFilter)

End Sub

Explanation:
SuburbID is a combo box that refers to the same table as [serviceman], [Postcode], [Melwaysref], [pacssuburb].
When you choose a suburb it austomaticly fills in the above 4 fields with the value set next to it in the table.

My suggestion is to make everything come from the same table saves dick arseing around with links and queries.

Hope this is of some help.