I need a graphic control to change based on number field value

[Guido_042]

I need a displayed image on a form to change based on related number-field value

I am trying to design a small Access database to create Bingo-style game sheets for a product we have developed.

I need to place an OLE control (or picture control) onto a form that will display one of eight small BMP images depending on the numerical value of a corresponding numerical field stored in a table.

See, the bingo-style game sheets have 6 rows of eight pictures per sheet, and there are 1000 sheets. Each row in the entire set must be unique. We accomplished this with an Excel spreadsheet with eight columns of random numbers 1-8 in each cell, times 6000 rows. Each row was assigned a sheet number (1-1000) as well as a row number (1-6). This was imported into Access and scanned for duplicate rows (based only on the game fields, not the sheet/row identifiers) to ensure uniqueness.

Next, a table was created containing 1000 rows, each row matching a sheet number (1-1000) with a "control number" (say, 12001-13000, to be printed on the sheet and used for later winner validation). A form was created based on this control numbers table, containing a text box for the control number up in the corner, and then the graphics for the column headers and row headers were placed across the top and down the left side.

Within the resulting "game area", a repeating subform was created by joining the control number table to the game-values table linked via the sheet number field. On the subform, 8 OLE controls were placed in a single row, one for each of the 8 game-values in each row.

In the same folder as the .mdb file, 8 .bmp files are located, each representing the 8 unique game symbols. What I want to be able to do is program the 8 OLE controls to look at the underlying 8 numerical game values, and then display the appropriate BMP image (1.bmp for value 1, 2.bmp for value 2, etc.). The subform will then repeat 6 times per sheet (once for each row in the game values table with the matching sheet value), and the form will show a grid of unique combinations of the 8 bmp images per sheet.

We can then create unique series's of our game simply by creating a new grid of numerical values. Also, different customers want different images, so we can easily change the 8 bmp files to create a unique look for each customer.

My problem? I don't know how to make those OLE controls swith based on the numerical value. I'm not even sure exactly how to "link" OLEfield1 to game-value field 1, etc.

Obviously, I am limited by my abilities in Access. I don't know any VBA, and I've just learned all this as I go along.

 

[The_Doc_Man]

I wouldn't do OLE.

I would make the controls image controls. If you have .BMP files, that works. When you want, say, PICTURE1.BMP to be displayed, perhaps because the numerical value is appropriate, what you do is to load the STRING C:\MyOverlyLargePathName\PICTURE1.BMP to the .picture property of the image control. OLE not required. Just reload the string. Play with the properties. WHATEVER you choose, DON'T choose EMBED as a picture option. You can play with the other properties if you wish, but setting the image storage property to EMBED would lead to rapid database bloat. LINK is all you need here.

 

[Guido_042]

I understand using a picture control, but how do I make that control decide on its own WHICH picture to display?

For example, when it is displaying the image from sheet1 row1, the value in the number-field may be a 3, so it needs to display 3.bmp

But when it's displaying the next row (remember, this is a repeating form), it the numerical value may be a 7, so it needs to display 7.bmp

Remember, I don't know VBA or how to use the "code" screen, but if you told me how to do it, i could follow your steps. I've done that in the past.

What I have in mind, functionality wise, is something like this (and for simplicity, let's pretend there's only 4 different symbols, not 8): in some kind of source or path property for the picture box, I can insert some code that would provide functionality similar to a long, nested IIf statement, like IIF ([numbergrid].[column1]=1, "c:\longpathname\1.bmp", (IIF([numbergrid].[column1]=2, "c:\longpathname\2.bmp", (IIF([numbergrid].[column1]=3, "c:\longpathname\3.bmp", "c:\longpathname\4.bmp")))))

That way, for each row, the control analyzes the numeric value of the column1 field and then displays either 1.bmp, 2.bmp, 3.bmp, or by default, 4.bmp if the value wasn't 1,2,or3.

And I didn't see any .picture property under the properties tab for the image control object. Is this a code thing? How would I implement this?

Thanks again, and sorry for being such a neophyte. I just went out and bought the WROX Beginning Access 2002 VBA last night, so I'm trying, but this project can't wait for me to read the whole book.

-Guido

 

[The_Doc_Man]

I see you are asking the same question in different ways. You have another thread on this exact topic in the General category. Guido, this is not within proper forum etiquette, but I'll let you slide today. Please consider that for many forum members, a person repeatedly asking variants of the same question in different threads is considered a big waste of time and is the fastest way to get ignored.

Now, to your problem:

Look in your VBA guide and learn about EVENTS, PROPERTIES, and CASE statements. Also look at the Help files for these same topics.

The events that make sense for you are

1. Form_Current event - which occurs when you successfully use one of the record navigation controls like Next, Back, Go To First, Go To Last, etc. It also occurs the first time the form is loaded and immediately follows the Form_Load event. But Form_Current fires after every record change while Form_Load does not. So you want to muck about in Form_Current events.

In the Form_Current event, you can make a simple VBA sequence to determine the value in a control on the form. You can then make that code load a particular file name to the image control's .PICTURE property.

2. Data-control's AfterUpdate event - which occurs after you change a value in a data control. You can use the same code nucleus here that you used for the Form_Current event - to make the image control's chosen .PICTURE property get updated to a new file name.

I don't think this can be done any other way than through VBA. So it is time to take some books home and study the topics.

Conceptually, here is how events work in Access.

Access is sitting on top of your data, monitoring everything that occurs inside your .MDB and through your keyboard and mouse. There are literally many dozens of possible events. When Access sees one of these events, it looks for applicable event handlers. Access calls the event handler as a subroutine. The subroutine can either have zero parameters or sometimes one parameter (usually ONE for events that can request event cancellation, zero for all other events).

So your task is to write the EVENT HANDLER subroutine for a specific event. The confusion comes because there are so many events and it looks like there is no place to jump in. But the truth is, ALL event handlers do the same thing from YOUR perspective. When a specific condition arises that is associated with an event AND you have defined the event code entry point, Access calls your code at that entry point. Your handler RESPONDS to the event that Access detected.

So your task is to write the code to handle the event in whatever way is required. Then you let the code return to its caller.

The good news is that most event handlers, because they are SO specific to a particular event, tend to be short therefore easy therefore not so much of a brain stretch. Find some sample event code in your book to get a feel for what you can do.