Solved

VBA - Combo Box ID

Posted on 2006-07-07
16
529 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
  • 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
 

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
What Security Threats Are You Missing?

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.

 
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 500 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

757 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

20 Experts available now in Live!

Get 1:1 Help Now