Returning a single value to a variable from a recordset

  • Thread starter Thread starter G Mann
  • Start date Start date
G

G Mann

I've got a list of projects in a table that I want to query. Using a form I
get the ProjID (variable "strProject') from the user. With the ProjID I
then want to determine the Server and UserName and assign their values to
variables to connect to an Oracle database.

The Access table is ProjectsList that contains the following data:

Plant Server UserName ProjID
Unit 1 RP1 REB01 0029
Unit 2 RP4 REB02 0030
Unit 3 RP2 REB03 0017
Unit 4 RP3 REB04 0018

The code I've tried is this:


Dim dbCurrent As Database
Dim dbProjects As Database
Dim rsProjects As Recordset
Dim strServer, strUserName As String
Dim strProject As String

strProject = "0030"
Set dbProjects = CurrentDb()
Set rsProjects = dbProjects.OpenRecordset("ProjectList")
Set strServer = dbProjects.OpenRecordset("SELECT Server FROM ProjectList
WHERE PROJID = 'strProject'")
Set strUserName = dbProjects.OpenRecordset("SELECT OracleUser FROM
ProjectList WHERE PROJID = 'strProject'")

Since strServer should be one value I declared it as a string variable but
VBA balks.

What should I do?

G. Mann
 
I have solved the problem. No Reply needed.

G. Mann

G Mann said:
I've got a list of projects in a table that I want to query. Using a form I
get the ProjID (variable "strProject') from the user. With the ProjID I
then want to determine the Server and UserName and assign their values to
variables to connect to an Oracle database.

The Access table is ProjectsList that contains the following data:

Plant Server UserName ProjID
Unit 1 RP1 REB01 0029
Unit 2 RP4 REB02 0030
Unit 3 RP2 REB03 0017
Unit 4 RP3 REB04 0018
Click to expand...
Dim dbCurrent As Database
Dim dbProjects As Database
Dim rsProjects As Recordset
Dim strServer, strUserName As String
Dim strProject As String

strProject = "0030"
Set dbProjects = CurrentDb()
Set rsProjects = dbProjects.OpenRecordset("ProjectList")
Set strServer = dbProjects.OpenRecordset("SELECT * FROM ProjectList _
WHERE PROJID = 'strProject'")
rsProjects.MoveFirst
strServer = rsProjects("Server")
strUserName = rsProjects("UserName")
 
Back
Top