Update query Still having probs

[CM]

Sorry Reposted due to reply being down list and may not be seen!! Thanks!


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

 

[Phobos]

Private Sub command7865_Click()
Dim strSQL As String
strSQL = "UPDATE Central System " & _
"SET [RepsName] = """ & Me.txtReplaceName & _
""" WHERE [RepsName] =""" & Me.cbofindname & """"

Need a space here^ (after the = character)


P

 

[CM]

Thanks Phobos,

It is still having a problem and highlights in yellow this bit "DoCmd.RunSQL
(strSQL)" I have tried also "DoCmd.RunSQL strSQL" both come up with prob in
Visual Basic (I have Version 6.3)

I am lost for ideas!!

Sorry Reposted due to reply being down list and may not be seen!! Thanks!


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

 

[John Spencer (MVP)]

Since your table name "Central System" has a space in it, you MUST surround the
name with brackets []. Otherwise Access will try to figure out what table is
Central and what is SYSTEM supposed to be after Central. It is a good idea to
avoid spaces and special characters #,?.<>, etc. in field and table names.
Access allows them, but you must surround the name with brackets in most cases.

strSQL = "UPDATE [Central System] " & _
"SET [RepsName] = """ & Me.txtReplaceName & _
""" WHERE [RepsName] =""" & Me.cbofindname & """"

MsgBox strSQL 'lets take a look at it

DoCmd.RunSQL (strSQL)

Sorry Reposted due to reply being down list and may not be seen!! Thanks!

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

 

[CM]

Thanks very much John, It works!!

Just another question, Is it possible to add to the code to make it change
the data in the table which holds the info for the drop down list at the
same time rather than doing two forms. It would make life easier! How would
I add to the code to point to update a different table name etc?

Any help with that would be appreciated.

Cheers

Colin

Since your table name "Central System" has a space in it, you MUST surround the
name with brackets []. Otherwise Access will try to figure out what table is
Central and what is SYSTEM supposed to be after Central. It is a good idea to
avoid spaces and special characters #,?.<>, etc. in field and table names.
Access allows them, but you must surround the name with brackets in most cases.

strSQL = "UPDATE [Central System] " & _
"SET [RepsName] = """ & Me.txtReplaceName & _
""" WHERE [RepsName] =""" & Me.cbofindname & """"

MsgBox strSQL 'lets take a look at it

DoCmd.RunSQL (strSQL)

Sorry Reposted due to reply being down list and may not be seen!! Thanks!

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

 

[John Spencer (MVP)]

Sorry, I really don't understand what you are asking.

Thanks very much John, It works!!

Just another question, Is it possible to add to the code to make it change
the data in the table which holds the info for the drop down list at the
same time rather than doing two forms. It would make life easier! How would
I add to the code to point to update a different table name etc?

Any help with that would be appreciated.

Cheers

Colin

Since your table name "Central System" has a space in it, you MUST surround the
name with brackets []. Otherwise Access will try to figure out what table is
Central and what is SYSTEM supposed to be after Central. It is a good idea to
avoid spaces and special characters #,?.<>, etc. in field and table names.
Access allows them, but you must surround the name with brackets in most cases.

strSQL = "UPDATE [Central System] " & _
"SET [RepsName] = """ & Me.txtReplaceName & _
""" WHERE [RepsName] =""" & Me.cbofindname & """"

MsgBox strSQL 'lets take a look at it

DoCmd.RunSQL (strSQL)

Sorry Reposted due to reply being down list and may not be seen!! Thanks!

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

 

[CM]

Sorry John, I will try and be a bit clearer...

I have a form that will change the data in access table (i.e a Reps Name) it
will select the old rep from "tblrepsname" which gives the drop down list to
select the old rep. Then I type in a new name for the new rep in box 2,
click update button and it updates the data in a table.

What I then have done is made a similar form which will update the
Tblrepsname (Which holds the data of reps names) to replace the date, so
that the new rep is shown on the drop down boxes also. I have put a msgbox
to remind them that they have to alter the dat first otherwise it will not
allow any changes.

Is there a way it will merge the procedure into one. I.e once it updates the
data, it would update a second table which holds the dropdown boxes?

I hope this is clearer???

Thanks John

Regards,

Colin

Sorry, I really don't understand what you are asking.

Thanks very much John, It works!!

Just another question, Is it possible to add to the code to make it change
the data in the table which holds the info for the drop down list at the
same time rather than doing two forms. It would make life easier! How would
I add to the code to point to update a different table name etc?

Any help with that would be appreciated.

Cheers

Colin

Since your table name "Central System" has a space in it, you MUST surround the
name with brackets []. Otherwise Access will try to figure out what

table
is

Central and what is SYSTEM supposed to be after Central. It is a good idea to
avoid spaces and special characters #,?.<>, etc. in field and table names.
Access allows them, but you must surround the name with brackets in

most
cases.

strSQL = "UPDATE [Central System] " & _
"SET [RepsName] = """ & Me.txtReplaceName & _
""" WHERE [RepsName] =""" & Me.cbofindname & """"

MsgBox strSQL 'lets take a look at it

DoCmd.RunSQL (strSQL)



CM wrote:

Sorry Reposted due to reply being down list and may not be seen!! Thanks!

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