HELP WITH COMBO BOX

  • Thread starter Thread starter DK
  • Start date Start date
D

DK

Hello Everybody.

I using following procedure to search combobox

Private Sub Text36_Change()
Me.Combo7.Requery
Me.Transactions_Subform.Requery
If Me.Combo7.ListCount = 1 Then
With Me.Combo7
.Value = .ItemData(0)
End With
With Me.Transactions_Subform
.Requery
.SetFocus
End With
End If
If Me.Combo7.ListCount = 0 Then
Dim strMsg, x As String
strMsg = "CLIENT IS NOT RECORDED"
MsgBox strMsg
Me.Text36 = ""
Me.Combo7.Requery
End If
End Sub

the source of combo7 is "SELECT Clients.ClientID, Clients.Name FROM Partneri
WHERE (((Clients.Name) Like [Forms]![Transactions]![Text36].[Text] & "*"))
ORDER BY Clients.Name;"

Id like to change above procedure into:

Private Sub Text36_Change()
Dim optChoose As String
Select Case Me.Frame98
Case Is = 1
optChoose = "SELECT Clients.ClientID, Clients.Name FROM Partneri WHERE
(((Clients.Name) Like [Forms]![Transactions]![Text36].[Text] & "*")) ORDER
BY Clients.Name;"
Case Is = 2
optChoose = "SELECT Clients.ClientID, Clients.Name FROM Partneri WHERE
(((Clients.ClientID) Like [Forms]![Transactions]![Text36].[Text] & "*"))
ORDER BY Clients.ClientID;"
End Select

With Me.Combo7
.RowSource = optChoose
.Requery
End With
Me.Transactions_Subform.Requery
If Me.Combo7.ListCount = 1 Then
With Me.Combo7
.Value = .ItemData(0)
End With
With Me.Transactions_Subform
.Requery
.SetFocus
End With
End If
If Me.Combo7.ListCount = 0 Then
Dim strMsg, x As String
strMsg = "CLIENT IS NOT RECORDED"
MsgBox strMsg
Me.Text36 = ""
Me.Combo7.Requery
End If
End Sub

But it doesnt work.

Can anybody help with procedure.

Regards,
DK
 
Your SQL doesn't make sense!

optChoose = "SELECT Clients.ClientID, Clients.Name FROM Partneri WHERE
(((Clients.Name) Like [Forms]![Transactions]![Text36].[Text] & "*")) ORDER
BY Clients.Name;"

only lists one table in the FROM clause (Partneri), yet you're qualifying
the fields as though they're coming from Clients.

Assuming that the FROM clause should actually say Clients, try putting the
reference to the form field outside of the quotes:

optChoose = "SELECT ClientID, Name " & _
"FROM Clients " & _
"WHERE Name Like " & Chr$(34) & _
[Forms]![Transactions]![Text36].[Text] & "*" & Chr$(34) & _
" ORDER BY Name"

(I removed a lot of the unnecessary stuff Access added to the SQL, btw)

Also, in order to use Like, ClientID must be text. Is it?
 
Back
Top