SQL in VBA not working as planned

[ghobbit]

Hi

I have a form with some unbound boxes on it. The user types in one box and the other two they select an option from a drop down box. They then click a button and the 'on click' event runs an SQL statement and returns the result to another text box.

I had this working the other day but then my PC crashed and I lost the database but now I cant remember how I did it.

So I three boxes - text1, combo1 and combo2. The result is returned to text2.

I'm using Office XP which I believe is ADO but I also have Access 97 installed as my employers are a bit behind the times and havent crossed over fully yet.

The 'on click' event is this

Private Sub Command8_Click()
Dim strSQL As String


strSQL = "SELECT tblRecall.Barcode FROM tblRecall WHERE tblRecall.Reqno='" & text1 & "' AND tblRecall.Type='" & Combo1 & "' AND tblRecall.Period= '" & Combo2 & "' "
Text2 = strSQL

End Sub

When I run this all that happens is that the text 'SELECT tblRecall.Barcode FROM tblRecall WHERE tblRecall.Reqno='" & text1 & "' AND tblRecall.Type='" & Combo1 & "' AND tblRecall.Period= '" & Combo2 & "' "
is placed into the text2 box. The text1,combo1 and combo2 are replaced with the options I have selected. It doesnt look like its even tried to run the SQL or if it has the result hasnt been put into the strSQL variable.

I thought before the crash I had
text2.rowsource = strSQL but when I try that I get an error that the method or data member is not found and the .rowsource is highlighted so I'm probably wrong on that point and perhaps I didnt have the .rowsource there.
I know I had this working as it gave me the right result before the crash.

The form property 'Record Source' is set to tblRecall

any help would be most appreciated

steve

 

[ghobbit]

Edit

This is the code that worked and I indeed used the .rowsource

Private Sub Command14_Click()
Dim strSQL As String

strSQL = "SELECT DISTINCT Barcode FROM tblRecall WHERE Reqno='" & Reqsearch & "' AND Type='" & Typesearch & "' AND Period=" & Yearsearch
text2.RowSource = strSQL

End Sub

so now the questions is why does .rowsource return an error?

When I start typing .rowsource and you get that automatic box appear with all the options you can select, rowsource or recordsource for that matter isnt in it whereas before I'm sure it was.

Heeeeellllppp

regards

Steve

 

[fredericofonseca]

wrong forum.

This is the ANSI SQL, ,and your question in not even to do with SQL issues, but with VBA issues.

Please post on either the VBA forum or on the ACCESS forums

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com