Combo Box Question

  • Thread starter Thread starter LJG
  • Start date Start date
L

LJG

Hi Guys,

I am trying to get this code to work and wonder if someone can give me a
hand.

I have a form with a combo control and need to select different row source
if the value of a field meets selected criteria. My Code is:

Private Sub qty_AfterUpdate()
If Me.qty <= 4 Then
Me.cbosoft.RowSource = "SELECT [Software Costs].softID, [Software
Costs].Description, [Software Costs]. 1_off "
FROM [Software Costs] WHERE [Software Costs].type = "epos"

If Me.qty >= 5 and <=9 Then
Me.cbosoft.RowSource = "SELECT [Software Costs].softID, [Software
Costs].Description, [Software Costs]. advantage"
FROM [Software Costs]
WHERE [Software Costs].type = "epos"

If Me.qty >= 10 and <=14 Then
Me.cbosoft.RowSource = "SELECT [Software Costs].softID, [Software
Costs].Description, [Software Costs]. premier"
FROM [Software Costs]
WHERE [Software Costs].type = "epos"

If Me.qty >= 15 and <=19 Then
Me.cbosoft.RowSource = "SELECT [Software Costs].softID, [Software
Costs].Description, [Software Costs]. corporate"
FROM [Software Costs]
WHERE [Software Costs].type = "epos"

End If
End If
End If
End If
End Sub

The problem I get initially is that the system fails on the FROM section,
also need help in the correct format of the IF part of the statement.

All suggestions appreciated.

TIA

Les
 
The problem with "From" is that you've got it outside of your quotes so VBA
is trying to recognize it.

This line:

Me.cbosoft.RowSource = "SELECT [Software Costs].softID, [Software
Costs].Description, [Software Costs]. advantage" FROM [Software Costs]
WHERE [Software Costs].type = "epos"

Should be

Me.cbosoft.RowSource = "SELECT [Software Costs].softID, [Software
Costs].Description, [Software Costs]. advantage FROM [Software Costs]
WHERE [Software Costs].type = ""epos"""

There are two pairs of quotes around the text value "epos".

On the if statement, you have to explicitly name the comparison field for
each comparison value. Where you have:

If Me.qty >= 5 and <=9 Then

You need:

If Me.qty >= 5 and Me.qty<=9 Then
 
Thanks for that Sandra,

When I enter the code VBA keeps putting in the quotes? I have tried forcing
a line break with a _ but it still keeps putting in the quotes!!

Les




Sandra Daigle said:
The problem with "From" is that you've got it outside of your quotes so
VBA is trying to recognize it.

This line:

Me.cbosoft.RowSource = "SELECT [Software Costs].softID, [Software
Costs].Description, [Software Costs]. advantage" FROM [Software Costs]
WHERE [Software Costs].type = "epos"

Should be

Me.cbosoft.RowSource = "SELECT [Software Costs].softID, [Software
Costs].Description, [Software Costs]. advantage FROM [Software Costs]
WHERE [Software Costs].type = ""epos"""

There are two pairs of quotes around the text value "epos".

On the if statement, you have to explicitly name the comparison field for
each comparison value. Where you have:

If Me.qty >= 5 and <=9 Then

You need:

If Me.qty >= 5 and Me.qty<=9 Then

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi Guys,

I am trying to get this code to work and wonder if someone can give
me a hand.

I have a form with a combo control and need to select different row
source if the value of a field meets selected criteria. My Code is:

Private Sub qty_AfterUpdate()
If Me.qty <= 4 Then
Me.cbosoft.RowSource = "SELECT [Software Costs].softID, [Software
Costs].Description, [Software Costs]. 1_off "
FROM [Software Costs] WHERE [Software Costs].type = "epos"

If Me.qty >= 5 and <=9 Then
Me.cbosoft.RowSource = "SELECT [Software Costs].softID,
[Software Costs].Description, [Software Costs]. advantage"
FROM [Software Costs]
WHERE [Software Costs].type = "epos"

If Me.qty >= 10 and <=14 Then
Me.cbosoft.RowSource = "SELECT [Software Costs].softID,
[Software Costs].Description, [Software Costs]. premier"
FROM [Software Costs]
WHERE [Software Costs].type = "epos"

If Me.qty >= 15 and <=19 Then
Me.cbosoft.RowSource = "SELECT [Software Costs].softID,
[Software Costs].Description, [Software Costs]. corporate"
FROM [Software Costs]
WHERE [Software Costs].type = "epos"

End If
End If
End If
End If
End Sub

The problem I get initially is that the system fails on the FROM
section, also need help in the correct format of the IF part of the
statement.
All suggestions appreciated.

TIA

Les
Click to expand...
Click to expand...
 
I think this is what you want, but before I offer my suggestion, I have to
chastize a bit. Do Not put spaces in table names or field names. Now,
** DisClaimer **
This is untested "air code"
**
Dim strSQL as String
Dim strFieldName as String
strSQL = "SELECT [Software Costs].softID, [Software Costs].Description, _
& [Software Costs].$$$$ FROM [Software Costs] " _
& "WHERE [Software Costs].type = 'epos';"
Select Case Me.qty
Case 1 to 4
strFieldName = "1_off"
Case 5 to 9
strFieldName = "advantage"
Case 10 to 14
strFieldName = "premier"
Case 15 to 19
strFieldName = "corporate"
End Select
strSQL= Replace(strSQL,"$$$$",strFieldName)
Me.cbosoft.RowSource = strSQL

Good Luck, post back if you have problems with this
 
To continue on a second line, you need to terminate the string:

Me.cbosoft.RowSource = "SELECT [Software Costs].softID, " & _
"[Software Costs].Description, [Software Costs]. advantage " & _
"FROM [Software Costs] WHERE [Software Costs].type = ""epos"""

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



LJG said:
Thanks for that Sandra,

When I enter the code VBA keeps putting in the quotes? I have tried
forcing a line break with a _ but it still keeps putting in the quotes!!

Les




Sandra Daigle said:
The problem with "From" is that you've got it outside of your quotes so
VBA is trying to recognize it.

This line:

Me.cbosoft.RowSource = "SELECT [Software Costs].softID, [Software
Costs].Description, [Software Costs]. advantage" FROM [Software Costs]
WHERE [Software Costs].type = "epos"

Should be

Me.cbosoft.RowSource = "SELECT [Software Costs].softID, [Software
Costs].Description, [Software Costs]. advantage FROM [Software Costs]
WHERE [Software Costs].type = ""epos"""

There are two pairs of quotes around the text value "epos".

On the if statement, you have to explicitly name the comparison field for
each comparison value. Where you have:

If Me.qty >= 5 and <=9 Then

You need:

If Me.qty >= 5 and Me.qty<=9 Then

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi Guys,

I am trying to get this code to work and wonder if someone can give
me a hand.

I have a form with a combo control and need to select different row
source if the value of a field meets selected criteria. My Code is:

Private Sub qty_AfterUpdate()
If Me.qty <= 4 Then
Me.cbosoft.RowSource = "SELECT [Software Costs].softID, [Software
Costs].Description, [Software Costs]. 1_off "
FROM [Software Costs] WHERE [Software Costs].type = "epos"

If Me.qty >= 5 and <=9 Then
Me.cbosoft.RowSource = "SELECT [Software Costs].softID,
[Software Costs].Description, [Software Costs]. advantage"
FROM [Software Costs]
WHERE [Software Costs].type = "epos"

If Me.qty >= 10 and <=14 Then
Me.cbosoft.RowSource = "SELECT [Software Costs].softID,
[Software Costs].Description, [Software Costs]. premier"
FROM [Software Costs]
WHERE [Software Costs].type = "epos"

If Me.qty >= 15 and <=19 Then
Me.cbosoft.RowSource = "SELECT [Software Costs].softID,
[Software Costs].Description, [Software Costs]. corporate"
FROM [Software Costs]
WHERE [Software Costs].type = "epos"

End If
End If
End If
End If
End Sub

The problem I get initially is that the system fails on the FROM
section, also need help in the correct format of the IF part of the
statement.
All suggestions appreciated.

TIA

Les
Click to expand...
Click to expand...
Click to expand...
 
Back
Top