Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VBA - Combo Box ID

Posted on 2006-07-07
16
Medium Priority
?
536 Views
Last Modified: 2008-02-01
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?
0
Comment
Question by:andyb7901
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
  • +1
16 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 17059978
Hi andyb7901,

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

Pete
0
 

Author Comment

by:andyb7901
ID: 17060077
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
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 17060095
Is the RecordSource the same for both forms? If not, the ConrolSource may not be available for the combo box.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:andyb7901
ID: 17060171
I dont know what you mean by that, im not really clued in about combo boxes??
0
 

Author Comment

by:andyb7901
ID: 17060179
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
 
LVL 77

Expert Comment

by:peter57r
ID: 17060207
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
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 17060270
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
 

Author Comment

by:andyb7901
ID: 17060297
The combo data will be from two different tables though. Will that matter? Will I not have to assign two record sources??
0
 
LVL 77

Expert Comment

by:peter57r
ID: 17060342
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
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 17060355
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
 

Author Comment

by:andyb7901
ID: 17060406
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
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 17060467
>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
 

Author Comment

by:andyb7901
ID: 17060674
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
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 17062007
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
 

Author Comment

by:andyb7901
ID: 17064351
SELECT TblOffice.IDOffice, TblOffice.fldOffice
FROM TblOffice
ORDER BY TblOffice.IDOffice;
0
 
LVL 1

Accepted Solution

by:
millsco earned 2000 total points
ID: 17067634
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

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

609 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