Solved How to "hard code" the criteria of a query... from vba? (1 Viewer)

561414

Active member
Local time
Today, 15:15
Joined
May 28, 2021
Messages
280
Hello everyone, first of all, I'm experiencing a very strange situation.

There's a saved query in my database, I called it "csPortada_Dictamen". Its purpose is to serve a mail merge word document. I went through the mail merge wizard to link the fields and it all works fine... if done manually.

If I go to the word document in windows explorer, and then I double click it to open it, it opens correctly. Then it asks if I want to run a select query to link its fields to those of the "csPortada_Dictamen" query. I accept and it all goes well, it shows me all of the available merges.

If I then "hard code" my query by entering, say, record 234 in the Where criteria of the query builder, and then I save it, and then I go and double click the docx file from windows explorer, it works fine too. It shows me only the record that I need. I was happy and everyone rejoiced.

BUT THEN I did the logic step of going into my form, where I can select the record I really want. And then I went into the saved query and entered the forms!frmSomething.form!FieldID in its criteria. I tested it and the query worked fine, effectively filtering the results to show only that of the form. Saved all, created a button to simply open the word document like this:
Code:
Application.FollowHyperlink strFile
AND NOPE. It said it didn't find my database. It's stored in a path like this: c:/folder/db.accdb and when the button opened the file, it said "can't link because c:/folder.mdb can't be found"

So I'd like to know how to just press a button to open the word document with its mail merge properly assigned from my query. If I hard code the record I want, instead of forms!frmSomething.form!FieldID and then I press the button, it works fine. Why can't it do it the same when the form is open and the query has its forms!frmSomething.form!FieldID in its criteria field?

Does any of this make sense to you?
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:15
Joined
Sep 21, 2011
Messages
14,047
Windows uses backslash \ not forward slash?
Code:
application.FollowHyperlink "c:\temp\alpha.csv"
Also the form is going to have to be open for your query to work?
 

561414

Active member
Local time
Today, 15:15
Joined
May 28, 2021
Messages
280
The file path uses \ slashes. The button seems to work because it opens the docx. What it does not do is link the query filtered by the current record on the open form to anything in the docx file. I suppose the form should be open simply because that's how I filter the query but if the query can take the current record on the form in some other way, then it's probably fine.
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:15
Joined
Sep 21, 2011
Messages
14,047
I would open your DB and the form the query uses.
Then open the word document manually.

Does all that work.?

You could compact, zip and upload the DB and word doc for someone to look at?
I have to go out now for a passenger pickup, but can look later.

Please also supply explicit instructions on how to run this if you do upload.
 

561414

Active member
Local time
Today, 15:15
Joined
May 28, 2021
Messages
280
Oh thanks. I hope aws allows me to post links this time. I'll go get some sleep then I'll try to post a compact version. Thanks again. By the way, no, opening the file manually with the form open does not work. Right now, it only works if the query has a number instead of a reference to a number.
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:15
Joined
Sep 21, 2011
Messages
14,047
You need one more to link (probably to upload as well)
Well I'd get it working manually first?
Open the DB and relevant form, select record, then start the mailmerge.
 

Cronk

Registered User.
Local time
Tomorrow, 08:15
Joined
Jul 4, 2013
Messages
2,770
AND NOPE. It said it didn't find my database. It's stored in a path like this: c:/folder/db.accdb and when the button opened the file, it said "can't link because c:/folder.mdb can't be found"
Just to make sure. There are 2 different file names and each is stored in a different folder.
 

561414

Active member
Local time
Today, 15:15
Joined
May 28, 2021
Messages
280
Just to make sure. There are 2 different file names and each is stored in a different folder.
Yes, unfortunately that is no mistake from me, word threw an error where the database folder name became the database, and with a different file extension.
 

561414

Active member
Local time
Today, 15:15
Joined
May 28, 2021
Messages
280
Ok guys! I think this database is going to help me explain myself better.

This database contains 3 different queries, one for each .docx file in the folder "Formatos - db".

Please open the main form of the database. You'll see 3 big buttons:
"template all records"
Opens: 0705-CVM-FFP-0705 (FORMATO - FORMATO DE PORTADA DE INSTALACIONES ELECTRICAS)_01.docx
Query: csPortada_Dictamen_allRecords

If you click this, it will open the word document properly, just tell ms word to execute the query upon opening the file. You will be able to navigate all 14 records using the mailings tab.

"template current record in form"
Opens: 0705-CVM-FFP-0705 (FORMATO - FORMATO DE PORTADA DE INSTALACIONES ELECTRICAS)_02.docx
Query: csPortada_Dictamen_linked2form

THIS IS NOT LINKED TO ANY QUERY, BECAUSE MS WORD DOES NOT LET ME CHOOSE THE QUERY THAT USES THE CURRENT RECORD IN THE FORM. To try to link it, go to the Mailings tab >> Select Recipients >> Use an existing list >> select database >> select query, in this case, you should be able to choose "csPortada_Dictamen_linked2form", but if the same happens to you, that query won't even be in the list, despite being available in the database. Therein lies the problem, and it's precisely what I wanted to do: just click a button to open the template filled with the data from the current record in the form. Even if I make copies of this query, it seems that if it has a reference to a form, it won't be available.

"template hardcoded where clause"
Opens: 0705-CVM-FFP-0705 (FORMATO - FORMATO DE PORTADA DE INSTALACIONES ELECTRICAS)_03.docx
Query: csPortada_Dictamen_hardcoded

This opens the word file with the hardcoded where clause, if you open the query it depends on, you can manipulate which of the 14 records appears in the .docx file.

So that's what's happening. This is why I named the thread How to "hard code" the criteria of a query... from vba?, because if I modify that query from vba using the button, I think it will work. So what do you guys think?

Thanks for reading all this nonsense.
 

Attachments

  • test db.zip
    80.5 KB · Views: 396

Isaac

Lifelong Learner
Local time
Today, 14:15
Joined
Mar 14, 2017
Messages
8,738
Non-Access Office apps (Excel, Word) that connect directly to Access (like using Excel to run a database query as a Table Properties Connection query), if I remember right, uses an engine called Microsoft Query, or something like that. It is VERY PICKY and has its own set of rules, a set unlike anywhere else. (Dennis from UA, [RIP]) used to have a lot of experience with this and was a great source of information on MS Query's odd little universe of rules.

I'm not 100% sure that modern Excel and Word features (which connect to MS Access) still use MS Query, but the lessons I learned from MS Query limitations still apply, generally speaking.

I have a feeling that connecting to an Access query that uses a Forms! reference as a criteria in the query, is simply one of the things on the list of things that will not work in that context...

There are many other odd ones, too. Excel will not load a query (containing a VBA function) in its list of objects to select data from. Nor will it let you choose a Union query.

Just use VBA instead to modify the .sql property of the currentdb.querydefs("name of query") object and you should be all set.

PS, just tested and confirmed this is the case. As soon as I added a Forms!FormName!Controlname reference to my Access Query criteria, it was no longer available to select as a data source object when trying to retrieve MS Access Database data using MS Excel. The same is probably true for Word - just no can do.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:15
Joined
May 7, 2009
Messages
19,169
see if this will work for you.
it will also create a Folder (MailMerge).
 

Attachments

  • test db.zip
    133.2 KB · Views: 523

561414

Active member
Local time
Today, 15:15
Joined
May 28, 2021
Messages
280
Dear Isaac: Thank you for enlightening me about those topics. I was able to create this little piece of code based on your suggestion with querydefs, which effectively opens the word document with no issues, other than having to confirm the action when the document opens.
Code:
Private Sub btnAll_Records_Click()
    Dim strFile As String
    Dim strSQL As String    
    strSQL = _
    "SELECT tst.ExpedienteID, tst.csSolicitantes_Nombre, tst.csSolicitantes_Dato, tst.csSolicitantes_Numero, tst.csSolicitantes_CorreoElectronico " & _
    "FROM tst " & _
    "WHERE tst.ExpedienteID=" & Me.frmTst.Form.ExpedienteID    
    CurrentDb.QueryDefs("csPortada_Dictamen_allRecords").SQL = strSQL    
    strFile = Application.CurrentProject.Path & "\Formatos - db\" & "0705-CVM-FFP-0705 (FORMATO - FORMATO DE PORTADA DE INSTALACIONES ELECTRICAS)_01.docx"    
    Application.FollowHyperlink strFile        
End Sub

And right when I was about to reply, arnelgp enters the ring with this killer solution. Oh dear. That takes care of EVERYTHING I wanted to do! thank you so much, arnelgp! I'll examine the code, but I can totally say I would have never come up with it. I also see that I need to start naming my threads properly hehe.

Anyway, thanks everyone.

arnelgp, you absolute legend, you've saved me so many times already, thanks sincerely
 

Users who are viewing this thread

Top Bottom