Search Form

[Wayneyh]

Hi all

My access and vb knowledge is limited.What i am wondering is - Is it possible to have a textbox you can enter data into, click a button to start the search that searches a combobox that is linked to a table via a query?

I would be grateful for any help and examples

[PianoMan64]

Hi all

My access and vb knowledge is limited.What i am wondering is - Is it possible to have a textbox you can enter data into, click a button to start the search that searches a combobox that is linked to a table via a query?

I would be grateful for any help and examples

Hey Wayneyh, Welcome to Byte:

To answer your question in general, yes it can be done.

But normall the way that I've been doing it successfully is to put a List control on the form that isn't bound to any data source at all, and in VB code I create the string value that becomes that data soruce by which fields on the form that I fill in set the values that I'm looking for and when I click the search button, it builds the search string that is passed as the Rowsource value for the list control. Then when I refresh the list control using me.listcontrolname.requery, and the me.repaint, I then get a list of the items that I am looking for. I hope that makes sense.

If you need code examples, please let me know, I'm more than happy to provide them.

Hope that helps,

Joe P.

[missinglinq]

Actually, you can use a combobox for this and it doesn't require writing any code at all!

If you haven't already done so, create a form based on your table or query, including all the fields you want displayed. Then simply:

Add a combo box to your form. The Combobox Wizard will pop up

Select "Find a record based on the value I selected in my combobox."

From the table or query the form is based on, click on the field you're searching by (a field that is unique for each record) to move it to the right side.

Hit Next.

Size the column appropriately.

Hit Next.

Name the combobox.

Hit Finish.

Now you can drop the combobox down and scroll down to the item to search by, or you can start to enter the item, and the combobox will "autofill" as you type. Hit <Enter> and the record will be retrieved.

Welome to Bytes!

Linq ;0)>

[Wayneyh]

Thanks PianoMan64

I think what you are saying would work for me and if you have an example it would be most appreciated as i have reached my limit with coding.

Regards

[Wayneyh]

Hi missinglinq

This wouldn't work as i don't want to search through the combo box. I want a text box to type the info i am wanting to look for, then click a search button that searches the combo box that is linked to a table through a query.

Regards

[PianoMan64]

Hi missinglinq

This wouldn't work as i don't want to search through the combo box. I want a text box to type the info i am wanting to look for, then click a search button that searches the combo box that is linked to a table through a query.

Regards

Here is a code example of how I build a SQL String with a tables that is either local or linked to the current MDB/MDE/ACCDB file:

Private Sub SeachBtn_Click()
    Dim strSQL As String
    Dim Criteria As String
 
    If IsNull(Me.sIDNo) And IsNull(Me.sSFG_ID) And _
       IsNull(Me.sLastName) And IsNull(Me.sFirstName) And _
       IsNull(Me.sPolicyNo) And IsNull(Me.sCompanyID) And _
       IsNull(Me.sReceivedDate) And IsNull(Me.sSignDate) And _
       IsNull(Me.sBatchNo) And Not Me.sShowMine Then
 
        strSQL = strSQL & "SELECT Doc_View.ID, Doc_View.SFG_ID as [SFG ID], " & vbCrLf & _
                          "       Doc_View.LASTNAME as [Last Name], " & vbCrLf & _
                          "       Doc_View.FIRSTNAME as [First Name], " & vbCrLf & _
                          "       Doc_View.POLICYNO as [Policy No], " & vbCrLf & _
                          "       Doc_View.DOCRECDATE as [Rec Date], " & vbCrLf & _
                          "       Doc_View.DOCSIGNDATE as [Signed Date], " & vbCrLf & _
                          "       Doc_View.RECDATE as [Received], " & vbCrLf & _
                          "       Doc_View.BATCHNO as [Batch No], " & vbCrLf & _
                          "       Doc_View.USERNAME as UserName, " & vbCrLf & _
                          "       Doc_View.STATUS as Status " & vbCrLf & _
                          "FROM Doc_View;"
        Me.BenDes.RowSource = strSQL
    Else
 
               strSQL = strSQL & "SELECT Doc_View.ID, Doc_View.SFG_ID as [SFG ID], " & vbCrLf & _
                          "       Doc_View.LASTNAME as [Last Name], " & vbCrLf & _
                          "       Doc_View.FIRSTNAME as [First Name], " & vbCrLf & _
                          "       Doc_View.POLICYNO as [Policy No], " & vbCrLf & _
                          "       Doc_View.DOCRECDATE as [Rec Date], " & vbCrLf & _
                          "       Doc_View.DOCSIGNDATE as [Signed Date], " & vbCrLf & _
                          "       Doc_View.RECDATE as [Received], " & vbCrLf & _
                          "       Doc_View.BATCHNO as [Batch No], " & vbCrLf & _
                          "       Doc_View.USERNAME as UserName, " & vbCrLf & _
                          "       Doc_View.STATUS as Status " & vbCrLf & _
                          "FROM Doc_View " & vbCrLf & _
                          "WHERE (" & vbCrLf
                If Not IsNull(Me.sIDNo) Then
                    If Len(Criteria) Then
                        Criteria = Criteria & " AND ((Doc_View.ID)=" & Me.sIDNo & ") " & vbCrLf
                    Else
                        Criteria = Criteria & "((Doc_View.ID)=" & Me.sIDNo & ")" & vbCrLf
                    End If
                End If
                If Not IsNull(Me.sSFG_ID) Then
                    If Len(Criteria) > 0 Then
                        Criteria = Criteria & " AND ((Doc_View.SFG_ID) = '" & Me.sSFG_ID & "')" & vbCrLf
                    Else
                        Criteria = Criteria & "((Doc_View.sfg_id) = '" & Me.sSFG_ID & "')" & vbCrLf
                    End If
                End If
                If Not IsNull(Me.sLastName) Then
                    If Len(Criteria) > 0 Then
                        Criteria = Criteria & " AND ((Doc_View.LastName)='" & UCase(Me.sLastName) & "')" & vbCrLf
                    Else
                        Criteria = Criteria & "((Doc_view.LastName)='" & UCase(Me.sLastName) & "')" & vbCrLf
                    End If
                End If
                If Not IsNull(Me.sFirstName) Then
                    If Len(Criteria) > 0 Then
                        Criteria = Criteria & " AND ((Doc_View.FirstName) = '" & UCase(Me.sFirstName) & "')" & vbCrLf
                    Else
                        Criteria = Criteria & "((Doc_view.FirstName)='" & UCase(Me.sFirstName) & "')" & vbCrLf
                    End If
                End If
                If Not IsNull(Me.sPolicyNo) Then
                    If Len(Criteria) > 0 Then
                        Criteria = Criteria & " AND ((Doc_View.PolicyNo) = '" & Me.sPolicyNo & "')" & vbCrLf
                    Else
                        Criteria = Criteria & "((Doc_View.PolicyNo) = '" & Me.sPolicyNo & "')" & vbCrLf
                    End If
                End If
                If Me.sCompanyID <> "SI" Then
                    If Len(Criteria) > 0 Then
                        Criteria = Criteria & " AND ((Doc_View.CompanyID) = '" & UCase(Me.sCompanyID) & "')" & vbCrLf
                    Else
                        Criteria = Criteria & "((Doc_View.CompanyID) = '" & UCase(Me.sCompanyID) & "')" & vbCrLf
                    End If
                Else
                    If Len(Criteria) > 0 Then
                        Criteria = Criteria & " AND ((Doc_View.CompanyID) = 'SI')" & vbCrLf
                    Else
                        Criteria = Criteria & "((Doc_View.CompanyID) = 'SI')" & vbCrLf
                    End If
                End If
                If Not IsNull(Me.sReceivedDate) Then
                    If Len(Criteria) > 0 Then
                        Criteria = Criteria & " AND ((Doc_View.DocRecDate) = #" & Me.sReceivedDate & "#)" & vbCrLf
                    Else
                        Criteria = Criteria & "((Doc_View.DocRecDate) = #" & Me.sReceivedDate & "#)" & vbCrLf
                    End If
                End If
                If Not IsNull(Me.sSignDate) Then
                    If Len(Criteria) > 0 Then
                        Criteria = Criteria & " AND ((Doc_View.DocSignDate) = #" & Me.sSignDate & "#)" & vbCrLf
                    Else
                        Criteria = Criteria & "((Doc_View.DocSignDate) = #" & Me.sSignDate & "#)" & vbCrLf
                    End If
                End If
                If Not IsNull(Me.sBatchNo) Then
                    If Len(Criteria) > 0 Then
                        Criteria = Criteria & " AND ((Doc_View.BatchNo) = " & Me.sBatchNo & ")" & vbCrLf
                    Else
                        Criteria = Criteria & "((Doc_View.BatchNo) = " & Me.sBatchNo & ")" & vbCrLf
                    End If
                End If
                If Me.sShowMine Then
                    If Len(Criteria) > 0 Then
                        Criteria = Criteria & " AND ((Doc_View.UserName) = '" & CurrentUser() & "')" & vbCrLf
                    Else
                        Criteria = Criteria & "((Doc_View.UserName) = '" & CurrentUser() & "')" & vbCrLf
                    End If
                End If
 
 
                strSQL = strSQL & Criteria & ") ORDER BY RecDate"
 
                'MsgBox strSQL, vbOKOnly
                Me.BenDes.RowSource = strSQL
    End If
 
End Sub
 

Now what I've done is copy and pasted a working example of one of my projects.

What you have to keep in mind is that this assumes that you have a table that is named "Doc_View" and all the items that start with "me." are all controls that are on my current form. this includes the me.BenDes control which is a List Control.

As you can see, I build the SQL portion and the Criteria Portion separately, and then combined them together at the bottom and then that becomes the data source for the List Control box.

Just make sure that on the Listbox Control that you set the following field values ahead of time.

Column Count = <the number of columns of data that is returned by the query>
ColumnWidths =<The value in Picos separated by ; 1" = 1440 picos> example 720 = .5"
Column Heads=<Yes/No> If you want Column Fields names at the top of the list, set this to yes, otherwise set to NO.

Bound Column=1 I'm assuming that the Primary key value will be the first field in the list of records coming out. If you want to bind the value of the ListControl to another value, then change the number to the column that has the value in it that you seek. Keep in mind that this must be a Key value that will uniquely identify each record. That is why I say just leave it at one.

That should get you started.

Oh, one more thing, I'm assuming by the code that I've sent, that the name of the search button control is SearchBtn and that it is going to be connected to the On Click event.

Hope that helps,

If you have any other questions, please let me know.

THanks,

Joe P.

[Wayneyh]

Thanks Joe

I will have a mess about with your code and see if i can get it to work for me

Regards

Wayne

[PianoMan64]

Thanks Joe

I will have a mess about with your code and see if i can get it to work for me

Regards

Wayne

I await to hear how it works out.

[Wayneyh]

Hi Joe

I have created a Table and Form for the code you gave to me. I see the code has an sCompanyID, how do i put that into the form? I am also getting a runtime error as follows -

If IsNull(Me.sIDNO) And IsNull(Me.sSFG_ID) And _
IsNull(Me.sLASTNAME) And IsNull(Me.sFIRSTNAME) And _
IsNull(Me.sPOLICYNO) And IsNull(Me.sCompanyID) And _
IsNull(Me.sRECEIVEDDATE) And IsNull(Me.sSIGNDATE) And _
IsNull(Me.sBATCHNO) And Not Me.sSHOWMINE Then

All this part is highlighted in yellow. Please advise.

Regards

Wayne