SQL question 1

[twoody54]

Hi,

I'm trying to avoid writing any queries at all and installed use the VBA code to do SQL.

I have a form in which I have a combo box (cboClient)allowing people to either select their client with the drop down or to manually type it in until their client appears. Once they have their client they should have the client's industry show up in an unbound textbox (txtIndustry).

I've written the query in order to do so and it runs fine:
SELECT [Client Industry].[Industry]
FROM Clients LEFT JOIN [Client Industry] ON [Clients].[ClientId]=[Client Industry].[ClientID]
WHERE ((([Clients].[ClientId])=[forms]![frmEnterIndustry]![cboClient]));

The problem I'm having is to have the industry appear in the text box.

basically I want the after_update event to trigger the code to populate the txtIndustry box. The part I'm hung up on is this...

me!txtIndustry= DoCmd.RunSQL strSQL

I made the SQL statement a string. I know this is not the correct syntax to pull in the info but you can see what I want done. If anyone could help I would appreciate it.

I know this is probably pretty simple. I appreciate the help

Thanks
Tom

 

[scclem]

Tom,

You should be able to do this with a simple DLOOKUP() command associated with your text box statement.

me!txtIndustry = DLOOKUP("[Industry]","Client Industry", "[ClientId])=[forms]![frmEnterIndustry]![cboClient]))


Much easier than involving an SQL statement.

Hope it helps,
Scott

 

[jbpelletier]

'***********************************************************
'DAO.Recordset need that the componant
'"Microsoft DAO 3.6 Object Library"
'is included in the projet
'In [Menu-->Tools-->Reference...] Check the box
'***********************************************************
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(strSQL)
If not rs.Eof Then
rs.MoveFirst
me!txtIndustry = rs.Fields(0) 'where 0 is the 1st field
End If
rs.Close

jb

 

[scclem]

Sorry Tom,

After posting I noticed typos. Statement should be:

me!txtIndustry = DLOOKUP("[Industry]","Client Industry", "[ClientId]=[forms]![frmEnterIndustry]![cboClient])

Scott

 

[twoody54]

The DLookup worked, Thanks. The helps me out a lot actually with quite a few other things I'll be doing.

I've opened recordsets based on queries before but figured there was an easier way in this case.