Ms Access 2000 VBA DAO query problem.

[PejayukAtWork]

I am using Ms Access 2000, and have created a Query that uses the value held in a forms object as the criteria. This query when run from a button returns the results fine in a datasheet. However, when I try to use this query in VBA DAO to return a recordset that I can manipulate, I get prompted for the criteria! even though it is available on the form. Does anyone know how to fix this?

Many thanks

Pejayuk

 

[Mile-O]

Check for typos? Ensure the form is open?

 

[BoroLee]

Posting your actual code always helps others solve the problem

 

[PejayukAtWork]

Checked.

I have checked the code and it works fine when the criteria isn't liked to a form object (Text box, combo box etc.). If the infomation is paisted into the prompt I get the result I want so the query works. The form is open and when the query is called from a button it works. But I need to be able to manipulate the returned recordset from the query so have to use. Is DAO the best way to go about this or should I be using something else?

Many thanks

Pejayuk.

 

[BoroLee]

post the code

 

[PejayukAtWork]

Here is the code, tables, query, and relationship

#####Code that returns the data to show the query works #####
Private Sub Command3_Click()
On Error GoTo Err_Command3_Click

Dim stDocName As String

stDocName = "qryFindEmpByJobType"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command3_Click:
Exit Sub

Err_Command3_Click:
MsgBox Err.Description
Resume Exit_Command3_Click

End Sub


#####Code behind the button usaing VBA and DAO#####
Private Sub cmdFindEmpByJobType_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

'Get handle to database
Set dbs = CurrentDb

'Get handle to query results
strQry = "qryFindEmpByJobType"
Set rst = dbs.OpenRecordset(strQry)

'Display record count of results
rst.MoveLast
MsgBox rst.RecordCount
End Sub


#####SQL code for Query#####
SELECT EmpDetails.EmpID, EmpDetails.JobTypeID, EmpDetails.Name
FROM EmpDetails
WHERE (((EmpDetails.JobTypeID)=[Forms]![frmFindEmpByJobType]![comboJobTypeSelected]));


#####JobType Table#####
#####Filed Name - Data Type #####
JobTypeID - AutoNumber (Primary Key)
JobTypeDescription - Text


#####EmpDetails Table#####
#####Filed Name - Data Type #####
EmpID - AutoNumber (Primary Key)
JobTypeID Number (Linked to JobType.JobTypeID using Lookup Wizard.
Name - Text


#####Relationships#####
One to Many relationship as
JobType.JobTypeID to EmpDetails.JobTypeID

 

[Mile-O]

And the exact parameter you are being asked for?

 

[PejayukAtWork]

That would be [Forms]![frmFindEmpByJobType]![comboJob
TypeSelected].

 

[Mile-O]

WHERE (((EmpDetails. JobTypeID)=[Forms]![

Look at the space between EmpDetails. and JobTypeID...

 

[PejayukAtWork]

The space must of been me when I paisted it in, as the space isn't in the query and the query works fine. Here is the SQL statement again.

SELECT EmpDetails.EmpID, EmpDetails.JobTypeID, EmpDetails.Name
FROM EmpDetails
WHERE (((EmpDetails.JobTypeID)=[Forms]![frmFindEmpByJobType]![comboJobTypeSelected]));

 

[PejayukAtWork]

It would seem that this forum is inserting the space. If I paste the SQL query into NOTEPAD, there is no space. When I paste it into the textbox to poste the statement it still has no space, but when viewing it on the forum after it is posted the space appears.!. ???!!!!

 

[Mile-O]

When you open a recordset that includes parameters in VBA , you need to supply the parameters with VBA also.

D'oh! Tail goes betwen legs. Smacks wrist. etc.

 

[PejayukAtWork]

Thanks everyone for your help. Many thanks to Pat Hartman for the solution to the problem. I have been trying to get this problem fixed for a long time.

Thanks a million

Pejayuk