VBA - Combo Box ID

I ave a combo box, which I want to move from one form to the other. On the new form I will have a combo box listing staff members and another listing offices. I have tried to simply copy and paste the box, but it tells me that "this control has an invalid control source" But the control source works fine on the other form. I have also tried recreating the combo using the wizard, but i get the same error message. Can someone tell me why?
andyb7901Asked:
Who is Participating?
 
millscoConnect With a Mentor Commented:
SELECT Ucase([IDOffice]) AS OfficeID, Ucase([fldOffice]) AS OfficeFld
FROM TblOffice
ORDER BY TblOffice.IDOffice;

Note you will need to name your fileds AS something different from teh filed name in the underlying table and then you will need to change teh recoirdsource on your combo box to read the revised field name.
0
 
peter57rCommented:
Hi andyb7901,

What is the controlsource?  Is it part of the form's underlying query/table?

Pete
0
 
andyb7901Author Commented:
The control source is the Office Id (IDOffice), its exactly the same as the other form, except it doesnt work?? It populates the combo box with names, but woant allow you to click on one or select one, or populate a name to start the box of??
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Chuck WoodCommented:
Is the RecordSource the same for both forms? If not, the ConrolSource may not be available for the combo box.
0
 
andyb7901Author Commented:
I dont know what you mean by that, im not really clued in about combo boxes??
0
 
andyb7901Author Commented:
Ah sorry I get what you mean now, the two different forms, have two different record sources. Is there anyway to assign the actual combo box to a record source apposed to the form?
0
 
peter57rCommented:
The point is you have to set the ControlSourse for your combo box to the name of the field where you want to store the selected value.

You will get a list of all the fields as a drop-down when you click into the controlsource property.

Pete
0
 
Chuck WoodCommented:
You might want to remove the ControlSource from the combo box, copy the combo box to the new form, restore the ControlSource to the original combo box, and select a new ControlSource from the drop-down box in the ControlSource property in the new combo box.
0
 
andyb7901Author Commented:
The combo data will be from two different tables though. Will that matter? Will I not have to assign two record sources??
0
 
peter57rCommented:
In that case you should scrap your current combo box in your second form and use the wizard to create one that is suitable for the form.

Pete
0
 
Chuck WoodCommented:
Set the RowSourceType property to Table/Query, select the elipses (...) button next to the RowSource property, and build your own query for the combo box.
0
 
andyb7901Author Commented:
I kind of have it working now, but does anyone know how to put the first office in the combo box before the drop down is pressed, apposed to all being hidden until you select one, and also how to turn them all into capitals??
0
 
Chuck WoodCommented:
>how to put the first office in the combo box before the drop down is pressed

I just have the combo box drop down. Would that do it for you? If so, I can give you the code to add so that happens.

>how to turn them all into capitals

In the query you made to populate the combo box, use the Ucase() function.
For example, if your field is named "LastName" then in the top box in the query enter:

LastName: Ucase([LastName])
0
 
andyb7901Author Commented:
Basically the two combos I have the first one just has the first field in combo visible in the combo before the combo is expanded. The second combo doesnt do this, so looks odd?

Im not to sure what you mean by the Ucase() function. I havent ran a query on it, i have simply called data using the wizard. All I want is for the cmobo box to be in all uppercase?
0
 
Chuck WoodCommented:
1. Select the ellipses (...) button next to the RowSource property of your new combo box.
2. Select the View menu => SQL View.
3. Copy the SQL statement and paste it in here so I can see your query.
0
 
andyb7901Author Commented:
SELECT TblOffice.IDOffice, TblOffice.fldOffice
FROM TblOffice
ORDER BY TblOffice.IDOffice;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.