Using a Text Box Field in a Query

[dabowles]

Hi,
I have a form that I have a combo box on. In that combo box, I am going to base it on a SQL statement. In that SQL statement I want to tell it to SELECT ID#, where the ID# is going to be the ID# inside the ID# field that the user will first type whenever he/she first brings up the form, so the drop down box will not be able to be chosen until a user first fills out the first field which would be ID#. Is this possible? Is there another way around that I can do this? Basically, I have a table that has a list of all ID# and one to three codes that is associated with that user ID#. I want the drop down box to be able to use the ID that has been typed in the form to select the codes that are in the same field as that specific ID# inside the table.

I hope I have explained myself clearly.

Thanks for your time,
David

 

[bcoats]

I think you can do whta you are wanting to.

Try something like this.

Dim SQLString as String

SQLString = "select codes from tablename where idNum = """ & idNumberBox.value &""";"

This should build the querry:
select codes from tablename where idNum = "value";

then you can put this in the rowsource property I think and you combo box should function properly. Let me know if it doesnt work.

Brian

 

[Guest_imported]

hey dabowels,

i think i may be doing a similar thing with SQL inside vb, so here is something that may help too:

Private Sub Combo1_Click()
Dim conn As New ADODB.Connection
Dim rsClientName As New ADODB.Recordset

'Set db connection:
Set conn = New ADODB.Connection
conn.ConnectionString = mstrCONNECTIONSTRING
conn.Open

'Fill the text fields with corresponding data from the recordset:
'make a new rs that stores just one row that we want to update:
'(this same code needs to be in the click event of the combobox.)
rsClientName.Open "SELECT * FROM tblClients WHERE ClientName = '" & _
Combo1.Text & "'", conn, adOpenStatic, adLockOptimistic, adCmdText

'----Fill the control array txtboxes with same data as above:
txtUpdateClient(0).Text = rsClientName.Fields(0).Value
txtUpdateClient(1).Text = rsClientName.Fields(1).Value
txtUpdateClient(2).Text = rsClientName.Fields(2).Value
txtUpdateClient(3).Text = rsClientName.Fields(3).Value
txtUpdateClient(4).Text = rsClientName.Fields(4).Value
txtUpdateClient(5).Text = rsClientName.Fields(5).Value
txtUpdateClient(6).Text = rsClientName.Fields(6).Value
txtUpdateClient(7).Text = rsClientName.Fields(7).Value
txtUpdateClient(8).Text = rsClientName.Fields(8).Value
txtUpdateClient(9).Text = rsClientName.Fields(9).Value
'----

' Close the connection and Cleanup:
conn.Close
Set conn = Nothing
Set rsClientName = Nothing
End Sub

I built the combo1.Text right into the SQL statement above.
Also, if you are going to update a record, i used some sql statements that first delete the record to be updated, then insert a whole new one based on the original one (which may be edited by users in the text fields).

Private Sub cmdUpdateClient2_Click()
Dim conn As New ADODB.Connection
Dim rsClients As New ADODB.Recordset
Dim statement, strDelSQL, strAddSQL, strSortASC As String
Dim i, NumCols As Integer
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table, col As ADOX.Column

'Set db connection:
Set conn = New ADODB.Connection
conn.ConnectionString = mstrCONNECTIONSTRING
conn.Open

rsClients.Open "SELECT * FROM tblClients", _
conn, adOpenStatic, adLockOptimistic, adCmdText

'-----
'Count fields in tblClients:
Set cat.ActiveConnection = conn
Set tbl = cat.Tables("tblClients&quot
For Each col In tbl.Columns
NumCols = NumCols + 1
Next
'-----

'1. Perform an UPDATE by first deleting a row, then inserting a whole _
new row based on user input:
strDelSQL = "DELETE * FROM tblClients WHERE ClientName"
strDelSQL = strDelSQL & " = '" & Combo1.Text & "'"

'2. build the INSERT statement:
strAddSQL = "INSERT INTO tblClients VALUES("
For i = 1 To NumCols
strAddSQL = strAddSQL & "'" & txtUpdateClient(i - 1).Text & "'"
If i <> NumCols Then
strAddSQL = strAddSQL & &quot;,&quot;
End If
Next i
strAddSQL = strAddSQL & &quot&quot;

'3. ReOrder the records on the mainform ASCENDING:

' Execute the DELETE statement.
conn.Execute strDelSQL, , adCmdText

' Execute the INSERT statement.
conn.Execute strAddSQL, , adCmdText

'close connection.
conn.Close

'---
'RE-LOAD CLIENT DATA from tblClients so it is updated to reflect the update of the client:
conn.CursorLocation = adUseClient
conn.Open mstrCONNECTIONSTRING

rsClients.Open &quot;SELECT * FROM tblClients&quot;, _
conn, adOpenStatic, adLockOptimistic, adCmdText
Set frmMainRDL.dgdClients.DataSource = rsClients
'---

' Close the connection and Cleanup:
Set rsClients = Nothing
Set conn = Nothing

End SUb

I think this will do something like what you want...