Using dlookup in a dao recordset 1

[MikeGeitner]

Hello,

I don't know if I'm going about this the right way, but I'm working with a form based a recordset that comes from a different database.

When the form loads I'm getting the list of materials in a combobox. Now, what I'd like to do is populate a textbox on the form with a field from the matching record.

I'm getting a syntax error with the dlookup statement. Any help is appreciated.

Private Sub Form_Load()

Set ws = DBEngine.Workspaces(0)
Let strConnection = "ODBC;DSN=" & "jobboss32" & ";UID=" & "Support" & ";PWD=" & "lonestar"
Set db = ws.OpenDatabase("", False, False, strConnection)

Set rs = db.OpenRecordset("SELECT * FROM Material WHERE type = 'F' AND status = 'Active' ORDER BY Material", dbOpenDynaset, dbReadOnly)


Do Until rs.EOF = True
cboMat.AddItem rs.Fields("Material")
rs.MoveNext
Loop

End Sub

Here's the dlookup:

Private Sub cboMat_AfterUpdate()

Me!txtDesc = DLookup("[Description]", "Material", "[Material] = " & [cboMat] & "'")

End Sub

 

[PHV]

As for the syntax error:
Me!txtDesc = DLookup("[Description]", "Material", "[Material] = [!]'[/!]" & [cboMat] & "'")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MikeGeitner]

Thanks PHV!

That took care of the syntax. But now there's an error 3708, "....cannot find the input table or query 'Material'.....".

I'll bet you already knew this wasn't going to work.


What I'm trying to do is give some folks here at work an easy way to get some data out of our JobBoss database, without them having to log-on. So far, I can connect to it and with your help yesterday, have a recordset to navigate around. Now, I'd like to populate the form with the corresponding fields from the material chosen in the combobox. What am doing wrong?

Thanks,
Mike

 

[PHV]

Why not simply use linked table(s) with password remembered ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MikeGeitner]

Thanks! I will look at that.

 

[MikeGeitner]

PHV, that is it. Once I got connected to the JobBoss db and used the Linked Tables wizard to get the "material" table, I saw the simplicity of your suggestion. Thanks!