ComboBox not working

[Zack Barresse]

Working with ComboBox's here. I've got two of five to populate on my form. For some reason it's not working. I've attached a zipped copy of the tables/forms used to generate the data. It's all for a report I need to get working to filter for.

Now the report works if the first two ComboBox fields are used. I'm not sure what I need to do in order to populate these objects. I was trying to use the RowSource property, but doesn't seem to be grabbing anything.

[Zack Barresse]

Doh! Can't believe I realized this after I posted.. I didn't have the ID field in the query of the RowSource property. LOL! It works now!

Hmm, I wonder if there is a way to not have duplicates show in the ComboBox's though.. Anybody know about that?

[Norie]

Zack

If you are using a query to populate the combobox(s) then just set the query to only show unique records/values.

I'll download your attachment and see if I can work out what's needed and post back.

[Zack Barresse]

Thanks Norie, appreciate the extra set of (experienced) eyes. :)

[Norie]

Zack

I wasn't sure which comboboxes you meant but the SQL for cbField would be something like this.

SELECT DISTINCTROW Fields.ID, Fields.Field
FROM Fields
ORDER BY Fields.Field;

or

SELECT DISTINCT Fields.ID, Fields.Field
FROM Fields
ORDER BY Fields.Field;

To set the properties for the query just right click, select properties and look for Unique Values/Unique Records.

[Zack Barresse]

I don't see any Unique Values/Unique Records in the Properties dialog box for the controls. Should I be looking somewhere else? This is AC 2003 btw.

[Norie]

No it's for the query that is the record(row?) source of the combobox.

Click the ellipsis ... in that property and the Query Builder should open.

That's where you can set the property(s) I mentioned.

[Zack Barresse]

Okay, I see the query builder, but where is this ellipsis or properties?

[Zack Barresse]

Okay, found those properties. But it did nothing to change the items in the ComboBox's. They are still listing all records, not just the uniques.

[Norie]

Zack

I couldn't actually see any duplicates in the data.

Also since you have a primary key then that might be causing the problem.

Is it the primary key you need for this or the value?

[Zack Barresse]

I'm not sure I fully understand what you're asking; some of this is still confusing to me. I don't really need the primary key and I'm thinking about deleting the ID field here, I don't use it for anything and it's just caused me problems. There are four other fields which are sharing a primary key, that will prevent the users from doubling a sample entry.

This seperate form is for my report only, not data entry. I am using the combobox's to allow the users an ability to filter the report for specific values only if desired. This doesn't have to be working, would only be nice. My thinking was, if (when the user clicked the dropdown) only unique values from the table referenced would show. Some of the combobox's are looking at a seperate table.

[Norie]

Zack

I did download the attachment but I didn't examine it closely - I'll take another look.

I believe your opening the report with code and trying to use a filter based on the selections from the comboboxes? Is that right?

[Zack Barresse]

Yes that is correct. And everything works very good, except the duplicates. I can live with them if it's too difficult though. Thanks for looking at this Norie. :yes

[Norie]

Zack

I can't see any duplicates in your data.

Could you point them out, perhaps I'm missing somehing.:)

Like a t in something.:)

[Zack Barresse]

If you open the Soil Sample Filter form and select the Date drop down box, you should see 1-Feb-2006 duplicated. Do you see them?

[Norie]

I'm afraid I don't.

When I try to open the form I get an error.

But I sorted that by removing the Record Source property for the form.

I then went to the properties of cbDate and changed ColumnCount to 1 column and cleared out the ColumnWidths property.

And I also changed the RowSource to this.

SELECT DISTINCT [Soil Samples].[Date] FROM [Soil Samples];

Does that help any?

[Zack Barresse]

This is the RowSource property of the Date object ..


SELECT DISTINCTROW [Soil Samples].ID, [Soil Samples].Date FROM [Soil Samples] ORDER BY [Soil Samples].ID;