Update Query....is it possible to....

[CM]

Hi,

I have an update query that I presently make it come up with a box to enter
new name and then enter old name before updating the database.

Is it possible to select the old name in a combo box type from the table
that has all these names inputted? Its called tbl-names

Any help with the right coding would be appreciated.

Cheers

Colin

 

[Duane Hookom]

It is very dangerous to do this with a parameter query where a box will
"come up". You should consider creating a small form with two text boxes.
One to select the name to replace and the other to enter the new name.
Assume frmReplaceName, cboFindName, and txtReplaceWith. Add a command button
that has the following code:

Dim strSQL as String
strSQL = "UPDATE tblYourTable " & _
"SET [YourNameField] = """ & Me.txtReplaceWith & _
""" WHERE [YourNameField] =""" & Me.cboFindName & """"
DoCmd.RunSQL strSQL

I would also add code to make sure there were values in both controls.

 

[CM]

Thanks Duane..

I am having difficulties with what you suggested,

I have a form called "UpdateRepsName"
The field to amend is called "repsname"
The Table with the info is called "CentralSystem"
Command Button Name "Commandfindname"
The form has two text boxes, one called "TxtRepsName" which has the data
from the correct source (Works fine!)
The other box is named "newentry1"

It keeps coming up with errors, is there something stupid I am doing wrong,
it especially brings up
DoCmd.RunSQL strSQL as an error.

I am quite new at these things, so perhaps its just me!!!

Cheers

Colin

It is very dangerous to do this with a parameter query where a box will
"come up". You should consider creating a small form with two text boxes.
One to select the name to replace and the other to enter the new name.
Assume frmReplaceName, cboFindName, and txtReplaceWith. Add a command button
that has the following code:

Dim strSQL as String
strSQL = "UPDATE tblYourTable " & _
"SET [YourNameField] = """ & Me.txtReplaceWith & _
""" WHERE [YourNameField] =""" & Me.cboFindName & """"
DoCmd.RunSQL strSQL

I would also add code to make sure there were values in both controls.

--
Duane Hookom
MS Access MVP

Hi,

I have an update query that I presently make it come up with a box to enter
new name and then enter old name before updating the database.

Is it possible to select the old name in a combo box type from the table
that has all these names inputted? Its called tbl-names

Any help with the right coding would be appreciated.

Cheers

Colin

 

[CM]

Thanks Duane..

I am having difficulties with what you suggested,

I have a form called "UpdateRepsName"
The field to amend is called "repsname"
The Table with the info is called "CentralSystem"
Command Button Name "Commandfindname"
The form has two text boxes, one called "TxtRepsName" which has the data
from the correct source (Works fine!)
The other box is named "newentry1"

It keeps coming up with errors, is there something stupid I am doing wrong,
it especially brings up
DoCmd.RunSQL strSQL as an error.

I am quite new at these things, so perhaps its just me!!!

Cheers

Colin

It is very dangerous to do this with a parameter query where a box will
"come up". You should consider creating a small form with two text boxes.
One to select the name to replace and the other to enter the new name.
Assume frmReplaceName, cboFindName, and txtReplaceWith. Add a command button
that has the following code:

Dim strSQL as String
strSQL = "UPDATE tblYourTable " & _
"SET [YourNameField] = """ & Me.txtReplaceWith & _
""" WHERE [YourNameField] =""" & Me.cboFindName & """"
DoCmd.RunSQL strSQL

I would also add code to make sure there were values in both controls.

--
Duane Hookom
MS Access MVP

Hi,

I have an update query that I presently make it come up with a box to enter
new name and then enter old name before updating the database.

Is it possible to select the old name in a combo box type from the table
that has all these names inputted? Its called tbl-names

Any help with the right coding would be appreciated.

Cheers

Colin

 

[Duane Hookom]

Please show your code.

--
Duane Hookom
MS Access MVP

Thanks Duane..

I am having difficulties with what you suggested,

I have a form called "UpdateRepsName"
The field to amend is called "repsname"
The Table with the info is called "CentralSystem"
Command Button Name "Commandfindname"
The form has two text boxes, one called "TxtRepsName" which has the data
from the correct source (Works fine!)
The other box is named "newentry1"

It keeps coming up with errors, is there something stupid I am doing wrong,
it especially brings up
DoCmd.RunSQL strSQL as an error.

I am quite new at these things, so perhaps its just me!!!

Cheers

Colin

It is very dangerous to do this with a parameter query where a box will
"come up". You should consider creating a small form with two text boxes.
One to select the name to replace and the other to enter the new name.
Assume frmReplaceName, cboFindName, and txtReplaceWith. Add a command button
that has the following code:

Dim strSQL as String
strSQL = "UPDATE tblYourTable " & _
"SET [YourNameField] = """ & Me.txtReplaceWith & _
""" WHERE [YourNameField] =""" & Me.cboFindName & """"
DoCmd.RunSQL strSQL

I would also add code to make sure there were values in both controls.

--
Duane Hookom
MS Access MVP

Hi,

I have an update query that I presently make it come up with a box to enter
new name and then enter old name before updating the database.

Is it possible to select the old name in a combo box type from the table
that has all these names inputted? Its called tbl-names

Any help with the right coding would be appreciated.

Cheers

Colin

 

[Duane Hookom]

I think you need to add a space after CentralSystem
Dim strSQL as String
strSQL = "UPDATE CentralSystem " & _
"SET [repsname] = """ & Me.txtReplaceWith & _
""" WHERE [repsname] =""" & Me.cboRepsList & """"
MsgBox strSQL 'lets take a look at it
DoCmd.RunSQL strSQL


--
Duane Hookom
MS Access MVP

Hi Duane,

Thanks for the quick reply.. Here it is... It is put behind the command
button expression under onclick.

Cheers

Dim strSQL as String
strSQL = "UPDATE CentralSystem" & _
"SET [repsname] = """ & Me.txtReplaceWith & _
""" WHERE [repsname] =""" & Me.cboRepsList & """"
DoCmd.RunSQL strSQL

Please show your code.

Duane Hookom
MS Access MVP

Thanks Duane..

I am having difficulties with what you suggested,
I have a form called "UpdateRepsName"
The field to amend is called "repsname"
The Table with the info is called "CentralSystem"
Command Button Name "Commandfindname"
The form has two text boxes, one called "TxtRepsName" which has the data
from the correct source (Works fine!)
The other box is named "newentry1"

It keeps coming up with errors, is there something stupid I am doing wrong,
it especially brings up
DoCmd.RunSQL strSQL as an error.

I am quite new at these things, so perhaps its just me!!!

Cheers

Colin



It is very dangerous to do this with a parameter query where a box will
"come up". You should consider creating a small form with two text boxes.
One to select the name to replace and the other to enter the new name.
Assume frmReplaceName, cboFindName, and txtReplaceWith. Add a command
button
that has the following code:

Dim strSQL as String
strSQL = "UPDATE tblYourTable " & _
"SET [YourNameField] = """ & Me.txtReplaceWith & _
""" WHERE [YourNameField] =""" & Me.cboFindName & """"
DoCmd.RunSQL strSQL

I would also add code to make sure there were values in both controls.

--
Duane Hookom
MS Access MVP


Hi,

I have an update query that I presently make it come up with a box to
enter
new name and then enter old name before updating the database.

Is it possible to select the old name in a combo box type from the table
that has all these names inputted? Its called tbl-names

Any help with the right coding would be appreciated.

Cheers

Colin

 

[CM]

Hi

I am still having problems with this coding, It now comes up with a box
saying replace XXXXXX with XXXXX etc but it doesn't actually perform the
function?
I get Syntax error in update statement Runtime error 3144 ? I think its
almost there but something needs altering somewhere???

Any help would be appreciated...

Thanks

Colin



Private Sub command7865_Click()
Dim strSQL As String
strSQL = "UPDATE Central System " & _
"SET [RepsName] = """ & Me.txtReplaceName & _
""" WHERE [RepsName] =""" & Me.cbofindname & """"
MsgBox strSQL 'lets take a look at it

DoCmd.RunSQL (strSQL)

End Sub

I think you need to add a space after CentralSystem
Dim strSQL as String
strSQL = "UPDATE CentralSystem " & _
"SET [repsname] = """ & Me.txtReplaceWith & _
""" WHERE [repsname] =""" & Me.cboRepsList & """"
MsgBox strSQL 'lets take a look at it
DoCmd.RunSQL strSQL


--
Duane Hookom
MS Access MVP

Hi Duane,

Thanks for the quick reply.. Here it is... It is put behind the command
button expression under onclick.

Cheers

Dim strSQL as String
strSQL = "UPDATE CentralSystem" & _
"SET [repsname] = """ & Me.txtReplaceWith & _
""" WHERE [repsname] =""" & Me.cboRepsList & """"
DoCmd.RunSQL strSQL

Please show your code.

Duane Hookom
MS Access MVP

Thanks Duane..

I am having difficulties with what you suggested,
I have a form called "UpdateRepsName"
The field to amend is called "repsname"
The Table with the info is called "CentralSystem"
Command Button Name "Commandfindname"
The form has two text boxes, one called "TxtRepsName" which has the data
from the correct source (Works fine!)
The other box is named "newentry1"

It keeps coming up with errors, is there something stupid I am doing
wrong,
it especially brings up
DoCmd.RunSQL strSQL as an error.

I am quite new at these things, so perhaps its just me!!!

Cheers

Colin



It is very dangerous to do this with a parameter query where a box will
"come up". You should consider creating a small form with two text
boxes.
One to select the name to replace and the other to enter the new name.
Assume frmReplaceName, cboFindName, and txtReplaceWith. Add a command
button
that has the following code:

Dim strSQL as String
strSQL = "UPDATE tblYourTable " & _
"SET [YourNameField] = """ & Me.txtReplaceWith & _
""" WHERE [YourNameField] =""" & Me.cboFindName & """"
DoCmd.RunSQL strSQL

I would also add code to make sure there were values in both controls.

--
Duane Hookom
MS Access MVP


Hi,

I have an update query that I presently make it come up with a

box
to

enter
new name and then enter old name before updating the database.

Is it possible to select the old name in a combo box type from the
table
that has all these names inputted? Its called tbl-names

Any help with the right coding would be appreciated.

Cheers

Colin

 

[Duane Hookom]

In your earlier postings there was no space in the table name CentralSystem.

--
Duane Hookom
MS Access MVP

Hi

I am still having problems with this coding, It now comes up with a box
saying replace XXXXXX with XXXXX etc but it doesn't actually perform the
function?
I get Syntax error in update statement Runtime error 3144 ? I think its
almost there but something needs altering somewhere???

Any help would be appreciated...

Thanks

Colin



Private Sub command7865_Click()
Dim strSQL As String
strSQL = "UPDATE Central System " & _
"SET [RepsName] = """ & Me.txtReplaceName & _
""" WHERE [RepsName] =""" & Me.cbofindname & """"
MsgBox strSQL 'lets take a look at it

DoCmd.RunSQL (strSQL)

End Sub

I think you need to add a space after CentralSystem
Dim strSQL as String
strSQL = "UPDATE CentralSystem " & _
"SET [repsname] = """ & Me.txtReplaceWith & _
""" WHERE [repsname] =""" & Me.cboRepsList & """"
MsgBox strSQL 'lets take a look at it
DoCmd.RunSQL strSQL


--
Duane Hookom
MS Access MVP

Hi Duane,

Thanks for the quick reply.. Here it is... It is put behind the command
button expression under onclick.

Cheers

Dim strSQL as String
strSQL = "UPDATE CentralSystem" & _
"SET [repsname] = """ & Me.txtReplaceWith & _
""" WHERE [repsname] =""" & Me.cboRepsList & """"
DoCmd.RunSQL strSQL


Please show your code.

Duane Hookom
MS Access MVP

Thanks Duane..

I am having difficulties with what you suggested,
I have a form called "UpdateRepsName"
The field to amend is called "repsname"
The Table with the info is called "CentralSystem"
Command Button Name "Commandfindname"
The form has two text boxes, one called "TxtRepsName" which has

the
data

from the correct source (Works fine!)
The other box is named "newentry1"

It keeps coming up with errors, is there something stupid I am doing
wrong,
it especially brings up
DoCmd.RunSQL strSQL as an error.

I am quite new at these things, so perhaps its just me!!!

Cheers

Colin



It is very dangerous to do this with a parameter query where a box
will
"come up". You should consider creating a small form with two text
boxes.
One to select the name to replace and the other to enter the new name.
Assume frmReplaceName, cboFindName, and txtReplaceWith. Add a command
button
that has the following code:

Dim strSQL as String
strSQL = "UPDATE tblYourTable " & _
"SET [YourNameField] = """ & Me.txtReplaceWith & _
""" WHERE [YourNameField] =""" & Me.cboFindName & """"
DoCmd.RunSQL strSQL

I would also add code to make sure there were values in both controls.

--
Duane Hookom
MS Access MVP


Hi,

I have an update query that I presently make it come up with a box
to
enter
new name and then enter old name before updating the database.

Is it possible to select the old name in a combo box type from the
table
that has all these names inputted? Its called tbl-names

Any help with the right coding would be appreciated.

Cheers

Colin