sql issues in record set

[shutzy]

i seem to be having real issues with creating record sets.

it started yesterday with an sql that had a date range. i thought that it was something to do with the sql.

but then today i am trying something more simple.

SELECT tblReportListOptions.ActualReportName AS ChosenReport, tblReportListOptions.ID FROM tblReportListOptions WHERE (((tblReportListOptions.ID)=[Forms]![rptReportsMainScreen]![txtChosenReport]));

i am getting an error that is very annoying as the error will not let me close access down normally so i have to end task in task manager

errors:
3061. too few parameters. expected 1

91 object variable or with block variable set

i havent a clue what these mean but the record set is pasted below

Dim rs As DAO.Recordset
On Error GoTo Err_Proc
Set rs = CurrentDb.OpenRecordset("SELECT tblReportListOptions.ActualReportName AS ChosenReport, tblReportListOptions.ID FROM tblReportListOptions WHERE (((tblReportListOptions.ID)=[Forms]![rptReportsMainScreen]![txtChosenReport]));")
        If rs.RecordCount <> 0 Then
        DoCmd.OpenReport rs.Fields("ChosenReport")
    End If
    
    
Exit_Proc:
    rs.Close
    Set rs = Nothing
    Exit Sub
Err_Proc:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_Proc

any help please

 

[pr2-eugin]

shutzy, again you are missing the main part, Concatenation.. When using variables you need to concatenate them.. If you dont, the ID you are searching for is literally [Forms]![rptReportsMainScreen]![txtChosenReport] not 123 or 45 or any value that you enter..

Dim rs As DAO.Recordset, strSQL As String

On Error GoTo Err_Proc
strSQL = "SELECT tblReportListOptions.ActualReportName AS ChosenReport, tblReportListOptions.ID FROM tblReportListOptions " & _
         "WHERE (((tblReportListOptions.ID)= [COLOR=Red][B]" &[/B][/COLOR] [Forms]![rptReportsMainScreen]![txtChosenReport] [COLOR=Red][B]& "[/B][/COLOR]));"
Set rs = CurrentDb.OpenRecordset(strSQL)

If rs.RecordCount <> 0 Then
    DoCmd.OpenReport rs.Fields("ChosenReport")
End If    
    
Exit_Proc:
    rs.Close
    Set rs = Nothing
    Exit Sub
Err_Proc:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_Proc

 

[shutzy]

is this another way of telling the record set what the sql is. before you had shown me a record set where it looked like the one i did and and this has

strSQL = and then it refers to strSQL = to get the sql.

i need to get what is a variable nailed down. is a variable something that is in a form for instance. and would a constant be from a tbl.

also what i do not get is this is sql right? so when i build my query in Query Design View and then copy and paste the sql it should be correct.

could you tell me what Concatenation needs to be on each occation. i am going to put it in a word doc or something to refer to .

thanks for your help. i can tell that Concatenationwith me is getting you MAD!

 

[pr2-eugin]

Okay in VBA any variables you use like Me.TextBoxcontrolName or Forms!FormName!ControlName should be concatenated, when using with SQL statements.. If not concatenated the values will be taken as such.. Concatenations should also be used along with any Domain functions..

The reason I assigned it to a String (which I normally tend to do) is because it is easier to identify any errors while Debugging.. That is a very good practice to create a variable.. The reason why we are not using we are not using any user/form input.. The values are constructed and is ready to be used..

In Query, they are a bit more sensible, they are pre compiled.. to address this issue.. They understand that "Forms", refer to object.. so they look for that value, without the need for concatenation..

thanks for your help. i can tell that Concatenation with me is getting you MAD!

I am Sorry.. Did not mean to come up like that..