Results 1 to 13 of 13
  1. #1
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496

    changing control source on a textbox then filtering the form

    Continous form that has txtSchoolEmail textbox that gets its control source changed by an option box (the form filters accordingly).



    However I want to filter the form by null email fields so that the user can see if any of the schools have an email field that is blank

    because the form keeps changing the control source for txtschoolemail - which is the best way to filter for this?

    Currently I use this and of course it won't work.

    If Me.Command47.Caption = "Blank Emails" Then
    Me.Filter = "[Area] Like '*" & Me.Text36 & "*'" & IIf(Not IsNull(Me.Text38), " and SchoolTypeID=" & Me.Text38, "") & IIf(Not IsNull(Me.txtStates), " and StateID=" & Me.txtStates, "") And IsNull(Me.txtSchoolEmail)
    Me.FilterOn = True
    Me.Command47.Caption = "Show All"


    ElseIf Me.Command47.Caption = "Show All" Then
    Me.Filter = "[Area] Like '*" & Me.Text36 & "*'" & IIf(Not IsNull(Me.Text38), " and SchoolTypeID=" & Me.Text38, "") & IIf(Not IsNull(Me.txtStates), " and StateID=" & Me.txtStates, "")
    Me.FilterOn = True
    Me.Command47.Caption = "Blank Emails"


    End If

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    Why are you changing the control source of a textbox? What is Command47? Is it a toggle control? You could use me.Command47.value = -1 and me.Command47.value = 0 to determine how it is toggled.

  3. #3
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by ItsMe View Post
    Why are you changing the control source of a textbox? What is Command47? Is it a toggle control? You could use me.Command47.value = -1 and me.Command47.value = 0 to determine how it is toggled.
    The command47 button swaps between showing all records and showing records with null email fields.

    The control source of the textbox is changed on each record because each record has 1 - 7 different emails. When you click the option box 11-7 it swaps the txtSchoolEmail with the according control source.

    normally I would filter the one field but because it keeps changing so too does the filter need to.

    I could start mucking about with if but I thought there could be a better way...

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    Is command47 a toggle control?

    For the other thing, you could have your text box Unbound and have a Select Case statement assign a value to the unbound Text Box. You could set .Visible to al 7 of the fields to False and just use the unbound textbox.

    I believe you are using an option group so maybe

    Select Case Me.Frame12.Value

    Case 1
    Me.Text38 = Me.FieldOne
    Case 2
    Me.Text38 = Me.FieldTwo
    Case 3
    Me.Text38 = Me.FieldThree
    Case Else
    Msgbox "Do Not Recognize this selection"

    end select

  5. #5
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by ItsMe View Post
    Is command47 a toggle control?

    For the other thing, you could have your text box Unbound and have a Select Case statement assign a value to the unbound Text Box. You could set .Visible to al 7 of the fields to False and just use the unbound textbox.

    I believe you are using an option group so maybe

    Select Case Me.Frame12.Value

    Case 1
    Me.Text38 = Me.FieldOne
    Case 2
    Me.Text38 = Me.FieldTwo
    Case 3
    Me.Text38 = Me.FieldThree
    Case Else
    Msgbox "Do Not Recognize this selection"

    end select
    command47 is pretty much a toggle, it switches between the two options, show all records or only show records with null email fields (in order for the user to find out what the emails are that are missing and fill them in).

    I won't be using the option group, that is designated for a different task - I will make a new option box to switch between the 3 fields.

    Changing the fields using the combo box and dlookupdoes work - however it is locked - so I made clicking in it open up in a new form that gives you more area to type in and once you close it refreshes to show the changes.

    However if combo box is null then I get error (but it is unseen because it is invisible) so unless I default it to a number which I don't want to do I don't know what else to do.

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    Quote Originally Posted by Ruegen View Post
    Changing the fields using the combo box and dlookupdoes work - however it is locked - so I made clicking in it open up in a new form that gives you more area to type in and once you close it refreshes to show the changes.

    However if combo box is null then I get error (but it is unseen because it is invisible) so unless I default it to a number which I don't want to do I don't know what else to do.
    I am having a hard time following this. Instead of querying the 3 or 7 fields in the main recordset, why don't you link them in a subform? The form you open when the user clicks the combo could be a subform. You can hide the subform until needed. You can hide the Master link field too. You can adjust the subform's recordsource on the fly.

    Halfway through post #12 in this thread I explain how
    https://www.accessforums.net/program...lts-39894.html

    So this is where you can help yourself with the Null thing.

    Adjust the where criteria as the user interacts with the form. They will not even know they are changing anything until the final step. Each time they change something, the code adjusts the strWhere

    strWhere = (strWhere & " AND ") & "[SomeField] = '" & Me.txtField.Value & "'"

    you could have a "Clear" button bring everything back to a default strWhere and the original RecordSource for the subform. Your base/default strWhere would be based on a condition that will always exist, like the PK.

    strWhere = "FieldPK > 0"

    When the user executes, you reassign the strSQL to the subform.

    Code:
    strSQL = "SELECT MyQry.* " & _
                  "FROM MyQry " & _
                  "WHERE " & strWhere & _
                  "ORDER BY [FieldPK];"
    
    Me.RecordSource = strSQL
    the syntax of the strSQL does not change, only the value of the strWhere changes.

    You check for null or empty strings at the time the user requests to add or concatenate additional criteria to strWhere.

  7. #7
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by ItsMe View Post
    I am having a hard time following this. Instead of querying the 3 or 7 fields in the main recordset, why don't you link them in a subform? The form you open when the user clicks the combo could be a subform. You can hide the subform until needed. You can hide the Master link field too. You can adjust the subform's recordsource on the fly.

    Halfway through post #12 in this thread I explain how
    https://www.accessforums.net/program...lts-39894.html

    So this is where you can help yourself with the Null thing.

    Adjust the where criteria as the user interacts with the form. They will not even know they are changing anything until the final step. Each time they change something, the code adjusts the strWhere

    strWhere = (strWhere & " AND ") & "[SomeField] = '" & Me.txtField.Value & "'"

    you could have a "Clear" button bring everything back to a default strWhere and the original RecordSource for the subform. Your base/default strWhere would be based on a condition that will always exist, like the PK.

    strWhere = "FieldPK > 0"

    When the user executes, you reassign the strSQL to the subform.

    Code:
    strSQL = "SELECT MyQry.* " & _
                  "FROM MyQry " & _
                  "WHERE " & strWhere & _
                  "ORDER BY [FieldPK];"
    
    Me.RecordSource = strSQL
    the syntax of the strSQL does not change, only the value of the strWhere changes.

    You check for null or empty strings at the time the user requests to add or concatenate additional criteria to strWhere.
    I've provided an image (records below form header, what you see in the image is the form header).

    The toggle button filters the form to show emails in the continuous form.

    At the same time the user may select from one of the choices in the option group and it will change the control source to the different field.

    Click image for larger version. 

Name:	Capture.JPG 
Views:	7 
Size:	69.6 KB 
ID:	14722
    e.g.
    I can't have me.filter = "and isnull([SchoolEmail])" because [SchoolEmail] might change to a different control source - say [LibrarianEmail]

    If I could some how "and isnull(dim string)" then whatever field string is - it will filter the form accordingly. (or along the lines of that theory).

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    Can't assign Null to a string variable.

    One of the reasons why I recommend getting the validation taken care of sooner than later. The control source thing has you painted into a corner it seems.

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    Thought just crossed my mind to use Variant type. I would not. Seems very messy.

  10. #10
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by ItsMe View Post
    Thought just crossed my mind to use Variant type. I would not. Seems very messy.
    I could assign 7 dim strings

    when you select a choice the command gets a different dim string

    then when you press it filters accordingly by the field...

    so no matter what choice you make when you press the command it is filtering accordingly because it has had what it filters (the whole darn thing) swapped i.e.

    e.g.

    str1 = "[Area] Like '*" & Me.Text36 & "*'" & IIf(Not IsNull(Me.Text38), " and SchoolTypeID=" & Me.Text38, "") & IIf(Not IsNull(Me.txtStates), " and StateID=" & Me.txtStates, "")

    str2 = (and so on)


    filter = str1
    else if
    filter = str2

  11. #11
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    This makes little sense to me. I would have approached it differently and I provided a few examples how I handle these things. What exactly is not working and why are you reassigning Nulls to equal empty strings?

  12. #12
    Ruegen's Avatar
    Ruegen is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2013
    Location
    Australia
    Posts
    1,496
    Quote Originally Posted by ItsMe View Post
    This makes little sense to me. I would have approached it differently and I provided a few examples how I handle these things. What exactly is not working and why are you reassigning Nulls to equal empty strings?
    well (and I hope) I am not assigning nulls to anything

    there are fields that are null and I require those to filter into view.

    It's the field itself that I need to be dynamic on the continous form as there are more than 1 email field in the same table

    each time the forms records textbox swaps it's control source to another source from the same table - I need to filter into view those nulls each and every time depending on what is chosen.

    so if txtSchoolEmail has it's control source swapped from [SchoolEmail] to [LibrarianEmail] I need to then show all the nulls for LibrarianEmail

  13. #13
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,863
    Let's concentrate on one thread at a time.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 10-28-2012, 02:55 PM
  2. Textbox Control Source As Field Input
    By Jester0001 in forum Forms
    Replies: 4
    Last Post: 03-02-2012, 10:50 AM
  3. Replies: 17
    Last Post: 02-08-2012, 10:06 AM
  4. Textbox Control Source As An Expression help!
    By emilyrogers in forum Forms
    Replies: 11
    Last Post: 02-11-2011, 07:31 AM
  5. Changing the record source in a form
    By lmichaud in forum Forms
    Replies: 1
    Last Post: 07-09-2006, 09:20 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums