Query Variables

[Guest]

I have a query that returns one record when it is run. I want to save the
information from the fields using VBA. The query always has the fields
“EmployeeIdâ€, “HireDateâ€, “Department†& “Titleâ€. I have dim some Variables
using VBA to capture the information but it does not work. Example – I used
- dim xemployeeId as string. Then I said xemployeeId = employeeId but I get
nothing. Can this be done or am I going about it incorrectly?

Thanks In advance

 

[Dirk Goldgar]

"open a adobe file from a command button"

I have a query that returns one record when it is run. I want to
save the information from the fields using VBA. The query always has
the fields "EmployeeId", "HireDate", "Department" & "Title". I have
dim some Variables using VBA to capture the information but it does
not work. Example - I used - dim xemployeeId as string. Then I said
xemployeeId = employeeId but I get nothing. Can this be done or am I
going about it incorrectly?

Did you open a recordset on that query? Based on what you've told us, I
would do it something like this:

Dim strEmployeeID As String
Dim dtHireDate As Date
Dim strDepartment As String
Dim strTitle As String

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("YourQueryName")

With rs
If .EOF Then
MsgBox "No records in query! Something's wrong."
Else
strEmployeeID = !EmployeeID
dtHireDate = !HireDate
strDepartment = !Department
strTitle = !Title
End If
.Close
End With

Set rs = Nothing

 

[Guest]

Dirk, Hi. I'm over my head but I entered the code. I understand it but I
get an error about too few Parameters expected 1. I am reading help but
don't understand. Help!!

Thanks

 

[Dirk Goldgar]

Dirk, Hi. I'm over my head but I entered the code. I understand it
but I get an error about too few Parameters expected 1. I am reading
help but don't understand. Help!!

What's the SQL of the query? Does it make reference to a control on a
form, as a criterion perhaps?

 

[Guest]

Dirk, Hi. Yes. I created a form and on it I ask for the employee No I'm
looking for. The "employee No" is the Primary Key so It will only select 1
record. I created the query using the design view and reference the "text
box" in the command button someone will press to start the query.

Thanks!

 

[Dirk Goldgar]

"open a adobe file from a command button"

Dirk, Hi. Yes. I created a form and on it I ask for the employee No
I'm looking for. The "employee No" is the Primary Key so It will
only select 1 record. I created the query using the design view and
reference the "text box" in the command button someone will press to
start the query.

You didn't actually answer my question about the SQL of the query, but
I'm going to guess that it does use a parameter reference to the text
box on your form. The trouble is that, while *Access* understands such
references and can fill them in automatically, when you use DAO to open
a recordset, Access isn't really involved in the process and won't fill
in the value of that parameter. So you have to do it yourself. Since
you didn't post the SQL so I could see what the name of the form and
control are, I'll show you how to open a recordset in such a way that it
doesn't matter what the names are. Try this, or something like it:

Dim strEmployeeID As String
Dim dtHireDate As Date
Dim strDepartment As String
Dim strTitle As String

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

Set db = Application.DBEngine(0)(0)
Set qdf = db.QueryDefs("YourQueryName")

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

Set rs = qdf.OpenRecordset

With rs
If .EOF Then
MsgBox "No records in query! Something's wrong."
Else
strEmployeeID = !EmployeeID
dtHireDate = !HireDate
strDepartment = !Department
strTitle = !Title
End If
.Close
End With

Set rs = Nothing
Set qdf = Nothing
Set db = Nothing

 

[Guest]

Dirk, Hi. I followed your instructions and it works! I think! I'm having
trouble with some fields being 'null' but for the most pary it owrks. Thank
you.

Wm.

 

[Dirk Goldgar]

Dirk, Hi. I followed your instructions and it works! I think! I'm
having trouble with some fields being 'null' but for the most pary it
owrks. Thank you.

You're welcome. I suggest you post a followup question about these Null
fields as the start of a new discussion thread.