473,324 Members | 2,257 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Searching query displaying results in listbox and generating report.

Ok so here it goes, I'm not sure if this can be done but I'm trying.

I am trying to create a form "pickpatients" with a button (newsearch) and a listbox (list2) to run a query of my Visits1 table where all my patient visit data is stored. I want to be able to generate the results of this query in list2. I would then like to be able to click or highlight a result from the query and press another button on the form to generate a report with all of the person's data from the Visits1 table. Can it be done?

So far I set the rowsource of list2 to the query and created the button newsearch to run the query. Unfortunately list2 will only generate the results of the query once when the form is open, not each time the button is pressed. This is the vba code of the command button:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub newsearch_Click()
  3.  
  4. On Error GoTo Err_newsearch_Click
  5.  
  6.     Dim stDocName As String
  7.  
  8.     stDocName = "PatientQuery1"
  9.     DoCmd.OpenQuery stDocName, acNormal, acEdit
  10.  
  11. Exit_newsearch_Click:
  12.     Exit Sub
  13.  
  14. Err_newsearch_Click:
  15.     MsgBox Err.Description
  16.     Resume Exit_newsearch_Click
  17.  
  18. End Sub
  19.  
And here's the rowsource of list2:

SELECT [PatientQuery1].[Patient ID], [PatientQuery1].[Medical Records ID], [PatientQuery1].[Last Name], [PatientQuery1].[First Name], [PatientQuery1].[Sex], [PatientQuery1].[Diagnosis Code 1], [PatientQuery1].[Date of Visit] FROM PatientQuery1 ORDER BY [Patient ID];

Thanks for any and all help in advance!
Aug 10 '10 #1
1 3401
jimatqsi
1,271 Expert 1GB
It's not really clear what you want. Why do you want to generate the list more than once? What is changing to make the list contents different each time the button is clicked?

The code you want is simply
Expand|Select|Wrap|Line Numbers
  1. me!list2.requery
That will force the list box content to be refreshed.

But it seems to me maybe you want to add a Where clause to your list2 rowsource so that the content of the listbox changes based on something you have entered or selected on the form.

In that case you would do something like this
strSQL="select somestuff where someotherstuff" and then
me!list2.rowsource = strSQL
me!list2.requery

I hope that helps

Jim
Aug 11 '10 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Nathaniel Price | last post by:
I'm new to this list, so I'm not sure if this is the right place to post this. If not, please direct me to where it would be better to post it. Anyway, I'm creating a report generation tool of...
2
by: Geir Baardsen | last post by:
Hi! From a listbox I'd like to send only selected items to a report. Items will include: OrderNr,Date,EmployeeNr from tblOrders ZipCode,City from tblZipCodes Name,Adr,ZipID from...
1
by: SC | last post by:
I'm developing a site that may eventually have a very large database of users (hopefully, but who knows). It will be a community website where users can search each other (think Friendster,...
1
by: Materialised | last post by:
Hi everyone, I'm not exactly sure what I am talking about here so please bear with me. I am developing a database driven application (access), which will query a database and return matching...
3
by: ML | last post by:
I have used Allen Brown's technique for filling a listbox on a form with the names of files in a certain disc folder. It works well. I am now giving the user the option to print the form...
4
by: lorirobn | last post by:
Hi, I have a report displaying items that are missing from a room. I created 2 queries, the first getting the items IN the room, and the second being an "unmatched" query that references the...
1
by: allj | last post by:
Hope you had a good Thanksgiving!! I am stumped. I am trying to make a query that looks like the report with different groupings but one that is exportable to a delimited txt file. In my case a...
7
by: Stephenoja | last post by:
Hello Guys, I have a challenge here and would really appreciate some help. I have customer bills that are all done in excel with a template and stored in directories by month order. At the end...
2
jinalpatel
by: jinalpatel | last post by:
I have two tables MainFirm Name Address City State Zip County
7
by: tsuedesu | last post by:
Hi, I am trying to create a report that will show the totals of a query. I have a table as follows Table :Sales Fields: CustName, Product, $Value, VolumeOrdered, DeliveredQuantity, Period ...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.