Unanswered: Extracting data to a form from various tables...
Hi I was wondering if anyone could help me?
I have two requests for help here, and I would be so grateful if someone could help!
I've nearly finished making a database for a car hire company and basically the first request is...
I have added a drop-down menu onto a form entitled, "Car" and when you select a car from the drop-down menu (called cmbCarMake), e.g. Ford Fiesta the category of the car is automatically put into a text box called txtCarCategory. The code that was used to achieve this was kindly given to me by a guy on here and can be seen below...
Private Sub cmbCarMake_AfterUpdate()
Dim CarCat As String
CarCat = DLookup("[CarCategory]", "CarTable", "[CarMake] = cmbCarMake.Text")
On Error Resume Next
txtCarCategory.Text = CarCat
On Error GoTo 0
CarTable is the table that contains the list of the cars and then their respective categories.
However, what I now also need is when you select the car, e.g. Ford Fiesta from the drop-down menu, as well as the category of the car automatically being put into the CarCategory text box (as I have already done - and works) I need then for the prices relating to each category to automatically be put into a field called txtChargingScheme.
I am having trouble explaining this properly, so I'll do my best! Basically if you hire out a Ford Fiesta, it belongs to the A category and the prices for category A are...
One Day Unlimited = £26
One Day and Mileage = £21 + 5p per mile
Daily Unlimited = £22 per day
Daily and Mileage = £17.50 per day + 5.5p per mile
Weekly Unlimited = £110
Weekly + Mileage = £70 + 6p per mile
There are five categories, A, B, C, D and E and there are various different fees for each category, but they all come under the same price categories as outlined above.
I hope I have explained this properly - it is rather tricky! If anyone could help me achieve this, I would be very grateful!
Another problem I am having is, when you enter a date that the car was hired out (and then also returned) into two fields in a Rents form, how can I get these same fields in the Car form, therefore when you enter the date into one form, the same date is automatically put into another form? If I can get these done, I will have finished the database! There are also various fields in the Car form that need to be automatically put into the Rents form, and then the Driving Licence number of a customer (from the Customer form) also needs to go into the Rents form. How is this achieved?
I am assuming that there is a 1->M relationship established between CarCatagory and CarMake. The primary key from the CarCatagory table needs to be a foreign key in the CarMake table. Each pricing scheme should be a field in the CarCatagory table.
Your combobox should be based on a query linking the two tables. It should return the catagory, make, and pricing scheme (and any thing else that you want to "pop" into the form). You could create a multiline readonly textbox and parse each pricing scheme field onto a separate line. Or you can put each scheme into a separate field (I am assuming this is only for reference at this time). This is all attached to the combobox afterupdate event. This combobox would be on a "Rent" form. Have your check out and check in fields here.
Renting the car creates an "open record" which when completed could perform the cost calculations, prints invoice, updates a history or maintenance table, etc.
Since you have already loaded up your cmbCarMake combo box with car makes/models, which I assume the originating data comes from a table via SQL statement, most of the battle is already won. One way to accomplish what you need is to ensure that your SQL statement loads in ALL the data you want and places that data into seperate columns of the cmbCarMake combo box. Obviously, the car make/model will be the first column item, your six pricing schemes can be contained within other columns of the combo box and then eveything is placed onto your form once the car make/model is selected. I would place the code in the 'OnClick' event of the cmbCarMake combo box:
Private Sub cmbCarMake_Click()
Dim x As Integer, Strg As String
Me.txtCarCategory = Me.cmbCarMake.Column(1)
For x = 2 To Me.cmbCarMake.ListCount
Strg = Strg & me.cmbCarMake.Column(x) & vbCrLf
Me.mySchemeTextBox = Strg
Another way to accomplish what you want, (which will only work if the fields in your Form are bound to the fields in your CarTable), is to change the contents of the CarForms' RecordSource property or entering a filter into the Filter property and requerying to display the required data for the car selected. For example:
Private Sub cmbCarMake_Click()
Me.Filter = "[CarMake] = '" & me.cmbCarMake & "'"
Me.FilterOn = True
Now all the data pertainig to that car is displayed within the form.
Your Second Problem:
There are a few long winded ways to do this but the easiest way is to use a Relationship. See Relationships in your Access help system.