Populating a field based on the value of another field using FORM COMBOBOX or Lookup

[ZEVVER]

Sorry to say, no STAR for your reputation - - -yet

seemed like good logic
well explained
BUT
No error, and no change in behavior

I tried it in Afterupdate , dirty, and on the 2nd combobox ONCLICK



I think I must have a typo bug - - - this makes no sense

I also tried using the correct box name:

If Me.COMBOcatTP.OldValue <> Me.COMBOcatTP Then
Me.COMBOsubC = Null
End If
COMBOsubC.Requery

Try This:

Private Sub COMBOcatTP_AfterUpdate()    
    If Me.ActiveControl.OldValue <> Me.ActiveControl Then
       Me.COMBOsubC = Null
    End If
    COMBOsubC.Requery
 
End Sub

[ZEVVER]

So everybody is stumped, huh?

get this: Unless I manually close the form and reopen it, nothing works!

The following code did nothing, no msgbox pop up and no error, whether I put it at the FORM level CURRENT or AFTERUPDATE
or at the CONTROL LEVEL Private Sub COMBOcatTP_AfterUpdate()


Private Sub COMBOsubC_Click()
'----------------------------------------------------------------------------
' this section for troubleshooting


Dim Msg, Style, Title, Help, Ctxt, Response, MyString

Msg = "Do you want to continue?"
Style = vbYesNo + vbCritical + vbDefaultButton2
Title = "MsgBox Demonstration"
Help = "DEMO.HLP"
Ctxt = 1000
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.

MyString = "Yes"
' Perform some action.

Else ' User chose No.
MyString = "No" ' Perform some action below.
GoTo Exit_bail:
End If

Exit_bail:
Exit Sub


Err__testing:
MsgBox Err.Description
' Resume COMBOcatTP_AfterUpdate
GoTo Exit_bail:


DoCmd.Close
DoCmd.OpenForm "originalresourceinputform"
'-----------------------------------------------------------------------




you can insert that could into the uploaded test DB and see for yourself !!
Unless the form is manually closed and reopened, nothing happens, even if you advance to a different record and return.

If I don't get a response in 24 hours, I am going to repost this under FORMS with a different title "FORM DOES NOTHING unless manually closed and reopened"
because I feel like I am being ignored by the community after 15 replies and because 350 views produce no ideas in programming community.
I have spent 20 hours on this, and read similar threads. I am trying all kinds of VBA and EVENTS. AS a beginner ! It is not like I am freeloading. I am trying.
This is out of hand. . .It just cannot be that difficult. The community has fixed tougher problems than this puppy

[Welshgasman]

No. I am volunteering at a beer festival and on my phone.
No access to Access until tomorrow.

[ZEVVER]

I added this VBA code to the DESCRIPTION BOX CLICK and to the 1st combobox Category CLICK and to the FORM level CURRENT
and it never executes.

Try pasting that code into the text DB and watchit do nothing.

Unless the form is manually closed and opened, the CASCADE does not work and the table fields are not updated.
You guys have fixed more coplex bugs than this.
What is going on here?


'----------------------------------------------------------------------------
' this section for troubleshooting


Dim Msg, Style, Title, Help, Ctxt, Response, MyString

Msg = "Do you want to continue?"
Style = vbYesNo + vbCritical + vbDefaultButton2
Title = "MsgBox Demonstration"
Help = "DEMO.HLP"
Ctxt = 1000
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.

MyString = "Yes"
' Perform some action.

Else ' User chose No.
MyString = "No" ' Perform some action below.
GoTo Exit_bail:
End If

Exit_bail:
Exit Sub


Err__testing:
MsgBox Err.Description
' Resume COMBOcatTP_AfterUpdate
GoTo Exit_bail:

DoCmd.Close
DoCmd.OpenForm "originalresourceinputform"
' originalresourceinputform.show
'-----------------------------------------------------------------------
End Sub

[mike60smart]

Hi
See the attached

[pbaldy]

I'm on a plane, but jealous of WGM.

[ZEVVER]

Mike60smart was kind enough to try to rescue me. Apparently, he made changes to the DB and wants me to download a zip.
I got an error. What do I do? see the snip of the error I uploaded.

[davegri]

ZEVVER said:

I added this VBA code to the DESCRIPTION BOX CLICK and to the 1st combobox Category CLICK and to the FORM level CURRENT
and it never executes.

mike60smart upload in post #20 fixes the cascading combobox problem.
I added your troubleshooting code in post 19 to that DB and the messagebox code works fine. I don't understand why it would not work in your DB.

for forum RESOURCE upload-MOD.zip

[ZEVVER]

2 more snips for you. . . I tried this.
can yu just download the zip and upload it so i can view it on line without downloading?
or tell me what he did?
I just spent 30 minutes trying to trouble shoot his zip post.
I don't want to look a gift horse in the mouth, but wha ta tease to fix the problem only to lay on me an obstacle to view it.
talk about frustrating. He gave me the answer and I have no idea how to view it,
Just post his fix, please

[ZEVVER]

I very much appreciate your comment.
1) I have absolutely positively no idea how to download a zip and get it to open. (see snip errors I posted)
no one is willing to just type the fix. . . so I have no idea what you all saw Mike60smart so kindly did
2) Do you think i have some sort of pop up blocker on my WINDOWS that is stopping ACCESS from showing msg box and blocking passed variables [FORMS]![OriginalResourcesINPUTFORM]![COMBOcatTP]))? I mean, I know you wrote "I don't know", but who can we ask ? Where do I go next? I mean, I am pitiful here.
3) maybe I need to handle this in subform? but that is adding another layer of complexity that will probably stump me and
should not benecessary.

[ZEVVER]

Is it a 2007 problem? does this not work in 2007?

[davegri]

ZEVVER, in your posts, please indicate to whom you are directing your replies. If your latest is for me, I suggest the following actions:
WelchGasMan has the same Access version as do you, so to alleviate much possible confusion caused by version differences, maybe he can clear all of this up for you when he returns from his celebrations.

[Welshgasman]

I will try it out tomorrow.

[ZEVVER]

davegri, Welshgasman, Mike60smart, Micron,

You guys have been great. I know you are trying. I have really knocked off a lot of rust to get back to a real beginner hack status on basic tables and forms and VBA.\
WELSHman - enjoy.
I can wait.
I'd really like to get to the bottom of the CASCADE and MSGBOX issue though, to avoid bothering you guys in the future on other bugs, like using MSGBOX, CURRENT(), and subforms,
when I get back to designing subforms (g-forbid).

In the meantime, what I did AND IT IS A GOOD FUNCTIONAL BANDAID:

1) I created a new comboboxBAIL
It is not a cascade box: ramification is hat the user can select a subcategory that belongs to a different or any category. They can slect bad subcategory.
However, all subacegories are listed by category ID
Here, the user can select a subcategory and insert it into the main table . A LABEL will warn the user not to select the wrong CAT-subcat choices
2) I changed the 2nd cbox inthe cascade to LOCKED. It immediately reflects the subcategory choice selected from the standard cbox in #1 above.
While it is LOCKED, the user can still click on the dropdown, but any selection made is not accepted. I will use a LABEL to inform the user not to use this box, it is for display purpose only
3) I notice that for new records on the maintable, they do not show up visibly in the tableview until the FORM is closed. The new records are indeed held in short term memory and I can scroll through them and make changes, but if I peek into the table, they are not visible. Maybe that is std operating procedure? Seems like a misleading deal with the devil, but whatever, I am not going to argue with processing steps
4) I also notice that the DB is stored on a partition E:\AAA OSR TEST and when I compact and repair and close out, the time stamp on the DB does not update. It shows 8/27 even though today is 9/2. . .thought I think I started the project on the 24th, not sure, maybe I changed the DB name . . .irrelevant, it is not updating to 9/2....If I copy the file to C: then the dates update, I opened the DB to see if any of the bugs got fixed, but no dice. It operates the same. But for full disclosure and transparency, I am posting the issue of partitions

Professionally speaking: This sucks. .. but it works, after 4 days, and I can move on to designing the rest of the GUI and see if I even need any subforms, g-forbid.

[Welshgasman]

ZEVVER said:


mike60smart upload in post #20 fixes the cascading combobox problem.
I added your troubleshooting code in post 19 to that DB and the messagebox code works fine. I don't understand why it would not work in your DB.

for forum RESOURCE upload-MOD.zip

I cannot open that DB