Question Auto fill in query parameter with a forms field value + more.

[mcclunyboy]

Right guys.

I have created a workflow database - its not complicated, at least I created most of it so it can't.

I have a form which displays details of what needs attention by admin. Once the admin has done the work they email the originator back with a report which includes all the records needing their attention. This query requires a parameter of the originators username. It outputs this to word and attaches to email. However then the admin has to retype the user name into the address bar of the email...(user names are same as domain users on the network).

Is there anyway I can take the name from the field in the form, "submitted by", and automatically enter it into the query to produce the report. Then automatically enter it into the address bar on the email (outlook).

Hope it explains my problem...just to make life easier for users and reduces human error when dealing with the work flow.

 

[Zaeed]

How are you sending the email? Most functions have a To parameter for the recipient list..

How are you generating the report you send?

 

[John Big Booty]

In the Query criteria you can use;

Forms!FRM_FormName!FieldName

This will pick up the Field FieldName on Form FRM_FormName

If you are feeding this into a report it should be a relatively easy matter to also feed it into a DoCmd.SendObject command.

 

[mcclunyboy]

thanks ill give it ago this morning and see where i get.

i couldn't use the "to" command from the macro because the email recipient changes depending on who submitted the issue. In reality 2 people recieve emails from originators but potentially those 2 must send to anyone within my organisation.

 

[John Big Booty]

......

i couldn't use the "to" command from the macro because the email recipient changes depending on who submitted the issue. In reality 2 people recieve emails from originators but potentially those 2 must send to anyone within my organisation.

I'm not fully aware of what you can and can't do using a macro in this situation, but if you use some VBA and use the DoCmd.SendObject as previously mentioned the To: portion of this command can be assigned dynamically as previously mentioned. With a little bit of work you can even assign more than one To: recipient.

 

[mcclunyboy]

is this right:

Forms!Need LLPG Action!Submitted By

where - "Need LLPG Action" is the form and "Submitted By" the text box containing the originators name

Where do I add this though - in the macro containing the send object command or the query which generates the data for the report.

If it is the query - where!?

 

[John Big Booty]

Put;

Forms!Need LLPG Action!Submitted By

In a new column in the Field Row (Top row) of your query it will appear as;

Expr1:Forms!Need LLPG Action!Submitted By

You can then pass it into the To: portion of the DoCmd.SendObject command.

 

[mcclunyboy]

hi

it says it contains invalid sytnax - i may have entered an operand without an operator.

 

[John Big Booty]

I'm not 100% sure but I suspect it may be because you have embedded spaces in your form name. Get rid of any spaces or other special characters you have in object and field names. Stick with alphanumerics and underscores ("_"), it will eliminate a whole lot of future heart ache when it comes to writing code.

 

[mcclunyboy]

Yes unfortunately there was no naming convention used at the creation of the DB.

I added 3/4 so but haven't edited the original forms/table...i may go through it and try and sort it out but it will take a while...

ill get back to you later on if it doesnt work once I have edited it

 

[John Big Booty]

Good luck with that, as any time you change an object name you will need to ensure you change all references to that object.

 

[John Big Booty]

As an interim measure try using

[Forms]![Need LLPG Action]![Submitted By]

 

[mcclunyboy]

Hi,

I have changed what I can - I am not going to change the original table names/queries because I didnt create them and without going through every form in detail - basically I don't have enough time at work.

I did change form names/reports/macros/module names where it was needed.

However when I use the PC of code as an expression in the query it just supplies ALL records - not just the user who submitted them

 

[John Big Booty]

OK I think I may have lead you down the wrong path, my apologies

Put the code in the Criteria under field [Submitted By]