SQL query question [Resolved]

[Kalilian]

I have a db with a table called Customers. There are two columns in Customers: CustomerCode and CustomerName.

My vb form has a combo box with a list of the customer code, a text box, and a button.

What I want to do is: If a user chooses a customer code from the combo box I want the customer name to show in the text box.

This is the SQL query that I have been using:

strSQL = "SELECT CustomerName FROM Customers WHERE CustomerCode = '" & cboCustomerCode.Text & "'"

The problem i'm having is that the result of the customer name will not show on the text box.

This is what I used to put the result in the text box:

txtCustomerName = strSQL

Is this correct?

[lintz]

The query syntax looks correct however you need to execute the query before the result will show in the text box.

[Kalilian]

Yes, I think that's where I'm having the problem. How would you execute the SQL?

[dee-u]

What did you use to populate the combo box in the first place?

[Kalilian]

I used the values from the db. Therefore I did all the connection, recordsets and all that.

I was wondering if I should do:

recordset.Open strSQL

[lintz]

Yes

[dee-u]

Then create a recordset too based on what is selected in the combo box....

Dim adoRecordset As ADODB.Recordset
Set adoRecordset = New ADODB.Recordset
With adoRecordset
    .Open "SELECT CustomerName FROM Customers WHERE CustomerCode = '" & cboCustomerCode.Text & "'",'your connection'
    If Not (.EOF And .BOF) Then
        txtCustomerName = .Fields("CustomerName")
    End If
    .Close
End With
Set adoRecordset = Nothing

[Kalilian]

Ok.

A different scenario now.

What if I have multiple SQL queries in different Subs? Would you still do the same thing?

[jobs_arghya]

try this code:
In the General Declaration:
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Form_Load()
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & app.path _ & "\db.mdb;Persist Security Info=False"

In the event You want:

strSQL = "SELECT CustomerName FROM Customers WHERE CustomerCode = '" & trim(cboCustomerCode.Text) & "'"
cnn.close
cnn.open
rs.open strsql,cnn
rs.requery
text1.text=rs.fields("Customername")

Yes it is possible to have multiple Sql queries in different subs. Write the exact thing which is written in the event You want. Simply change the Sql query

[Kalilian]

What if I use the 'command' property? How would you go about doing that?

[Mark Gambo]

Why not populate your combobox with the CustomerCode and CustomerName.
and then parse what you need and put it into the textbox

"1234-Jones"
"2345-Roberts"
"234-Davis"

Private Sub Combo1_KeyPress()
Dim strTex as String
     strText = Me.Combo1.Text
     strText = Mid(strText,Instr(strText,"-")+1)
     Me.Text1.Text = strText
End Sub

[Kalilian]

No, I can not do that. The customer code can only be a combo box and the customer name has to be a text box.

[Mark Gambo]

No, I can not do that. The customer code can only be a combo box and the customer name has to be a text box.

No I understand that but I don't think you understand what I am posting take a look at the attached project:

[Kalilian]

Ok, I understand you Mark Gambo. But it's all right. I got it working. However, there is another thing that I need to fix.

I am now able to get the customer name and place that in the text box. My combo box does the auto-fill thingy where the user types in a letter and tries to find it in the list. How can I get the method to work (customer name into text box) only when the user has finished typing in the combo box?

Because every time I type in the combo box it gives me the customer name but if the customer name doesn't exist in the list then it gives me an error.

[Mark Gambo]

Ok, I understand you Mark Gambo. But it's all right. I got it working. However, there is another thing that I need to fix.

I am now able to get the customer name and place that in the text box. My combo box does the auto-fill thingy where the user types in a letter and tries to find it in the list. How can I get the method to work (customer name into text box) only when the user has finished typing in the combo box?

Because every time I type in the combo box it gives me the customer name but if the customer name doesn't exist in the list then it gives me an error.

You can either trap for the error or fire the code that gets the Customer's name on the comboboxes Lost Focus Event.

[dglienna]

Sounds like you are using the Changed event, when you would want to use the lost focus, or even a button. Post your code that you are using.

[Kalilian]

How would you use the Lost Focus Event?

[Mark Gambo]

You can either trap for the error or fire the code that gets the Customer's name on the comboboxes Lost Focus Event.

or better yet add code to the GetData Function that checks that there is a valid value in the combobox before running the query. Post your code for the Auto-Fill.

[Kalilian]

Here's my auto-fill code:

Public Sub cboCustomerCode_Change()

Dim i As Long
Dim MyText As Long

'Doesn't run the autofill when deleting or using the backspace
If Backspaced = True Or cboCustomerCode.Text = "" Then
Backspaced = False
Exit Sub
End If

'Run through the available items and grab the first matching one.
For i = 0 To cboClustomerCode.ListCount - 1

If InStr(1, cboCustomerCode.List(i), cboCustomerCode.Text, vbTextCompare) = 1 Then

MyText = cboCustomerCode.SelStart
cboCustomerCode.Text = cboCustomerCode.List(i)
cboCustomerCode.SelStart = MyText
cboCustomerCode.SelLength = Len(cboCustomerCode.Text) - MyText

Exit For
End If

Next
End Sub

[Kalilian]

And this is what I used to fill the text box:

Public Sub getName()
comm.CommandText = "SELECT CustomerName FROM Customers WHERE CustomerCode = '" & cboCustomerCode.Text & "'"

Set recordSet = comm.Execute

txtCustomerName.Text = recdSet.Fields("CustomerName")
End Sub

[Mark Gambo]

This is what I use for a Combo AutoFill:

In A Module:

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, _
lParam As Any) As Long
 
Public Function AUTOFIND(ByRef cboCurrent As ComboBox, _
    ByVal KeyAscii As Integer, Optional ByVal LimitToList As Boolean = False)
    
    Dim lCB As Long
    Dim sFindString As String
    
    If KeyAscii = 8 Then
        If cboCurrent.SelStart <= 1 Then
            cboCurrent = ""
            AUTOFIND = 0
            Exit Function
        End If
            If cboCurrent.SelLength = 0 Then
                sFindString = UCase(Left(cboCurrent, Len(cboCurrent) - 1))
            Else
                sFindString = Left$(cboCurrent.Text, cboCurrent.SelStart - 1)
            End If
    
    ElseIf KeyAscii < 32 Or KeyAscii > 127 Then
        Exit Function
    Else
        If cboCurrent.SelLength = 0 Then
            sFindString = UCase(cboCurrent.Text & Chr$(KeyAscii))
        Else
            sFindString = Left$(cboCurrent.Text, cboCurrent.SelStart) & Chr$(KeyAscii)
        End If
    End If
    lCB = SendMessage(cboCurrent.hWnd, CB_FINDSTRING, -1, ByVal sFindString)
    If lCB <> CB_ERR Then
        cboCurrent.ListIndex = lCB
        cboCurrent.SelStart = Len(sFindString)
        cboCurrent.SelLength = Len(cboCurrent.Text) - cboCurrent.SelStart
        AUTOFIND = 0
    Else
        If LimitToList = True Then
            AUTOFIND = 0
        Else
            AUTOFIND = KeyAscii
        End If
    End If
End Function

On the Form:

Private Sub Combo1_KeyPress(KeyAscii As Integer)
On Error GoTo Combo1_KeyPress_Error
 
     KeyAscii = AUTOFIND(Me.Combo1, KeyAscii, True)
 
On Error GoTo 0
Exit Sub
 
Combo1_KeyPress_Error:
 
MsgBox "Error " & Err.Number & " (" & Err.Description & ") "  & _ 
"in procedure Combo1_KeyPress of Form " & Me.Name
End Sub

[Mark Gambo]

and Then run your code to fill the TextBox:

Public Sub getName()
comm.CommandText = "SELECT CustomerName FROM Customers " & _
"WHERE CustomerCode = '" & cboCustomerCode.Text & "'"
 
Set recordSet = comm.Execute
 
txtCustomerName.Text = recdSet.Fields("CustomerName")
End Sub

I put the AutoFind function into a module so that I can use it throughout my application. I still think that you should combine the Customer Number and the Customer Name in the Combobox and then after that proper name has been selected then you strip away the customer number from the combobox and take the customer name and put it into the textbox, just my two cents.

[Kalilian]

How would you write the syntax for:

If 'text in combo box not in the list' then
'do nothing'
Else
'get the name'

[Kalilian]

How would you write the syntax for:

If 'text in combo box not in the list' then
'do nothing'
Else
'get the name'

Figured it out

[Kalilian]

I have resolved my problems. Thanks for all your help.