Returning a single value to a variable from a recordset

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
 
G

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")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

trying to assign a variable to the field part of recordset!field 2
returning a variable from a form to a VBA procedure 1
How to have a variable in a recordset field name 1
Assigning the value in recordset field to a variable 3
Passing a variable from a password form back to code. 3
Assigning value to a variable using SQL statement 2
Passing a variable value from Javascript to asp.net server variable 2
How to make the part of the field name as a variable in a recordset 1

Top