Unbound Text Box To Filter All Fields In Split Form

[data808]

Here is my situation:



Everyday we send a request for 15 items (max amount they will allow us to send) to another agency. We send it to them through email in a PDF file format. That PDF file is name is dated and stored on a network drive in a folder called "SENT". The agency that receives it will work on getting that 15 items for us, it's basically just information documents on each item. Once finished, they will email mail us back another PDF file (non-searchable text) that contains these documents for the 15 items. So there are pages to go through. On average, it's about 1-3 pages per item. So when we get it back, I wanted to put this PDF into a folder on the network drive called "RECEIVED". From there I set up a button on the form with the 15 items that is called View Attached. When the user clicks this button it will look for that PDF file in the "RECEIVED" folder and open it for viewing. There the user will be able to see all the information on all 15 items that was sent. The PDF in the "RECEIVED" folder must be named by the reference number (auto number) in order for the View Attached button to find it in the "RECEIVED" folder. I chose the reference number because it's unique and will not cause duplicate PDF files.

So one of the main purposes of this database in keeping track of our daily 15 item request is that we were wasting a lot of requests by requesting the same item over and over. 15 items a day is not a lot and so we decided that we need to make every item count that we add to this request form. If we can keep track of it better, we can make sure that if we requested for an item already, we will not include it on our daily 15 item request form. So we would need some kind of query to be able to look up items to see if and when we sent it, and if so, be able to view the documents that came back from the agency for reference. The tricky part is there is an expiration date for these documents but the expiration date is dynamic because it is based on if the item gets updated which we have no control over. So in order for the documents received from the agency to be valid, we have to always check another program to see if there was any activity or updates on an item to see if these documents are still valid or if we need to send another request for the item with updated documentation. So as time goes on, the query, when used by the user to look up items, will show more than one entry because if we have to request for the item again in the future we will now have multiple results for the query which is fine. That is where the date will be important because we will only be looking at the most recently dated record and viewing those documents.

Hope this makes sense. If it's too long and complicated to read then I'm ok in just doing it my way even though I know its not the best way to go about it but the good thing is that at least I can understand somewhat of how my way works and I think even though it won't be perfect, it should work for the most part for what we need it for.

Thanks for your time and let me know if we should start from scratch or just continue with what I have been doing so far.

[Gicu]

You don't need to have one button just to open the report; in the click event of your current button on the form you just add a few more lines before the Docmd.OutputTo. If you filter the report to only include the current record you wouldn't even needs those extra lines, just modify the existing one to output the report instead of the form.

Cheers,

[Gicu]

Can you upload a sample file with the table (with 15 item fields) and the form you want to search in using the unbound textbox (why not an unbound combo that lists all existing items)?

Cheers,

[data808]

You don't need to have one button just to open the report; in the click event of your current button on the form you just add a few more lines before the Docmd.OutputTo. If you filter the report to only include the current record you wouldn't even needs those extra lines, just modify the existing one to output the report instead of the form.

Cheers,

Thanks I will see if I can figure it out and get back to you.

Also the split form with the one unbound text box hasn't been created yet. I just wanted to make sure that it was possible to search multiple fields with one unbound text box. I am still fine tuning the current 15 item form. Once I get that up and running with all functioning buttons, I'll start working on the split form to do the queries. An unbound combo wouldn't work because the amount of different items is in the hundreds of thousands, if not millions. Each item number is specific though like a serial number so if the user can type the item number into the unbound text box to do a search, then that's all that would be needed. We just wanted to keep track if we were asking the agency multiple times for the same item. What makes it a bit tricky is that asking multiple times could be a good thing if the documentation on that item has been updated. However, if the documentation is the same with no current changes from the last time we requested it, then we can continue using it and we do not need to do another request. The way we verify if the documentation has been updated is in another totally different program. The program only tells us if there were updates. It doesn't show us details of what was updated which is pretty annoying. That is why we need to always request for the documentation.

[data808]

You don't need to have one button just to open the report; in the click event of your current button on the form you just add a few more lines before the Docmd.OutputTo. If you filter the report to only include the current record you wouldn't even needs those extra lines, just modify the existing one to output the report instead of the form.

Cheers,

Ok @Gicu. Would you be able to check my work? I just want to make sure I am doing it correctly. It seems to be working. Here is the code:

Private Sub cmdProcessToPDF_Click()Dim FileName As String
Dim FilePath As String


If Me.NewRecord And Me.Dirty Then
    MsgBox "Please Save Record Before Exporting.", vbInformation, "Requirement"
    Me.txtTabStopper2.SetFocus
    Exit Sub
End If


If Me.NewRecord And Me.Dirty = False Then
    MsgBox "Please Create And Save Record Before Exporting.", vbInformation, "Requirement"
    Me.txtTabStopper2.SetFocus
    Exit Sub
End If



If Me.txtProcessed.Value <> 0 Then ' to let user know that they already processed a PDF for this record
    If MsgBox("Already Processed." & vbCrLf & vbCrLf & _
    "Are You Sure You Want To Process To PDF Again?", vbYesNo, "Export") = vbYes Then
        FileName = "Example " & Format(Me.txtDateSent, "yyyymmdd") & "_Ref-" & Me.txtRefNum
        FilePath = "K:\READY\" & FileName & ".pdf"
        'FilePath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & FileName & ".pdf"
        DoCmd.OpenReport "Request_Screenshot_report", acViewReport, , "[Ref Num] =" & _
        Me.txtRefNum
        DoCmd.OutputTo acOutputReport, "Request_Screenshot_report", acFormatPDF, FilePath
        Me.txtProcessed.Value = Me.txtProcessed.Value + 1 ' showing the amount of times it was processed
        Me.Dirty = False
        MsgBox "PDF File Was Exported As (Example yyyymmdd_Ref#.pdf) To Network Folder.", _
        vbInformation, "Notification"
        DoCmd.Close acReport, "Request_Screenshot_report"
        Me.txtRefNum.SetFocus
        Exit Sub
    Else
        Me.txtRefNum.SetFocus
        Exit Sub
    End If
End If


If MsgBox("Process To PDF?", vbYesNo, "Export") = vbYes Then
    FileName = "Example " & Format(Me.txtDateSent, "yyyymmdd") & "_Ref-" & Me.txtRefNum
    FilePath = "K:\READY\" & FileName & ".pdf"
    'FilePath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & FileName & ".pdf"
    DoCmd.OpenReport "Request_Screenshot_report", acViewReport, , "[Ref Num] =" & _
    Me.txtRefNum
    DoCmd.OutputTo acOutputReport, "Request_Screenshot_report", acFormatPDF, FilePath
    Me.txtProcessed.Value = Me.txtProcessed.Value + 1
    Me.Dirty = False
    MsgBox "PDF File Was Exported As (Example yyyymmdd_Ref#.pdf) To Network Folder.", _
    vbInformation, "Notification"
    DoCmd.Close acReport, "Request_Screenshot_report"
    Me.txtRefNum.SetFocus
Else
    Me.txtRefNum.SetFocus
End If


End Sub

So basically the user clicks the process button and prompt user to ask if they want to process to PDF and if they click yes it will flash a blank window which I think is the report and then a msgbox will appear saying it is finished. The problem I am facing right now is that I have a label and a text box, separate from each other that shows up on the PDF even though I have the display property for those controls set at "Screen Only". Everything else like the buttons and what not are not showing up so I can't figure out why this one label and text box is showing up. Lastly, the reference number is showing in green font. I want the reference number to show on the PDF but in black font. I tried changing the font color but I think it may be VBA or conditional formatting that is causing it to show as green. When the user is creating a new record, the reference number shows as green but once they save the record, it will change the font to black. When I process to PDF, the report reference number is in black font so not sure why it turns it back to green.

UPDATE: I figured out how to get rid of the label and text box. I just removed everything on the report that I did not want showing up in the PDF. I also had to remove all the VBA in the report since it was not needed and causing compile errors when debugging. The report really does copy every single thing from the form when saving object as report. lol

[Gicu]

The code looks a bit more complicated than it should be, please try this updated version and see if does the some thing (I wrote it in Notepad++ so couldn't try it out):

Private Sub cmdProcessToPDF_Click()Dim FileName As StringDim FilePath As String


Dim sMessage as string


If Me.NewRecord = True Then
'new record
    If Me.Dirty =True Then
        sMessage= "Please Save Record Before Exporting."
    Else
        sMessage="Please Create And Save Record Before Exporting." 'not sure how to create as it is already created 
    End If
    
    MsgBox sMessage, vbInformation, "Requirement"    
    Me.txtTabStopper2.SetFocus
    Exit Sub
Else
'existing record -can you (re)export those??????


End If




If Me.txtProcessed <> 0 Then ' to let user know that they already processed a PDF for this record
    sMessage="Already Processed." & vbCrLf & vbCrLf & "Are You Sure You Want To Process To PDF Again?"
Else
    sMessage="Process To PDF?"
end if


If MsgBox(sMessage, vbYesNo, "Export") = vbNo Then GoTo No_PDF 'user want to cancel


FileName = "Example " & Format(Me.txtDateSent, "yyyymmdd") & "_Ref-" & Me.txtRefNum
FilePath = "K:\READY\" & FileName & ".pdf"
DoCmd.OpenReport "Request_Screenshot_report", acViewPreview, , "[Ref Num] =" & Me.txtRefNum,acHidden
DoCmd.OutputTo acOutputReport, "Request_Screenshot_report", acFormatPDF, FilePath
Me.txtProcessed = Me.txtProcessed + 1 ' showing the amount of times it was processed '.Value is the default property of a textbox
Me.Dirty = False


MsgBox "PDF File Was Exported As (Example yyyymmdd_Ref#.pdf) To Network Folder.", _
        vbInformation, "Notification"
DoCmd.Close acReport, "Request_Screenshot_report"      


No_PDF:
Me.txtRefNum.SetFocus


End Sub

An alternative would have been to edit the record source of the "Request_Screenshot_report" to only return the current record in the form (add a reference to RefNum from the form, something like "SELECT tblRequests.* FROM tblRequests WHERE [RefNum] = Forms!frmRequests!RefNum"); if you do that you don't need the Docmd.OpenReport and Docmd.Close acReport lines as the OutputTo will only export that record.

Cheers,

[data808]

The code looks a bit more complicated than it should be, please try this updated version and see if does the some thing (I wrote it in Notepad++ so couldn't try it out):

Private Sub cmdProcessToPDF_Click()Dim FileName As StringDim FilePath As String


Dim sMessage as string


If Me.NewRecord = True Then
'new record
    If Me.Dirty =True Then
        sMessage= "Please Save Record Before Exporting."
    Else
        sMessage="Please Create And Save Record Before Exporting." 'not sure how to create as it is already created 
    End If
    
    MsgBox sMessage, vbInformation, "Requirement"    
    Me.txtTabStopper2.SetFocus
    Exit Sub
Else
'existing record -can you (re)export those??????


End If




If Me.txtProcessed <> 0 Then ' to let user know that they already processed a PDF for this record
    sMessage="Already Processed." & vbCrLf & vbCrLf & "Are You Sure You Want To Process To PDF Again?"
Else
    sMessage="Process To PDF?"
end if


If MsgBox(sMessage, vbYesNo, "Export") = vbNo Then GoTo No_PDF 'user want to cancel


FileName = "Example " & Format(Me.txtDateSent, "yyyymmdd") & "_Ref-" & Me.txtRefNum
FilePath = "K:\READY\" & FileName & ".pdf"
DoCmd.OpenReport "Request_Screenshot_report", acViewPreview, , "[Ref Num] =" & Me.txtRefNum,acHidden
DoCmd.OutputTo acOutputReport, "Request_Screenshot_report", acFormatPDF, FilePath
Me.txtProcessed = Me.txtProcessed + 1 ' showing the amount of times it was processed '.Value is the default property of a textbox
Me.Dirty = False


MsgBox "PDF File Was Exported As (Example yyyymmdd_Ref#.pdf) To Network Folder.", _
        vbInformation, "Notification"
DoCmd.Close acReport, "Request_Screenshot_report"      


No_PDF:
Me.txtRefNum.SetFocus


End Sub

An alternative would have been to edit the record source of the "Request_Screenshot_report" to only return the current record in the form (add a reference to RefNum from the form, something like "SELECT tblRequests.* FROM tblRequests WHERE [RefNum] = Forms!frmRequests!RefNum"); if you do that you don't need the Docmd.OpenReport and Docmd.Close acReport lines as the OutputTo will only export that record.

Cheers,

Thanks @Gicu. I like how the report acts now with the acViewPreview and acHidden functions. Now the flashing of screens when creating the PDF is minimal. Looks much better now.

I will be back to this post when I get to the Query section. Thanks @Gicu.

[data808]

Ok @Gicu I started creating the Query section. Right now I am trying to do the VBA for the filter button. I have a total of 5 unbound text boxes right now. Here are the 5:

txtItemNumberFilter - Item Number with the 15 fields

txtStartFilter - Start Date (Both of these unbound date text boxes are for the Date Sent field) User can do searches for a date range from this date to that date.
txtEndFilter - End Date

txtRefNumberFilter - Auto Number

txtUpdatedByFilter - User that updated the record

All the fields are filtering fine with the filter button except for the txtItemNumberFilter unbound text box because I'm not sure how to make that one txtItemNumberFilter unbound text box search 15 fields.

Here is what I tried so far. I only tested it for Items 1 and 2 because I wanted to see if I could get it to work with just two items first:

Private Sub cmdFilterAll_Click()

Dim searchfor As String


If Not IsNull(Me.txtUpdatedByFilter) Then
    searchfor = " AND [Updated By] like ""*" & Me.txtUpdatedByFilter & "*"""
End If


If Not IsNull(Me.txtRefFilter) Then
    searchfor = searchfor & " AND [Ref Num] Like ""*" & Me.txtRefFilter & "*"""
End If


' if both date fields are entered
If Not IsNull(Me.txtStartFilter) And Not IsNull(Me.txtEndFilter) Then
    searchfor = searchfor & " AND [Date Sent] Between #" & Nz(Me.[txtStartFilter], _
    "1/1/1900") & "# AND #" & Nz(Me.[txtEndFilter], "12/31/2900") & "#"
End If
' if only start date is entered
If Not IsNull(Me.txtStartFilter) And IsNull(Me.txtEndFilter) Then
    searchfor = searchfor & " AND [Date Sent] Like ""*" & Me.txtStartFilter & "*"""
End If
' if only end date is entered
If Not IsNull(Me.txtEndFilter) And IsNull(Me.txtStartFilter) Then
    searchfor = searchfor & " AND [Date Sent] Like ""*" & Me.txtEndFilter & "*"""
End If


If Not IsNull(Me.txtItemNumberFilter) Then
    searchfor = searchfor & " AND [Item1] Like ""*" & Me.txtItemNumberFilter & "*"""
End If


If Not IsNull(Me.txtItemNumberFilter) Then
    searchfor = searchfor & " AND [Item2] Like ""*" & Me.txtItemNumberFilter & "*"""
End If


If searchfor = "" Then
    Me.Filter = ""
    Me.FilterOn = False
Else
    Me.Filter = Mid(searchfor, 6)
    Me.FilterOn = True
End If


Me.txtItemNuberFilter.SetFocus
Me.cmdFilterAll.BackColor = RGB(255, 242, 0)

End Sub

Private Sub cmdFilterAll_Click()

Dim searchfor As String


If Not IsNull(Me.txtUpdatedByFilter) Then
    searchfor = " AND [Updated By] like ""*" & Me.txtUpdatedByFilter & "*"""
End If


If Not IsNull(Me.txtRefFilter) Then
    searchfor = searchfor & " AND [Ref Num] Like ""*" & Me.txtRefFilter & "*"""
End If


' if both date fields are entered
If Not IsNull(Me.txtStartFilter) And Not IsNull(Me.txtEndFilter) Then
    searchfor = searchfor & " AND [Date Sent] Between #" & Nz(Me.[txtStartFilter], _
    "1/1/1900") & "# AND #" & Nz(Me.[txtEndFilter], "12/31/2900") & "#"
End If
' if only start date is entered
If Not IsNull(Me.txtStartFilter) And IsNull(Me.txtEndFilter) Then
    searchfor = searchfor & " AND [Date Sent] Like ""*" & Me.txtStartFilter & "*"""
End If
' if only end date is entered
If Not IsNull(Me.txtEndFilter) And IsNull(Me.txtStartFilter) Then
    searchfor = searchfor & " AND [Date Sent] Like ""*" & Me.txtEndFilter & "*"""
End If


If Not IsNull(Me.txtItemNumberFilter) Then
    searchfor = searchfor & " AND [Item1] Like ""*" & Me.txtItemNumberFilter & "*"""
    searchfor = searchfor & " AND [Item2] Like ""*" & Me.txtItemNumberFilter & "*"""
End If


If searchfor = "" Then
    Me.Filter = ""
    Me.FilterOn = False
Else
    Me.Filter = Mid(searchfor, 6)
    Me.FilterOn = True
End If


Me.txtItemNuberFilter.SetFocus
Me.cmdFilterAll.BackColor = RGB(255, 242, 0)

End Sub

Basically what happens is I search for the items in a split form and I see the filter icon showing in the datasheet sheet of the split form in the column headers so I know it's functioning somewhat for those two fields (Item1 and Item2) but it shows no results probably because it's conflicting with each other. If I deactivate the Item2 line in the code it will work but the search will only be good for Item1. Do you know how to fix this?

Thanks.

[Gicu]

You would need to use OR instead of AND for the txtItemNuberFilter to check all 15 fields or use the approach I suggested earlier. Add a calculated field to the form's recordsource: Items: ("|" + "Item1" + "|") & ("|" + "Item2" + "|") & ("|" + "Item3" + "|") &.......("|" + "Item15" + "|"). Add it to the form and hide it. Now for the filter code:

'............your previous code.....
If Not IsNull(Me.txtItemNumberFilter) Then
    searchfor = searchfor & " AND InStr(Me.txtItems,"|" & Me.txtItemNumberFilter & "|")>0 "  'assumes you have a textbox named txtItems bound to the calculated Items field above
   
End If
'..........rest of your code

Cheers,

[data808]

You would need to use OR instead of AND for the txtItemNuberFilter to check all 15 fields or use the approach I suggested earlier. Add a calculated field to the form's recordsource: Items: ("|" + "Item1" + "|") & ("|" + "Item2" + "|") & ("|" + "Item3" + "|") &.......("|" + "Item15" + "|"). Add it to the form and hide it. Now for the filter code:

'............your previous code.....
If Not IsNull(Me.txtItemNumberFilter) Then
    searchfor = searchfor & " AND InStr(Me.txtItems,"|" & Me.txtItemNumberFilter & "|")>0 "  'assumes you have a textbox named txtItems bound to the calculated Items field above
   
End If
'..........rest of your code

Cheers,

Thanks @Gicu. I tried the OR statement where I thought it would fit into the code but couldn't get it to work. Here is a few things I tried:

If Not IsNull(Me.txtItemFilter) Then
searchfor = searchfor & " AND [Item1] Like ""*" & Me.txtItemFilter & "*""" Or _
searchfor = searchfor & " AND [Item2] Like ""*" & Me.txtItemFilter & "*"""
End If

If Not IsNull(Me.txtItemFilter) Then
searchfor = searchfor & " AND [Item1] Like ""*" & " OR [Item2] Like ""*" & Me.txtItemFilter & "*"""
End If

If Not IsNull(Me.txtItemFilter) Then
searchfor = searchfor & " OR [Item1] Like ""*" & " OR [Item2] Like ""*" & Me.txtItemFilter & "*"""
End If

It would give an error and I think some times the debug would point to this line:

Me.Filter = Mid(searchfor, 6)


Anyway, I felt this method was going to be faster rather than creating a calculated field in the recordsource and hiding the text box. But I assume the long way will be more useful in the future?

Just to clarify before I try your calculated field suggestion, I go into the backend table and create a field called txtItems. Then add that field to the form and make it hidden (Change Property Visible to No). Not sure where I add this:

Items: ("|" + "Item1" + "|") & ("|" + "Item2" + "|") & ("|" + "Item3" + "|") &.......("|" + "Item15" + "|"

Then go to Code Editor for the filter button and enter this:

If Not IsNull(Me.txtItemNumberFilter) Then
searchfor = searchfor & " AND InStr(Me.txtItems,"|" & Me.txtItemNumberFilter & "|")>0 " 'assumes you have a textbox named txtItems bound to the calculated Items field above
End If

So if everything sounds good, I just need help where to plug in this:

Items: ("|" + "Item1" + "|") & ("|" + "Item2" + "|") & ("|" + "Item3" + "|") &.......("|" + "Item15" + "|"

Thanks @Gicu.

[Gicu]

No need to add the field to the table, open the form in design view and click on the builder (the three vertical dots on the right) for the record source property. If the form is bound to a table you' lol get prompted if to create a query based on it, say yes and add the Items calculated field in there.

[data808]

No need to add the field to the table, open the form in design view and click on the builder (the three vertical dots on the right) for the record source property. If the form is bound to a table you' lol get prompted if to create a query based on it, say yes and add the Items calculated field in there.

I opened the form in design view. I think my 3 dots is horizontal. I clicked on that and it brings up the Query that is tied to it. I created a Query that will sort in descending order for the Auto Number. Then I scrolled down the row of fields and added a field called "Items". After I press Enter, it will add it like this "Expr1:[Items]". Is that correct? I also see Show with a checkbox so I guess that is what you meant by hiding it if I just uncheck that box? Lastly, do I just paste this in the criteria:

Items: ("|" + "Item1" + "|") & ("|" + "Item2" + "|") & ("|" + "Item3" + "|") &.......("|" + "Item15" + "|"

[Gicu]

Almost there 😁. Scroll to the right in your query, remove the Expr1: [Items] field if still there and copy the expression I gave you making sure it includes all 15 fields ( fill the ....blanks with the same pattern for each field from 1 to15). When you run the query now you should see that the last field is a concatenation of pipe-separated values of the populated Items through 15 fields. Leave the Show check box checked.
Now on the form add somewhere to the side a text box, set it's control source to "Items" and it's Visible property to false.

Cheers,

[data808]

Almost there . Scroll to the right in your query, remove the Expr1: [Items] field if still there and copy the expression I gave you making sure it includes all 15 fields ( fill the ....blanks with the same pattern for each field from 1 to15). When you run the query now you should see that the last field is a concatenation of pipe-separated values of the populated Items through 15 fields. Leave the Show check box checked.
Now on the form add somewhere to the side a text box, set it's control source to "Items" and it's Visible property to false.

Cheers,

I removed Expr1: [Items] field and I pasted your expression into the field for the Query:

Items: ("|" + "Item1" + "|") & ("|" + "Item2" + "|") & ("|" + "Item3" + "|") & ("|" + "Item4" + "|") & ("|" + "Item5" + "|") & ("|" + "Item6" + "|") & ("|" + "Item7" + "|") & ("|" + "Item8" + "|") & ("|" + "Item9" + "|") & ("|" + "Item10" + "|") & ("|" + "Item11" + "|") & ("|" + "Item12" + "|") & ("|" + "Item13" + "|") & ("|" + "Item14" + "|") & ("|" + "Item15" + "|")

I left the "Show" checked. Then I created the text box for "Items" which was now available because of its creation in the Query and also made sure the control source was set to "Items" then I set the Visible property to "No" and also named the text box txtItems.

I then felt like I was supposed to add this to the filter button:

If Not IsNull(Me.txtItemNumberFilter) Then
searchfor = searchfor & " AND InStr(Me.txtItems,"|" & Me.txtItemNumberFilter & "|")>0 " 'assumes you have a textbox named txtItems bound to the calculated Items field above
End If

But it kept giving a compile error: invalid character and would point to "|" so I took it out.

I opened the form in form view and I tested some searches and it is able to search only the Item1 field as it always has even before all of these changes but none of the other 14 item fields. I also notice that the hidden field (txtItems) is showing in the datasheet section since this is a split form. So I guess the Visible property being set to "No" didn't do anything.

Sorry if I am doing this wrong and thanks for your patience @Gicu.

[data808]

I think I got it to work but I went back to the original method which, to me, seems much easier but then again, I am a noob so what do I know? Lol. So far I've tested it up to Item5 and it seems to be searching all 5 fields at the same time. I will let you know once I do more testing. Here is the code I went with:

If Not IsNull(Me.txtItemFilter) Then
    searchfor = searchfor & " AND [Item1] Like ""*" & Me.txtItemFilter & _
    "*"" or [Item2] Like ""*" & Me.txtItemFilter & "*"" or [Item3] Like ""*" & _
    Me.txtItemFilter & "*"" or [Item4] Like ""*" & Me.txtItemFilter & _
    "*"" or [Item5] Like ""*" & Me.txtItemFilter & "*"""
End If