View a dynaset (recordset) w/SELECT

[redFred]

I want to view a dynaset of a tbl recordset.

However in the code below I get a run time 3011 .. "make sure the object exists and that you spelled….etc" Hours of varifing spelling is correct I am stumped.

thanks in advance

'_______
Private Sub cmd_Go_Click() ‘ dialog frm with combo

' get the varable from the dialog frm's combo
projectID = Combo_projectNumber.Value

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim frmNewSepProject As String
Dim strSQL As String

'close the dialog frm
DoCmd.Close

'dim the recordset to include all projetID = [Project_ID]
strSQL = "SELECT * FROM tbl_separationKeyInputs WHERE Project_ID = " & projectID

Set db = CurrentDb
Set rs = db.OpenRecordSet(strSQL, dbOpenTable)


End Sub

 

[HiTechCoach]

What should the correct data type be for the variable projectID l?

You code is expecting a numeric data type. Is that correct?

 

[Khalid_Afridi]

It could be like this also

strSQL = "SELECT * FROM tbl_separationKeyInputs WHERE Project_ID = " & Me!Combo_projectNumber ' For Numeric Data Field

'OR 

strSQL = "SELECT * FROM tbl_separationKeyInputs WHERE Project_ID = '" & Me!Combo_projectNumber & "'" ' For Text Data Field

 

[namliam]

Set rs = db.OpenRecordSet(strSQL, dbOpenTable)

Check again, what are you opening??

 

[redFred]

What should the correct data type be for the variable projectID l?

You code is expecting a numeric data type. Is that correct?

___________reply
Yes numeric

projectID is dimensioned in a seprate module as: Public projectID As Long

Project_ID is the field in the tbl_separationKeyInputs

My goal is for the user to open/view a subset of tbl_separationKeyInputs for all records where Project_ID (keyIndexed) that match the varable in projectID (picked from a combo)

 

[lcook1974]

Does the dialog box have anything to do with the strSQL? My thought is that the docmd.close (of the dialog box) may be the reason, if you are closing it before a value can be transferred.

Just a thought...

 

[redFred]

Does the dialog box have anything to do with the strSQL? My thought is that the docmd.close (of the dialog box) may be the reason, if you are closing it before a value can be transferred.

Just a thought...

the varable projectID is being populated and retains the vaule becasue the dimensioning of the varable is: Public projectID As Long in a seprate module

 

[namliam]

Set rs = db.OpenRecordSet(strSQL, dbOpenTable)

Check again, what are you opening??

Erm?

Have you even seen this?