Unique Values Combo Box

[Navyguy]

I think I am learning to ask for help well before spending hours on something simple now...

Another simple one I am sure...

Same form as my previous post with a slightly modified query, query works perfectly outside the form, returns distinct values. When used for the second combo all the values (where there are multiples) appear duplicated.

Looking to only return singular or unique values.

Cheers

 

[arnelgp]

have you tried to requery your Motor Serial Number combobox on OnChange event of your equipment id number combobox?

 

[Navyguy]

Hi and thanks for the quick response...

I have Me.Cbo_Motor_Ser_No.Requery on my AfterUpdate() event of Cbo_Equipment_ID_Number.

I removed it from the AfterUpdate() and added it to the OnChange() event and it had no effect. I also tried having it in both places, not effect either.

Cheers

 

[spikepl]

Please attach your stuff here using the facility provided to do so, not to some external site (that will die and also some people are prevented from accessing those, and your threads and the solutions will no longer benefit subsequent readers)

 

[arnelgp]

How about if you change the query:

SELECT
Tbl_Motor.Motor_Ser_No,
FROM
Tbl_Motor GROUP BY Tbl_Motor.Motor_Ser_No
WHERE
(((Tbl_Motor.Equipment_ID_Number)=[Form]![Frm_Select_Motor_Maintenance_Report]![Cbo_Equipment_ID_Number]))

 

[Navyguy]

Please attach your stuff here using the facility provided to do so, not to some external site (that will die and also some people are prevented from accessing those, and your threads and the solutions will no longer benefit subsequent readers)

Hi spikepl

I tried to that originally, however the file size was too large to allow attachment, thus used my personal FTP site. File size is limited to 100 KB and these were slightly over that amount.

Edit - I used the suggestion from jdraw and was able to add the file to both posts as per your suggestion - Thanks

Cheers

 

[jdraw]

navyguy,

Did you compact and repair and then zip your attachment?
Here are 2 older videos on cascading combos -- concepts are good - it's in ACC2003 .
http://www.datapigtechnologies.com/flashfiles/combobox1.html
http://www.datapigtechnologies.com/flashfiles/combobox2.html

 

[Navyguy]

How about if you change the query:

SELECT
Tbl_Motor.Motor_Ser_No,
FROM
Tbl_Motor GROUP BY Tbl_Motor.Motor_Ser_No
WHERE
(((Tbl_Motor.Equipment_ID_Number)=[Form]![Frm_Select_Motor_Maintenance_Report]![Cbo_Equipment_ID_Number]))

Hi arnelgp

I tried the above and kept getting a syntax error on the WHERE Clause.

I played around with it a bit adding and removing brackets, adding and removing spaces and added the semi colon. Could not seem to get the error to go away.

I took your idea and created a duplicate query and included the GROUP BY and access added the HAVING clause (read up on the difference between WHERE and HAVING); after manipulating the query also I could not get it to return any results.

Thank you for the suggestion.

Cheers

 

[Navyguy]

navyguy,

Did you compact and repair and then zip your attachment?
Here are 2 older videos on cascading combos -- concepts are good - it's in ACC2003 .
http://www.datapigtechnologies.com/flashfiles/combobox1.html
http://www.datapigtechnologies.com/flashfiles/combobox2.html

Hi jdraw

I have to admit, I did not consider zipping up such a small file... I note that the zip attachment limits are larger and will endeavour to do that next go around. Feel free to edit my previous posts so that can be done for the benefit of the group.

Edit - I just realized I could edit my previous posts, so I zipped the files as suggested.

The links you provided don't seem to work (they time out). Went to the main page without difficulty and looked at the index of videos (numerous ones) but I think they are no longer available (or at least for the moment).

Thanks for your suggestion.

Cheers

 

[jdraw]

I just ran to the link from your post and it worked fine?????

I'd like to see your tables and relationships.

 

[Navyguy]

Hi jdraw

For some reason they don't work here... I have a pretty good connection and machine... maybe I will try a different browser...

Here is a pic of the relationship window for your comments.

Generally the Equipment_ID_Number is the main thing connect to all things - think of it like a SIN number connecting all this to a person.

The tables represent things that might be attached to the equipment (thus separate tables) although one could argue they all will have wiring related stuff.

The only out of the ordinary is that there cold be multiple motors connected to the equipment, but only one will be used at a time (so the others are sitting on the shelf as spares / replacements) so I need to add something to define which one is in use (likely via a check box control).

Hope this helps.

Cheers

 

[Navyguy]

I just ran to the link from your post and it worked fine?????

I'd like to see your tables and relationships.

I just watched the videos from another machine... good videos and I think I am on track with the combos.

Cheers

 

[spikepl]

The query designer is a free syntax checker. Use it. Start with less and add- - or rather drag bits and inspect the sql in the sql view.

 

[arnelgp]

sorry about that there was an error on the sql i posted, an extrac comma, try without comma:

SELECT Tbl_Motor.Motor_Ser_No FROM Tbl_Motor GROUP BY Tbl_Motor.Motor_Ser_No WHERE (((Tbl_Motor.Equipment_ID_Number)=[Form]![Frm_Select_Motor_Maintenance_Report]![Cbo_Equipment_ID_Number]))

 

[Navyguy]

The query designer is a free syntax checker. Use it. Start with less and add- - or rather drag bits and inspect the sql in the sql view.

Hi spikepl, I have no idea what you are talking about, but thanks for the suggestion.

Cheers

 

[Navyguy]

sorry about that there was an error on the sql i posted, an extrac comma, try without comma:

SELECT Tbl_Motor.Motor_Ser_No FROM Tbl_Motor GROUP BY Tbl_Motor.Motor_Ser_No WHERE (((Tbl_Motor.Equipment_ID_Number)=[Form]![Frm_Select_Motor_Maintenance_Report]![Cbo_Equipment_ID_Number]))

Hi arnelgp

I actually took the comma away previously knowing that I cannot have two criteria on a SELECT DISTINCT; however I did try your suggestion again and continue to get a syntax error on the WHERE portion of the clause.

I have to admit, I am not convinced that it is the query that is the issue, the query works fine on its own... it is just the combo box that is displaying multiple returns... like the combo is pulling information from the query prior to it displaying unique records.

Cheers

 

[Navyguy]

Hello All

Just an update, I decided to scrap the whole thing and start fresh... It is now working.

I am not sure what the issue is / was but it is not sorted out, but I went back to the basics, I I must have had some setting or code somewhere that was conflicting... don't know.

Thanks everyone for their assistance.

Cheers