ADDING MULTIPLE FORMS TO A SET FUNCTION

Question

Hey guys, I believe this question would be better understood by Imb or theDBguy since you guys are familiar with my database. The question is... on this loop update, can I add a second form say "frmMain_POPUP" on top of the "frmMain" ?

Set prev_form = Forms("frmMain")

The reason I'm asking is because I created a different start form for administrators that contains 1 button per department instead of just having that department populate (if you're not an administrator). I want this form for admins to be the same as the form for the departments, but as a popup not a flat form. So I created that second form, but the colors won't update from there. A picture is worth a thousand words so:

Answer 1

The reason I'm asking is because I created a different start form for administrators that contains 1 button per department instead of just having that department populate (if you're not an administrator). I want this form for admins to be the same as the form for the departments, but as a popup not a flat form. So I created that second form, but the colors won't update from there. A picture is worth a thousand words so:

If I understand you right, then you want a form that is only for the administrator, where the administrator can click to open frmMain for any Service. Is that right?

Why you want a popup form, and not a flat form. Until now I have in all my applications never need the use of a popup form, but that is a matter of taste.

Imb.

Answer 2

There is one thing I don't like about access, that is if I make my main form a pop-up then the access shell runs in the background and closing the main form doesn't close access. So I like to have my main form be part of access so when you close it, the entire database closes. For other forms that are not part of the main form I like them to be pop-up modals because the close button clearly identifies that you can close that form without closing the entire database (make it simple for users who aren't the most computer-savy).

EDIT: I'm also having another tag along issue. On my form "frmContactList" I have a list of all current records. The issues was, that since each department has set records "1-75" then records that were empty would show on this list as well. So I made a different query "qryContactList" and made a criteria that last name HAD to be included otherwise don't show the record. Problem now is on my frmContactList when I filter by "Service" with a dropdown and I select a service with no records in it I get the error "You can't reference a property or method for a control unless the control has the focus." Here's a code for the on change and after update of the unbound combo box and a picture of what I'm looking at.

Private Sub cmbWardSearch_AfterUpdate()
Dim strFilter As String

With Me.cmbWardSearch
    If IsNull(.Value) Or .Value = "**ALL**" Then
        ' If the combo box is cleared or ALL selected, clear the form filter.
        Me.Filter = vbNullString
        Me.FilterOn = False
    Else
        ' item other than ALL is selected, filter for an exact match.
        strFilter = "[Service] = '" & _
            Replace(.Value, "'", "''") & "'"
        Debug.Print strFilter ' check this in Immediate window in case of trouble
                              ' you can use Ctrl+g to go to the Immediate window
        Me.Filter = strFilter
        Me.FilterOn = True
    End If
End With
End Sub

Private Sub cmbWardSearch_Change()

' If the combo box is cleared, clear the form filter.
  If Nz(Me.cmbWardSearch.Text) = "" Then
  Me.Form.Filter = ""
  Me.FilterOn = False

' If a combo box item is selected, filter for an exact match.
' Use the ListIndex property to check if the value is an item in the list.
  ElseIf Me.cmbWardSearch.ListIndex <> -1 Then
  Me.Form.Filter = "[Service] = '" & _
                 Replace(Me.cmbWardSearch.Text, "'", "''") & "'"
  Me.FilterOn = True
  End If

End Sub

Answer 3

I changed .Text to .Value in my code and made the error go away, the list filters well now. Messed up my search box, but I guess that can be fixed easy. Heh, back to the original issue >.<

Answer 4

There is one thing I don't like about access...

If there is one thing we have taught you is that there's almost always a workaround to any problem in Access using code. For example, if you want to close the entire application when a particular form closes, then you can use the Close event of said form with something like:

Application.Quit

Hope it helps...

Answer 5

I changed .Text to .Value in my code and made the error go away...

That makes sense because Text is only available when the control has the focus.

Cheers!

Answer 6

Hey Leo, can you check out my filter and see what I'm doing wrong? This is supposed to show only the people who you search for in my "frmContactList."

 If txtSearch.Text = "" Then
    Me.Form.Filter = ""
    Me.Form.FilterOn = False
 Else
   Me.Form.Filter = txtSearch.Text
   Me.Form.FilterOn = True
 End If

Answer 7

Hi,

You'll need a complete WHERE condition. For example:

...

Else

   Me.Filter="[FieldName]='" & Me.txtSearch & "'"

   Me.FilterOn = True

End If

Hope it helps...

Answer 8

There is one thing I don't like about access, that is if I make my main form a pop-up then the access shell runs in the background and closing the main form doesn't close access. So I like to have my main form be part of access so when you close it, the entire database closes. For other forms that are not part of the main form I like them to be pop-up modals because the close button clearly identifies that you can close that form without closing the entire database (make it simple for users who aren't the most computer-savy).

Hi Jamie,

As far as my knowledge goes (I must admit I do not know everything about Access, still learning) forms can be closed independant of each other, and without closing the application. Parhaps the new Navigation pane closes the application, but I have no Navigation pane (still using A2003).

It is easy to make the application close on closing your frmMain, with the line  Application.Quit in the Close event of frmMain. This is also what .theDBguy advised. Your dislike is not a thing of Access, it is that you use Access in a way you don't like, in my opinion.

All other "flat forms" can normally be closed with the close button, without closing the application, at least in all my applications. So for the reason not to close the application, you do not need popup forms.

Your second question, through EDIT, is better used in a different thread. Else you have two completely different discussions running through each other.

Probably you want to know which positions on the frmMain are still free. There are other ways to find that.

Imb.

Answer 9

You guys are right, I ended up just making all the forms "flat" with individual close buttons. Made my life easier and it's functional.

Answer 10

Congratulations! Continued success with your project.

Answer 11

The reason I'm asking is because I created a different start form for administrators that contains 1 button per department instead of just having that department populate (if you're not an administrator). I want this form for admins to be the same as the form for the departments, but as a popup not a flat form. So I created that second form, but the colors won't update from there. A picture is worth a thousand words so:

Hi Jamie,

On your main form you can make a button that is only visible when you are logged in as Administrator. Pressing the button will open the form with the Services.

In the Click event of the button you can:

- Close the main form (without an Application.Quit
- Re-open the main form using the Service according the pressed button of the Services form through OpenArgs
- Close the Service form.

As Administrator you can now switch easily between the different Services.

Imb.

Answer 12

Everything seems to be working effortlessly, except my search box. I added that unbound combo box that is able to apply a filter based on "Service" but I can't search for names within that filter (or without).

Answer 13

Everything seems to be working effortlessly, except my search box. I added that unbound combo box that is able to apply a filter based on "Service" but I can't search for names within that filter (or without).

Hi Jamie,

What is the purpose of the search box, who uses it for what?

Imb.

Answer 14

If we have an emergency, i.e. active shooter, missing person,... a user can view the roster and quickly search for an employee by name or email.

Answer 15

If we have an emergency, i.e. active shooter, missing person,... a user can view the roster and quickly search for an employee by name or email.

Hi Jamie,

And what will be the result? A list with persons or will the respective persons be visible on the frmMain by way of a different backcolor?

Sincse frmMain is un unbound form, the form filter will not work. But you can make a (filtered) recordset with a querystring like:

  "SELECT * FROM Contacts_tbl" _ 
& " WHERE Service = '" & cur_service & "'" _
& " AND (Lastname LIKE '*" & search_text & "*' OR Email LIKE '*" & search_text & "*')"

This returns the records for the contact in the current service that have a Lastname or an Email-address that contains the search text.

Next is what to do with the recordset?

Imb.

Answer 16

Well, the form doing the filtering is a different form called "frmContactList" (refer to the picture above, with the green line) that shows all contacts based on a "qryContactList." I just want that split form to filter the data displayed.

Answer 17

Well, the form doing the filtering is a different form called "frmContactList" (refer to the picture above, with the green line) that shows all contacts based on a "qryContactList." I just want that split form to filter the data displayed.

Hi,

Did you use the Filter format I posted earlier? If so, what happened?

Answer 18

After I applied the filter it locked my cursor on that textbox and wouldn't let me do anything else. If I filtered through my dropdown based on service it would also lock up. Also, it would require multiple textboxes for each field, where I'm hoping 1 textbox would search for last name, first name, and email.

Answer 19

Hi,

You won't need separate textboxes to filter/search on multiple columns/fields. You just simply add them to your criteria. For example:

Me.Filter = "Field1=" & Textbox & " OR Field2=" & Textbox & " OR Field3=" & Textbox

Make sense?

Answer 20

Ah, yes it does. What do you think is going on with my filtering though? Why is it locking up like that?

Answer 21

Well, the form doing the filtering is a different form called "frmContactList" (refer to the picture above, with the green line) that shows all contacts based on a "qryContactList." I just want that split form to filter the data displayed.

Hi Jamie,

In the AfterUpdate event of the Search control you could define something like:

    Me.Recordsource =   "SELECT * FROM qryContactList" _
                             & " WHERE Service = '" & cur_service & "'" _
                             & " AND (Lastname LIKE '*" & search_text & "*' OR Email LIKE '*" & search_text & "*')

The search text may occur anywhere in Lastname or Email. You have to substitute your own fieldnames or variables.

If this Alert list is not limited to one service, then you drop the Service selection.

Imb.

Answer 22

Ah, yes it does. What do you think is going on with my filtering though? Why is it locking up like that?

Hi,

I can't say for sure without being able to step through the process as it executes. Sorry...

Answer 23

The filter works wells, but I can't get it to include my combo box that filters Services. Here's what I made so far.

Private Sub txtSearch_AfterUpdate()
Dim cur_service As String

cur_service = cmbWardSearch

Me.RecordSource = "SELECT * FROM qryContactList" _
                & " WHERE Service = '" & cur_service & "'" Or IsNull(cur_service) _
                & " AND ([Last Name] LIKE '*" & txtSearch & "*' OR [First Name] LIKE '*" & txtSearch & "*' OR [E-mail Address] LIKE '*" & txtSearch & "*')"
                
End Sub

EDIT: The point if including my combo box is so if I search text in the txtSearch for last name, first name, or email it will search the entire record. If I use the dropdown and select a filter by ward, then I want to apply a search to just those people in that ward filtered.

Answer 24

Hi,

Just so I'm clear, are you saying if you enter "john" in the textbox, you want to search all wards with first or last name or email with "john" in it, correct? However, if you enter "john" in the textbox and then select "3-center" in the combobox, then you want to search only 3-center for anyone assigned there with first or last name or email with "john" in it. Is this correct? Or, did you mean you want to show anyone assigned to the selected ward no matter their names or email?

Answer 25

The filter works wells, but I can't get it to include my combo box that filters Services. Here's what I made so far.

Private Sub txtSearch_AfterUpdate()
Dim cur_service As String

cur_service = cmbWardSearch

Me.RecordSource = "SELECT * FROM qryContactList" _
                & " WHERE Service = '" & cur_service & "'" Or IsNull(cur_service) _
                & " AND ([Last Name] LIKE '*" & txtSearch & "*' OR [First Name] LIKE '*" & txtSearch & "*' OR [E-mail Address] LIKE '*" & txtSearch & "*')"
                
End Sub

EDIT: The point if including my combo box is so if I search text in the txtSearch for last name, first name, or email it will search the entire record. If I use the dropdown and select a filter by ward, then I want to apply a search to just those people in that ward filtered.

Hi Jamie,

If you want to include that sometime you want to include the Service selection and other times not, I usually break it down in smaller pieces:

Private Sub txtSearch_AfterUpdate()
Dim where_str as String

If (cmbWardSearch > "") Then where_str = where_str & " AND Service = " & As_text(cmbWardSearch)

'here you can add many more conditions 

where_str = where_str & " AND ([Last Name] LIKE " & As_text("*" & txtSearch & "*") & " OR [First Name] LIKE " & As_text("*" & txtSearch & "*") " OR [E-mail Address] LIKE " & As_text("*" & txtSearch & "*") 


Me.RecordSource = "SELECT * FROM qryContactList" & Replace(where_str," AND"," WHERE",1)

End Sub

Yes/no testing for all kind of conditions can make the query string quite unreadable/un-understandable. I build the string in steps, and only add a part if it has a value to test for, e.g. If (cmbWardSearch > ""). This automatically excludes a Null-value.

I use this technique to give the user a possibility to filter any table/query on any field he needs.

Finanlly you replace only the first " AND" to a " WHERE".

You also see the I use  a general function As_text. This enhances the readability of the string enormously. This function is placed in a general module. It "just" adds single quotes around the text-parameter, but it can also double the single quote inside the text-parameter. This is important for names as: O'Sullivan.

Imb.

 

Answer 26

Yes, pretty much.

Answer 27

I think I still don't understand how to add general functions. I know I go into the code window and dropdown to general, but where do I just drop a "As_text"?

Answer 28

I think I still don't understand how to add general functions. I know I go into the code window and dropdown to general, but where do I just drop a "As_text"?

Hi Jamie,

Go to "Modules", and make a new Module. Make sure that you have included:

Option Explicit

In this module you copy this function:

Function As_text(cur_text As Variant, Optional on_null As String) As String
  If (IsNull(cur_text)) Then
    As_text = "Null"
  Else
    As_text = "'" & Replace(cur_text, "'", "''") & "'"
  End If
End Function

or some modified version of it.

At closing the datrabase you can give the module a name, e.g. String_functions

Imb.

Answer 29

The filter works still, but the search box is not working - nothing happens when text is written in it.

Private Sub txtSearch_AfterUpdate()
Dim where_str As String

If cmbWardSearch > "" Then
where_str = where_str & " AND Service = " & As_text(cmbWardSearch)

'here you can add many more conditions

where_str = where_str & " AND ([Last Name] LIKE " & As_text("*" & txtSearch & "*") & " OR [First Name] LIKE " & As_text("*" & txtSearch & "*") & " OR [E-mail Address] LIKE " & As_text("*" & txtSearch & "*")
Me.RecordSource = "SELECT * FROM qryContactList" & Replace(where_str, " AND", " WHERE", 1)

End If

End Sub

EDIT: So I removed the first "(" in where_str & " AND ([Last Name] and it made the search work well, but then it messed up my dropdown filter!!! >.<

Private Sub txtSearch_AfterUpdate()
Dim where_str As String

If cmbWardSearch > "" Then where_str = where_str & " AND Service = " & As_text(cmbWardSearch)

'here you can add many more conditions

where_str = where_str & " AND [Last Name] LIKE " & As_text("*" & txtSearch & "*") & " OR [First Name] LIKE " & As_text("*" & txtSearch & "*") & " OR [E-mail Address] LIKE " & As_text("*" & txtSearch & "*")
Me.RecordSource = "SELECT * FROM qryContactList" & Replace(where_str, " AND", " WHERE", 1)


End Sub

Answer 30

The filter works still, but the search box is not working - nothing happens when text is written in it.

Hi Jamie,

What kind of control is cmbWardSearch, and what kind is txtSearch? I think there is some messing up between the 2, at least in my perception.

Does the user enter some text in txtSearch, and then leaves that control in order to fire the AfterUpdate event?

Imb.

 

Answer 31

EDIT: So I removed the first "(" in where_str & " AND ([Last Name] and it made the search work well, but then it messed up my dropdown filter!!! >.<

Hi Jamie,

The round brackets around the OR part (LastName etc.) are in my opinion functional. This gives that part a higher prevalence in relation to all the other conditons (the "AND"'s).

Imb.

Answer 32

They are both "unbound" but cmbWardSearch has a control source of 

SELECT DISTINCT [tblContacts].Service FROM tblContacts; 

Here's an add-on misc. question, that I hope is simple: In my "qryContacts" I have Contact Name display the person's "Rank" (a space) "Last Name" (a comma and a space) "First Name". Is there a way to make it "Rank" "Last Name" "First letter of first name" instead of the whole first name?

Contact Name: [Rank] & " " & [Last Name] & ", " & [First Name]

Answer 33

They are both "unbound" but cmbWardSearch has a control source of 

SELECT DISTINCT [tblContacts].Service FROM tblContacts; 

Here's an add-on misc. question, that I hope is simple: In my "qryContacts" I have Contact Name display the person's "Rank" (a space) "Last Name" (a comma and a space) "First Name". Is there a way to make it "Rank" "Last Name" "First letter of first name" instead of the whole first name?

Contact Name: [Rank] & " " & [Last Name] & ", " & [First Name]

Does this mean that when a Service is selecte in cmbWardSearch, that must be build in in the selection using:   " AND Service = As_text(...)? That is already covered in the previous code.

Does the AfterUpdate event of txtSearch fire (no spelling errors in control names?). You can check this with the debugger.

Finally, try:

         Contact Name: [Rank] & " " & [Last Name] & ", " & Left([First Name],1)

Imb.

Answer 34

Figured it out, apparently I had a field in the form that no longer existed and for some reason messed up all my filter options. I deleted these fields and now it works flawlessly. I'm going to start a new thread, because I have an idea that may take this database to the next level, but don't know if it's possible - and now I know better about different discussion in the same thread (>.<). You guys rock, thanks for the knowledge and guidance!

NEW THREAD

P.S. Don't strangle me, this one is tough.

Answer 35

Hi,

Glad to hear you got it sorted out. Good luck!