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

  • Thread starter Thread starter CM
  • Start date Start date
C

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
 
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.
 
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



Duane Hookom said:
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


CM said:
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
Click to expand...
Click to expand...
 
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


Duane Hookom said:
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


CM said:
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
Click to expand...
Click to expand...
 
Please show your code.

--
Duane Hookom
MS Access MVP


CM said:
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



Duane Hookom said:
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


CM said:
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
Click to expand...
Click to expand...
Click to expand...
 
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


CM said:
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


Duane Hookom said:
Please show your code.
Click to expand...
Duane Hookom
MS Access MVP

CM said:
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
Click to expand...
Click to expand...
Click to expand...
 
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


Duane Hookom said:
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


CM said:
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


Duane Hookom said:
Please show your code.
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
In your earlier postings there was no space in the table name CentralSystem.

--
Duane Hookom
MS Access MVP


CM said:
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


Duane Hookom said:
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


CM said:
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top