Find recordset based on form selection

[ChrisLayfield]

I have been trying to have a form that would push data from Access to a Word document with bookmarks. (I would make a report, but the forms are controlled by the government.)

On the form the user selects the ProjectID (displayed as the project name on form) and I need to export the project name to the word document. I thought the SQL coded here would work, but it apparently finds nothing and ends the routing at that code. Can anyone indicate why this is failing? When I put a code break on the set rstProjName line, everything is =Nothing.

Function OpenIncidentReport()
Dim wrdApp As Object
Dim dbs As Database
Dim rstProjName As Recordset
Set dbs = CurrentDb
Set rstProjName = dbs.OpenRecordset("SELECT ProjectName " & _
                                    "FROM tbl_NCS_Projects " & _
                                    "WHERE frmIncidentReport.ProjectID = '" & tbl_NCS_Projects.ProjectID & "';")
MsgBox rstProjName

 

[pbaldy]

You've reversed the table and form references. Try

"WHERE ProjectID = '" & Forms!frmIncidentReport.ProjectID & "';")

 

[pbaldy]

Oh, also:

MsgBox rstProjName!ProjectName

 

[boblarson]

Two things -

1. Is ProjectID text or numeric?

2. You can't use
MsgBox rstProjName
to display anything because it can't display a recordset object. You can use something like this:

MsgBox rstProjName!ProjectName

 

[boblarson]

You've reversed the table and form references. Try

"WHERE ProjectID = '" & Forms!frmIncidentReport.ProjectID & "';")

I missed that part too

 

[ChrisLayfield]

Thanks for the quick response. ProjectID on the form is set to General Number and on the tbl_NCS_Projects it is the primary key and set to Autonumber.

I made the switch in the query and added a msgbox to make sure the selected project ID was pulled and that is correct, but the query is still crapping out before getting the record from the table. The codebreak on the MsgBox rstProjName!ProjectName never hits.

 

[boblarson]

I made the switch in the query and added a msgbox to make sure the selected project ID was pulled and that is correct, but the query is still crapping out before getting the record from the table. The codebreak on the MsgBox rstProjName!ProjectName never hits.

Since Paul and I gave you some conflicting info, can you post what you just tried?

 

[ChrisLayfield]

Function OpenIncidentReport()
    Dim wrdApp As Object
    Dim dbs As Database
    Dim rstProjName As Recordset

    Set dbs = CurrentDb

    MsgBox Forms!frmIncidentReport.ProjectID

    Set rstProjName = dbs.OpenRecordset("SELECT ProjectName " & _
                                        "FROM tbl_NCS_Projects " & _
                                        "WHERE tbl_NCS_Pojects.ProjectID = '" & Forms!frmIncidentReport.ProjectID & "';")
    MsgBox rstProjName!ProjectName

 

[boblarson]

You need to change this line:
WHERE tbl_NCS_Pojects.ProjectID = '" & Forms!frmIncidentReport.ProjectID & "';")

to this (withOUT the single quotes - just like we said before)

WHERE tbl_NCS_Pojects.ProjectID = " & Forms!frmIncidentReport.ProjectID")

Oh, and you don't need the semi colon at the end.

 

[ChrisLayfield]

I didn't see where you mentioned to take off the single quotes, but I got rid of them and the couble quotes at the end and it works fine now.

Set rstProjName = dbs.OpenRecordset("SELECT ProjectName " & _
                                    "FROM tbl_NCS_Projects " & _
                                    "WHERE tbl_NCS_Projects.ProjectID = " & Forms!frmIncidentReport.ProjectID)

 

[boblarson]

I didn't see where you mentioned to take off the single quotes,

You are correct. I had asked what data type but didn't mention that if it was numeric to not use quotes.

Glad you got it working.

 

[ChrisLayfield]

Thanks for the follow-up, that little tidnit will be handy when I am doing the rest of the data push from the form to the Word doc. You guys are a great asset.