Alternative to DLOOKUP?

[Gavin Webb]

I am trying to retrieve three different fields from a
record in a table with a WHERE clause using the value
entered in a text box in a form.

So far, I have got a SELECT query that returns the three
fields I need. I don't know how to get those three fields
from the table or query into fields I can use in the code
of the form. All it does at the moment is display the
results in a table.

DoCmd.OpenQuery "Qry_SelUserDetails", acViewPreview,
acReadOnly

I can see that I could return one value at a time by using
DLOOKUP on Qry_SelUserDetails three times in a row.

Is there a better way?

Thanks
Gavin

 

[gavin webb]

Yes it does, and it will, and thanks very much

G

-----Original Message-----
Gavin

Does your query return just 1 row (with your selected 3 fields)? Will it
*always* return 1 row?

If so use a RecordSet variable as follows;

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset

("Qry_SelUserDetails", dbOpenDynaset)

 

[Andy Cole]

Glad to be of help

Andy

Yes it does, and it will, and thanks very much

G

("Qry_SelUserDetails", dbOpenDynaset)

 

[Gavin Webb]

Umm... seems I was a bit premature with the celebrations.

I get the following error when I used that code

"Run-time error '3061':
Too few parameters. Expected 1."

The code I've actually got in my form is exactly as
follows:

Private Sub CmdContinue_Click()

Me.TxtUserName.SetFocus
If TxtUserName.Text = "" Then
MsgBox "Please Enter a Valid User Name and Password"

Else

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("Qry_SelUserDetails",
dbOpenDynaset)


rs.MoveFirst
' populate some unbound controls on your form with the
values
Me.strUserName = rs.Fields("FirstField").Value
Me.strPassword = rs.Fields("SecondField").Value
Me.intSecLevel = rs.Fields("ThirdField").Value
rs.Close
Set rs = Nothing

End If

End Sub

 

[Andy Cole]

Gavin

The error you are getting suggests a possible problem with the query,
"Qry_SelUserDetails" but without knowing what the SQL of this is, I can't
say for sure. Can you post the SQL?

However, there's another problem as well. The code I posted for extracting
the fields from the record set was an example only. You need to replace
"FirstField"..."ThirdField" with the actual name of your fields in the query
recordset, or, reference them by the Field index (which starts at 0). For
example, if your 'FirstField' was called fldUName, then you could write;

.......rs.Fields("fldUName").Value

or

.......rs.Fields(0).Value

The other question I have is where you are assigning the records set value;

Me.strUserName etc

Is strUserName the name of a control on your form or a variable? If its a
control, then no problem, but if its a variable, you don't need the 'Me.'
part

 

[Dirk Goldgar]

Umm... seems I was a bit premature with the celebrations.

I get the following error when I used that code

"Run-time error '3061':
Too few parameters. Expected 1."

The code I've actually got in my form is exactly as
follows:

Private Sub CmdContinue_Click()

Me.TxtUserName.SetFocus
If TxtUserName.Text = "" Then
MsgBox "Please Enter a Valid User Name and Password"

Else

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("Qry_SelUserDetails",
dbOpenDynaset)


rs.MoveFirst
' populate some unbound controls on your form with the
values
Me.strUserName = rs.Fields("FirstField").Value
Me.strPassword = rs.Fields("SecondField").Value
Me.intSecLevel = rs.Fields("ThirdField").Value
rs.Close
Set rs = Nothing

End If

End Sub

Because the query has a parameter -- the reference to the text box on
the form -- you need to resolve that paramter before you can open a
recordset on it. When you tell Access to open the query (via
DoCmd.OpenQuery), it resolves the parameter for you, but when you use
DAO to do it, resovling parameters becomes your job. Try this version
of the recordset-opening code:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rs As DAO.Recordset


Set db = CurrentDb
Set qdf = db.QueryDefs("Qry_SelUserDetails")

For Each prm in qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset

That ought to work, and then you can go on with your processing of the
recordset.

An alternative to this might be to build the SQL statement in code,
rather than using a stored query. Then you could embed the value from
the text box directly in the SQL statement, and wouldn't need a querydef
object. It would look something like this (though I don't know your
table and field names):

Dim strSQL As String
Dim rs As DAO.Recordset

strSQL = _
"SELECT UserName, Password, SecLevel " & _
"FROM tblUsers WHERE UserName = " & _
Chr(34) & Me!TxtUserName & Chr(34)

Set rs = CurrentDb.OpenRecordset(strSQL)