Update Query

[BillyMac]

Update Query

Can anyone help with this one. I want to create an e-mail address from the details I collect on my form and achieve this with the following expression as the control source in a text box on my form

=[Forename] & "." & [Surname] & "@thee-mailaddress.com;"

This works fine, but I want to then send this address to the e-mail field in the underlying table. I have tried using an update query with the update to criteria:

Forms.MyForm.email

but it puts the email address of the field that has the focus on the form on every record in my table ie the first e-mail address john.smith@thee-mailaddress.com is entered against every record in my table. How can I get the update query to update the e-mail field for each individual record?

 

[DBL]

In your update query you need to add the unique identifier for the current record on your form. Add the unqiue field to the query and in the criteria field build an expression back to the unique field on the form: [Forms]!YourFormName!UniqueIDFieldName

 

[BillyMac]

Thanks for that DBL. I thought it would work and so simple I don't know why I didn't think of it. Unfortunately it now only updates the record that has the focus on the form and not all of them. Do I need to run the query each time with a different record in focus in order to create the address for all the records in my table ot is there any way I can unpdate them all at once?

 

[DBL]

Is your form a continuous form or a datasheet rather than a single form?

 

[BillyMac]

It's a continuous form

 

[DBL]

How about an AfterUpdate event on the form to fill the email field as you go. If you include the email field as part of the query for the form (you don't have to show it on the form) And on the AfterUpdate event of the surname field or the BeforeUpdate event of the form itself you could do:

Me.youremailfieldname =[Forename] & "." & [Surname] & "@thee-mailaddress.com"

 

[BillyMac]

DBL abd Pat,

Thanks for the excellent advice. I have now populated all of the e-mail address fields and new additions are working a treat also. Thanks again