Question Auto-filled fields not populating table (1 Viewer)

elbowman

Registered User.
Local time
Today, 13:12
Joined
Nov 10, 2011
Messages
27
I'm a newbie at Access, but have been tasked with creating a web-enabled database. The goal is to allow a requester to input an issue for review. The reviewer must be notified a new issue has been directed to them via email.

I've started by using the Microsoft Issues web-enabled template as my base. I've added some fields and a table to the template to meet my needs. I've also found an online tip that describes how to build a data/name macro to email a notifier when a new record is entered for review. This macro resides in the Issues table. In case you don't know, you must use macros in web-enabled Access databases. You cannot use VB code, since it does not translate to the web.

Now here's my problem...

On my Issues form the requester must select a School, from a combo-box. The school record in the Users table contains fields that include the reviewer for that school's name and email address. I have established combo-box fields that display the reviewer's name and email address from the User table record, after the School is selected (auto-fill). I can see the values I want are being populated on the form. On the Issues form I've created a button that when clicked calls the macro in the Issues table to email an alert to the reviewer. However, those values are not being transferred into the Issues table. And, consequently, my email macro is returning an error that 'The SendEmail action failed because no recipients were specified.'

I've attached a zipped image of my name/data macro, if that will help anyone sleuth a solution to my problem.

Basically, I need to know why my auto-filled fields are not storing their values in the Issues table after being pulled from my Users table.

Any and all help is appreciated!
 

Attachments

  • macro.zip
    66.9 KB · Views: 155

crxftw

Registered User.
Local time
Today, 23:12
Joined
Jun 9, 2011
Messages
81
Do you actually need the values to be stored in Issues table?

As I see you could just add few more parameters like prmTo and prmCC, set references of your fields to your button and have the e-mail populated with that info you have gotten from autofill on your fields.
 

MStef

Registered User.
Local time
Today, 21:12
Joined
Oct 28, 2004
Messages
2,251
1) As CRXFTW said, you don't need the same data in two tables.
2) If you want it, link the field (from the form) with the field in the table,
and dislocate the instruction in VBA.
Look at "DemoCombo2A2000.mdb" (attachment, zip).
Look at tables, query, form (VBA).
Open form and try.
 

Attachments

  • DemoCombo2A2000.zip
    16.9 KB · Views: 179

elbowman

Registered User.
Local time
Today, 13:12
Joined
Nov 10, 2011
Messages
27
Thank you for the replies, so far, but they don't resolve my problem.

This is a web-enabled database. My understanding is you cannot use VBA in this type of application. Only macros can be used.

I understand it's not good to store the same data in two different tables, and I'm trying to avoid doing that.

However, I need to email the 'Issue' information to the 'Reviewer'. The Issue and the Reviewer data is kept in two different tables. The macro I'm using needs to pull information from both tables. The email address of the reviewer being stored in the Reviewer table, and the information relating to the Issue stored in the Issues table. I placed the macro in the Issues table since I'm only pulling one value from Reviewers, and want several items pulled from Issues.

So, my question remains...How do I get the value of the reviewer's email address to be applied and appear in the Issues form and table, so it can be carried forward into an email message via macro?
 

Beetle

Duly Registered Boozer
Local time
Today, 14:12
Joined
Apr 30, 2011
Messages
1,808
You're going to need another data macro to retrieve the missing data. You can use SetReturnVar within a data macro to return a value that can then be used by another macro. In the second macro you would use RunDataMacro to grab the values you need from the first macro using ReturnVars.

Whether the first macro should be in the Issues table and the second macro on the Reviewers table, or the reverse, you'll need to decide. I provided some illustrations in this thread that I replied to. It's not about sending e-mails but it may give you some insight into the basic concept.
 

elbowman

Registered User.
Local time
Today, 13:12
Joined
Nov 10, 2011
Messages
27
Beetle, thanks so much!

I'll do my best to put this information to use.

Cheers to you and the Sgt @ Camp Swampy!

Lance
 

Beetle

Duly Registered Boozer
Local time
Today, 14:12
Joined
Apr 30, 2011
Messages
1,808
Glad I could help. With the Access web apps being so new, there aren't (it seems) very many people out there using them yet so design information/help is still a bit hard to find at times.
 

elbowman

Registered User.
Local time
Today, 13:12
Joined
Nov 10, 2011
Messages
27
Well, I'm having no joy trying to make this work, Beetle. I'm getting a datatype mismatch error when I select a school on the IssueNew form.

I'm going to upload some screenshot images to help explain what's happening.

I've created the 2 macros.

One in my form IssueNew in the After Update event of the School field. School is a combo box that pulls values from the Users table to populate the AssignedToEmail field on the same form, and in the Issues table (which is the ultimate goal).

The second macro is in my Users table. It's a named/data macro called AssignedToEmail. It should return the value of the AssignedTo user's email address based on the School selected in the IssueNew form.

The attached jpg files are my IssueNew form, the Issues Table, the Users table, and the two macros.

I hope you have time to look at what I've setup and can tell where I've gone wrong. Thanks in advance!

Lance
 

Attachments

  • IssueNewForm.jpg
    IssueNewForm.jpg
    64.6 KB · Views: 141
  • IssuesTable.jpg
    IssuesTable.jpg
    49.7 KB · Views: 133
  • UsersTable.jpg
    UsersTable.jpg
    39.7 KB · Views: 138
  • FormAfterUpdateMacro.jpg
    FormAfterUpdateMacro.jpg
    55.1 KB · Views: 131
  • TableReturnVarMacro.jpg
    TableReturnVarMacro.jpg
    73.4 KB · Views: 128

Beetle

Duly Registered Boozer
Local time
Today, 14:12
Joined
Apr 30, 2011
Messages
1,808
I must be missing something. You say your are selecting a School on your form and want to retrieve the User e-mail address based on this choice. However, it looks like each School can have more than one User, so I don't see how simply selecting the School on your form gets you a given User e-mail.

If you can zip your db with some dummy data and post it here it would be easier for me to see what you've got going on there.
 

elbowman

Registered User.
Local time
Today, 13:12
Joined
Nov 10, 2011
Messages
27
OK. Here's the zipped db.

Strictly speaking the Users table contains users from schools, bus drivers (Transportation) who are issue submitters, and Transportation Admins who need to track all submitted issues. Each school only has one user, who is a reviewer.

So, the School field SELECT statement displays all schools, not like Transportation. That limits the selections to just the actual schools.

Thanks for your time!

Lance
 

Attachments

  • Bus Conduct IssuesMOD2.zip
    349.6 KB · Views: 157

Beetle

Duly Registered Boozer
Local time
Today, 14:12
Joined
Apr 30, 2011
Messages
1,808
I got your db. It will be Monday before I can look at it in depth, but I would like to know how many people you want to be e-mailed when a new issue is created.

The AssignedTo person, the TransAdmin and the OpenedBy person?

Just 1 or 2 of the above?
 

elbowman

Registered User.
Local time
Today, 13:12
Joined
Nov 10, 2011
Messages
27
All three.

The main issue is to get the issue to the reviewer. The originator should get a CC, for their own reference. The Trans Admins need to see every issue sent for review.

You're the best!

Lance
 

Beetle

Duly Registered Boozer
Local time
Today, 14:12
Joined
Apr 30, 2011
Messages
1,808
Sean,
How's it going?
Funny you should ask because I was writing this response when you posted.

I have re-attached your database. I made some modifications to the data macro and your IssueNew form. The first thing to note is that you do not need to store the e-mail addresses in the Issues table. The ID values for those users (the AssignedTo user and the OpenedBy user) are already stored in the table, so you just need to retrieve those values in order to look up the correct e-mail addresses from the Users table. You also do not need the hidden e-mail combo boxes on your form. I did not actually delete any of these objects (the fields or the form controls), but you do not need them to make this work.

The logic is this;

1) You look up the record in the Issues table that matches the ID value of the record you just created on your IssueNew form. Once you find that record you get the ID values for the AssignedTo and OpenedBy fields. Keep in mind these are lookup fields, so although they display the person's name, the value that is actually stored in the field is the ID value form the Users table.

2) Once you have those values you store them in variables. In this case we're using Local variables (SetLocalVar) because all of the work is done within the data macro. We do not need to return any values for use by another macro.

3) You use those local variables to then look up the appropriate record in the Users table and return the e-mail addresses. We need two look ups because we need the addresses for the AssignedTo user and the OpenedBy user.

4) Since you also want to BCC the Transpo Admins, we need to lookup those e-mails addresses as well. In this case we don't need to reference any values in the Issues table because we want an e-mail copy to go to all Transpo Admins (at least as I understood it that's what you want), so we create another Local variable and then use a For Each Record loop to return the e-mail addresses for all the Transpo Admins in the Users table and assign them to the variable using a semi-colon as the delimiter.

5) Last, send the e-mail using the variables we just created. Note that the variables for the User Names are used in the Subject and Body of the e-mail so the e-mail ends up looking like;

Subject: Attention Lance. A new issue has been assigned to you.

Body: Please log in to the database and act on Issue 14 created by Beetle Bailey.

Again, all of the logic is handled within the data macro (EMailAssignedTo). The only thing the form macro does (in the Click event of the button) is provide the parameter value and run the data macro.

I also fixed your Assigned To combo box on the form. I don't know why but you had this bound to the [School] field, so I reset the Control Source to the AssignedToUserID field and fixed the embedded macro in the AfterUpdate event of the School combo box so it sets the value of the AssignedTo combo box.

On a side note, your Users table isn't really normalized. You should have separate fields for FirstName and LastName and you would normally have a separate table for school information. It looks like you started with a template for this application so I suppose you were just working with what you had, I'm not sure.
 
Last edited:

elbowman

Registered User.
Local time
Today, 13:12
Joined
Nov 10, 2011
Messages
27
Sean,
I think you attached the wrong zip file.

Thanks so much for all your help!

You are correct I was working with a Microsoft Template file. Being a real rookie, as I'm sure you could tell from my footprints in the template, I didn't try to change too much in fear I would break something.

Lance
 

Beetle

Duly Registered Boozer
Local time
Today, 14:12
Joined
Apr 30, 2011
Messages
1,808
I think you attached the wrong zip file.

Yep, I just realized that.:eek:

Sorry, got too many irons in the fire right now I guess.;)

Here's the correct one.
 

Attachments

  • Bus Conduct IssuesMOD2.zip
    204.6 KB · Views: 114

elbowman

Registered User.
Local time
Today, 13:12
Joined
Nov 10, 2011
Messages
27
Sean,
I received the database last night, just prior to the end of my shift. I didn't have time to do anything but extract the zip, and take a quick look at the macros, etc.
This morning I loaded up my test record and clicked the Email button to see what would happen and got errors. I'm going to try and puzzle this out on my own.
I can't thank you enough for your help!

Lance
 

Beetle

Duly Registered Boozer
Local time
Today, 14:12
Joined
Apr 30, 2011
Messages
1,808
Just FYI I did test this using Outlook 2010 and it worked. Sent an email to four different e-mail addresses that I put in for testing purposes (the "Assigned" person in the To field, the "Opened By" person in the CC field and two "Trans Admins" in the BCC field). Post back if you don't get it solved.
 

elbowman

Registered User.
Local time
Today, 13:12
Joined
Nov 10, 2011
Messages
27
Sean,
I'm stumped. The error log reports the Email is erroring in the Named Macro,
Issues.EmailToAssigned. It says, "The identifier '[varTransAdmins]' could not be found." in the context, "SetLocalVar varTransAdmins, [varTransAdmins] & ";" & [Users].".
There is no statement referring to "SetLocalVar varTransAdmins, [varTransAdmins] & ";" & [Users].[Email]" in the macro. Your statement is, "SetLocalVar varAdminEmails, [varAdminEmails] & ";" & [users].[Email]".
I don't see varTransAdmins in here anywhere.

Another thing. I'm also getting an error that my PC is not set to send/receive emails. But, it sure is! I work in Outlook all day. Is there an Access setting that I need to set to allow email to be sent from there?

Lance
 

Beetle

Duly Registered Boozer
Local time
Today, 14:12
Joined
Apr 30, 2011
Messages
1,808
That's interesting because [varTransAdmins] was an old variable name I had used when I was first building the macro, but I deleted that so I'm not sure why it would be hanging you up. It's not there or hanging anything on my end. However, I did somehow screw something up after I tested it but before I uploaded it back to the forum (I had 4 different databases I was working with yesterday, so maybe it's not surprising I deleted something I shouldn't have :eek:). There should be an If Then statement in the For Each record loop for creating the variable for the Admin e-mails.

Another thing. I'm also getting an error that my PC is not set to send/receive emails. But, it sure is! I work in Outlook all day. Is there an Access setting that I need to set to allow email to be sent from there?

That's just a generic error message. All it means is that it couldn't send the e-mail for some reason (not necessarily because your PC isn't configured for e-mails). It's because the string for the BCC field isn't being built correctly (because some dumb-ass developer deleted the If Then block :D).

So, I'm attaching a text file. This is the XML version of the corrected data macro. Here's what you need to do;

1) Make a backup copy of your current database.

2) Open the backup, open the Issues table and delete the EmailToAssigned macro using the Rename/Delete Macro option.

3) Go to Create Named Macro to start a new data macro, but just leave it open and blank for now.

4) Open the text file and copy the XML (make sure you get all of it).

5) Go back to your new macro and paste the XML (using Ctrl + V).

6) Save the new macro with the same name as the old one.

Then test and see if it works in the backup.
 

Attachments

  • DataMacro.txt
    3.8 KB · Views: 114

Users who are viewing this thread

Top Bottom