Automatically populate a field using a field on another form

[SikhSioux]

I wonder if someone can help me, I have a customer enquiry form (frmCustEnquiry) within which is a combo box field, the combo box stores the customers department ID (DeptId). The email address for the department sometimes changes, the changes are stored in a table (tblDeptEmailAddress) which contains the fields DeptId, deptEmailAddress and dateEmailAddressIntroduced. After all the fields in the frmCustEnquiry form are completed a new form (frmEmailCust) opens which allows the user to email a response to the customer. I need to include a field in form frmEmailCust which uses the DeptId in the frmCustEnquiry form and the tblDeptEmailAddress table to display the latest email address for the department. I can then use this to send an email reply to the customers department

Thanks

 

[SikhSioux]

I forgot to mention what I've tried so far:

I created a totals query which shows the deptEmailAddress where the DeptId is equal to the value of the DeptId in the frmCustEnquiry form and the dateEmailAddressIntroduced field has its maximum value. I then successfully tested this query. I then created a form based on this query which displayed the deptEmailAddress field. I successfully tested this form i.e. the email address displayed successfuly on the form. I then included this form as a subform on the frmEmailCust form, for some reason the email address no longer displayed!

You maybe wondering why the email address always changes, well sometimes a new customer might not be able to access the departments group mailbox, in which case they would use there own email address until they were given access

 

[Insane_ai]

Try This:

Recreate the query you referenced to gather the DeptID and Email as the row source for a combo box.

You may want to set the Column Width to hide the email address in the combo box. ( 1.5";0 results in the first column at 1 1/2 innches while the second is hidden)

To reference the value in the combo box use:
[cboMyComboBox].column(0) for the DeptID or [cboMyComboBox] if the bound to property is set to 1

[cboMyComboBox].column(1) for the Email address

You may also want to set a variable for the value and use the Nz function to avoid an error when the user clicks a button to send the email.

For Example:

Dim strMyCombo as String
strMyCombo = [cboMyComboBox].Column(0)

If Nz(strMyCombo,"Error") = "Error" Then 'User clicked the button without choosing from the box.
MsgBox "Please choose a department", vbOkOnly,"Error Message"
Else
' code for sending Mail
End if

 

[SikhSioux]

Thanks for helping out, I've tried what you said but again the email address does not display. I've tried specifiying the field as combo box field as well as text field but the problem remains, by the way, I'm using Access 97

 

[SikhSioux]

I get the #name? error in all cases

 

[mikebaldam]

Can you post an example to look at?

You can get around the problem by doing a seperate lookup for the email address ie do a query for email and set on subform current action (but its not the best way and will re-query data so slow down the db).

If its working on the form but not as a subform, there is most likely an issue with the relationships between the Master and Child fields conflicting with the email or the subforms query.

 

[Insane_ai]

After re-reading your inquiry, it appears that your project requires the use of two independent forms. This requires the use of public code.
The earliest version of Access I have available is Access 2000.
I created the following to emulate your problem and explain the solution; it should work in Access 97:

tblDeptEmailAddress:
FieldName: Data type:
DeptID Text
DeptEmailAddress Text
dateEmailAddressIntroduced Date/Time

frmCustEnquiry:
cboDeptID
rowsource:
SELECT qryDeptwithMostRecentEmail.DeptID, qryDeptwithMostRecentEmail.DeptEmailAddress
FROM qryDeptwithMostRecentEmail;3

cmdEmail
On click:
Private Sub cmdEmail_Click()
OpenMailForm [cboDeptID].Column(0), [cboDeptID].Column(1)
End Sub


frmMessage:
txtEmailAddress
txtDeptID
txtSubject
txtMessage
cmdSendMail

qryMaxDate:
SELECT Max(tblDeptEmailAddress.dateEmailAddressIntroduced) AS MaxOfdateEmailAddressIntroduced, tblDeptEmailAddress.DeptID
FROM tblDeptEmailAddress
GROUP BY tblDeptEmailAddress.DeptID;

qryDeptWithMostReecentEmail:
SELECT tblDeptEmailAddress.DeptID, tblDeptEmailAddress.DeptEmailAddress
FROM tblDeptEmailAddress INNER JOIN qryMaxDate ON tblDeptEmailAddress.dateEmailAddressIntroduced = qryMaxDate.MaxOfdateEmailAddressIntroduced;


Private Sub cmdEmail_Click()
OpenMailForm [cboDeptID].Column(0), [cboDeptID].Column(1)
End Sub

Public Sub OpenMailForm(strDeptID As String, strEmailAddress As String)

DoCmd.OpenForm "frmMessage"
Form_frmMessage.[txtDeptID] = strDeptID
Form_frmMessage.[txtEmailAddress] = strEmailAddress

End Sub

 

[Insane_ai]

Search the sample databases for the actual database my example shows. You should find it once the moderators approve it.