sql query arrays

[drewdrewdrew]

I have tried adding the Microsoft DAO 3.6 Object Library and I get "Error Loading DLL" and it doesn't stay ticked.

 

[vbaInet]

I have tried adding the Microsoft DAO 3.6 Object Library and I get "Error Loading DLL" and it doesn't stay ticked.

Well that's curious. How did it get through the rst declaration without complaint?

Create a new Access db and see if you can set it there.

What version of Access do you have?

 

[drewdrewdrew]

Running access 2010..

getting the complaint now

strArray = Split(strArrayValue, ",")

Runtime error 13 Type mismatch

I have changed all references to strArray to Integer as well

 

[drewdrewdrew]

same error with a blank database

 

[vbaInet]

Ah yes that's right, Split() returns a String. Before you save the value to the listbox, cast it from string to an Integer type using CLng() or CInt().

If you Debug > Compile the code does it compile?

 

[drewdrewdrew]

SORRY ITS LATE HERE...bit tired

the database compiles fine.

With Me.question_list
.AddItem Item:=CInt(rs4!question_id) & ";" & rs4!config_ID
End With

is where I am at after changing the integer back to string for the array.

with the above still get the error 3464 with the OnChange event

 

[vbaInet]

Me.question_list is not returning a different datatype. Test for two things using a message box:

Msgbox "Listbox value is: " & Me.question_list & vbNewline & "Type Name: " & TypeName(Me.question_list)

Show me the result of the above.

And also, I mentioned you should use the After Update event, not the On Change event.

 

[vbaInet]

A listbox shouldn't even have an On Change event. Unless you're using a textbox.

 

[drewdrewdrew]

only if I comment out the line in the AfterUpdate event:

' .FindFirst "[question_id] = " & Me.question_list

The results are:
Listbox value is : 371
Type Name: Combobox

 

[vbaInet]

Oh sorry, repeat that test and append .Value to the end of Me.question_list

 

[drewdrewdrew]

Listbox value is : 371
Type Name: String

I have back tracked to the previous form where the array originates and from there to the strArray() in the OP code ...... it is set to Integer throughout.

im stumped to why its string. I am holding the array temporarily in a textbox to pass the value... can this effect it?

 

[vbaInet]

Ok, we've got two problems to deal with, one very minor and the other major. Your major one is the DLL problem. I'll provide you with a link. In the meantime, change DAO.Recordset to Object and make sure you cast the string to Long (using CLng()) in the FindFirst part. I want to know if it runs with it being set as an Object.

Link for the DLL problem:
http://support.microsoft.com/kb/2019235

 

[drewdrewdrew]

I am working on the DAO reference at the moment.

It seems I have a conflicting with a module name, object name etc when I add it.... googling seems to take some time for this... nothing directly answering it.

this database has no modules or objects that would even come close to a naming conflict.

 

[vbaInet]

Sorry you lost me there. What's going on? What is telling you that you have conflicting module names?

 

[drewdrewdrew]

I am trying to still add DAO 3.6 object library to the database

 

[vbaInet]

Did you follow the link?

 

[drewdrewdrew]

 Private Sub question_list_AfterUpdate()
 Dim rst As Object
 ' Get a copy of the form's record source
Set rst = Me.RecordsetClone
    
With rst
    ' Find the record
    .FindFirst "[question_id] = " & CLng(Me.question_list)
    
    ' If there's a match set the bookmark of the form to that of the one we've just searched for
    If .NoMatch = False Then
        Me.Bookmark = .Bookmark
    End If
End With
MsgBox "Listbox value is: " & Me.question_list.Value & vbNewLine & "Type Name: " & TypeName(Me.question_list.Value)
 ' Clean up
Set rst = Nothing
 End Sub

still get 3464 error

 

[drewdrewdrew]

Did you follow the link?

yes I did... and did the repair... after the repair I proceeded to add the DAO 3.6 library.... and instead of dll error I get the "Name conflicts with existing module, project, or object library" error which seems to be common.... but no direct fix

I am running Win7 64bit with Office 2010.... all patched and updated..

the Database file is a accdb extension..... not sure what else to do

 

[vbaInet]

Upload your db with some test data.

 

[vbaInet]

Follow these steps (in this order):

1. Take down a list of all your references
2. Untick all of them and hit OK
3. Perform a Compact & Repair
4. Re-run the Fix from the site
5. Tick all the relevant references
6. Perform a Debug > Compile.