Populating fields in another form via checkbox selection

Originally Posted by wideasleep

Greetings all,

I am fairly new to Access but am learning quick by just using it at work while I'm recovering from a work injury.

I am using Access 2007 on Vista Home Premium.

Here's a long explanation of what I am trying to do. I hope this is feasible.

I have two forms that I am creating, the 1st form is as follows.


This form has a checklist for each ITEM NUMBER. I have a choice of SATISFACTORY, SUBSTANDARD and N/A. What I need to do is to be able to pass on only the ITEM NUMBER that has the SUBSTANDARD selection on to another form. Each ITEM NUMBER is listed as 1.1, 1.2 and so on. After each ITEM NUMBER is a description of that number such as, fire extinguisher inspection up to date

The 2nd form is a listing for all the SUBSTANDARDS from the 1st form. The heading for each line item is, ITEM NUMBER (which is what I need from the 1st table), HAZARD CLASS, LOCATION, CORRECTIVE ACTION and STATUS.

If any code modification is needed be aware I am green when it comes to code but I learn quick!

Originally Posted by ADezii

I've made the following assumptions:

1. The 1st Form contains the [ITEM NUMBER] Field and 3 Check boxes named SATISFACTORY, SUBSTANDARD, AND N/A.
2. The 2nd Form, for code demo purposes only, is called frmSUBSTANDARDS.
3. The Item Number Field in the 1st Form is named ITEM NUMBER.
4. The Item Number Field in the 2nd Form (frmSUBSTANDARDS) is named ITEM NUMBER.
5. frmSUBSTANDARDS is Open.
6. Place this code in the AfterUpdate() Event of the 1st Form as indicated inorder to pass the Item Number to the 2nd Form only if the SUBSTANDARD Check Box is checked.
   
   Expand|Select|Wrap|Line Numbers

   1. Private Sub Form_AfterUpdate()
   2. If Me![SUBSTANDARD] Then
   3.   'Pass ITEM NUMBER to 2nd Form only if SUBSTANDARD Check Box is checked
   4.   Forms!frmSUBSTANDARDS![ITEM NUMBER] = Me![ITEM NUMBER]
   5. End If
   6. End Sub

Originally Posted by wideasleep

Your assumptions are correct. I'll be going into work this morning and give it a go. Thanks for the assistance.

Originally Posted by ADezii

No problem, let me know how you make out.

Originally Posted by wideasleep

Having a bit of problems but not giving in yet. Any chance I can send you a few word docs to give you an idea of what I'm doing? That way it might be a bit more clear and maybe you can give me a better way of doing this. I appreciate your time.

Originally Posted by ADezii

Post only the relevant information here, so others may also assist you if needed.

Originally Posted by wideasleep

I was getting a few error messages which I figured out but the only thing not happening is the populating of the ITEM NUMBER in the other form.

Originally Posted by wideasleep

Could this be because of the way I put a check box in my form? Using an option group with check boxes or just 3 individual check boxes.

Originally Posted by ADezii

You did not tell me that your choices were mutually exclusive which they are by being contained within an Option Group. It is usually not a good idea to use Check Boxes inside an Option Group, but you are correct in that this would affect everything. You must now check and see if the User checked the Substandard Box via the Value returned by the Option Group. If you do not know how to do this, or you are confused, please ask. Either myself or someone else will get back to you with a reply. Good Luck.

Originally Posted by wideasleep

Sorry if my explanation was misleading. Let me try this again for anyone that wants to take a stab at assisting me. I can see now where my explanation was quite a bit misleading especially as I learn access.

This is a multiple line checklist. Each line has a description of what needs to be inspected. Each description is assigned a static ITEM NUMBER so they are listed together (1.2 No chemical spills are present) and each has a choice of SATISFACTORY, SUBSTANDARD or N/A. A check box exists for each of the three choices. This is how it's laid out on our paper forms and how it needs to stay in electronic form due to standards of informational safety in the chemical industry. The electronic form can deviate a little from this as long as the point gets across during an inspection of our records.

On the second form any substandards (number only ie 1.2) must be listed in a field called ITEM NUMBER along with additional fields that aren't related to the 1st form. So in a nutshell the SUBSTANDARD check box must pass on the ITEM NUMBER to the other form.

I hope that paints a better picture.

Originally Posted by ADezii

Assumptions:

1. Your Option Group Name is fraRating - just replace fraRating with your Option Group Name if it isn't.
2. Items within this Option Group are listed in the following order: SATISFACTORY, SUBSTANDARD, N/A.
3. In the AfterUpdate() Event of the Option Group, place the following code.
   
   Expand|Select|Wrap|Line Numbers

   1. Private Sub fraRating_AfterUpdate()
   2. If Me![fraRating].Value = 2 Then      'SUBSTANDARD Check Box selected
   3.   Forms!frmSUBSTANDARDS![ITEM NUMBER] = Me![ITEM NUMBER]
   4.   'You may want to set Focus to the SUBSTANDARDS Form at this point
   5. End If

Originally Posted by wideasleep

Looks like this is working but I'm going to try tweaking it a few different ways especially if I'm understanding the VB language right. Anyone have any suggestions on good reading materials on VB?