asked on

Combo Box not updating

Great Day, I have a form that keeps track of repair orders at an automobile repair shop. The parent form has a number of fields - date of repair, mileage of vehicle, technician who worked on it, etc. I also have 4 subforms:

1. frmJobCustomer Sub - Identifies customer for work being done
2. frmJobVehicle Sub - identifies vehicle for service
3. frmPartsUsedSub - identifies the parts used during repair and their costs
4. frmScopOfJobSub - Identifies labor done on vehicles and the cost of the labor.

In frmJobVehicle /Sub, I created a combo box to permit the user to select which vehicle is being serviced. HOwever, the combo box does not permit me to select a vehicle. I can see all of the vehicles, but in the bottom of the screen, I see the following message:

Control can't be edited; It's bound to autonumber field 'VehicleID'.

The subform does allow for edits and the field is enabled and not locked. VehicleID is ot visible, but the other three fields are, and the bound column is '1'.

The SQL for the combo box is provided below.

Thanks in advance,

r/David

SELECT tblVehicles.VehicleID, tblMakes.Make, tblModels.Model, tblVehicles.[Tag-State]
FROM tblModels INNER JOIN (tblMakes INNER JOIN tblVehicles ON tblMakes.MakeID = tblVehicles.MakeID) ON tblModels.ModelID = tblVehicles.ModelID
ORDER BY tblMakes.Make;

Open in new window

mkrohn

bound column 1 is the VehicleID. Use another bound column and check if it works. You can create another field with the concatenation of all the information you need this way:
Select VehicleID, Make & " " & Model & " " & [Tag-State] AS MyVehicle
FROM tblModels INNER JOIN (tblMakes INNER JOIN tblVehicles ON tblMakes.MakeID = tblVehicles.MakeID) ON tblModels.ModelID = tblVehicles.ModelID
ORDER BY tblMakes.Make;

yddadsjd95

ASKER

thank you mkrohn, I tried all of your suggestions; however, I am still not getting the desired results. I tried to change the bound field, but I am still getting the same error that the control is bound to VehicleID.

Thoughts?

r/David

Mike Eghtebas

I guess you want the combo box to display your choice as well as to allow you to change your selection, right?

Mike

Mike Eghtebas

If so, then you have to make your combo box unbound. Now, to display your choice as you navigate through different records,

In the ONCurrent event of that subform have:

cboYouComboName= me!txtYourChoiceOnThesubform

Change the control names as required.

Also, in the AfterUpdate event of your combo box, have:

me!txtYourChoiceOnThesubform=cboYouComboName

Mike

yddadsjd95

ASKER

Thanks Mike, I'm halfway there. Converting the combo box to an unbound object, did the trick as far as permitting me to make a selection, but I tried your code to populate the appropriate field int eh subform, and it is not working. Here is the subform's Current event code:

Private Sub Form_Current()

cboVehicles = Me.txtMakeModelYear2

End Sub

and the after update event code is below.

Thanks,

David

Private Sub cboVehicles_AfterUpdate()

    Me.txtMakeModelYear2 = cboVehicles
    mbResponse = MsgBox("Is this vehicle with tag number " & Me.TagNo & " the correct vehicle for this repair order?", vbYesNo, "Correct Vehicle?")
    If mbResponse = vbYes Then
        Me.cboVehicles = Me.txtMakeModelYear2
        Me.VehicleMake = Me.cboVehicles
        Me.txtTagNo2 = Me.TagNo
        Me.txtVIN2 = Me.VIN
  
    Else
        mbResponse = MsgBox("Please select the correct vehicle.", vbOKOnly, "Correct Vehicle?")
    End If
    Exit Sub
    

End Sub

Open in new window

Mike Eghtebas

The table bound to subform must have a field to hold the data you select from the combo box like VechicleID (number/long).

Give me the exact name of that field. Also, give me the name of the text box this field is bound to it (maybe txtVehicleID).

So,

txtVehicleID is number/long so should the data your combo box supplying to it.

In the afterupdate event of the combo box, test:

MsgBox cboVehicles '<-- to make sure it gives you the right data. If not then try
'MsgBox cboVehicles.Column(0) or 'MsgBox cboVehicles.Column(1) etc.

Then, make sure it is delivering as number/long. you can do this using:

MsgBox VarType(cboVehicles)

The final code in your AfterUpdate better to be like

txtVehicleID =Clng(cboVehicles)

brb

yddadsjd95

ASKER

Thanks Again Mike, I have attached a copy of the form for you to look at. The field in frmJobCustomer Sub that I want to place the VehicleID is txtVehicleID. Also when I did the msgbox for all of the columns in the Combo Box, they delivered the expected results. However, I cannot get those fields to populate.

If you Open frmJob and go to the subform, frmJobVehicle Sub, you will see that the first and third records populate fine, but when I try to select a vehicle for record # 2, the fields will not populate

Thanks a million for your assistance

r/David
Independent-Auto---EE-9-30-2011-.zip

Mike Eghtebas

I just got it

ASKER CERTIFIED SOLUTION

Mike Eghtebas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Eghtebas

There seems to be lots of other issues with your database.

yddadsjd95

ASKER

Thanks Mike, Since I have not been able to get the vehicle subform, frmJobsVehicle Sub, to work, the other two records were entered via the query and not the form. I also take note of your concern that...

There seems to be lots of other issues with your database.

I am not sure where to start, but that is why I am leaning on you guys to help me. Also, I'm not sure what your suggested code was to do, but it is giving me the same results. I guess my question is "What do I have to do to frmJobVehicle Sub to select a vehicle and have the data populate the fields for any new record (like Record 2) like it does for the first and third records?

thanks again.

r/David

Mike Eghtebas

Hi David,

Last I posted that, I was heading out to handle something, but now I am back.

During my tries, I got some errors (I didn't write it down). I guess I could go over it and try to give you some suggestions for your consideration.

re:> Since I have not been able to get the vehicle subform, frmJobsVehicle Sub, to work, the other ...

May be this condition you describe is the cause of those errors. Could you please correct the data entry error (or inconsistency seems you are writing about) so we can put the existing issue behind. Later, we can discuss some other issue there may be.

Thank you,

Mike

yddadsjd95

ASKER

Thank you Mike, I guess my challenge right now is to clear up the problem with the vehicle subform, frmJobVehicle Sub so I can enter a repair order by using the form. I've been working on it all day, with no success. To me, it really does seem like a simple approach:

1. I have a parent form, frmJob that tracks data relative to a repair order;
2. The subform, frmJobVehicle Sub permits the user to select the vehicle to be repaired.
3. Once the vehicle is selected, the results that I am looking for is that frmJobVehicle Sub will populate with information relative to the vehicle - make, model, tag no., and VIN.
4. frmJob (the parent form) and frmJobVehicles sub are linked by JobID
5. JobID is the Primary Key in tblJobs and JobID is the foreign key in tblVehicles

In my little more than a novice mind, the above approach makes sense to me, but I have veered off the track somewhere.

Any continued assistance will be greatly appreciated.

r/David

yddadsjd95

ASKER

Thank you eghtebas. I've been away from this for a while, but your solution worked.

Have a great day,

David