Search Bar Creation

[Phillipc1]

Hello all!

I have been trying to create a search bar for my form that will return the correct value if true or "no results found" if false. I have tried many different ways to do this search bar, but have been having issues with it either not producing the error message for not found or pulling up a different value entirely. See screen shot attached for more details on how I have this set up. This is still a very early work in progress, but I wanted to get this search function working before cleaning up the layout. If anyone has any pointers on the layout feel free, but my main concern is the search function. My last attempt, which is shown, was to utilize a combo box for this. I have the VBA code I found online and tried to replicate below as well for it. I appreciate any assistance on this one and look forward to communicating with everyone here.


Private Sub cboEquipmentSearch_AfterUpdate()
If Not IsNull([cboEquipmentSearch]) Then
Me.Filter = "Equipment # = '" & Me.cboEquipmentSearch & "'"
Me.FilterOn = Not IsNull(Me.cboEquipmentSearch)


Else
Me.FilterOn = False
End If
End Sub

[Micron]

First, do not use special characters (save for perhaps underscore) in any object name, otherwise you must then enclose [in brackets], which you are missing - Equipment #
Second, please use code tags (# button on posting toolbar) when posting code so that indentation and readability is maintained.If you stick with the combo but don't know about its bound column property, you should check that out, especially if your combo has more than one column.

Naming conventions
http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

[Minty]

Having special characters and spaces in your field names and controls is a going to get you in trouble. Here is one example.

Me.Filter = "[Equipment #] = '" & Me.cboEquipmentSearch & "'"


Because of the spaces and characters you have to surround the field name with square brackets. Every time you use it in code.

Change the Field name to EquipmentNo, and remove any other # etc. in your field names.
# is used as a date delimiter in Access so you can see where that could get you into problems.

[Phillipc1]

Ah, that makes sense. I was able to change the naming convention and that sorted it out, thank you! However, I am running into an issue where when searching, it only populates the Equipment_Registration subform with the EquipmentNo but leaves the DOT_Inspection Subform missing. Essentially I am trying to utilize this form to keep track of registration and DOT information for each equipment, and if I pull up a piece of equipment that has one of the items or none, it auto fills in that EquipmentNo so that I can then enter the additional information for that particular subform.

Attachment 49543

[Micron]

Whatever your attachment attempt is, it didn't work (at least not for me).

[Phillipc1]

Hopefully this will pull up for you. Not sure what the issue on the other attachment is.

Attachment 49545

[Micron]

Nope. See "how to attach files" at the top of the page. You must be trying to paste something.
If it's just a pic of code or something, probably won't help. If it's a db that would be good, but only zip file attachments are allowed.

[Phillipc1]

Here is the zipped folder of the db and also the screenshot of what I am referencing. I have it set-up that when the db is open, it will auto open into the main menu fullscreen. Appreciate the assistance on the attachment, as I was trying to hit "Insert Image" to attach the picture.

[Micron]

You only have one record of DOT data. I'm betting there is no issue - you never chose that equipment from the combo.

[Phillipc1]

Good day all! I am back with a new question as I am expanding some things on my database. I have decided to add in a search function to the main menu of my database that when a value is typed in and searched, it will pull open the appropriate value in the equipment list form. See attached images for the two forms. I feel as though I need to have a line in the VBA that pulls open this form, but I am unsure what that may be or if by doing that I will need to change the entire code for the search function completely.

[Minty]

There are at least two ways to do this:
1. Pass in the equipment number using the OpenArgs parameter of the open form command, check for it's presence on form load and act appropriately.
2. Filter the form to that record using the Where or Filter parameter of the form open command.

Both options are documented here: https://learn.microsoft.com/en-us/of...DoCmd.OpenForm