Multiple Tables, Multiple Columns

[dstrat6]

Hi Everybody, I'm creating a program in VBA Excel and what I would like to do is make a selection from a drop down list that retrieves data from different tables. The data will come from multiple columns within the tables. I can make this work from one table but one table only.
HERE'S SOME CODE:

stSQL = "SELECT OperationInitials, Cycles, CycleTime, LotNumber, PartNumber, Speed1, Distance1, SetTemp1, ActualTemp1 FROM Operation JOIN Speed_Distance ON Operation.OperationID = Speed_Distance.OperationID JOIN Temperature ON Temperature.TempID = Speed_Distance.TempID WHERE MachineNumber='" & Me.cmbMachine.Text & "'"

Hope I'm in the right forum. Sorry if I'm not and thank you in advance

dstrat6,
regaurds

 

[gmmastros]

I don't see anything wrong. What, specifically, is the problem? No records? Error Message?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dstrat6]

The problem is an error message. Maybe I'll try the Visual Basic forum because the problem might be the format. As far as SQL the format is correct, I suppose. I'm new to this so just wanted to double check with someone who new more. Thank you gmmastros for your reply.

Best Regaurds,
dstrat6

 

[chrissie1]

I think the errormessage could be important in helping you.

Christiaan Baes
Belgium

My Blog

 

[gmmastros]

What error message are you getting?

It's usually a good idea to accommodate single-quotes by doubling them, like this...

stSQL = "SELECT OperationInitials, Cycles, CycleTime, LotNumber, PartNumber, Speed1, Distance1, SetTemp1, ActualTemp1 FROM Operation JOIN Speed_Distance ON Operation.OperationID = Speed_Distance.OperationID JOIN Temperature ON Temperature.TempID = Speed_Distance.TempID WHERE MachineNumber='" & [!]Replace([/!]Me.cmbMachine.Text[!], "'", "''")[/!] & "'"

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dstrat6]

The error is from a error handler that I created to protect my program from a runtime error.

 

[ca8msm]

Can you tell us what the error says or is it a secret?


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[dstrat6]

No secret here's just this
On Error GoTo ErrorHandler



ErrorHandler:
MsgBox "Could not retrieve values from server.", vbCritical

I went ahead and created four command buttons that work and collect the correct data based on the selection of the drop down box. I'll be glad to show code if needed, nothing fancy. Thanks again for everyone's help so far. I'll keep working on what I was tring to do to begin with. I hate leaving a job unfinished.

Best Regaurds,
dstrat6

 

[gmmastros]

Change it to....

[tt][blue]
ErrorHandler:
MsgBox "Could not retrieve values from server." [!]& vbCrLf & vbCrLf & Err.Description[/!], vbCritical
[/blue][/tt]

Then, when you run it, you will get a more descriptive error message.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom