looking up data from different tables

[deadmanmss]

hi, sorry if this is in the wrong thread but i need some help, may seem preety basic to you but its driving me up a wall trying to solve it

basically i have two tables Service & Order, in the service table i have 3 fields where i have stored all the services that my database is suppose to hold, ServiceID(primary key), Service(what the services are) and Price (price of the services)

now in my second table Order is where the user makes orders for whatever and have 11 fields, one of the fields in the order table (ordertype) gets the services from the Service table, which is fine, but the other field in the order table is what is giving me the problem, this is the Price field.

What i am trying to do is, when the user selects an order type from the order table, i want the price of that order to display in the price field in the order table , it should look at what has been selected and display the price




please help

many thanks

 

[The_Doc_Man]

There will immediately be two schools of thought on this, and to resolve which school, you need to think about a couple of issues.

A: Price of service. Is this time-invariant or can it ever change? Were you planning to track changes in price in a price history table? This will govern what you really need to store.

B. Can your user only order one service at a time? Because if more than one service is involved, you now have an invoice/line-item case (which usually means another table in parent/child relationship or one-many form).

In general, this is the type of thing you would do from a form. In the form, you can make a combo-box which can be multi-field to select the service name AND the service cost.

If you are not tracking the history of the price of service, then you must store the price of service in the line-item. If there will be a price history table that tells you what dates a given service charge was in effect, then the date on the invoice (parent record) will be enough for you to look up the service price at the given time. I always use VBA for this sort of thing, but it isn't usually very hard. You can do searches of this forum for "combo boxes" to see lots of discussion on how to use them.

 

[deadmanmss]

hello again, im still having problems with this and was woundering if someone can give me a clear cut answer to this. i searched around and found out what i was doing wrong and hopefully fixed it.

but anyways basically ive got a table (Service) which has 3 fields serviceid, Service and Price (obviously services are what they are and price is how much).

Ive created a form (Order) and in that form i got a combo box that selects the services which is fine, but my other field in the form Price, needs to be able to automatically populate the correct price form the service table for the service selected in the ordertype combo box in the order form (hope that makes sense)

now i know it an After Update command is needed but i cnt for the life of me understand what command to put in, ive tryed a number of commands but to no avail nothing works...pleaseplease help

 

[elite311]

I would create a query and use that to populate the price. So make a new query in design mode and add all the fields you want to use to the query by draging them down. Then in the query design under criteria enter [Forms]![Order]![CboBoxname] where CboBoxname is the name of the combo box on your form., Save you queary Also create a textbox and call it price on your form. Then input some VBA Code on the combobox afterupdate function

Private Sub CboBoxname_AfterUpdate()
Me.price = DLookup("Service", "queryname")
Me.Requery
End Sub

That should do it, but I didn't test this just worte it. It would be helpfull if you post a copy of what your working on. I would be able to help you better.

Hope this helps