Incorporate Search using a SQL Query into A Variable

[cinders]

I have a form where I need to populate an unbound box with values from a table, based on an entry in a textbox on that form. As well the entry in the textbox populates a different table:

Tables: tblDataInput & tblCont

My form is a data entry form based on tblDataInput. The user will input a container number into a field called InputContNbr, this updates the container number in tblDataInput. Then a field on the form called InputContOwn will populate based on the value held in tblCont where the container number on the form matches the container number in tblCont.

I tried the following Event Procedure to make this happen:

Private Sub InputContOwn_Enter()
Dim strSQL As String
Set dbs = CurrentDb()
strSQL = "SELECT tblCont.ContOwn FROM tblCont WHERE (((tblCont.ContNbr)='[Forms]![frmTran1]![InputContNbr]'));"
Me!InputContOwn = strSQL
End Sub

I received an error message: Can't assign a value to this object The debugger points to this line --->Me!InputContOwn = strSQL

Can anyone help me here. This is foreign territory for me.

Thanks

Cindy

 

[helloworld]

hey there...do a search on this forum for something called a query def. that code takes input from an query and puts it into a variable!

 

[ByteMyzer]

Try this code:

Private Sub InputContOwn_Enter()
    Dim strSQL As String, rst As Recordset
    strSQL = "SELECT tblCont.ContOwn FROM tblCont WHERE (((tblCont.ContNbr)='" _
        & Me!InputContNbr & "'));"
    Set rst = CurrentDb.OpenRecordset(strSQL)
    Me!InputContOwn = rst!ContOwn
    rst.Close
End Sub

 

[cinders]

I get an error mismatch type and it points to the line:

Set rst = CurrentDb.OpenRecordset(strSQL)

Any thoughts?

Thanks for your help

Cindy

 

[ByteMyzer]

Works fine for me. You may wish to confirm that you have Microsoft DAO Object Library checked under your Visual Basic References.

 

[ByteMyzer]

Alternatively, if you don't wish to mess with your references (tho I don't know why not, it's more fun), here's a revision of the above code:

Private Sub InputContOwn_Enter()
    Dim strSQL As String, dbe As Object, dbs As Object, rst As Object
    strSQL = "SELECT tblCont.ContOwn FROM tblCont WHERE (((tblCont.ContNbr)='" _
        & Me!InputContNbr & "'));"
    Set dbe = CreateObject("DAO.DBEngine")
    Set dbs = dbe.OpenDatabase(CurrentDb.NAME)
    Set rst = dbs.OpenRecordset(strSQL)
    Me!InputContOwn = rst!ContOwn
    rst.Close
End Sub