I keep getting an "object required" message on the following code:

[Triacona]

The relevant code doesn't get highlighted
I am trying to take criteria from the list box I have created and put it into a query, I have created a query with the following code:
SELECT DISTINCT UNI7LIVE_SCROLE.rolename
FROM UNI7LIVE_SCROLE;
UNION SELECT "All"
FROM UNI7LIVE_SCROLE;
called Select from table row source

The table I am getting the data from is UNI7LIVE_SCROLE


Private Sub Run_Query_Click()
On Error GoTo Err_Run_Query_Click

Dim stdocname As String

On Error GoTo Err_Run_Query_Click
Dim mydb As DAO.Database
'Dim mydb As AccessObject
Dim qdef As DAO.QueryDef
'Dim qdef As AccessObject
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant

Set mydb = CurrentDb()

strSQL = "SELECT * FROM UNI7LIVE_SCROLE.rolename"

'Build the IN string by looping through the listbox
For i = 0 To UNI7LIVE_SCROLE.ROLENAME.ListCount - 1
If UNI7LIVE_SCROLE.ROLENAME.selected(i) Then
If UNI7LIVE_SCROLE.ROLENAME.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & UNI7LIVE_SCROLE.ROLENAME.Column(0, i) & "',"
End If
Next i

'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [UNI7LIVE_SCROLE.rolename] in " & _
"(" & Left(strIN, Len(strIN) - 1) & ")"

'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If

mydb.QueryDefs.Delete "Select from table row source"
Set qdef = mydb.CreateQueryDef("Select from table row source", strSQL)

'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "Select from table row source", acViewNormal

'Clear listbox selection after running query
For Each varItem In Me.Rolename_listbox.ItemsSelected
Me.Rolename_listbox.selected(varItem) = False
Next varItem

Exit_Run_Query_Click:
Exit Sub

Err_Run_Query_Click:

If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list" _
, , "Selection Required !"
Resume Exit_Run_Query_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_Run_Query_Click
End If

Exit Sub

'Clear listbox selection after running query
For Each varItem In Me.Rolename_listbox.ItemsSelected
Me.Rolename_listbox.selected(varItem) = False
Next varItem

End Sub

It would be great if anyone could help me
Many thanks

 

[HarleyQuinn]

If you take out every occurance of On Error GoTo Err_Run_Query_Click then you should have the error highlighted in the code.

Also, the following code will NEVER be executed

 'Clear listbox selection after running query
    For Each varItem In Me.Rolename_listbox.ItemsSelected
        Me.Rolename_listbox.selected(varItem) = False
    Next varItem

Should it be running?


HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[Triacona]

Thank you very much for your help it is greatly appreciated
I have taken out both instances of the code:
'Clear listbox selection after running query
For Each varItem In Me.Rolename_listbox.ItemsSelected
Me.Rolename_listbox.selected(varItem) = False
Next varItem
I have also taken out On Error GoTo Err_Run_Query_Click
I now get a message that states Run-time error '424' object required and the following code gets highlighted when I click on debug:

For i = 0 To UNI7LIVE_SCROLE.ROLENAME.ListCount - 1

Is it not counting through the list of roles?

Any help would again be very much appreciated.

Many thanks

 

[ZmrAbdulla]

I am seeing an extra ";" in the sql

SELECT DISTINCT UNI7LIVE_SCROLE.rolename
FROM UNI7LIVE_SCROLE[COLOR=red];[/color]
UNION SELECT "All"
FROM UNI7LIVE_SCROLE;

Zameer Abdulla

 

[PHV]

When you build the IN string, replace all occurances of this:
UNI7LIVE_SCROLE.ROLENAME
with this:
Me!Rolename_listbox

Tip: use the Option Explicit instruction.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

For this portion of code (and a few others):

    strSQL = "SELECT * FROM [HIGHLIGHT]UNI7LIVE_SCROLE.rolename[/HIGHLIGHT]"

Is the [HIGHLIGHT]highlighted[/HIGHLIGHT] portion a table name, or are you trying to reference something else?

If it's a table name, I'd suggest (if possible at this point) to remove the period - can make for some confusion, I would think, to the programmer, as well as to the VB Editor, and/or the Jet SQL engine.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Triacona]

Thank you guys very much for your help
I have the following code now and I have removed the SQL ";"
:

Private Sub Run_Query_Click()
'On Error GoTo Err_Run_Query_Click

Dim stdocname As String

'On Error GoTo Err_Run_Query_Click
Dim mydb As DAO.Database
'Dim mydb As AccessObject
Dim qdef As DAO.QueryDef
'Dim qdef As AccessObject
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant

Set mydb = CurrentDb()

strSQL = "SELECT * FROM UNI7LIVE_SCROLE.rolename"

'Build the IN string by looping through the listbox
For i = 0 To Me!Rolename_listbox.ListCount - 1
If Me!Rolename_listbox.selected(i) Then
If Me!Rolename_listbox.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & Me!Rolename_listbox.Column(0, i) & "',"
End If
Next i

'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [UNI7LIVE_SCROLE.rolename] in " & _
"(" & Left(strIN, Len(strIN) - 1) & ")"

'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If

mydb.QueryDefs.Delete "Select from table row source"
Set qdef = mydb.CreateQueryDef("Select from table row source", strSQL)

'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "Select from table row source", acViewNormal

'Clear listbox selection after running query
' For Each varItem In Me.Rolename_listbox.ItemsSelected
' Me.Rolename_listbox.selected(varItem) = False
'Next varItem

Exit_Run_Query_Click:
Exit Sub

Err_Run_Query_Click:

If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list" _
, , "Selection Required !"
Resume Exit_Run_Query_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_Run_Query_Click
End If

Exit Sub

'Clear listbox selection after running query
'For Each varItem In Me.Rolename_listbox.ItemsSelected
' Me.Rolename_listbox.selected(varItem) = False
'Next varItem

End Sub
I am now getting another error after the changes made:
Run time error 2465

Security Report can’t find the field ‘Rolename_listbox’ referred to in your expression

It then highlights the text
For i = 0 To Me!Rolename_listbox.ListCount - 1
as you have recommended I changed the
UNI7LIVE_SCROLE.ROLENAME to Me!Rolename_listbox in the For statement as well

 

[Triacona]

oops sorry I clicked on submit post by accident.

Your help would be greatly appreciated.
and thank you for all your help so far

 

[kjv1611]

Run time error 2465

Security Report can't find the field 'Rolename_listbox' referred to in your expression

The name used in your code doesn't match the listbox name on your form OR this code is in a module separate from your form module. Is it the form module where the code is located, or is the code in an additional non-form module?

If it's in the form module, then you've got a different name in your code from the listbox name.

If it's in another module (not the form module) OR if it is referring to a different form than the module in which it's placed, then you need a different apporach for referencing the control (listbox). Something like this (if this is the case):

Forms!MyFormName!MyListBoxName.ListCount

Of course, substitute the correct form/control names.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Triacona]

Thanks for all you help, I have found another way

 

[kjv1611]

Can you share a little? What way? Perhaps someone would benefit from whatever found.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Triacona]

I created a form from scratch but clicked on new instead, then I chose a query from where the data would come from, Access then creates a link,
Then I inserted a combo box (you can use a list box, but in this case I prefer a listbox because there are many records and you can start typing and it will display findings accordingly)
the wizard then asks you a few options choose Find a record on my form based on the values I selected in my combo box
Then insert the fields you want displayed.
Then insert a subform this will ask you which data to link itr with,
Then choose the relevant query.
Then it will ask you to define link criteria specify which fields you want linked.
Click finish and now run the form and you the combo box should link with the subform

The code is as follows:

Private Sub List0_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CODEVALUE] = '" & Me![List0] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

Thanks again

 

[kjv1611]

Thanks for the update. Glad you got it worked out.

--

"If to err is human, then I must be some kind of human!" -Me