Reference text field from form in a query

[cjmitton]

I have a query I for mailmerging, Its working great but now I need to add the contense of a text box on the form that I'm running to add it in to the merge information. (the information is from a dlookup ran on opening the form and works fine)

I've tried adding a reference in to query its self as a custom field like this:

ref: [Forms]![frm_Matters]![txtSecRef]

but it does not get the information required?

Any ideas please.

 

[vbaInet]

Is it showing no records?

 

[cjmitton]

I've tried to run the query and it prompts me for the field information.
When I manually enter the field details (initials in the case) it works. But when i use it in the merge function it does not work and asks to access the database. An error I've had before when the query has an issue.

 

[vbaInet]

That's because the form frm_Matters must be open before you run the query.

 

[cjmitton]

When i run the query as part of the merge, the form is active and has the relevant data in the fields required.

 

[vbaInet]

What merge function is this?

 

[cjmitton]

I use the query as part of a mailmerge to word. It ran fine and merged the required letter in word. But as soon as I added the reference to the text field it stopped working.

 

[vbaInet]

Let me see the code line where it fails. If the line is not where you wrote the sql, add that too.

 

[cjmitton]

Thats part of the problem. When i run it the only error I get is when word does the merge. If I run the query from the editor it runs when i give it the data. I don't know how to run the query while the form has live data in it to see if it references correctly or not. I hope I'm making that clear! My terminology could be off a bit....

 

[vbaInet]

Oh, wait. You are talking about the built-in Merge to Word functionality?

 

[cjmitton]

Yep! I'm running the code from access to produce a mailmerge in word. As part of the code it uses my query to pick up all the fields for the merge and use usings form information (active matter id and client id) to select the correct records to merge. It's just a matter of adding in the initials from this text box To the merge, hence the reference to the text box in the query.

 

[vbaInet]

I know what you mean, it doesn't like references to a control but I can't remember the resolution. I think if you create a function instead of the textbox, it should work.

So:

Public Function GetSecRef()
    GetSecRef = Forms!frm_Matters!txtSecRef
End Function

Then put GetSecRef() in the criteria. Make sure to put the parentheses and ensure that the function is in a global Module.

 

[cjmitton]

Sorry, parenthesis? My terminology is not that good. If i need to use the function on different forms for different queries I guess I just make them unique for each time as it references the form.

 

[vbaInet]

The brackets.

Use just that one function and reference different textboxes using an IF...ELSE IF...END IF block or a SELECT CASE block.

 

[cjmitton]

I'll use the same text box reference its just the form that would change. Sorry to be a complete pain could you give an example of how I'd do that. Thanks

 

[cjmitton]

Learn something each day....

Sorry to be a complete pain, could you give me an example of how I'd do the function with different forms to reference to as the text box would be the same on the different forms.

Thanks

 

[vbaInet]

See what you can come up with:

http://www.techonthenet.com/access/functions/advanced/case.php

If the name of the textbox is the same, then all you need to do is to change the form reference.

 

[cjmitton]

Thanks, I'll give it a try as its something I've never done before.