Combo Box Value

[dparikh]

I have combobox in my application where i am populating SSN of all the employee.I want to generate report when user select SSn from combo and dates from Cal. How do i select what user is selecting from Combo and write in my SQL . My Code is something like this.

Thanks in Advance
Private Sub Form_Load()
Set rs = New ADODB.Recordset
Dim sql As String
rs.Open "select SSN from EmpDetailInfo order by ssn", cnn, adOpenDynamic, adLockOptimistic
'"select [Firstname] + ' ' + [LastName] as name from EmpDetailInfo"
Do While Not rs.EOF
cboname.AddItem rs(0)
rs.MoveNext
Loop
cboname.Refresh
End Sub
Private Sub txtfrom_dblclick()
Cal.Show
End Sub

Private Sub txtto_dblclick()
Cal.Show
End Sub
Public Function sqlReport()
sqlReport = "select checkdate,grosspay, amountofcheck from empcheckinfo where ssn = ' " & ssn & "' and right(checkdate,4) + left(checkdate,4) between '" & txtfrom & "' and '" & txtto & "'"

End Function

 

[ulwitch]

dparikh,
I am not sure if this is what you want, but here goes.

I would create a Sub that is called when each control(SSN, FromDate, and ToDate) changes. I don't see in your code where you populate the SSN, but I am assuming that you have a combo box with them in it.

Public Sub sqlReport()
Dim FromDate As Date
Dim ToDate As Date
Dim sSSN As String

If Len(cboSSN.Text)=0 Then
Exit Sub
Else
sSSN=cboSSN.Text
End If
If Not IsDate(txtFrom.Text) Then
Exit Sub
Else
FromDate=CDate(txtFrom.Text)
End If
If Not IsDate(txtTo.Text) Then
Exit Sub
Else
ToDate=CDate(txtTo.Text)
End If



sqlReport = "select checkdate,grosspay, amountofcheck from empcheckinfo where ssn = ' " & sSSN & "' and right(checkdate,4) + left(checkdate,4) between '" & FromDate & "' and '" & ToDate & "'"

End Sub

 

[johnwm]

Is SSN a 'Long' or an 'Int' or a 'String'? It looks like an Int or Long from your usage in sqlReport.
If thats the case then you can simplify with something like:

Dim mySQL as String
Dim myCount as long
mySQL = "SELECT ssn, [Firstname] + ' ' + [LastName] as name from EmpDetailInfo"
'set up db connection etc here
rs.Open mySQL
'populate Combo with names, using itemdata for SSN
Do While Not rs.EOF
cboname.AddItem rs(1) , myCount
cboname.Itemdata(myCount) = rs(0)
myCount = myCount + 1
rs.MoveNext
Loop

'Then use combo1.index to point to SSN for query
Sub whatever_click
dim mySSN as long
mySSN = Combo1.ItemData(Combo1.ListIndex)
mySQL = "SELECT whatever from tbl WHERE ssn = " & mySSN
Let me know if this helps
________________________________________________________________
If you are worried about how to post, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'