Update Query Question

[JOM]

I would like to put a button on my form that when I press it runs the update
query and updates that record i.e., update the email address field if the
Firstname and lastname are not null. how do I do that?

My update query is as follows:

UPDATE tblemployee SET tblemployee.EmailAdd =
Replace(Replace([FirstName],".","") & "." & [LastName]," ",".") &
"@Company.com";

 

[JOM]

Thanks that worked perfect, I have another question though, after I have
clicked the update button, the Email address does not update immediately, I
have to click the Email address text box and then the email comes up, or if I
try to go to the next record, a write conflict message comes up. How do I
take care of this?

You'll need code like this on the button Click event ...

Dim lngID As Long
Dim strEmail As String
Dim strSQL As String

Const q As String = "'"

lngID = Nz(Me.[YourID],0)

strEmail = Replace(Nz([FirstName],"x"), ".","") & "."
strEmail = strEmail & Replace(Nz([LastName],"x"), ".","")
strEmail = strEmail & ""@Company.com"

strSQL = "UPDATE tblEmployee SET [EmailAdd] = " & q & strEmail & q
strSQL = strSQL & " WHERE [YourID]=" & lngID

CurrentDB.Execute strSQL, dbFailOnError

--

Danny J. Lesandrini
[email protected]
http://amazecreations.com/datafast

I would like to put a button on my form that when I press it runs the update
query and updates that record i.e., update the email address field if the
Firstname and lastname are not null. how do I do that?

My update query is as follows:

UPDATE tblemployee SET tblemployee.EmailAdd =
Replace(Replace([FirstName],".","") & "." & [LastName]," ",".") &
"@Company.com";

 

[JOM]

Thanks for the reply, so where do I put the statement?

Me.txtEmail.Requery or Me.Recalc. I forget which is correct/best.

--

Danny J. Lesandrini
[email protected]
http://amazecreations.com/datafast/

Thanks that worked perfect, I have another question though, after I have
clicked the update button, the Email address does not update immediately, I
have to click the Email address text box and then the email comes up, or if I
try to go to the next record, a write conflict message comes up. How do I
take care of this?

You'll need code like this on the button Click event ...

Dim lngID As Long
Dim strEmail As String
Dim strSQL As String

Const q As String = "'"

lngID = Nz(Me.[YourID],0)

strEmail = Replace(Nz([FirstName],"x"), ".","") & "."
strEmail = strEmail & Replace(Nz([LastName],"x"), ".","")
strEmail = strEmail & ""@Company.com"

strSQL = "UPDATE tblEmployee SET [EmailAdd] = " & q & strEmail & q
strSQL = strSQL & " WHERE [YourID]=" & lngID

CurrentDB.Execute strSQL, dbFailOnError

--

Danny J. Lesandrini
[email protected]
http://amazecreations.com/datafast


I would like to put a button on my form that when I press it runs the update
query and updates that record i.e., update the email address field if the
Firstname and lastname are not null. how do I do that?

My update query is as follows:

UPDATE tblemployee SET tblemployee.EmailAdd =
Replace(Replace([FirstName],".","") & "." & [LastName]," ",".") &
"@Company.com";

 

[JOM]

Thank you so much that helped.

Well, I suppose it wouldn't help to requery or recalc until after the value has
changed, so why not start out by putting it after the line that does the update.

CurrentDB.Execute strSQL, dbFailOnError
Me.txtEmail.Requery

--

Danny J. Lesandrini
[email protected]
http://amazecreations.com/datafast/

 

[JOM]

The query is not producing the same results as my query, this is what happens,
[email protected] = Email
Firstname = John W.
LastName = Smith
therefore Email = [email protected]
The one you sent me is not adding the . between John and w

I have tried to change it but its not working right....

Please help

 

[JOM]

With the query you have just provided, the result is as follows
John.A..Smith there is an extra . between A and Smith

Possible First names are as follows
John A.
John

So Emails should be John.A.Smith or John.Smith
***************************************

I thought your code was designed to remove that dot, but now
I see that you want to remove the spaces, right. Try the code
below, where I've changed only the two Replace() function calls.


Dim lngID As Long
Dim strEmail As String
Dim strSQL As String

Const q As String = "'"

lngID = Nz(Me.[YourID],0)

strEmail = Replace(Nz([FirstName],"x"), " ","") & "."
strEmail = strEmail & Replace(Nz([LastName],"x"), " ","")
strEmail = strEmail & ""@Company.com"

strSQL = "UPDATE tblEmployee SET [EmailAdd] = " & q & strEmail & q
strSQL = strSQL & " WHERE [YourID]=" & lngID

CurrentDB.Execute strSQL, dbFailOnError
Me.txtEmail.Requery



--

Danny J. Lesandrini
[email protected]
http://amazecreations.com/datafast

The query is not producing the same results as my query, this is what happens,
[email protected] = Email
Firstname = John W.
LastName = Smith
therefore Email = [email protected]
The one you sent me is not adding the . between John and w

I have tried to change it but its not working right....

Please help

 

[JOM]

Thanks for the help, I did try, and I have been playing around with it, but
was not able to figure out....

John:

I'm sorry. I was distracted with my work while I was answering your question,
so I didn't concentrate on it. However, if you concentrate on it a little, and I think
you can figure it out.

Play with the Replace() functions until they behave as you require. You may
even need to nest them. View it as a learning exercise. Here's the requirements
you need to meet:

step 1 Replace spaces with dots
John A. Smith becomes John.A..Smith
step 2 Replace double dots with single dots
John.A..Smith becomes John.A.Smith

Give a man a fish, and he eats for a day, teach a man to fish ...

Which reminds me of another truism:
Give a man a fire, and he keeps warm for a day
Set a man on fire, and he's warm for the rest of his life.
--

Danny J. Lesandrini
[email protected]
http://amazecreations.com/datafast

With the query you have just provided, the result is as follows
John.A..Smith there is an extra . between A and Smith

Possible First names are as follows
John A.
John

So Emails should be John.A.Smith or John.Smith
***************************************

I thought your code was designed to remove that dot, but now
I see that you want to remove the spaces, right. Try the code
below, where I've changed only the two Replace() function calls.


Dim lngID As Long
Dim strEmail As String
Dim strSQL As String

Const q As String = "'"

lngID = Nz(Me.[YourID],0)

strEmail = Replace(Nz([FirstName],"x"), " ","") & "."
strEmail = strEmail & Replace(Nz([LastName],"x"), " ","")
strEmail = strEmail & ""@Company.com"

strSQL = "UPDATE tblEmployee SET [EmailAdd] = " & q & strEmail & q
strSQL = strSQL & " WHERE [YourID]=" & lngID

CurrentDB.Execute strSQL, dbFailOnError
Me.txtEmail.Requery



--

Danny J. Lesandrini
[email protected]
http://amazecreations.com/datafast


The query is not producing the same results as my query, this is what happens,
[email protected] = Email
Firstname = John W.
LastName = Smith
therefore Email = [email protected]
The one you sent me is not adding the . between John and w

I have tried to change it but its not working right....

Please help


:

Well, I suppose it wouldn't help to requery or recalc until after the value has
changed, so why not start out by putting it after the line that does the update.

CurrentDB.Execute strSQL, dbFailOnError
Me.txtEmail.Requery

--

Danny J. Lesandrini
[email protected]
http://amazecreations.com/datafast/


Thanks for the reply, so where do I put the statement?