Using recordset to set report checkbox equal to true/false

[rhewitt]

I'm new to working with recordsets; I've been able to create a new recordset and loop through each record for a given query. I have a database that was built to fill out a paper-based project proposal template. I have re-created the template as a report in my database. There are two sections where the template only has a small area for putting a check next to information (see attachment). I have the text for these options stored in the database rather than checkboxes (more normalized).



As the report is created I'd like to loop through a recordset of the current ProjectID and mark these areas with a checkbox. I need a suggestion as to how to accomplish this without going back and copying the data into individual checkbox fields. I think the answer is through recordsets using this basic logic:

IF field = "Restore and Conserve" THEN 
    Template!cboxRestoreConserve = true
ELSE IF field = "Restore Water Quality" THEM
    Template!cboxRestoreWaterQuality = true

and so on... Where cboxRestoreConserve would be a checkbox on my report.

[rzw0wr]

Question, is the a checkbox on the report now?
Are you want to make the check box yes/no, true/false?

Not sure what you are asking.

Dale

[rhewitt]

There isn't on the photo I attached to the original post, but yes, there will be a checkbox next to each of the options that has an underline prepended to it.

When I run the report I need to access the related tables "tblRESTOREact", and "tblRESTOREprj" to access each record. I'll match the value stored in the related table with one of the options showed above using the basic code logic provided in the original post.

Each time through the report (each project) those checkboxes should be updated (checked = true, unchecked = false) based on whats in the related table.

T

[rzw0wr]

Still a little confused.
This is a generic report. Correct?
Or, will this report be generated for a specific person or place?

and;

I need a suggestion as to how to accomplish this without going back and copying the data into individual checkbox fields. I think the answer is through recordsets using this basic logic:

This I don't understand at all.
Why did you not use check boxes in the first place.

I have the text for these options stored in the database rather than checkboxes (more normalized).

How is this data stored now? Is it referenced by an ID number?
I am assuming you have a table that stores something like "Yes" and "No".
Does the table just hold these 2 items?

Dale

[rhewitt]

This is a generic report.

There is a main table called "Project," which stores information about the project. Anytime there is a field that would contain multiple records (States) rather than having state1, state2, state3 as fields in the project table I have a related "States" table (the most basic level of database normalization).

This is the case with both of the above sections (proposed comprehensive plan priorities and RESTORE act priorities). In their respective related tables I have the text stored rather than a checkbox (Restore and Conserve Habitat, Restore Water Quality, etc.).

Rather than having a subreport that just lists the related records I need to maintain the format of the template that I was provided. Essentially I want to turn on or off checkboxes next to the options in the template provided in the OP.

DB normalization is a balancing act of data integrity and practicality and I likely would have better served using checkboxes in my project table, but hindsight is 20-20.

Clear as mud?

[rzw0wr]

OK, this is not going to be complete. I still don't have a good idea of your tables and structure.

I am going to assume you want to write code for a recordset.

Dim rst as DAO.Recordset
Set rst = CurrentDB.openRecordset("SomeTable/query") Or use a SQL here. There should be a where statement here to filter the data to get the recordset you want.
' If you have more than 1 recordset them do the above again using a different name for the rst.

do while not rst.EOF
    If rst!SomeField you want To Check = "Yes" then
         Do Something or a check box = True
    Elseif rst!SameField = "No" then
         Do Something else or a check box = false
   end if
rst.MoveNext
Loop

rst.close
Set rst =  Nothing

Not a copy and paste code.
You will have to enter the field names.

If you need more help or a better explanation let us know.

Dale

[rhewitt]

Maybe this'll help?

[rzw0wr]

Sorry, Not much.
I have no idea what the data is or what the names mean.

Did my last post get you started?
Are we talking the same thing or am I misunderstanding your question.

Dale