Limit query to 1 record via VB code (1 Viewer)

[davedenn]

I have posted this question before, but did not find a viable solution to my siuation so I am posting it again in the hopes that someone out there will have an answer.

I have a db with a form that is the data entry point for two linked tables. The tables are linked through a primary key of "PatientID." I have created a query via VB code that sucessfully queries the tables and return all records. I have attached the code to an unbound text box in the form. I would like to set the code up so that a user can enter the Patient ID into the text box and 1 record will be returned to a mailmerge formatted MS Word document. At this point, all records merge into the document creating 3-4 pages of data each. I have posted the existing code below and hope that someone out there can provide a solution to my problem. I have tried a DLookUp statement and a Where statement without success.

Here's the code. Please respond to my email address if you have a suggestion or solution. Thanks in advance for any help you can provide.

davedenn@sbcglobal.net

Private Sub Text151_BeforeUpdate(Cancel As Integer)

Dim objWord As Word.Document
Set objWord = GetObject("C:\aden.doc", "Word.Document")

objWord.Application.Visible = True
objWord.MailMerge.OpenDataSource _
Name:="C:\DoctorDB.mdb", _
LinkToSource:=True, _
Connection:="QUERY QryAdenCard", _
SQLStatement:="SELECT * FROM [QryAdenCard]"
objWord.MailMerge.Execute
'objWord.Application.Documents(1).SaveAs (strDir & "\" & strReportType & ".doc")
objWord.Application.Documents(2).Close wdDoNotSaveChanges
DoCmd.Hourglass False
Set objWord = Nothing
Set objDoc = Nothing
End Sub


I have told that the * in the Select statement returns all the records, I just can't seem to come up with an alternative that returns the one records that I need.

 

[Jack Cowley]

In the Criteria line of your query use code like this in the PatientID column:

[Forms]![NameOfYourForm]![NameOfTextBoxWithPatientID]

Now the query will return a single record.

You SQL should read if you want to use it:

"SELECT * FROM [QryAdenCard] WHERE [PatientID] = [Forms]![NameOfYourForm]![NameOfTextBoxWithPatientID];"

 

[davedenn]

In the Criteria line of your query use code like this in the PatientID column:

[Forms]![NameOfYourForm]![NameOfTextBoxWithPatientID]

Now the query will return a single record.

You SQL should read if you want to use it:

"SELECT * FROM [QryAdenCard] WHERE [PatientID] = [Forms]![NameOfYourForm]![NameOfTextBoxWithPatientID];"

Jack,

Thanks very much for the timely reply. I completed the above changes. When I run the query from the query side, it works fine after I enter the desired PatientID number. I modified my code in the Event for the txt151 box. Now, when I enter a number in the text box of the form, it starts the merge, opens the template, and asks for the table that the document is attached to. I have to change the view even to see the query that it is actually based upon, and then the merge doesn't work. I know that I am doing something wrong, I just don't know enough about code to figure out what.

Dave

 

[davedenn]

Additional problems

Jack,

I guess I should have been a plumber!!

Ever since I changed my db to reflect the changes you suggested, I have had a variety of problems. The first was with Access asking for the table to base the datasource on (It's based on a query). Now, I receive errors on my datasource line of my code stating that I now have errors in the syntax. I have been running and rerunning that base code for weeks with no errors on that line, and now I have one! I have had to remove all your suggestions and revert back to the original code I posted where all records are returned to the merge. I am frustrated beyond belief and wish I could figure out what the devil this problem is. I tried to attached the zipped db for your review, but even compacted, it is too large to send. Another nail in my coffin! Maybe I have been working on this thing too long to even recognize my mistakes when I see them. Anyway, thanks again for your suggestions.

Dave

In the Criteria line of your query use code like this in the PatientID column:

[Forms]![NameOfYourForm]![NameOfTextBoxWithPatientID]

Now the query will return a single record.

You SQL should read if you want to use it:

"SELECT * FROM [QryAdenCard] WHERE [PatientID] = [Forms]![NameOfYourForm]![NameOfTextBoxWithPatientID];"

 

[WayneRyan]

Hi David,

Is [PatientID] in the query QryAdenCard?

I know that the query works, but the next phase wants
[PatientID] to use it as criteria, so it must be returned by the
query. You will probably also have to tell the second phase
that it is Table1.PatientID.

Wayne

 

[davedenn]

Query Problem - still

Wayne,

Thanks to you and Jack for the assistance.

I noted that in your (Wayne's) sample db that the form box with PID is fixed with the number assigned in the query. I was unable to delete or change this. I am not sure how this can work in a situation where the user must type in the PatientID from previous data entries. Is this changeable so that this can be done.

Also, Jack, you stated something about a "second phase?" Are you referring to the code in the unbound text field (txt151) in the form? Or something else?

Dave

 

[Jack Cowley]

Hmmm. I don't see a reference to a 'second phase' in my response, but be that as it may... I do not see a demo from Wayne, but you want to use code similar to what I suggested so the query will look to the form for its criteria. Using that code you should be able to enter and PatientID and the query should return that record. If you mail merge is set up correctly it should print one record. Remember that the for with the PatientID must remain open for this to work. If you close it then the query will ask for the PatientID...

hth,
Jack

 

[davedenn]

OOOPPPPPSSSSS

First of all, let me say "I'm sorry" to all who responded to my cries for help on my db. I got the names of the postees all mixed up!!

It was really Rollen who sent me the sample db. I should have directed my questions about the fixed text box to him.

I also should have directed my question about the "second phase" to Wayne. Boy, oh boy, what a mess I am this morning!

Anyway, if you individually will please forgive the brain fade, I would really like your help.

Thanks again for your patience.

Dave

 

[Jack Cowley]

Dave -

No problem! Tell me, once again, what problem are you experiencing? Is it with the Mail Merge to Word or getting the query to show the correct record?

Jack

PS. Mail Merge and I are not good friends so I may not be able to answer a question on that subject....

 

[davedenn]

Wrong again!

Jack,

I was wrong again in the last message. The error is actually "Word is unable to open the datasource." And then I get a debug error on the lines of code I sent you.


Dave

Here's the entire code:

Private Sub Text151_BeforeUpdate(Cancel As Integer)
Dim objWord As Word.Document
Set objWord = GetObject("C:\aden.doc", "Word.Document")
objWord.Application.Visible = True
objWord.MailMerge.OpenDataSource _
Name:="C:\DoctorDB.mdb", _
LinkToSource:=True, _
Connection:="QUERY QryAdenCard", _
SQLStatement:="SELECT * FROM [QryAdenCard] WHERE [PatientID] = [Forms]![FrmNucCard]![txt151];"
'SQLStatement:="SELECT * FROM [QryAdenCard] WHERE [PatientID] = [Forms]![FrmNucCard]![Txt151];"
objWord.MailMerge.Execute


objWord.Application.Documents(2).Close wdDoNotSaveChanges
DoCmd.Hourglass False
Set objWord = Nothing
Set objDoc = Nothing
End Sub

 

[davedenn]

Code assistance

Could someone take a look at my code and tell me if you see anything wrong with the syntax? I keep getting an error and Word can't seem to find the data source as it is written. If I remove the Where part of the SQL statement, it works fine (except it then returns all the records, and not just the one that I want.

Thanks in advance for all your help. Jack, Rollen, and Wayne especially!

davedenn@sbcglobal.net

here's all the code from the event (in the unbound text box):

Private Sub Text151_BeforeUpdate(Cancel As Integer)
Dim objWord As Word.Document
Set objWord = GetObject("C:\aden.doc", "Word.Document")
objWord.Application.Visible = True
objWord.MailMerge.OpenDataSource Name:="C:\DoctorDB.mdb", LinkToSource:=True, Connection:="QUERY QryAdencard", SQLStatement:="SELECT * FROM [QryAdenCard] WHERE [PatientID] = [Forms]![FrmNucCard]![Text151];"
objWord.MailMerge.Execute
objWord.Application.Documents(2).Close wdDoNotSaveChanges
DoCmd.Hourglass False
Set objWord = Nothing
Set objDoc = Nothing
End Sub

 

[Jack Cowley]

Remove the WHERE bit in the SQL statement. I sent you the syntax by PM earlier. Add a criteria in the criteria line of the query. If you have a PatientID of 12 the put 12 in the criteria line of the query. Run the query and you should see that patients record. If you do then run your mail merge. If mail merge works then it should work if you use a parameter query and the parameter is coming from a form....

hth,
Jack

 

[GGib7711]

If PatientID is a text field, try this:-
SQLStatement:="SELECT * FROM [QryAdenCard] WHERE [PatientID] ='" & [Forms]![FrmNucCard]![Text151] & "';"

If PatientID is a numeric field, try this:-
SQLStatement:="SELECT * FROM [QryAdenCard] WHERE [PatientID] =" & [Forms]![FrmNucCard]![Text151] & ";"

 

[davedenn]

parameter query

Jack,

I am not sure if I understand what you mean by parameter query. If I set the query up as you said, the query does ask for a id # to be placed in the query box and then the correct record is displayed. What I am attempting to accomplish is have the same thing happen when the user types in that number in a box in the unbound text box of a form. Is that possible?

Dave

 

[davedenn]

Error

If PatientID is a text field, try this:-
SQLStatement:="SELECT * FROM [QryAdenCard] WHERE [PatientID] ='" & [Forms]![FrmNucCard]![Text151] & "';"

If PatientID is a numeric field, try this:-
SQLStatement:="SELECT * FROM [QryAdenCard] WHERE [PatientID] =" & [Forms]![FrmNucCard]![Text151] & ";"

 

[davedenn]

Error "Can't find data source"

I changed my code as you suggested. The main problem is that now Word returns a message that it can't find the datasource. I have the entire code setup so it opens the data source first, but when I attempt the merge, it locks on the merge document and debug highlights the "openDataSource" line. I am not sure why this is happening, except that when I remove the "Where" portion of the SQL statement, and the statement (as Jack suggested) in the query criteria, it works fine to return all records in the merge without an error! Strange.

Dave

 

[davedenn]

Error in merge from query

Jack,

I did as you suggested and removed the Where statement. From the query window, after entering the Form statement in the PatientID criteria, I ran the query and it worked fine. When I attempted to use the Office Links to merge it with a Word document, it asked for the source document and my only two options in the source window was the two tables the query (QryAdenCard) is based upon. There was no query showing in the data source window. I don't know where to go from here.

Dave

Remove the WHERE bit in the SQL statement. I sent you the syntax by PM earlier. Add a criteria in the criteria line of the query. If you have a PatientID of 12 the put 12 in the criteria line of the query. Run the query and you should see that patients record. If you do then run your mail merge. If mail merge works then it should work if you use a parameter query and the parameter is coming from a form....

hth,
Jack