Customizing Access parameter Query - No Data

[NLR]

Hello, I don't want my user to type in the parameter value for a query in case of miss spelling. Therefore, I'm using a dialog box form with a combo box field. The row source of the combo field is a table with one field for the list. I've added VB code (Event Procedure) to a buttons on the dialog box which says to run a query after click. I've created the query for the info I need displayed and am using the forms combo field as the criteria.

Private Sub cmdOK_Click()
DoCmd.OpenQuery "qryRequestsbyBranch", acViewNormal, acEdit
DoCmd.Close acForm, "frmDepartmentList"
End Sub

The query runs except I'm not getting any data. I'm not sure what I'm missing.
Do you have any ideas?

Thanks so much for your help!

 

[plog]

Whats the SQL of the query? Does it work when you run it not through VBA?

 

[NLR]

Hi, It is a simple select query. One of the fields, [RequestingDept], contain the following Criteria:

[Forms]![frmDepartmentList]![cboDepartments]

which refers to my dialog box form combo box field.


Thanks,
NLR

 

[plog]

If its referencing the dialog box, why are you closing the dialog box?

 

[NLR]

I no longer need the form open after the query runs. The query opens but the record is blank. I should see at least one record.

Thanks

 

[plog]

Actually, you do need the form open--the query requires it. Try leaving it open and see if it works then.

 

[NLR]

I removed the code that closes the form; but still no luck. The query doesn't return any info. ???

 

[plog]

Can you post your database?

 

[NLR]

Is there another way to provide the info? The site will not allow me to upload the DB due to security token missing.

 

[plog]

Post the SQL of your query.

 

[NLR]

SQL for qryRequestsByBranch follows:

SELECT tblRequests.RequestsID, tblRequests.DateReceived, [FirstName] & " " & [LastName] AS Name, tblRequests.AccountNumber, tblRequests.NumberOfFees, tblRequests.TotalAmtFees, tblRequests.AccountType, tblRequests.CustomerType, tblRequests.CourtesyReversal, tblRequests.BankError, tblRequests.Comments, tblRequests.RequestingEmployee, tblRequests.RequestingEmpExt, tblRequests.RequestingDept, tblRequests.ReversalGranted, tblRequests.ResolutionDate
FROM tblRequests
WHERE (((tblRequests.RequestingDept)=[Forms]![frmDepartmentList]![cboDepartments]));

 

[CJ_London]

the field RequestingDept in your table tblRequests wouldn't be a lookup by any chance?

 

[NLR]

Yes,

SELECT [tblBranchList].BranchID, [tblBranchList].Branch_Name FROM tblBranchList ORDER BY [Branch_Name];


This is used on the form, frmODReversalRequests.


Thanks,
NLR

 

[NLR]

Hi,
Any ideas...just checking in.

Thanks,
NLR

 

[plog]

WHERE (((tblRequests.RequestingDept)=[Forms]![frmDepartmentList]![cboDepartments]));

What CJ was getting at is your WHERE clause is mixing types, you are comparing text (Forms!frmDepartmentList!cboDepartments) to numbers (tblRequests.RequestingDept). Which means it will not find any data.

You need to compare either numbers to numbers or text to text.

 

[NLR]

Hi,
Thank you for your response.
I changed the field, [tblRequests.RequestingDept] to text and added a FK number field to connect the combo box to the table.
I also updated my query. (didn't know if I really had to do that)
My SQL remains the same:
SELECT tblRequests.RequestsID, tblRequests.DateReceived, [FirstName] & " " & [LastName] AS Name, tblRequests.AccountNumber, tblRequests.NumberOfFees, tblRequests.TotalAmtFees, tblRequests.AccountType, tblRequests.CustomerType, tblRequests.CourtesyReversal, tblRequests.BankError, tblRequests.Comments, tblRequests.RequestingEmployee, tblRequests.RequestingEmpExt, tblRequests.RequestingDept, tblRequests.ReversalGranted, tblRequests.ResolutionDate
FROM tblRequests
WHERE (((tblRequests.RequestingDept)=[Forms]![frmDepartmentList]![cboDepartments]));

There is still no results in my query. Also, is there a way (once it's working) to pull the results into a report and display the report instead of the query?
Thanks a bunch!!!!
NLR

 

[plog]

There is still no results in my query. Also, is there a way (once it's working) to pull the results into a report and display the report instead of the query?
Thanks a bunch!!!!

If that's the end game, that's easier than trying to do this. What you should do is omit your WHERE clause completley, build your report upon the query and then apply a Report filter when opening it.

Here's a link to the DoCmd.OpenReport method: https://msdn.microsoft.com/en-us/library/office/ff192676.aspx