Unbound Text Box To Filter All Fields In Split Form

[data808]

I have a split form that has a few unbound text boxes with one filter button. This filter button will check all the unbound text boxes and filter the data below based on the values typed into these unbound text boxes. It's working great but now I have another scenario where I may need to have only unbound text box and be able to search multiple fields. Is this possible?

For example, I have 15 bound text boxes in a form. The user will type in an item number into each field then save the record. Then to search these item numbers I would make a split form that the user can go to with one unbound text box at the top with all the records showing on the bottom. Can this one unbound text box be able to search all 15 fields at once? I would hate to make 15 unbound text boxes and type in a value 15 times to find what I'm looking for.

Is this possible?

Thanks.

[Welshgasman]

Yes. You just code for each control using the OR logic

[data808]

Yes. You just code for each control using the OR logic

Thank you. Will try and post back what I can come up with. Thanks again.

[CJ_London]

For example, I have 15 bound text boxes in a form. The user will type in an item number into each field then save the record.

this sounds suspiciously like your table is not constructed properly

[data808]

this sounds suspiciously like your table is not constructed properly

I do feel like there is a better way to do it but haven't thought of it. Do you have a better idea?

Also is there a way for the user to be able to change font size while typing into a text box that is on a form?

[CJ_London]

Do you have a better idea?

not without know what the app is all about

Also is there a way for the user to be able to change font size while typing into a text box that is on a form?

why not just make the font size bigger anyway? you can adjust the font size depending on the user quite easily or provide a couple of buttons (increase/decrease) for the user to change it themselves.

code on a button click event to increase font size might be something like

textboxname.fontsize=textboxname.fontsize+1

[Gicu]

I agree that your table is probably not normalized if you have Item1,Item2,....Item15. Those should be individual records in a separate table which stores the PK of the original table as a FK and One field for Item. What would happen when you get the 16th item and don't have a place to store it in?

For the question at hand I think the easiest way is to concatenate the 15 fields in the form's recordsource (ITEMS:"|" & [Item1] & "|" & [Item2] & "|" & ....& [Item15] & "|") then in the filter button code get the records that match using Instr("ITEMS","|" & Me.txtUnboundItemSearch) & "|")>0.

Cheers,

[data808]

not without know what the app is all about

why not just make the font size bigger anyway? you can adjust the font size depending on the user quite easily or provide a couple of buttons (increase/decrease) for the user to change it themselves.

code on a button click event to increase font size might be something like

textboxname.fontsize=textboxname.fontsize+1

Never thought of creating buttons to increase and decrease font sizes. Thank you for suggesting that.

I am still in the beginning phases and have not gotten to the query section yet but I will be back once I reach that section.

Thanks again.

[data808]

I agree that your table is probably not normalized if you have Item1,Item2,....Item15. Those should be individual records in a separate table which stores the PK of the original table as a FK and One field for Item. What would happen when you get the 16th item and don't have a place to store it in?

For the question at hand I think the easiest way is to concatenate the 15 fields in the form's recordsource (ITEMS:"|" & [Item1] & "|" & [Item2] & "|" & ....& [Item15] & "|") then in the filter button code get the records that match using Instr("ITEMS","|" & Me.txtUnboundItemSearch) & "|")>0.

Cheers,

Hi @Gicu. Yeah it is a bit repetitive to make 15 fields for one record but the thing is we send out one request per day for 15 items and we just want that to be recorded as one record. When we get to developing the query section I would like to just search for items with one unbound text box in a split form just to see how many times we requested for that item. We never ask for 16 items as 15 is the max. If anything, some days may be less than 15 but we always try to max it out to make the request as worth while as possible.

Right now I am having issues with this line for creating a PDF out of this form with the 15 fields and I think it's because of the date because if I remove the Me.DateSent part it will work. I think it can't figure out what to do with the format of the date because the date sent field has slashes (02/09/2023) and file names cannot contain slashes. Can you help with this? I would like the PDF to save in this format:

Example 2-9-23_Ref#.pdf

Here is the code:

Private Sub cmdExportToPDF_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.txtLP1.SetFocus
    Exit Sub
End If


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


If MsgBox("Export Record To PDF?", vbYesNo, "Export") = vbYes Then
    FileName = "Example " & Me.DateSent & "_"& Me.txtRefNum
    FilePath = "D:\TEST FOLDER\Ready\" & FileName & ".pdf"
    'FilePath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & FileName & ".pdf"
    DoCmd.OutputTo acOutputForm, "Request Form", acFormatPDF, FilePath
    MsgBox "PDF File Was Exported As (Example Date_Ref#.pdf) To Network Folder.", _
    vbInformation, "Notification"
    Me.txtRefNum.SetFocus
Else
    Me.txtRefNum.SetFocus
End If


End Sub

Also should mention that when I got it to work without the date sent, it exported the whole recordset. Not just the record I was looking at. Do you know how to fix that?

Thank you for your help in advance.

[Micron]

we just want that to be recorded as one record.

That is the sort of thinking that leads to where you are. Proper table design facilitates input and retrieval as long as that is done via forms and reports, which is the right way to manage inputs and presentation. IMO most of the time the users of presented data have no idea of how it looks behind the scenes and seems like one record to them.

[Minty]

You are now experiencing some of the problems with storing your data in an un-normalised fashion.
Although it seems easier to have 15 fields, you then have to deal with trying to search them all instead of one field in x number of child records.
If you want to count the items per order you have the same problem. And sooner or later something will change and it will need 16 or 18 or 20 items, and your entire process needs re-writing.

There are a myriad of reasons why databases are not Excel spreadsheets and this is one of them.

As for your date and pdf issue: You should create a report not use the form, you can then open a filtered version of the report, then output that.
Your file name would be something like
FileName = "Example_" & Format(Me.DateSent,"yyyy-mm-dd") & "_" & Me.txtRefNum

[Gicu]

For the query part I think you should now have an answer as per post #7. And for the PDF I suggest you follow Minty's advice and create a report instead of trying to use the form (the easiest way is to open the form in design and save a copy as a report:

Once you have it as a report you can modify its record source to only return the current record loaded in the form or open the report hidden using the Docmd.OpenReport method and its associated Where clause just before the Docmd.OutputTo line:
https://stackoverflow.com/questions/...cord-of-a-form

Cheers,

[data808]

That is the sort of thinking that leads to where you are. Proper table design facilitates input and retrieval as long as that is done via forms and reports, which is the right way to manage inputs and presentation. IMO most of the time the users of presented data have no idea of how it looks behind the scenes and seems like one record to them.

I agree that this is not the best way to design a database but I am limited in knowledge and it also may be the circumstances on how we need to handle this scenario with the records that we are about to start recording that made me go this route. With that being said, I plan to try and explain in a future post what we are dealing with in as much detail as possible and maybe you folks can help me come up with something better after you get all the details. Just keep in mind, the reason why I chose to do it this way was of course based on circumstances, I try to keep the user in mind and how it will look to them. I always want to make the transition as smooth and simple as possible especially when migrating to a new way or system of doing things. Right now, they are using a Excel spreadsheet and I basically made the Access form look almost exactly like that spreadsheet with the 15 cells for the items they enter. Then the other reason why I decided to do it this way is because of the lack of knowledge in designing for Access. I am trying to work with what I am familiar with and so if I do something new or different, I will need a lot of people to help step by step in developing it and I wanted to minimize that as much as possible because I didn't want to inconvenience anyone or take up too much of their time. I really do appreciate everyone on this site and you guys are so helpful but I know there are a lot of other people who need help too.

I'll post the details of what I am trying to do soon. Thank you very much for your time.

[data808]

You are now experiencing some of the problems with storing your data in an un-normalised fashion.
Although it seems easier to have 15 fields, you then have to deal with trying to search them all instead of one field in x number of child records.
If you want to count the items per order you have the same problem. And sooner or later something will change and it will need 16 or 18 or 20 items, and your entire process needs re-writing.

There are a myriad of reasons why databases are not Excel spreadsheets and this is one of them.

As for your date and pdf issue: You should create a report not use the form, you can then open a filtered version of the report, then output that.
Your file name would be something like
FileName = "Example_" & Format(Me.DateSent,"yyyy-mm-dd") & "_" & Me.txtRefNum

Thanks. I am trying to see if I can come up with something by doing what you suggest. Each item can be one record. It may take some time to wrap my head around it but may be its possible. I will explain the situation in as much detail as possible in a future post to see if your idea will work out based on our circumstances.

But going back to what I am doing currently, if the amount of items were to increase, we would just create a new record of 15 items. It's funny you mention Excel spreadsheets because that is exactly what they are currently using and one of the reasons why I decided to do it this way was because I designed the Access form to look exactly like that Excel spreadsheet that they are using. This would make the transition to Access easier for them because they are already familiar with the way it looks.

Thank you for the suggestion on the Output PDF, I have been told to do a report in the past and I actually did it both ways. The report way was great for fields to shrink and grow for long text boxes. I really liked that feature. The form way I liked because it was just so quick. The user clicks on the PDF Export button and away it goes. The report way requires the user to click a couple more times because they have to open a report first then click another button to export to PDF. Unless there is another way to do it more quickly. Someone mentioned about opening a report hidden and I also think I did try to do something like that where the report opens and it just automatically exports to PDF with just one initial click but I couldn't get it work.

[data808]

For the query part I think you should now have an answer as per post #7. And for the PDF I suggest you follow Minty's advice and create a report instead of trying to use the form (the easiest way is to open the form in design and save a copy as a report:

Once you have it as a report you can modify its record source to only return the current record loaded in the form or open the report hidden using the Docmd.OpenReport method and its associated Where clause just before the Docmd.OutputTo line:
https://stackoverflow.com/questions/...cord-of-a-form

Cheers,

Thanks @Gicu. When I get to the query section I'll probably need your help as I'm not sure I know how to apply your solution.

As for the report suggestion, thank you. You have suggested this in the past and I explained in post #14 on what I liked about it and what I did not like. If there is a way to make it so the user only has to click on the form and the report opens hidden and exports automatically so that the report isn't even seen by the user than that would be great. I do like how you have more control over reports than with forms but I'm not particularly fond of having to click a button to open a report and then click another button to print or export to PDF. But if that's the only we can get it to work then I'll do it.

Thanks.