Search Function on form doesn't return any data from table even thought the info in on the table

[Welshgasman]

FWIW, I would have an unbound combo with the wire numbers.
The user would supply a wire number and the after update event will find that record. benefit here, is you cannot select anything that does not exist.
If the wire number is new, then it can be added with the NotInList event.

So really all you need is to get domething to find and then either find it with a FindFirst, or what I would probably do, is open the wirenumber form with that wirenumber, as we are doing now.
As the wirenumber form s already open, you end up just going to that record.?

So all you need for the Search button is

Private Sub searchBtn_Click()
Dim strSearch As String
 
strSearch = Me.formSearch
   
DoCmd.OpenForm "wireCategoryQuery_V3", , , WhereCondition:="wireNumber='" & strSearch & "'"


End Sub

BTW you need to Dim variables in VBA with their type, else they all become variants. Look at how I have dimmed strSearch.

Another option as you are already on the form is just find the record, not filter, then you do not need to switch the filter off all the time.

Private Sub searchBtn_Click()
Dim strSearch As String
 
strSearch = Me.formSearch
   
'DoCmd.OpenForm "wireCategoryQuery_V3", , , WhereCondition:="wireNumber='" & strSearch & "'"
With Me.Recordset
    .FindFirst "wireNumber='" & strSearch & "'"
End With
End Sub

Try it in your DB. Make sure Data Entry is No though.
BTW, your Update button appears to create a new record?, so perhaps change the Caption to Add ?

[orange]

@drunyan0824
I modified your database to search and display a selected record as follows.

-copied your form and named it 'Copy of WireCategoryQuery_V3_Jack'
-added a combo in the form header: cboWhichWire
-rowsource of the combo is
"SELECT wireCategoryQuery.wireNumber
FROM wireCategoryQuery;"

-since the query offers only existing records, after selecting, the form opens to the selected record
-I have ignored the search etc buttons in the text area at bottom of the form!!!!

The database and gif are included in the attached zip file along with your original database.

[Welshgasman]

Nice Jack,
I would only add that I would have sorted the combo by wirenumber?
Hopefully OP can manage that should they desire that.

Nice video as well

[orange]

Thanks Paul.
I agree OP can add the sort ASC to the query.
I find the gif helpful to overview functionality.

[drunyan0824]

First off, I want to apologize for my last couple of posts. I know that everyone is doing their best to help me and I haven't been clear it what I have been trying to accomplish with this database. I really should have walked away from the problems for a bit and come back to it with fresh eyes, I am sorry for being rude.


Secondly,

As I thought about the problem over the weekend I managed to come up with a solution this morning. After some trial and error with some new queries and forms I came up with code for the search button on my wireCategory_v3 form

Private Sub searchBtn_Click()Dim strSearch As String
'Dim qryComp As Integer


strSearch = Me.formSearch








qryComp = StrComp([Forms]![wireCategoryQuery_v3]![formSearch], [strSearch], [vbBinaryCompare])


If [qryComp] = 0 Then
'    DoCmd.OpenQuery "qryWireNumber", [acViewNormal], acEdit
'    DoCmd.OpenForm "frmQryWireNumber", [acNormal], , WhereCondition:="wireNumber='" & formSearch & "'"
'    DoCmd.OpenForm "wireCategoryQuery_v3", acNormal, , WhereCondition:="wireNumber='" & frmSearchWireNumberTxtBox & "'"\
    DoCmd.OpenForm "wireCategoryQuery_v3", acNormal, , WhereCondition:="wireNumber='" & formSearch & "'"




Else




End If


End Sub

While the code does work I am guessing that I over complicated the code and better solutions have been provided.

@Welshgasman

Another option as you are already on the form is just find the record, not filter, then you do not need to switch the filter off all the time.
Code:
Private Sub searchBtn_Click()
Dim strSearch As String

strSearch = Me.formSearch

'DoCmd.OpenForm "wireCategoryQuery_V3", , , WhereCondition:="wireNumber='" & strSearch & "'"
With Me.Recordset
.FindFirst "wireNumber='" & strSearch & "'"
End With
End Sub
Try it in your DB. Make sure Data Entry is No though.

I tried the code and it works great. I like not having to switch the filter on and off all the time.

Can you help walk me through the code?

strSearch = Me.formSearch

takes what was typed into the formSearch text box

With Me.Recordset
    .FindFirst "wireNumber='" & strSearch & "'"

I am guessing that Me.Record set is the control telling access to look at all the records in the wireCatergoryQuery becasue the wireCategoryQuery_V3 form is based off that query.
Then everything after FindFirst is saying "hey, grab the first record that matches the variable strSearch".
Is that correct?





My only concern is having Data Entry set to NO. We have another access database for tracking equipment maintenance and we have ran it issues where we've tried to add equipment and over written a record because a record was open on the screen. So I was trying to avoid that by setting the form Data Entry to NO, but I am guess that a combination of checking what information is being entered against information already in the database and opening the form to a new record would address that issue. Would it be a good Idea to index my wireInfo table by wireNumber and not allow duplicates?

BTW, your Update button appears to create a new record?, so perhaps change the Caption to Add ?

The update button was my attempt at having a way to update an existing record, but I just used the macro wizard and created a glorified create new button. I think I need to look at the list Do.Cmd and look for something to simply update a record.

@orange

I like the idea of a drop down menus for existing the existing wire numbers, but with thousands of wire I think I would have to find a way to filter by category first. But I will give it a shot.

In the interest for getting away from using macros, I am going to try and redo my Save, Update and Delete buttons with VBA code.

Since this thread is about getting the search function working, I am betting it would be better to start a new thread for those if and when I need help on writing the code for those buttons, Correct?

[Welshgasman]

TBH I always used the * button on the navigation bar at the bottom of the form.
If your users cannot use that, then give them that button to add a new blank record.
Yes to your assumptions as well.
Yes, start a new post, this one is already too large.

[orange]

drunyan0824,

Yes a new thread simply because of size. Also recommend cascading combo where select from Category, then pick the wire within the selected category, all in sequential order is a good strategy and referenced often in the forums.