Colour Searching and Referencing...

[ABitOfANoob]

Good Morning All (Though its afternoon here!)



I hope this is in the correct section - apologies if not! Further apologies for the length of this post - I'd like to fully explain my situation so potential helpers have all the details.

A friend has a small beading/jewellery business and she has asked if I could make her a database to keep track of her stock. I rashly said "Yes, no problem" without really considering what she was asking for!

The vast majority of the database will be very simple and I think I can probably manage it but there is a bit that has me stumped.

Naturally she will want to catalog her beads, and whilst many of the variables will have only a few different values, colour is stored as an RGB value with over 16 million possibilities - thats a big combo box!

What I wanted to achieve was for her to be able to enter the colour via a picker so she can match the colour of the bead by eye as opposed to giving it a text string like "Dark Yellowy Orange" etc or by entering the exact RGB values. That part I have so far managed. The function I am using changes the BackColor property of the text box to the selected colour and also writes the RGB values in the box as a value.

The bit I really need help with is in searching for records. What I want to be able to do is to have a switchboard command that opens the same function so she can use the colour picker to choose an approximate colour (and its RGB values) for the beads she wants to find. The query/macro would then return all matching records (based on other parameter queries as well).

But for her to select the EXACT same colour as she entered when storing the data originally is going to be nigh on impossible (1 in 16 million) so is there a way for the query/macro to allow a 'lee-way' on the RGB values and return all matching records for the RANGE of RGB values, say +/- 20?

Am I babbling? Ill try and explain!

Original record stored (visually with colour picker) with RGB values of 100,100,100.

When the query/macro is run, the colour picker appears and she selects (again, visually, without entering RGB values)a purpley colour: 97,87,102.

She doesnt have any beads that match this precise colour, but with a code that allows values between 77-117, 67-107, 82-122 her above record is returned as a close match.

Is this possible?


Many thanks for simply reading this behemoth - many more thanks in advance for any help!


-----EDIT-----

Ok, the original code seems to not be that great for this purpose! First of all, instead of storing as RGB values, the value returned is the literal colour value (ie between 0 and 16.5millionish) and so instead of offering -/+ 20 on RGB values, I would need to offer -/+1000000 on the literal value.

Also, when scrolling through stored records on the form, although the actual colour value (number out of 16million) changes, the background colour stays the same as the last record entered for each record. I need to get the code to reference the number in the text box, convert it into a colour again and then set the background to that colour.

[June7]

If I understand, you need to convert the RGB code selected in color picker to OLE color code?

Check out these:
http://vbnet.mvps.org/index.html?cod...slatecolor.htm This link has hard time opening.

http://bytes.com/topic/access/answer...rs-rgb-colours

[ABitOfANoob]

Many thanks for your help, that is part of my problem solved so great!

What I'm really having the difficulty with is being able to search for a colour with the colour picker, and as Access cant return records based on the background colour of a field textbox (Can it?? that would be easier), I want it to take the Long value of the colour selected from the colour picker, and return records with colour fields within -/+ 1,000,000 of that OLE value.

[June7]

Apparently you can get the color of textbox. See this http://www.ehow.com/how_6934903_chec...ccess-vba.html (look at step 10)

[ABitOfANoob]

Riiiight,

I did look through Google but I was looking for help with the number problem so I didnt see this workaround.

So am I right in thinking thta I can get the colour of the textbox set by colour picker on record entry, then have a button on the Swiitchboard called "search by colour".

Could I manage it with a code like this?:

DoCmd.OpenForm "Beads", acNormal, , [Exact_Colour].BackColor Between [Please Choose Your Colour:] - 1000000 and [Please Choose Your Colour:] + 1000000, , acWindowNormal

Obviously I'll try this but at work atm and dont have Access handy to test.
I think I may have mixed up true VBA and Macro Coding there... The other thing is will this kind of code prompt the user twice? Also, can I use the same code which opened the colour picker from the "Beads" form on the switchboard to prompt for the colour?

I appreciate all your help!

[June7]

Need to backup. Color picker is a design tool. What are you going to offer the user as an interface to select color? If you don't have that solved, check this http://access.mvps.org/access/api/api0060.htm

I was under the impression color value is saved to a field in table for each item. You need to apply the value of the color selected as criteria for filtering records. I am not clear on how the user will pick color and then what event will the code go in?

lngColor = Me.textboxname.Backcolor
DoCmd.OpenForm "Beads", , , "[color field name] Between " & lngColor - 1000000 & " And " & lngColor + 1000000

[ABitOfANoob]

Righteo, sorry I may have confused myself!

That code that you have provided looks promising though!

On the form, there is a textbox called "Exact Colour". It has an OnClick event to open the colour picker and then change the BackColor of the textbox to the selected colour and also populating that textbox with the OLE number for the colour. This works (at least partly).

The reason for the colour picker is so the user can easily match the colour of the item being catalogued without having to know the code.

The reason for the OLE number was to make future searching easier for the system.

What I am aiming for is for the user to open a search from the switchboard which brings up the colour picker again. They can then choose the colour they are after, and have the database return matches within a certain range of that colour.

Am I right in thinking that I can have a switchboard button trigger a macro or function?

Does that make more sense?

I really appreciate your help with this! I'm trying to learn all I can!

[June7]

Certainly. Code in the button's Click event procedure can call other procedures (subs, functions, macros).

[ABitOfANoob]

Right!

So if i use the original code to bring up the colour picker upon entering the data, in line with the code you have given me, I should be on the right track!

Many thanks! I'll try this at home later and let you know how i get on!