HELP!!! Mulit selection box

Hi I am new to this forum and new to ACCESS. I am now working on the mulit selection box code that i copy from this site but when i try to debug/ complie it, it shows an error mgs: Error in loading DLL (error 48) when it runs to the red line below.. why it happens and how to sort it out?! can anyone help me ..PLEASE... i ve been working on it for a long time. actually wat i did is:

Private Sub lstSelectComparison_Click()
On Error GoTo Err_Handler
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("selectedhospital")
' Loop through the selected items in the list box and build a text string
If Me!lstPROCodes.ItemsSelected.Count > 0 Then
For Each varItem In Me!lstPROCodes.ItemsSelected
strCriteria = strCriteria & "tblProvider_lookup.PROCode = " & Chr(34) _
& Me!lstPROCodes.ItemData(varItem) & Chr(34) & "OR "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
MsgBox "Must Select An Item From The List First"
Exit Sub
End If
' Build the new SQL statement incorporating the string
strSQL = "INSERT INTO tblProvider_lookup ( PROCode ) " & _
"SELECT tblProvider_lookup.PROCode, * FROM tblProvider_lookup" & _
"WHERE " & strCriteria & ";"
' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.SetWarnings False
DoCmd.OpenQuery "selectedhospital"
DoCmd.SetWarnings True
' Empty the memory
Set db = Nothing
Set qdf = Nothing

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description
Resume Exit_Handler

End Sub


And also, my form now consist of one list box and one multiselect box. and I just wanna retrieve the record i selected in the boxes, and goes to another form (the list box and multi select box are not related...they are retrieving the same information, but for different purpose in the future). I just wonder will the execution code be the same as you mention in those previous post? or... actually I can't do so?

thanks very much
Betsy

Betsy,

Need to correct the first problem before proceeding with any more of your application development. I am not sure why the dll isn't loading at this point, but I will research further if you will provide what version Access you are using and current operating system. Also is this problem only occurring on this machine or other machines as well.

lwells

Hi Lwells,

I just checked it with other machines.. it works on others! so... i mite reinstall the access or use another machine. Thanks very much for reminding me this..

So, what about the 2nd bit of my post? will the execution code be the same if my frm consists of 1 combo box and 1 list box?

Thanks very much,
Betsy

Then most likely the DAO360.DLL is corrupt. You can probably copy the .DLL from a good machine to this one, but I would recommend to reinstall Access. I would copy the database over to a good machine first and then reinstall Access and then copy the database back in.

For your second question. I am not quite clear in what you are wanting to do here. Are you wanting to open another form that will display the record selected or just move to that record on the same form?

lwells

Hi lwells,

I am not pretty sure if I am in a wrong track or not. But what I am trying to do is:

one of my table looks like this
Hospital Code|Dieases Code|LoS_SS| LoS_MS| LoS_LS
1|A01|2|5|10
1|A02|2|7|10
1|A03|2|8|16
2|A01|2|9|20
2|A02|2|7|15
3|A03|2|9|25
3|A01|2|10|50

and so on

In the first form, I made a combo box in order to let user choose which hosptial code he belongs to. lets Say I am from Hospital Code 1, I wanna take information abt hospital 1 out and wont do anyfing further.

Then I've made a multi select box in order to let the user to choose what hospitals they wanna compare wif. Lets say if I wanna compare my hospital performance with hospital 2 and 3. I needa take records belong to these two hospital.

So, my purpose of my first form is to retrieve all the information I want from these two boxes. Then, after clicking OK, it will automatically go to another form in order to make further analysis.... and compare it with hospital 1.

u get what I mean?! I know its a bit confusing... but.. yea... Thanks sooo much

Well, the table didn't seem to be normalized and didn't see what the primary key was, but understanding what you wanted to do, I made just a sample database for you to take a look at. I took the above sample data and created two normalized tables with primary keys with the one to many relationship to index on. It should give you an idea to start on what I think you wanted to do.

If not let me know.

lwells

Thanks very much lwells, the sample DB really helps me alot... but wat I want after clicking the OK button (which is the open comparison form) is that I wanna make another form, which only contains 2 or 3 other buttons, each button calls different queries so that user can look at the records in different ways...e.g. using the example again:
Hospital Code|Dieases Code|LoS_SS| LoS_MS| LoS_LS
1|A01|2|5|10
2|A01|2|9|20
3|A01|2|10|50
1|A02|2|7|10
2|A02|2|7|15
3|A02|2|6|9
1|A03|2|8|16
2|A03|2|8|14
3|A03|2|9|25

One of the button is that: I wanna group them by disease code, and pick the (lets say) the smallest LoS_SS, LoS_MS, LoS_LS for each group... which means that I wanna make some queries to get the result ... like A01 2|5|10, for A02 2|6|9 ... and make a summary... belalla....

And other buttons do different things..... so, I really dun want to show the result summary once I press the first form button.......

And also, I can work on the code now... but...the table/ query always comes up whenever i choose the item... at the moment I juz wanna use the query retrieve the records I want... keep them... and when i need further analysis i can always get records from the table/ query..... u
I dunno if i am confusing you... but... should I say the main purpose of the first form is " telling Access, by using query, that I want these information so that I can further analysis later on"??
... I am always stuck with this point and I am sooo confusing right now....cuz i never experience such kind of case b4....


THANKS very much your help....
Betsy

Well, yeah it is a bit confusing of what you are asking. The subforms in the sample database I sent you were in datasheet view so that you can use the built in Access tools for filtering or sorting on any of the columns in either subform...which sounds like what you wanted to do by using buttons on a form. Why not just use the tools Access has already made for this purpose?

If you still need help, I will need to see your application to build any custom forms based on your criteria or requests. It's too difficult to visualize what your wanting by reading your post to be able to give you specific examples other than the very generic sample databases that you can find easily anywhere.

Hi lwells,

Thanks very much for you help....now I kinda sort out wat i m gonna do... i just use the original code i used to use at the moment !BUT... when i apply the code for the box, it shows the error mgs "syntax error:missing operator ...FROM clause" cant really remember exactly the mgs... but should be sthg abt my syntax mgs..... so i tried to cross the underlined bit out..and try:

' Build the new SQL statement incorporating the string
strSQL = "INSERT INTO tblProvider_lookup ( PROCode ) " & _
"SELECT tblProvider_lookup.PROCode, * FROM tblProvider_lookup" & _
"WHERE " & strCriteria & ";"

All the items in the multi select box will be transferred to the table dat i create for storing the selecteditems....I know its mite quite non-sense to cross that bit out but i fink dats problem wif dat bit...and i really dunno what that means ... can you explain?

And What do you think..?!... ARGH... i m really fed up wif that...... I cant believe it worked yesterday but it doesnt now... Sorry i mite ask non sense questions..but... i m really new to this... and... i ve no programming background at all..... sigh...

So yea..PLEASE HELP... thanks very much =)

Betsy

Couple of things,

Remove the "*" from your SELECT statement
and
in your WHERE clause add the ' to your syntax because you are passing a string

example

' Build the new SQL statement incorporating the string
strSQL = "INSERT INTO tblProvider_lookup ( PROCode ) " & _
"SELECT tblProvider_lookup.PROCode FROM tblProvider_lookup" & _
"WHERE '" & strCriteria & "';"

Try that and see what errors you get now.

lwells

Thanks lwells it works now. One more question: I wonder how should I change the code if I wanna add more columns from others tables (let's say the column "AAA", "BBB","CCC" from the table "EFG" on the my tblselected_provider? btw, my correct strSQL is this one:

strSQL = "INSERT INTO tblSelected_Provider (PROCode) " & _
"SELECT tblProvider_lookup.PROCode FROM tblProvider_lookup " & _
"WHERE " & strCriteria & ";"

Thanks very much again.
betsy

You would just change which fields to INSERT and which table and fields to SELECT from.

strSQL = "INSERT INTO tblSelected_Provider (AAA,BBB,CCC) " & _
"SELECT EFG.AAA,EFG.BBB,EFG.CCC FROM EFG " & _
"WHERE " & strCriteria & ";"

lwells

I got it thanks so much lwells =)

Hi,
The MultiSelection box again! I juz wonder how can I reset the items that user choose in the Multi-selection box?! I would like to have a re-set button to clear the selected items in my form. AND also, at the moment, I have to go to the the table which storing the selected item and delete the chosen record manually... is there any way that the table is clear whenever starting up?

THANKS again.... I really need help
Betsy

Betsy,

To clear the items selected in your list box, in the On Click event of your clear button use the following code

Dim varItem As Variant
For Each varItem In Me!ListBoxName.ItemsSelected
Me.ListBoxName.Selected(varItem) = False
Next varItem

Change the syntax to the name of your list box

To delete the contents of a table when you open your form, in the Open Event of your form use the following code

Dim strSQL As String
DoCmd.SetWarnings False 'Turn the standard warning message off
strSQL = "DELETE * FROM TableName"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True 'Turn the standard warning message back on

Change the syntax to the name of your table

lwells