SQL Correct Syntax

[tbassngal]

Can someone please help me to know how to change this to the appropriate VBA query syntax:

SELECT tblApplicationDescription.Application, tblApplicationDescription.Desc1
FROM tblApplicationDescription
WHERE (((tblApplicationDescription.Application) = "Membership Kit") And ((tblApplicationDescription.Desc1) = "KD3336"))
ORDER BY tblApplicationDescription.Application, tblApplicationDescription.Desc1;

This is what I had - but I am adding now another field to look up (application + desc1 = row source for desc2:

sSQL = "SELECT Desc1 FROM tblApplicationDescription"
sSQL = sSQL & " WHERE [Application] = '" & Me.[cboApplication] & "' & Me.[cboDesc1] & " ' "


sSQL = sSQL & " ORDER BY Desc1"

[rpeare]

sSQL = "SELECT Application, Desc1 "
sSQL = sSQL & "FROM tblApplicationDescription "
sSQL = sSQL & "WHERE ("
sSQL = sSQL & "((Application) = '" & me.cboApplication & "') AND "
sSQL = sSQL & "((Desc1) = '" & me.cbodesc1 & "')"
sSQL = sSQL & ") "
sSQL = sSQL & "ORDER BY Application, Desc1"

[Robeen]

I ran your sSQL string and saw that it was not returning a correct SQL string.

The value after your 'WHERE [Application] = ' shoulld be couched in double quotes.
When I ran your sSQL:
1. It was not concatenating both your combo box values
AND
2. It was not putting what was after 'WHERE [Application] = ' in double quotes either.

Try this:

 
sSQL = "SELECT Desc1 FROM tblApplicationDescription"
sSQL = sSQL & " WHERE [Application] = " & Chr(34) & Me.[cboApplication]  & Me.[cboDesc1] & Chr(34)
sSQL = sSQL & " ORDER BY Desc1"
MsgBox sSQL

I put a MsgBox right after the SQL string build to give you a visual of your SQL string [sSQL]. You can remove it later.

Let me know if this helps.

[tbassngal]

Robeen, I tried the second one after your recommendation and the SQL comes out a little weird, I just have to figure out the correct syntax I think - where the quotes are suppose to be ...

this is what I am getting:

"SELECT Desc1 FROM tblApplicationDescription WHERE [Application] = "Membership KitKD3346" ORDER BY Desc1"

The "Membership Kit" is the application and the "KD3346" is the Desc1 value.

[Robeen]

This is what I got. [I used two String Variables - with values "Boxer" & "Shorts" - in building sSQL - instead of Combobox values].

Is this not what you needed?

[Robeen]

Robeen, I tried the second one after your recommendation and the SQL comes out a little weird, I just have to figure out the correct syntax I think - where the quotes are suppose to be ...

this is what I am getting:

"SELECT Desc1 FROM tblApplicationDescription WHERE [Application] = "Membership KitKD3346" ORDER BY Desc1"

The "Membership Kit" is the application and the "KD3346" is the Desc1 value.

In that case - rpeare's post might be what you need. Have you tried that?

[rpeare]

I assumed your original SQL statement was giving you the results you wanted. If it is mine should work. What you are doing with the code you're passing back and forth with robeen is concantenating two fields and attempting to search ONE field of your database which is not going to give you what you want.

[Robeen]

I think rpeare is correct.

I was trying to replicate this in your sSQL string:

sSQL = sSQL & " WHERE [Application] = '" & Me.[cboApplication] & "' & Me.[cboDesc1] & " ' "

[tbassngal]

sSQL = "SELECT Application, Desc1 "
sSQL = sSQL & "FROM tblApplicationDescription "
sSQL = sSQL & "WHERE ("
sSQL = sSQL & "((Application) = '" & me.cboApplication & "') AND "
sSQL = sSQL & "((Desc1) = '" & me.cbodesc1 & "')"
sSQL = sSQL & ") "
sSQL = sSQL & "ORDER BY Application, Desc1"

That isn't exactly working. This is what the data looks like:



After running SQL, the Desc2 = "Membership Kit" vs "Replacement Cards" - any ideas?

[tbassngal]

I can't do a print screen?

This is what the data looks like attached.

I need the value of Desc2 to be "Replacement Cards" and the value of Desc3 to be "2 Card" if I select the value of "KD3346" from cboDesc1

[rpeare]

I have no idea what you're asking.

Your original question was about limiting a query to items that you select from a combo box. My code does that, assuming you have two fields on your form called APPLICATION and CBODESC1. If the BOUND COLUMN of those combo boxes is the the description and application text then your query will correctly isolate the first row of your table.

It looks to me though, as though your table is not normalized which is going to make things more difficult for you in the long run.

[tbassngal]

Thank you everyone, I figured it out. RPeare, you are correct, I did not create this database, I am only trying to help someone who has something already. I have already advised them of many database rules that could cause potential issues in the long run if not corrected.

This is what the code ended up looking like which may help you to understand what I was actually trying to do? Bottom line is I just wanted to have Desc1 be a combo box that was enabled but Desc2 and Desc3 to populate their values based upon the selection of application and Desc1 (just grabbing the value from the same row of data = SQL statement).

Private Sub cboApplication_AfterUpdate()
Dim sSQL As String

'Desc1 Value
'clear cboDesc1
Me.cboDesc1 = Null
sSQL = "SELECT Desc1 FROM tblApplicationDescription"
sSQL = sSQL & " WHERE [Application] = '" & Me.[cboApplication] & "' "
sSQL = sSQL & " ORDER BY Desc1"

'Debug.Print sSQL1
Me.cboDesc1.RowSource = sSQL
'check if cboDesc1 has values
If IsNull(Me.cboDesc1.ItemData(0)) Then
Me.cboDesc1.Enabled = False
Else
Me.cboDesc1.Enabled = True
Me.cboDesc1 = Me.cboDesc1.ItemData(0)

'optional
Me.cboDesc1.SetFocus
Me.cboDesc1.Dropdown
End If
End Sub

Private Sub cboDesc1_AfterUpdate()
Dim sSQL As String
'cboDesc2 Value
'clear cboDesc2
Me.cboDesc2 = Null

sSQL = "SELECT Desc2 "
sSQL = sSQL & "FROM tblApplicationDescription "
sSQL = sSQL & "WHERE(((Application) = '" & Me.cboApplication & "') AND "
sSQL = sSQL & "((Desc1) = '" & Me.cboDesc1 & "')) "
sSQL = sSQL & "ORDER BY Application, Desc1"
'Debug.Print sSQL
Me.cboDesc2.RowSource = sSQL
'check if cboDesc2 has values
If IsNull(Me.cboDesc2.ItemData(0)) Then
Me.cboDesc2.Enabled = True
Else
Me.cboDesc2.Enabled = False
Me.cboDesc2 = Me.cboDesc2.ItemData(0)
End If

'cboDesc3 Value
'clear cboDesc3
Me.cboDesc3 = Null

sSQL = "SELECT Desc3 "
sSQL = sSQL & "FROM tblApplicationDescription "
sSQL = sSQL & "WHERE(((Application) = '" & Me.cboApplication & "') AND "
sSQL = sSQL & "((Desc1) = '" & Me.cboDesc1 & "')) "
sSQL = sSQL & "ORDER BY Application, Desc1"
'Debug.Print sSQL
Me.cboDesc3.RowSource = sSQL
'check if cboDesc3 has values
If IsNull(Me.cboDesc3.ItemData(0)) Then
Me.cboDesc3.Enabled = True
Else
Me.cboDesc3.Enabled = False
Me.cboDesc3 = Me.cboDesc3.ItemData(0)
End If

End Sub