How do I read in a record / row from a table.

[Dennis]

Hi,

I have a form where I need to read in the parameter setting for the current
user.

I know I can use DLookup but I have to lookup about 14 different parameters
off a single row. But that seems quite inefficient.

I need to read the record and extract the values from the record / row.

Thanks.

 

[Jeff Boyce]

Dennis

Here's one possible approach ... there are many ...

You could create a form that is bound to that record, and make the form
hidden. You can refer to controls on the form with:

Forms!YourForm!YourControl


Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John W. Vinson]

Hi,

I have a form where I need to read in the parameter setting for the current
user.

I know I can use DLookup but I have to lookup about 14 different parameters
off a single row. But that seems quite inefficient.

I need to read the record and extract the values from the record / row.

Thanks.

One way (of many) would be to open a Recordset based on the criteria, and
reference the individual fields. The specifics will depend on the context, the
nature of the criteria, and what you'll be doing with the data, so it's hard
to be specific!

 

[Dennis]

Dear John,

I have a user parameter table called tblUser. It is keyed by the user's name
which the report can get.

I'm coming from a Cobol backgroup where you read a record from a control /
user parameter and extract the values. I don't know what you mean by "what
you'll be doing with the data". Some of the data will use used internally
to control options on the form (current prompt field color), other
information will be displayed on the form (UserName), and other will control
what data the user can see.


What I want to do is read the user's row from tblUser and extract the
different parameters. These parameters are such things as current prompt
field color, User Name, job title, user security code (defines what
information they have access to), their e-mail address, and other such things.

The psuedo code is:

Read UserRcd from tblUser with key of UserName.
PromptFldColor = UserRcd.PrtFldColor
UserName = UserRcd.UserNm
UserTitle = UserRcd.JobTitle


Does that help?

 

[John W. Vinson]

Read UserRcd from tblUser with key of UserName.
PromptFldColor = UserRcd.PrtFldColor
UserName = UserRcd.UserNm
UserTitle = UserRcd.JobTitle

Ok, try this in a VBA procedure:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT UserNm, JobTitle, <other fields> " _
& "FROM tblUser WHERE UserNm = '" & UserName & "'"
Set db = CurrentDb
Set rs = db.OpenRecordset strSQL, dbOpenSnapshot
If rs.RecordCount = 0 Then
MsgBox "No data for this user!", vbOKOnly
Else
PromptFldColor = rs!PrtFldColor
UserName = rs!UserNm
<etc>
End If

 

[Dennis]

John,

Thank you for your assistance. I just wanted to publish the final working
code incase anyone else had this question. Also, I extracted about 20 fields
from the record, but I just showed two in my example.

Note pstrUserNm is a public variable that was set in the On Open event.


Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strErr As String

strSQL = "SELECT tblUserInfo.*, tblUserInfo.UserNm"
strSQL = strSQL & " FROM tblUserInfo"
strSQL = strSQL & " WHERE (((tblUserInfo.UserNm)= ‘†& pstrUserNm & “'))"

Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rst.RecordCount = 0 Then
strErr = "There is no User Info in tblUserInfo table." & vbCrLf
Err.Number = 0
Err.Description = strErr
Call BaseUtil.Dsp_Err_Msg(Err.Number, Err.Description, "Report_Open")
Else
' Get the user information from the record
Me.txtPromptFldColor = rst!PrtFldColor
Me.txtUserName = rst!UserName
End If

rst.Close ‘ Close all of the tables variables
db.Close

Set rst = Nothing ‘ Set the variable contents to nothing.
Set db = Nothing

End Sub



Dennis