Solved

Combo Box not updating

Posted on 2011-09-30
14
315 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:yddadsjd95
  • 7
  • 6
14 Comments
 
LVL 3

Expert Comment

by:mkrohn
ID: 36893183
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;
0
 

Author Comment

by:yddadsjd95
ID: 36893394
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
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 36893558
I guess you want the combo box to display your choice as well as to allow you to change your selection, right?

Mike
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 36893592
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
0
 

Author Comment

by:yddadsjd95
ID: 36894053
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

0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 36894455
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
0
 

Author Comment

by:yddadsjd95
ID: 36894706
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
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 36894765
I just got it
0
 
LVL 33

Accepted Solution

by:
Mike Eghtebas earned 500 total points
ID: 36894823
in that subform, if you change the following line:

Private Sub Form_Current()
    cboVehicles = txtVehicleID 'Me.txtMakeModelYear2

End Sub

it will show the type of cars as you navigate for two of them.

When it works for two but not the other it means we have problem with the data not the application.

brb
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 36894845
There seems to be lots of other issues with your database.
0
 

Author Comment

by:yddadsjd95
ID: 36895038
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
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 36895168
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
0
 

Author Comment

by:yddadsjd95
ID: 36895378
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
0
 

Author Closing Comment

by:yddadsjd95
ID: 36964961
Thank you eghtebas. I've been away from this for a while, but your solution worked.

Have a great day,

David
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now