Combo Box Default Value / Help Text

jerem

Registered User.
Local time
Today, 12:05
Joined
May 1, 2012
Messages
118
Hi,

Is there a way to create a text default value for a control box that is bound to a number field?

I have a combo box that is bound to an auto number ID but displays text. Bound column = 1, Number of column = 2, Column width = 0; 4cm

I want to use sample text in all my controls (and I know an alternative would be to use the control tip text). In a text box it is easy, I just set the default value to that value and then a before_update event considers the text box empty if the field value is the default value (e.g. Name text box has: Name... as default value). This doesn't work with my combo box since the bound column is a number. I could create a value in the table but then it would appear in the drop box and it is messy.

Any other ways of doing this?

Cross:
http://www.utteraccess.com/forum/Combo-Box-Default-Tex-t2019797.html
http://www.accessforums.net/forms/combo-box-default-value-help-text-45400.html#post236200
 
At least you mentioned that you were referenced your cross posts ;)

I want to use sample text in all my controls (and I know an alternative would be to use the control tip text).
What do you mean by this statement? Is it that you want some sort of default value in the combo box?
 
Yes correct... But since my field is a number, I guess the default value should be a number as well, right?
 
Ideally, yes. But what does the default text relate to? Does it relate to any item in the combo box?
 
It relates to nothing. For example I have a text box for the company name, I want the mention "Company name..." to appear inside. Then the user has to over-write this text with a selection of the combo box (which lists all the company names)
 
Alright, I get you. Do you know how to use the UNION keyword to build SQL statements?
 
I don't know if I have used UNION to its full extent but I have already built some.
 
1. Create a table that has only 1 record and 1 field. Let's say you call it tblMisc, where Misc stands for miscellaneous.
2. Create a Union between your combo box's row source and this form:
Code:
SELECT [COLOR="Blue"]FirstField[/COLOR], [COLOR="blue"]SecondField[/COLOR]
FROM [COLOR="blue"]qryCombo[/COLOR]
UNION
SELECT 0, "[COLOR="blue"]<Company Name>[/COLOR]"
FROM tblMisc
ORDER BY [COLOR="blue"]FirstField[/COLOR];
3. Use this as the Row Source of your combo box.

Amend the bits in blue.
 
That looks pretty cool. I'll try and get back to you. Thanks!
 
I think I am missing something here. The combo box is blank. I created tblMisc, created one filed called fldLabel and added one record in ("Contractor Name..."). I have not removed the AutoNumber primary key field ID - Should I remove it? Here is the SQL:
Code:
SELECT tblContractorContact.ID, [tblContractorContact].[FullName] & "  -  " & [tblContractor].[CompanyName] 
AS NameCompany, tblContractorContact.FullName, tblContractor.CompanyName 
FROM tblContractor 
INNER JOIN tblContractorContact ON tblContractor.ID = tblContractorContact.ContractorID 
WHERE (((tblContractorContact.Retired)=False) AND ((tblContractor.Retired)=False))  
UNION 
SELECT 0, fldLabel FROM tblMisc;
 
Sorry, I thought I would simplify to focus on the issue at stake. I will try again then. I guess I need to add fields in tblMisc and add them in the UNION query then?
 
Let me explain from the example I gave you.
Code:
SELECT 0, "[COLOR="blue"]<Company Name>[/COLOR]"
FROM tblMisc
0 is a fictitious field, "<Company Name>" is just text which you can also regard as a fictitious field. Fictitious because both of them don't exist as fields in the table tblMisc. We can write as many numbers and text in the SELECT part as we like as long as there's a real table in the FROM part. It's like having calculated fields in a query, it doesn't belong to the originating table but it can be made up in a query.

Put the above in a query and try it out for yourself. Then add two records to the table. You'll find that 0 and "<Company Name>" will repeat twice.

So to summarise, the field you created in tblMisc is not going to be used neither is the 1 record you entered. It's only there so that a table exist and so that 0 and "<Company Name>" can appear once.

Does this make sense?
 
It does...
Now I need to apply this to my query. I'll try and get back to you. Thank you!
 
Just did it! It works awesomely well. Exactly what I needed. Thank you vbaInet!

Here is with my initial SQL for those who might have the same need:
I created a table called tblMisc. Created 3 fields (fldLabel1, fldLabel2, fldLabel3). Wrote the text I wanted to appear in the first record of fldLabel1. Here is the SQL statement:
Code:
SELECT tblContractorContact.ID, [tblContractorContact].[FullName] & "  -  " & [tblContractor].[CompanyName] 
AS NameCompany, tblContractorContact.FullName, tblContractor.CompanyName  
FROM tblContractor INNER JOIN tblContractorContact ON tblContractor.ID = tblContractorContact.ContractorID  
WHERE (((tblContractorContact.Retired)=False) AND ((tblContractor.Retired)=False))  
UNION 
SELECT 0, fldLabel, fldLabel2, fldLabel3 FROM tblMisc
ORDER BY tblContractor.CompanyName;
 
You didn't need to write the text in the field but it's fine too. But now that you've got it in a table, perhaps you can re-use it for other combo boxes that you want to have different default values shown. I.e. create a record for each text but you will need an AutoNumber field for this so that you can filter down by which record you want. This is just fyi.

In any case, delete fldLabel2 and fldLabel3 and your final query should now be:
Code:
SELECT tblContractorContact.ID, [tblContractorContact].[FullName] & "  -  " & [tblContractor].[CompanyName] 
AS NameCompany, tblContractorContact.FullName, tblContractor.CompanyName  
FROM tblContractor INNER JOIN tblContractorContact ON tblContractor.ID = tblContractorContact.ContractorID  
WHERE (((tblContractorContact.Retired)=False) AND ((tblContractor.Retired)=False))  
UNION 
SELECT 0, fldLabel, [COLOR="Blue"]Null[/COLOR], [COLOR="Blue"]Null[/COLOR] FROM tblMisc
ORDER BY tblContractor.CompanyName;
 
Something like this?
Code:
SELECT tblSupplierContact.ID, [tblSupplierContact].[FullName] & "  -  " & [tblSupplier].[CompanyName] 
AS NameCompany, tblSupplierContact.FullName, tblSupplier.CompanyName  FROM tblSupplier 
INNER JOIN tblSupplierContact ON tblSupplier.ID = tblSupplierContact.SupplierID  
WHERE (((tblSupplierContact.Retired)=False) AND ((tblSupplier.Retired)=False))  
UNION 
SELECT 0, fldLabel, Null, Null FROM tblMisc
WHERE tblMisc.ID = 2
ORDER BY tblSupplier.CompanyName;
 

Users who are viewing this thread

Back
Top Bottom