Can somebody please help me in getting this code

[SAM453]

Hi folks,

I am a ms sql programmer and i been told to build front end using Ms Access with SQL as a back end. So far i'm doing good on basic designing.

In my current form i have 4 Command buttons and Each command button represents a table's data. I also have 5 combo boxes.

Now my task is When user click on any command button it should first look for any value entered in any combo box ...?? I already populated values for combo boxes.
if not it should display all records from that SQL table.
I HAD ESTABLISHED .dsn connection between Access and SQL SERVER.

This process repeats for all command buttons. Help me in getting one command button i will take care for rest.
Let me know if any one has any questions.

Thanks,

SAM

 

[dhookom]

What do you want to happen if any of the combo boxes have values? Can you provide some logic/context? Do you want the values from the combo boxes to some how filter the records return from a chosen table?

How do you want to display the records? Do you have a form to show them?

Duane
Hook'D on Access
MS Access MVP

 

[SAM453]

Hi dhookom,

Thanks for your reply.

I am using sql server has a back end. When a user click on a command button then it should look for the any values entered in any combo box. If the user selects the value in 2 - 3 combo boxes then by back end sql select statement should be using AND condition.If the user doesn't select any value in combo box then it should display simple select * statement.

Basically the back end sql select would be
if any combo box has value then it should have select * from ... where column name = combo.value
if combo box value is "" then
select * from table name...

All my combo box values are filters comes in sql statement. I hope you got it.

Thanks,

Sam.

 

[randy700]

What you need is a WHERE clause that you can build on the fly.
Try starting with something like...

strWhere = "1=1"

Then, when you're ready to build your query, include the something like...

If ComboBox1 <> "" Then
    strWhere = strWhere & " AND [i]TextField[/i] = '" & Me.ComboBox1 & "'"
End If
If ComboBox2 <> "" Then
   strWhere = strWhere & " AND [i]NumericField[/i] = " & Me.ComboBox2
End If

Randy

 

[MajP]

Put a ? mark in the tag property of the combos.

Private Sub Command2_Click()
Dim strSql As String
strSql = "Select * from blah, blah ..."
If Not getWhere("Field1") = "" Then
strSql = strSql & " " & getWhere("Field1")
End If
'do something with the sql
'use it as a row source or apply the filter
End Sub

Public Function getWhere(fldName As String, Optional AndOrOr As String = "AND") As String
Dim cntrl As Access.Control
For Each cntrl In Me.Controls
If cntrl.Tag = "?" Then
If Not IsNull(cntrl) Then
If getWhere = "" Then
getWhere = " WHERE " & fldName & " = ' " & cntrl & "'"
Else
getWhere = getWhere & " " & AndOrOr & " " & fldName & " = ' " & cntrl & "'"
End If
End If
End If
Next cntrl
End Function

 

[dhookom]

For performance, I would use code like others suggested and change the SQL property of a pass-through query to your SQL database.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya New PostSAM453 . . .

If more than one combo value is available, ... how do you want to handle it? ... [blue]OR[/blue] ... [blue]AND[/blue] ... what?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

The OP said "AND" in one of the original posts; however, when doing this I always build a function to pass in a choice for "And" or "Or" to provide more flexibility. Then you can put a check on the form to an all or any.