select something i cbo and change the text of another txt - #name? error

Question

For some sort of reason, which i can't seem to spot, this fail and i get my #Name? error, when i tried to do this, i use pretty much the same code another place in my form, the only difference is the cbo and the names of my txt and alike.

Private Sub cboVarenummer_AfterUpdate()
  Dim LSQLVareNavn  As String
    LSQLVareNavn = "select Varenavn from VARENUMMER where VARENUMMER.Varenummer = '" & cboVarenummer & "'"
    txtVarenavn.ControlSource = LSQLVareNavn
End Sub

Can anyone spot my error?

what i wish the code would do is when i Select something in my cboVarenummer, the my txtbox txtVarenavn changes its text to what the sql statement returns?

Answer 1

Did you not want cboVarenummer.SelectedValue.Tostring (Or cboVarenummer.SelectedItem.Tostring dependant on how you populated the combo box) in the SQL Query?

Answer 2

This is ms-access, right?

In which case, I do not believe you wish to set the Control Source (normally the name of a control in the bound table, a constant, or a function) to an SQL string, I believe you want the RowSource.

In addition, is Varenummer a text field? If not, drop the single quotes.

Answer 3

what i wish the code would do is when i Select something in my cboVarenummer, the my txtbox txtVarenavn changes its text to what the sql statement returns

In that case, I think you should be changing txtVarenavn's .Value property rather than its .ControlSource property.

Also use the DLookup() function to retrieve the value you want to store in txtVarenavn.Value

Private Sub cboVarenummer_AfterUpdate()
    'Dim LSQLVareNavn  As String '
    Dim strWhere As String
    'LSQLVareNavn = "select Varenavn from VARENUMMER where VARENUMMER.Varenummer = '" & cboVarenummer & "'" '
    'txtVarenavn.ControlSource = LSQLVareNavn '
    strWhere = "Varenummer = '" & Me!cboVarenummer & "'"
    Me!txtVarenavn = DLookup("Varenummer", "Varenummerf", strWhere)
End Sub

Compare the DLookup expression to your previous SELECT statement. You can see how the field name, table name, and WHERE clause correspond between the two. Think of DLookup as a simple SELECT ... it returns only a single value rather than a recordset ... but that's exactly what you need here.

I also changed the table name to Varenummerf, as you listed in your comment to @Ben. VARENUMMER as the table and Varenummer as the field name might not confuse Access' database engine, but it makes me uneasy. :-)