newbie trying to use dynamic values in access 2010 form

[T5M2J2]

I am setting up a bilingual database to register children for events. The database will only be used once per event. After the event, the database will be emptied out to use for the next event. During each event, the children will be placed into 4 groups that rotate to individual stations (ex. event, snack, video, exercise). Students will get name tags (printed from the database) that will be color coded based on their group.

Here is a simplified version of the relevant tables:

StudentTBL
---------------
StudentID
FirstName-Lang1
LastName-Lang1
FirstName-Lang2
LastName-Lang2
Grouping [one through four]
AlergyID [related to AlergyTBL]

ColorTBL
--------
ColorNumber
ColorName-Lang1


ColorName-Lang2
ColorHexValue

AlergyTBL
---------
AlergyID
AlergyName-Lang1
AlergyName-Lang2
AlergyImage

I plan to have a toggle button on my forms that will allow the user to toggle between a Lang1 or Lang2 interface on the forms -- but that is for anther day.

My issue is with printing name tags.

Problem 1:

I want to match[StudentTBL].[Grouping] to [ColorTBL].[ColorNumber] and then use the corresponding [ColorHexValue] as the value for the background color property on my nametag form. I've tried to several different ways that I've seen on forums, but I'm not having any success.

In other words, children in group 1 should have nametags with a green background; children in group 2 should have nametags with a blue background, etc.

Problem 2:

I want the nametags to display the child's allergies, if any. At this point, I don't care if it is a textual display or if it uses the PNG/JPEG icons in [AlergyTBL].[AlergyImage]. Since the number of alergies is not static, I assume I could not have the database form do this without some type of additional scripting but I thought I would ask.

[June7]

First, for name tags don't use form, use report for printing. Never done but I expect code in Format event can set BackColor of report sections specific to each record as the report renders. Definitely can't do that on forms.

Second, consider textbox Conditional Formatting - no color table and no code required.

Displaying allergy info with image is simple enough if only one allergy. How will you deal with multiple allergies?

[T5M2J2]

First, for name tags don't use form, use report for printing. Never done but I expect code in Format event can set BackColor of report sections specific to each record as the report renders. Definitely can't do that on forms.

Second, consider textbox Conditional Formatting - no color table and no code required.

Displaying allergy info with image is simple enough if only one allergy. How will you deal with multiple allergies?

First, thank you for the reply.
I changed everything to a form. It now looks like this:

This image is what I'm going for. A color background/border. An image based on the child's group number. The child's first name. Icons or texts for each of the child's alergies.

This form is based on a query called Crew Info. It pulls from an additional table that has crew info -- including the crew image and the adult crew leader info.

The form shown in the image does work -- but I have to do a different one for each group/color. I was hoping to have access do something like: IF [Group] = 1, then use the green nametag but I wanted to be able to change the colors.

I've decided to permanently set the colors to the group numbers. I think I can make that work -- it just means a little less flexibility. I really am a newbie. I first want to get it to work and understand how it works. I'll try to get it fancy later.

The conditional formatting you mentioned will only work for the textbox and will not set the background for the entire nametag.

I still have no clue what to do about displaying alergies. I have a one to many relationship between AlergyID in the StudentTBL to the AlergyID in the AlergyTBL. I've tested a StudentAlergy query and was able to get the correct number of rows from it (a student with 3 alergies returned three rows -- each with a different alergy name and alergy icon). Do I somehow concatenate the text of the alergy names and then print them in a text box? I don't have a clue how that would be done.
Is there a way to say -- if three rows, then print three icons on the form where the icons are icon1, icon2, and icon3?

[June7]

The report RecordSource has ColorTbl joined to StudentTbl on the Grouping and ColorNumber fields?

If the values in ColorHexValue are like '&H51A9FC' code in the Format event:
Me.Detail.BackColor = Me!ColorHexValue

If the values in ColorHexValue are like #FCA951 then review this about using hex color codes http://blogs.office.com/b/microsoft-...m-in-code.aspx

You can get the RGB number combinations from the color picker and code like:
Me.Detail.BackColor = RGB(100, 200, 100)

An alternative is a large textbox bound to Grouping field with conditional formatting and other controls are transparent and layered on top of the big box.

I think a subreport will be needed for the allergy records. Suggest positioning the subreport on the right side of the label. Unfortunately, this presents another issue of matching the subreport color to the main report color. Reports do not have transparency.

[T5M2J2]

June7,
I tried to simplify my table names/structure in my original post. Here is a view of my tables and the query I am using to make my nametags. The only other tables I have are: 1) guardian table and 2) student/guardian join table.


Hopefully you will be able to stretch/expand it so it's legible.

Anyway, I tried to use the code that you showed me but it's not working. Maybe I'm not putting it in the right place.
I opened a copy of the nametag form in design mode. I then hit the "view code" button and enter what you gave me.
When it didn't work the first time, I changed #008000 to &H008000 in my color table. The background color did not change.

Am I putting it in the wrong place?

[June7]

You are working with a report, not form?

The code I suggest would have to be in a report because reports have Format event, forms don't. Code in Format event executes only when report is in PrintPreview or direct to printer.

[T5M2J2]

yes, I'm working with a form -- I used the wrong word in my previous post.


I am not familiar with the term "Format event". I know that there is a "On Format" event in the properties section under the details section of a form. Is that what you mean by "Format event"? I entered what you suggested in that box. When I switched to print preview, I got an error: "Microsoft Access cannot find the object "Me." If 'Me' is a new macro or macro group, make sure you have saved it and that you have typed its name correctly."
I've tried to use google to find out if "format event" could refer to something else in Access 2010, but no luck.

I've also tried clicking on the view code button and entering it there:


Can "format event" refer to something other than the "On Format" event for the details section of the form? Sorry for the dumb question....

[June7]

You mean 'report' instead of 'form'?

They are the same event. Each section of a report has a Format event.

Forms do not have Format event.

Review this article about programming in Access http://office.microsoft.com/en-us/ac...010341717.aspx

Excerpt: On the Event tab of the property sheet, click in any property box that displays [Event Procedure], and then click the build button .

[T5M2J2]

Thanks for the input. I now have the background for my nametags exactly how I want them and they are easy to manage.

For the allergy icons, you suggested a subreport and to display them on the right side. If I understand correctly, that would display them from top to bottom. I'm a little concerned about what happens if they don't all fit. This year, we had a child with 4. Is there a way to get them to display horizontally instead of vertically? I was thinking of trying to set the height of the subreport to only fit a single icon and then have it have multiple columns. Am I on the right track?

[June7]

Rearranging records to display horizontally (left to right) would require a crosstab query or other methods demonstrated in http://forums.aspfree.com/microsoft-...ry-322123.html. However, I don't know if any can handle the icon image. Is the image in an Attachment field?

[T5M2J2]

yes, the image is in an attachment field.

[June7]

You can try the suggested methods and let us know if works with the attachment field.