After Update not working, Why?

[Sean]

Can you tell me why this isn't working? I get a Object doesn't support this
property or method.

Private Sub Combo37_AfterUpdate()

Dim strSQL As String

If IsNull(Me!ProductType) Then
Me!ListBx1.RowSource = ""

ElseIf Me!ProductType = "card" Then
strSQL = "SELECT qualitycheck FROM tblcard"

ElseIf Me!ProductType = "Roll" Then
strSQL = "SELECT qualitycheck FROM tblroll"

ElseIf Me!ProductType = "Sheet" Then
strSQL = "SELECT qualitycheck FROM tblSheet"

Me!ListBx1.RowSource = strSQL
End If

End Sub

 

[Allen Browne]

Which line generates the error?

Does the code compile (Compile on the Debug menu, in the code window)?

What version of Access and Windows?

 

[Klatuu]

I don't know why you are getting the error you are, but there is a logic
error in your code. As written, the row source will be set only if
Me!ProductType is null or = "sheet" because of where you set the row source
to strSQL. I would suggest rewritting to this:

Private Sub Combo37_AfterUpdate()
Dim strSQL As String
Dim strProdType As String

strProdType = Nz(Mee!ProductType, vbNullString)

Select Case strProdType
Case "card"
strSQL = "SELECT qualitycheck FROM tblcard"
Case "Roll"
strSQL = "SELECT qualitycheck FROM tblroll"
Case "Sheet"
strSQL = "SELECT qualitycheck FROM tblSheet"
Case "Else"
strSQL = vbNullString
End Case

Me!ListBx1.RowSource = strSQL

End Sub

 

[Sean]

I get the same error. Any thoughts?

Private Sub Combo37_AfterUpdate()

Dim strSQL As String
Dim strProdType As String

This line is highlighted on error ----> strProdType = Nz(Me!ProductType,
vbNullString)

Select Case strProdType
Case "card"
strSQL = "SELECT qualitycheck FROM tblcard"
Case "Roll"
strSQL = "SELECT qualitycheck FROM tblroll"
Case "Sheet"
strSQL = "SELECT qualitycheck FROM tblSheet"
Case "Else"
strSQL = vbNullString
End Select


Me!ListBx1.RowSource = strSQL

End Sub

 

[Klatuu]

Sorry for the dumb question, but is it a list box control?
On which line of code do you get the error?

 

[Sean]

Yes,
this is the line that errors out
strProdType = Nz(Me!ProductType, vbNullString)

 

[Douglas J. Steele]

I doubt this is relevant, but it should be

Case Else

not

Case "Else"

 

[Klatuu]

That doesn't make any sense. I don't see why you would be getting that
error. Have you done a compact and repair? Are you getting any other
unusual errors?
I'm wondering if you have a corruption issue going on.

 

[Sean]

I got it, I had my combo box wrong....duh! Can you help me further?

I found the property control that will allow me to select several rows
within my list box. I would like those selected records to print out on a
report. How do I distinguish between those that are selected and those that
are not and have them print out on a report along with other records that
appear on my form? Thanks again for your assistance.

 

[Klatuu]

Have a look at VBA Help for the ItemsSelected property. It shows how to loop
through the selected items in a multi select list box.

 

[Klatuu]

Sean, I just read Douglas J. Steele's post, Take the quotes off the word
"else"

Douglas is out there to make sure my syntax is correct. He does a wonderful
job of it.

(Thanks, again, Doug. Would you be interested in editing the book I am
writing?)

 

[Douglas J. Steele]

(Thanks, again, Doug. Would you be interested in editing the book I am
writing?)

I've been the technical editor for a couple of books already. If you're
serious about this, ping me off line.