Change a record in a SubForm after update

[frog40]

I have 3 choices in an Option Group Frame on the Main Form. I have a SubForm for a different table. If the user clicks on the 1st Option, the SubForm table (check boxes) is filled in, but not updated yet.

If the user changes his mind and decides to choose Option 2 instead, the system automatically updates the data in the SubForm for Option 1.

When Option 2 i selected (after Option 1 was selected), I need to Go To the Record in the SubForm Table forOption 1, which is now updated and put the data back to null.

I have tried several different methods, but cannot get anything to work.

Thanks in advance

 

[TheAceMan1]

How are ya frog40 . . .

You have alot of gray area here . . . hard to say up front.

Are you saying your updating checkboxes in the [blue]currently selected record[/blue] in the subform or [blue]all records?[/blue] . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[frog40]

Thanks for responding AceMan1 - what I need is this:

Go To Record (the table in the SubForm)which has already been updated. Once I get to that record I need to Go To the last ROW(record) to change the checkbox(s) from "true" to "false"

If Option 2 is never clicked, there is no problem. Option 1 will remain as the user entered the data. The problem is that the User might change his mind and then select Option 2. This is where I have a problem going back to the SubMenu table that was selected by Option 1. I need to get to the ROW (last record) in the SubMenu table and change the "True" checkbox(s) to "False".

I tried Undo, Undo Method, Undo Event, Cancel Event - none of those work because they do not directly apply to a checkbox except for the Undo Method. However, I could not get to the ROW I needed to get to.

If you still see a lot of gray area, let me know and I will try a better explanation.

 

[MD5150]

Frog,

There are a few ways to do what you need by using things like SetFocus, GoToRecord, Sendkeys, or recordset updates via code, but none of these are recommended as a solution to what you describe because it sounds like you have more of a design issue since you are wanting to dynamically update a detail record based on a main form option group.

You might be better off using default-value logic in the subform table, which will reference your main form option group. This way, nothing is set in stone until the user starts entering data into the subform. Your subform checkbox will then update accordingly as it references the main form option group before witing to the record.

Hope this helps,
Mike Dorthick

 

[TheAceMan1]

frog40 . . .

I'm sure you problem can be solved ([blue]overall you need to update thru queries and requery the subform, after which you set focus to the record modified[/blue]). There's just certain info needed and can only be pinned down by asking questions. Learn to answer the questions of those you seek help from particularly if your considering them one of the experts!. I'll try my question in another way:

[blue]When you select option1 what record on the subform is modified? . . . [purple]the currently selected subform record or some record selected by code?[/purple][/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[frog40]

Aceman1 - The answer to your questions is the Currently selected SubForm Record is being modified. Please don't give up on me. I am desparately trying to understand a lot of code and am not always successful.

Let me say this though, in relation to the records that are being updated by selecting Option 1. If this is a design issue, I have no problem starting over, I have already started over 4 times. I have a User problem, in that the Users are such that everything needs to be automated. I realize there are easier ways to do what I want to do, but because of the total automation issue, I am having trouble doing this.

Mike Dorthic suggested Default Value Logic. I have no idea what he is talking about or how to go about doing this. Sorry Mike, I am still quite a novice.

I have 3 tables I am working with. The 1st table does not seem to pose any problems for me. It is only the 2 that are referenced in the MainForm and SubForm. All 3 tables bring the RecordID# forward through relationships just fine. Table #1 will only be used to print a report and identify the data being entered into Table #2 and Table #3.
I hope all this helps. Ask any questions you need - I am at your mercy.

 

[TheAceMan1]

frog40 . . .

Yes it appears to be a design problem. On a functional basis post back what you each option to (no frills!).

[blue][tt]Example: Option1: set all checkboxes in record to True
Option2: set all checkboxes in record to False
Option3: . . .[/tt][/blue]

Also post back the following:
[ol][li]The Name of the OptionGroup control.[/li]
[li]MainformName, subFormName, Checkbox Names.[/li]
[li][blue]PrimaryKeyName[/blue] & [blue]datatype[/blue] of the subform.[/li]
[li]The underlying TableName of the subform.[/li][/ol]
I'm at work and may not be able to readily get back, but I will . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[frog40]

Here we go AceMan1:
Option1: Set A CHOICE (FROM 1 TO 10)OF checkboxes in record to true.
Option2: Same as Option1
Option3: Same as Option1

1. Name of the OptionGroup Control: froptBrkfst
Control Source: Breakfst

2. MainformName: FrmBrkfst - Pg2-WK1-1
subFormName: fSubSelectionsMenuB1
Checkbox Names: (1st in Opt1) - chkB1ScrambledEggs
Ctl Source- B1ScrambledEggs
(2nd in Opt1) - chkB1Toast
Ctl Source- B1Toast
(1st in Opt2) - chkB2ScrambledEggs
Ctl Source- B2ScrambledEggs
(2nd in Opt2) - chkB2Waffles
Ctl Source- B2Waffles
(1only Opt3) - chkB3BrkfstPizza
Ctl Source- B3BrkfstPizza

3. PrimaryKeyName & datatype of the subform:
IngzSelectID - Auto Number (TableName: tblMenuBWK1-1)

4. Underlying TableName of the subform:
TSelections Data WK1-1

I hope I answered 3 & 4 correctly (Underlying meaning the table that the subForm is attached to?)

Thanks Again for your help

 

[TheAceMan1]

frog40 . . .

Just a slight ambiguity in your post. In 3 & 4 you relate to the subform but [blue]mention two table names![/blue] . . . which is which? . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[frog40]

AceMan1, sorry for the confusion. Here is what I have:

FrmName - Pg1-Wk1-1: Record Source is (table)TDataNameWK1-1
This table contains Name, other data about the User.
The Primary Key is: IDSSelectID - Auto Number
After data is entered, a Macro sends it to
FrmBrkfst - Pg2-WK1-1

FrmName - FrmBrkfst - Pg2-WK!-1: Record Source is (table)
TSelections Data WK1-1
This table contains the Choices from Option1, 2, 3, as
well as Check Boxes that allows the User to select
Beverages. (These check boxes are displayed on
the Form and are not a problem)

subFrmTSelectionsMenuB1: Record Sours is (table)
tblMenuBWK1-1
This table contains the data from the Food check boxes.

All of these tables show the PrimaryKey SelectID#

 

[TheAceMan1]

frog40 . . .

While pumpimg up code do deal with this it occurred to me . . . [blue]any chance you can put the option Group on the subform[/blue] . . . this would save alot of VBA?

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[frog40]

Don't know how it would work, but I sure will give it a try. Thanks for the suggestion.

 

[TheAceMan1]

frog40 . . .

Your [blue]premature saving problem[/blue] occurs because [blue]the subform somehow receives focus after an option is selected[/blue] (perhaps the user is entering an additional field in the subform record). [blue]Its when focus is set back to the mainform (to select a different option) that saving of the subform record occurs![/blue]

The above is why I suggested moving the option group to the subform. [blue]This removes the premature saving issue because the subform still has the focus if the user decides to change options.[/blue]

With regards to moving the option group to the subform . . . Consider: Since you'll be making the proper checkbox selections via the option group, [blue]there's no need for the option group to be bound![/blue] [purple]Also the option group should be in the header or footer of the subform.[/purple]

Considering all the above, copy/paste the following code to the [blue]AfterUpdate[/blue] event of the option group:

[blue]   Dim idx As Integer, flg As Boolean, ChkBox As String
   
   Do
      idx = idx + 1
      
      ChkBox = Choose(idx, chkB1ScrambledEggs, chkB1Toast, _
                           chkB2ScrambledEggs, chkB2Waffles, _
                           chkB3BrkfstPizza)
      
      If Me!froptBrkfst = 1 Then
         flg = Choose(idx, True, True, False, False, False)
      ElseIf Me!froptBrkfst = 2 Then
         flg = Choose(idx, False, False, True, True, False)
      Else
         flg = Choose(idx, False, False, False, False, True)
      End If
      
      Me(ChkBox) = flg
   Loop Until idx = 5[/blue]

Be sure to disable/remove all other code involving the option group to prevent interaction.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[frog40]

Hi Ace Man. I followed your suggestions (except for moving the Group Option to the Header or Footer. This was a requested design from management. Is there any reason to move it, except for appearance?

I did not bind the Group Option to any field.
I deleted all other code.
The problem now is, when I click on Option 1, I get this error message:
"Run Time Error 2465 ...can't find the field listed in your expression"

 

[PHV]

I'd replace this:
ChkBox = Choose(idx, chkB1ScrambledEggs, chkB1Toast, _
chkB2ScrambledEggs, chkB2Waffles, _
chkB3BrkfstPizza)
with this:
ChkBox = Choose(idx, "chkB1ScrambledEggs", "chkB1Toast", _
"chkB2ScrambledEggs", "chkB2Waffles", _
"chkB3BrkfstPizza")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

Thanks for the pickup [blue]PHV[/blue] . . . don't believe I forgot the quotes! . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[frog40]

Thanks PHV and Ace Man, but this did not work. Here is what happened.

When I got the "Me(ChkBox) = flg" error "RunTime Error 2465 - can't find the field", I noticed the Code from AceMan did not have ElseIF complete for Me!froptBrkfst = 3, so I filled it in. This eliminated the RunTime error.

However, it created a different error:
"You tried to assign a Null value to a variable that is not a variant type". I changed the Code by putting in the QUOTES as suggested by PHV, but nothing changed. I still get this same error message.

I then tried this:
I clicked on B1ScrambledEggs; got the "Variant" error above, clicked on the OK from the error message, then continued on to Option2!B2ScrambledEggs, B2Waffles --
Option1!B1ScrambledEggs did NOT become Null(False).
I did not exit this screen yet, because I did not want to save the record. Instead I minimized the screen and went directly to the table (tblMenuWK1-1) that would store the data. It created a new record for every time I checked on the checkboxes (B1ScrambledEggs), (B2ScrambledEggs), (B2Waffles).

I looked at my tables and they are all check boxes Yes/No.

 

[TheAceMan1]

frog40 . . .

Did you add the quotations as shown by PHV? . . . if not, make it so!

[blue]I'd replace this:
ChkBox = Choose(idx, chkB1ScrambledEggs, chkB1Toast, _
chkB2ScrambledEggs, chkB2Waffles, _
chkB3BrkfstPizza)
with this:
ChkBox = Choose(idx, "chkB1ScrambledEggs", "chkB1Toast", _
"chkB2ScrambledEggs", "chkB2Waffles", _
"chkB3BrkfstPizza")[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[PHV]

I still get this same error message
Which line of code is highlighted when in debug mode ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

frog40 . . .

The Option Group should be on the subform! And following is the truth table of what you should get for each option:

[blue][tt] Checkbox Opt1 Opt2 Opt3
****************** **** **** ****
chkB1ScrambledEggs T F F
chkB1Toast T F F
chkB2ScrambledEggs F T F
chkB2Waffles F T F
chkB3BrkfstPizza F F T[/tt][/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884: