Search Form Using Combo

[dizy8]

Hi All,

I have a Search form using a combo. The program is already running but I want the output to be in ascending order. The program is below.

Private Sub cboDocType_AfterUpdate()
Dim myCompany As String
myDocumentType = "Select * from tbl_Incoming where ([Doc_Type] = '" & Me.cboDocType & "')"


Me.tbl_Incoming_subform.Form.RecordSource = myDocumentType
Me.tbl_Incoming_subform.Form.Requery
End Sub

Hope somebody can help.

Thank you!

[June7]

Either use a query (or SQL statement) with ORDER BY clause as RecordSource or set SortBy property of form.

[dizy8]

Hi June,

Thank you for the reply. I am new in Access and I am blurr on your instruction. Do you mind you can type it in?

Thank you so much.

[June7]

myDocumentType = "SELECT * FROM tbl_Incoming WHERE [Doc_Type] = '" & Me.cboDocType & "' ORDER BY Doc_Type"


or whatever field you want to base sort on.

[dizy8]

Hi June!

Thank you for the input. I finally figured it out based on your suggestion. for the benefit of others that may have encountered the same problem as mine, please refer below.

Thank you for the help!

Private Sub cboCompany_AfterUpdate()
Dim myCompany As String
myCompany = "Select * from tbl_Incoming where [Company] = '" & Me.cboCompany & "' ORDER BY tbl_Incoming.ID ASC"
Me.tbl_Incoming_subform.Form.RecordSource = myCompany
Me.tbl_Incoming_subform.Form.Requery
End Sub

[Welshgasman]

FWIW when you set a recordsource, there is no need for a requery.?
You are effectively doing that by setting the source, so just wasting resources, especially if that source is large.

[dizy8]

Oh gosh it only work for my first combo box. If I use my second combo box search which is the (Doc_Type), it got error already.

Private Sub cboCompany_AfterUpdate()
Dim myCompany As String
myCompany = "Select * from tbl_Incoming where [Company] = '" & Me.cboCompany & "' ORDER BY tbl_Incoming.ID ASC"
Me.tbl_Incoming_subform.Form.RecordSource = myCompany
Me.tbl_Incoming_subform.Form.Requery
Me.cboDocType = Null
End Sub


Private Sub cboDocType_AfterUpdate()
Dim myDocumentType As String
myDocumentType = "Select * from tbl_Incoming where ([Doc_Type] = '" & Me.cboDocType & "' ORDER BY tbl_Incoming.ID ASC"
Me.tbl_Incoming_subform.Form.RecordSource = myDocumentType
Me.tbl_Incoming_subform.Form.Requery
Me.cboCompany = Null
End Sub

[Welshgasman]

Text need single quotes ' or triple double quotes if value could contain a '.
Dates need #
Numerics do not need anything

[CJ_London]

you have an unnecessary bracket

..... where ([Doc_Type] = '" & M......

[June7]

Parenthesis ( )

Brackets [ ]

Apostrophes ' '

Quotes " "

Must always be in pairs when serving as special characters in SQL.