Help with VB and Access

Antelopethy · 22 Oct 2009 at 19:32

Hello,
I'm currently doing an extended project in computing, which is related to acoustics etc, and this consists of a program and a database linked together. Ideally, what I would like to happen is that there are two list boxes populated from the database. The user selects something from a combobox which then updates a listbox accordingly - then the user selects from that list box, which updates the second one. Then, when the user selects something from the second list box, I want a text box to be updated automatically with a field from that piece of data.
For example, the user selects "Ceiling" in the combo box, the first list box updates with a list of ceiling materials. The user then selects "Acoustic Tile", which updates the second list box with special properties. The program works up until here. Now, say the user selected "Suspended" from the second list box - I would then want the program to query the database for the field "Mat_Absorb_1000" of Suspended Acoustic Tile, where Acoustic Tile comes under "Material_Name", and Suspended comes under "Material_Special".
At the moment, when the user selects suspended, I get the error "No current record", and when I inserted a message box to tell me the number of fields in the recordset, it returned 0 - Now my SQL query works in Access, so I'm wondering what could be the problem? Here's the relevant code:

Code:

Option Explicit
Dim materials As Database
Dim rs As Recordset
Dim rs_lstsurfacemat As Recordset
Dim rs_lstsurfaceadj As Recordset
Dim rs_txtsurfacecoef As Recordset

Code:

Private Sub lst_surfaceadj_Click()
Set rs_txtsurfacecoef = materials.OpenRecordset("select Materials.Mat_Absorb_1000 from Materials where ((Materials.Material_Name = ' " & lst_surfacemat.Text & " ') AND (Material_Special = ' " & lst_surfaceadj.Text & " '))")
MsgBox rs_txtsurfacecoef.RecordCount
lbl_surfacecoef.Caption = rs_txtsurfacecoef![Mat_Absorb_1000]
End Sub

Sorry for the lengthy post, but if anyone could offer an insight it'd be much appreciated - my computing teacher was stumped :confused:

suarve · 22 Oct 2009 at 19:36

Not sure how you'd go about filling the different elements, but I saved the following link from ages ago - if you follow the tutorial it I'm sure you could adapt it to your needs:

http://www.blueclaw-db.com/comboboxlist/access_dependent_dropdown_list.htm

Antelopethy · 22 Oct 2009 at 22:34

Thanks for the link, I've got that saved now, but I'm not sure it helps solve my problem - the thing is that even though I seem to have a perfectly valid SQL query which should by all means find one record, it never finds anything. Any other suggestions?

~J~ · 22 Oct 2009 at 23:36

Just something that looks obvious in your code is that you're not actually executing the SQL Statement.

Sure you've set it, and yes you're wanting to see the results, but don't see anywhere where you've actually executed it.

Hambut_Bulge · 23 Oct 2009 at 00:06

Why have you got space characters between the single and double-quote in your query? I'm referring to the portion

(where ((Materials.Material_Name = ' " & lst_surfacemat.Text & " '

for example. That will add space characters into the query variable. As a result if you were to watch the variables in a debug session, the query would look like this (I've added the line breaks to make it clearer)

select Materials.Mat_Absorb_1000
from Materials
where ((Materials.Material_Name = ' Acoustic Tile ')
AND (Material_Special = ' Suspended '))

I suspect if you execute the above query, you'll get no rows back. And that is (I think) what is happening in your program. I am assuming that you haven't bookended each data item with a single space char, because I can't imagine why you'd want to do that!

So, change the "Set rs_txtsurfacecoef =" to

Set rs_txtsurfacecoef = materials.OpenRecordset("select Materials.Mat_Absorb_1000 from Materials where ((Materials.Material_Name = '" & lst_surfacemat.Text & "') AND (Material_Special = '" & lst_surfaceadj.Text & "'))")

That (I think) should fix your problem.

Antelopethy · 24 Oct 2009 at 20:25

Thanks very much for the help, the extra spaces were indeed the problem, and it now works just as intended! As you can tell I'm very much a beginner when it comes to SQL, and am yet to fully understand the syntax, but hopefully that's a mistake I won't make again