Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jul 2003
    Posts
    42

    Question Unanswered: 2 Questions: Drop Down Select / Button Value Change

    1st is a Form Question:

    -I have a drop down list [RequestorBox] which is based on a query of a table (advanced filtered sort) which upon selection will put the RequestorID [RID] in the main table for the record.

    -I also have a button which takes the person out to a small form [02_03-AddRequestor] where another name can be added if the name is not already on the list.

    I need to know how I can get the new name back to the original form [01_00-CRWorksheet] to the combo box [RequestorBox] to show as the chosen name from the list.


    2nd is a Query and/or Form Question:

    On form [01_00-CRWorksheet] I want the change the status in the Record [Status] Box to reflect a different value based on which date is not null.

    Example,

    Status should read: "Pending PR Group"
    when the new record is opened and the [PRC_Open_Date] isnt null.

    Status should read: "Pending PA Group"*
    when a the date in the [PRDA_Assigned_Date] isnt null.

    Status should read "Completed"
    when the date in the [Approved_On] is not null.

    This should be pretty easy but I am having a difficult time getting the expressions to work.

    *Also how do you send a date to the Pending PA Group once a checkbox is checked. (sorry unanticipated third question)


    Thanks in advance.. =)

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i don't know if this is the only or best way, but i just spent 10 minutes struggling with something similar.

    assuming the new occurs in a table with an autonumber myID

    i did an unbound form to gether the inputs for the wannabe new then

    dim dabs as dao.database
    dim recs a dao.recordset
    dim imHere as long
    set dabs = currentdb
    set recs = dabs.openrecordset("tableName")
    recs.addnew
    recs!thisField = ThisBox.value
    recs!thatField = ThatBox.value
    imHere = recs!myID 'grab the ID for future use
    recs.update

    requestorbox.requery 'to get the new entry in the combo...
    requestorbox.value = imHere '...and go there

    sorry that i didn't understand No 2 yet.

    izy

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    requestorbox.requery
    requestorbox.value = imHere

    needs to be

    forms![01_00-CRWorksheet]!requestorbox.requery
    forms![01_00-CRWorksheet]!requestorbox.value = imHere
    of course.

    izy

  4. #4
    Join Date
    Jul 2003
    Posts
    42

    oh my

    Im not trained in VB. Im working on learning it. What parts of that code are fill in the blanks, where do I input that code and how do I get that back to the forms? Is there anyway to do this with macros or expressions?


    Thanks for the help.






    Originally posted by izyrider
    i don't know if this is the only or best way, but i just spent 10 minutes struggling with something similar.

    assuming the new occurs in a table with an autonumber myID

    i did an unbound form to gether the inputs for the wannabe new then

    dim dabs as dao.database
    dim recs a dao.recordset
    dim imHere as long
    set dabs = currentdb
    set recs = dabs.openrecordset("tableName")
    recs.addnew
    recs!thisField = ThisBox.value
    recs!thatField = ThatBox.value
    imHere = recs!myID 'grab the ID for future use
    recs.update

    requestorbox.requery 'to get the new entry in the combo...
    requestorbox.value = imHere '...and go there

    sorry that i didn't understand No 2 yet.

    izy

  5. #5
    Join Date
    Jul 2003
    Posts
    42

    Regarding question 2

    Is there a way to get one long string from these. These arent working as separate items. It keeps giving me a bracketing error or a wrong number of arguments error:

    A. IIf (NotNull([PRC_Open_Date]) AND (IsNull([PRDA_Assigned_Date]) AND (IsNull([Approved_On]), "Pending PR Group","")

    B. IIf (NotNull([PRC_Open_Date]) AND (NotNull([PRDA_Assigned_Date]) AND (IsNull([Approved_On]), "Pending PA Group","Pending PR Group")

    C. IIf (NotNull([PRC_Open_Date]) AND (NotNull([PRDA_Assigned_Date]) AND (NotNull([Approved_On]), "Completed","Pending PA Group")

    Basically depending on what date is chosen I want it to modify the status. Thats what I am trying to do.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    OK, i'll try more explicitly. i don't know the names of all your object, so there are a few fill-in-blanks needed... they start FIB

    you have a form [01_00-CRWorksheet] containing combo [RequestorBox] bound to field [RID] which you get from FIBquery which gets the data from FIBtable.

    [RID] in FIBtable is an autonumber

    you have a second form [02_03-AddRequestor]: this form needs to be unbound. this form has FIBbox1, FIBbox2 etc which correspond to FIBfield1, FIBfield2 etc in FIBtable ...one box for everything that you need to include in a new record on FIBtable. there may be zero of these boxes if there is nothing that you MUST add to a new record in FIBtable except [RID]

    [01_00-CRWorksheet] has a button FIBbutton

    private sub FIBbutton_Click()
    dim dabs as dao.database
    dim recs a dao.recordset
    dim imHere as long
    set dabs = currentdb
    set recs = dabs.openrecordset("FIBtable")

    'add a new record to FIBtable ...triggering next autonumber on [RID]
    recs.addnew 'add a new record to FIBtable

    'now fill in all the MUST fields in FIBtable
    '(could be none at all, in which case you don't need the next line)
    recs!FIBfield1 = FIBbox1.value 'etc for each MUST field

    'now grab the RID of the new FIBtable record
    imHere = recs!RID

    recs.update

    'update the recordsource of your combo
    forms![01_00-CRWorksheet]![RequestorBox].requery

    'point the combo at your new RID
    forms![01_00-CRWorksheet]![RequestorBox].value = imHere
    end sub

    izy
    Last edited by izyrider; 07-24-03 at 16:52.

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    and, just in case, i should add that i'm using old-fashioned DAO so you must have the reference to DAO set.

    in a code module (Alt-F11) menu: Tools ¦ References and make sure you have Microsoft DAO 3.6 library ticked... if not scroll down to M-for-Microsoft and tick it.

    izy

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    for part two:

    combining all three is tough after a couple of glasses of wine. here's an attempt (using A, B, C rather than your complicated names):
    Code:
    iif(notnull(A), iif(notnull(B), iif(notnull(C),"Completed", "PendingPA"), "PendingPR"), "Nothing")
    ..and by the way, i have never used the notnull expression so i'm assuming it is valid (no A on this machine, so i can't check)

    izy

  9. #9
    Join Date
    Jul 2003
    Posts
    42

    Unhappy getting a wrong number of arguments error

    Ok I tried the ABC expression and I am getting a wrong number of arguments error:

    Basically its:
    If PRDA is not null = Pending PA Group
    If Approved Date is not null = "Completed"
    Otherwise = "Pending PR Group"

    Status: IIf(NotNull([Begin_PRDA_Assessment]), IIf(NotNull([Approved_On]),"Pending PA Group","Completed","Pending PR Group")

    I cant figure out whats wrong on this..

    Any help would be great, thank you.

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    your
    IIf(NotNull([Begin_PRDA_Assessment]), IIf(NotNull([Approved_On]),"Pending PA Group","Completed","Pending PR Group")
    is missing a ")". the trick is to count them through the line "(" = +1 and ")" = -1 ...you need to end up with zero.

    your code should do something with another parenthesis
    IIf(NotNull([Begin_PRDA_Assessment]), IIf(NotNull([Approved_On]),"Pending PA Group","Completed"),"Pending PR Group")
    gives you:
    notnull begin AND notnull approved: PA
    notnull begin AND isnull approved: Completed
    isnull begin: PR
    ...which is probably not what you want tho!

    try:
    IIf(NotNull([Begin_PRDA_Assessment]), IIf(NotNull([Approved_On]),"Completed","Pending PA Group"),"Pending PR Group")

    izy

  11. #11
    Join Date
    Jul 2003
    Posts
    42

    half way there

    Status: IIf(NotNull([Approved_On]), IIf(NotNull([PRC_Open_Date]), IIf(NotNull([Begin_PRDA_Assessment]), “Completed”,“Pending PR Group"),"Pending PA Group"),"Pending PR Group")

    That almost worked except you are right about the Undefined "NotNull" expression, so you are right that using a Not before the Null wont work. In order for this expression to work it has to find a value in the field (meaning Not Null). Im not sure how to get it to do this.

  12. #12
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    yeah - my a2k doesn't understand notnull, but...

    replace all "notnull" with "not isnull" and see if it flies.

    izy

  13. #13
    Join Date
    Jul 2003
    Posts
    42

    darn

    Status: IIf(NotNull([Approved_On]), IIf(NotNull([PRC_Open_Date]), IIf(NotNull([Begin_PRDA_Assessment]), “Completed”,“Pending PR Group"),"Pending PA Group"),"Pending PR Group")


    That wont work either even if I find the right term.

    If Approved On is not null = Complete
    If PRDA is not null = PA

    That conflicts in the field which means I will end up with a -1 or something related.

    Basically if the PRDA Date & the Approval Date are there then its "Completed"

    If the PRDA Date is there and the Approval date isnt there then its "Pending PA Group"

    If neither PRDA Date or the Approval date are there, then its "Pending PR Group"


    This is ugly. I have been looking for another way to do this completely. I tried running an update macro to change the status based on the "After Update" in those associated fields (PRDA and Approved). I have two Update Macros

    The first part runs on a click of a Checkbox (AssigntoPRDA)
    Macro is a two parter with conditions that run different update queries depending on the checkbox being Yes or No.

    MacroName:PAgrCheckbox

    Condition: [Forms]![00_02_03-CRWorksheet]![Begin_PRDA_Assessment]=Yes
    Actions: MsgBox, OpenQuery [00_12-Status_PAgrON], Requery [PRDA_Assigned_Date]
    Condition:[Forms]![00_02_03-CRWorksheet]![Begin_PRDA_Assessment]=No
    Actions: MsgBox, OpenQuery [00_11-Status_PAgrOff], Requery [PRDA_Assigned_Date]

    00_12 Update Query has two steps and reads as follows:
    Field: Status
    Table: 00_CostRed
    UpdateTo: [00-CostRed]![Status]="Pending PA Group"
    Criteria: [Forms]![00_02_03-CRWorksheet]![CRID] -note: based on record no.-

    00_12 Update Query has two steps and reads as follows:

    Field: Status
    Table: 00_CostRed
    UpdateTo: [00-CostRed]![Status]="Pending PA Group"
    Criteria: [Forms]![00_02_03-CRWorksheet]![CRID] -note: based on record no.-

    Field: Begin_PRDA_Assessment
    Table: 00_CostRed
    UpdateTo: [00-CostRed]![PRDA_Assigned_Date]=Now()
    Criteria: [Forms]![00_02_03-CRWorksheet]![CRID]

    00_11 Update Query has two steps and reads as follows:

    Field: Status
    Table: 00_CostRed
    UpdateTo: [00-CostRed]![Status]="Pending PR Group"
    Criteria: [Forms]![00_02_03-CRWorksheet]![CRID] -note: based on record no.-

    Field: Begin_PRDA_Assessment
    Table: 00_CostRed
    UpdateTo: [00-CostRed]![PRDA_Assigned_Date]=Null
    Criteria: [Forms]![00_02_03-CRWorksheet]![CRID]

    It doesnt update anything basically. It runs the correct message boxes based on the criteria selected but it wont change any of the data. Im not sure why.

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...and in reversed logic:

    IIf(isNull([FirstStep]), "nothing happened", IIf(isNull([SecondStep]), "InStep1", IIf(isNull([ThirdStep]), “InStep2”,“Completed")))

    sorry i can't get my mind around your field names.

    izy

  15. #15
    Join Date
    Jul 2003
    Posts
    42

    Ill give that a try

    Thanks for your help you sure are a trooper. The Not IsNull worked so I will try the reverse logic and get back to ya. Thanks for all your patience and help on this. =)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •