insert a select statement in a text box

[rsbutterfly16]

hi guys. i was hoping you guys could help me, i have a combo box "cbocontract" which gets populated according to a selection from another combobox(cboAll). now i have another combo box(cboStatus) which according to what the user selects in cbocontract list box it should display active or inactive, now i have the row source from the cboStatus like this:

SELECT DISTINCTROW Test.Status FROM TEST WHERE (TEST.Facility=forms![Change of Status]!txtInvoice.value) and (TEST.PM_Contract_ID=forms![Change of Status]!cboContract);

it works perfectly, however is there a way to make put this code in a text box? how do i insert the select distinctrow into a textbox??? it should only display one value according to what the user selects in the cbocontract combo box... also, the user should be able to edit this textbox.

 

[elbweb]

the only way you can have a text box populate based on data is having a bound form, or by filling it in with code.

here is a quick example code that shows how to do it that way.

    Dim myConn As ADODB.Connection
    Dim rsTemp As ADODB.Recordset
    
    Set myConn = CurrentProject.Connection
    Set rsTemp = New ADODB.Recordset
    
    rsTemp.CursorLocation = adUseClient
    rsTemp.CursorType = adOpenStatic
    rsTemp.LockType = adLockOptimistic
    
    rsTemp.Open "SELECT * FROM tblTable;", myConn

    txtTextBox.Value = rsTemp!Field
    
    rsTemp.Close
    
    Set rsTemp = Nothing
    myConn.Close
    Set myConn = Nothing

another thread i did a pretty expansive explination about recordsets and how to use them, you might find it helpful:

http://www.access-programmers.co.uk/forums/showthread.php?t=111842

 

[rsbutterfly16]

Thank you so much, this is what i am trying to put, (LOC is my table in sql server)


Private Sub cboContract_AfterUpdate()


Dim mysql1 As String
Dim rs As DAO.Recordset


mysql1 = "SELECT DISTINCTROW LOC.Status FROM LOC WHERE (LOC.Facility_Entity_Code=forms![Change of Status]!txtFEC.value) and (LOC.PM_Contract_ID=forms![Change of Status]!cboPMContract)"

Set rs = CurrentDb.OpenRecordset(mysql1)


txtStatus = rs("[Status]")


but it keeps telling me there are one or more empty parameters... do you why this could happen? as of now in my row source in my combo box i have the same exact select statement and it does not give me any errors....

 

[elbweb]

you need to ''build' the sql string before you execute it.

this is what you should have instead:

mysql1 = "SELECT DISTINCTROW LOC.Status FROM LOC WHERE (LOC.Facility_Entity_Code='" & txtFEC.value & "') and (LOC.PM_Contract_ID='" & cboPMContract.Value & "');"

i hope you see what this is doing, and make sure that you use quotes around all things that should be considered litteral values within the SQL statement (single quotes, like in my example).

 

[rsbutterfly16]

thank you!!!! but now when i run it it gives me a runtime error saying the "value you entered isn't valid for this field and it points to



txtStatus = rs("[Status]")




Any suggestions pleaseeeeeeeee... , status is a column field set to varchar in my sql server table....