How Can I make Default Combo Box Value Blank? 2

[jonmitch]

1) I have a Combo Box that once you make a selection, a SELECT query automically populates four other fileds. The next (and all records for that matter) have the last selection showing in the combo box. How can I make it so that no value is shown in that combo box?
2) Also, how do I make it so that the query will not only select distinct records, but also records that have data? Right now it is showing a line for records with no data.

Any help would be great!!!

 

[jryan224]

I am not sure I can answer #1 without knowing some of the code.

But for #2 you can add

and ((Tablename.fieldname) Is Not Null)

to your sql WHERE clause

Jdemmer
God Bless

 

[danvlas]

For the first, try the Current event of the form:

Sub Form_Current()
ComboName = Null
End Sub

Good luck

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[jonmitch]

Thanks danvlas, your code worked great!

As for #1: here is the SQL code from SQL View
SELECT DISTINCT [Master Table].CompanyID,[Master Table].ReferralCompanyName,[Master Table].ReferralAddress,[Master Table].ReferralCity,[Master Table].ReferralState,[Master Table].ReferralZip FROM [Master Table] WHERE [Master Table].CompanyName Is Not Null;

Is CompanyID messing this up? I don't think I can take it out though? Shouldn't the WHERE part only take those rows where there is value for CompanyName?

 

[jryan224]

jonmitch

okay, I may be brain dead this morning, but why would you have records in a company table with no data? or no company name?

But I think I found your problem, [Master Table].CompanyName has to be in the list of the SELECT clause in order to be evaluated in the WHERE clause.

So try:
SELECT DISTINCT [Master Table].CompanyID,[Master Table].ReferralCompanyName,[Master Table].ReferralAddress,[Master Table].ReferralCity,[Master Table].ReferralState,[Master Table].ReferralZip,[Master Table].CompanyName FROM [Master Table] WHERE [Master Table].CompanyName Is Not Null;

This should eliminate the blank records. BUT it may not give the results you are looking for since now the DISTINCT is acting on a different scenario.

You might try creating sequential queries.
The first query gets only records with data, then the second query is based on the first query and does the unique record selection you need.

For reference, read the following excerpt I took from Access97 help about DISTINCT.

"If the SELECT clause contains more than one field, the combination of values from all fields must be unique for a given record to be included in the results."


Hope this helps,
JDemmer
God Bless

 

[jonmitch]

Nope, I am the one who is braindead.
I made a mistake and you clued me in. The WHERE clause should be "ReferralCompanyName" not "CompanyName". As soon as I changed that, it worked fine for blank values.

However, I am still not getting distinct records? Is CompanyID messing that up? I tried taking it out, but then ReferralCompanyName does not show up anymore.

Any ideas?

Here is my revised SQL Code:
SELECT DISTINCT [Master Table].CompanyID, [Master Table].ReferralCompanyName, [Master Table].ReferralAddress, [Master Table].ReferralCity, [Master Table].ReferralState, [Master Table].ReferralZip FROM [Master Table]
WHERE ((([Master Table].ReferralCompanyName) Is Not Null)) ORDER BY [Master Table].ReferralCompanyName;

Thanks for any help!

 

[jryan224]

jonmitch,
If companyID is unique, then yes, as you put it, the companyID is messing it up.

For reference, read the following excerpt I took from Access97 help about DISTINCT.

"If the SELECT clause contains more than one field, the combination of values from all fields must be unique for a given record to be included in the results."

So if companyID is unique, each and every record is unique.

i.e. where first column is companyID

1 My Company lake st tampa
2 Your Company ocean st miami
3 My Company lake st tampa

The first and the third records look like duplicates on company name and you would like "My Company" to appear only once. But since companyID is in the select, each of the three above records is unique and will appear.

However,in the dummy db I am trying this out on, I deleted my CompanyID from the query and it worked ok. Are you sure there is not something else that is making the records unique. Company name the same, but address different?

Here is my dummby db.
tblONE
id(autonum)CoName(text) Address(txt) City(txt) State(txt)
1 My Company 123 fine la
2 My Company 234 same ak
3 Smart co
4 Dumb co
5 Dumb co
6 Dumb co
7 Smart co
8
9

In query design sql I have:
SELECT DISTINCT tblOne.CoName, tblOne.Address, tblOne.City, tblOne.State
FROM tblOne
WHERE (((tblOne.CoName) Is Not Null));


which gives the following results:
CoName Address City State
Dumb co
My Company
My Company 123 fine la
Smart co
Smart co 234 same ak

I did not put it in company order so that you can see which records are affected.

Note My Company is there twice because combination CoName/address/city/state are different between the two records.

Smart co is there twice for same reason.

Dumb co is there only once because the combination CoName/address/city/state are exactly the same in the records for id 4, 5, and 6.

Note recored id 8 and 9 are not there because CoName is null.

I would analyze the data a little more closely. Deleting CompanyID should not make the ReferralCompanyName go away. If you are using query designer, make sure all the "show" checkboxes are checked. Easy to accidentally uncheck one.

Good luck, let me know how it goes.
JDemmer

 

[jonmitch]

Thanks a lot jdemmer...you were very helpful! The Select does work now and my problem did not lie in the Query, but in the Column Count, Column Width and List Width fields. I also enhanced it even further to only show those records where none of the fileds are null and it works great!

Thanks again!