I was wondering if anyone could help me? Basically, I've set up a small database for a car hire company, and I have nearly finished it. However, something that I need to do, and am really not sure how to, is, when you select a car from a drop-down box, e.g. Ford Fiesta, the category that the car belongs to is automatically put into the CarCategory text box.
There are five categories - A, B, C, D and E and, for example, you choose the car 'Ford Fiesta' from the drop-down menu, the category 'A' is automatically put into the CarCategory text box (as the Ford Fiesta belongs to category A).
Does anyone know how to achieve this? I would be extremely grateful for any help!
I was able to display a category in a text box based on the item picked in a combo box with the following code:
Private Sub Combo2_AfterUpdate()
Dim CarCat As String
CarCat = DLookup("[Car Category]", "CarTable", "[Car Model] = Combo2.Text")
On Error Resume Next
Text5.Text = CarCat
On Error GoTo 0
My CarTable table has 2 fields: Car Model, and Car Category.
I was unable to run the statement "Text5.Text = CarCat" without seeing run-time error 2115 "The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Access from saving the data in the field.", but using the "On Error..." statement cleared that up for me. If anyone has any insight about this annoying error condition, please share your knowledge with us.
The solution in a small database with a form is attached.
There's still a better way (faster andmore reliable) :
Add the category as a hidden column in your combo.
When the user selects a car, he will automatically will have chosen its category as well but you can't see this.
Way to do this :
Create the combo (name cboCarMod) with 2 columns (CarModel and CarCategory) but set the column width of the second column to 0.
Also on the form : txtCarCat where you need the category.
In the AfterUpdate of cboCarMod, you write this code :