Update field question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have this statement:
DoCmd.RunSQL "UPDATE Patient_Registry SET Patient_Registry.[StudyTitle]
= " & Me.Studyholder & " WHERE Patient_Registry.ScreenID = '" &
Forms![NewStudy]![ScreenID] & "'"


When I run it a box comes up and whatever I have in Studyholder it has up at
the top of the little box. Then when I type something it updates the field.
What am I doing to make this box come up it should just update on its own
without being prompted.

Patient_Registry is the table which needs the update
StudyTitle is the field in Patient_Registy that needs the update
Studyholder is the text I would like to be updated
The current form is NewStudy
ScreenID is the specific place I would like it to be updated at.
 
Is the value for Studyholder a string? Try

DoCmd.RunSQL "UPDATE Patient_Registry SET Patient_Registry.[StudyTitle]
= '" & Me.Studyholder & "' WHERE Patient_Registry.ScreenID = '" &
Forms![NewStudy]![ScreenID] & "'"
Click to expand...


pokdbz said:
I have this statement:
DoCmd.RunSQL "UPDATE Patient_Registry SET Patient_Registry.[StudyTitle]
= " & Me.Studyholder & " WHERE Patient_Registry.ScreenID = '" &
Forms![NewStudy]![ScreenID] & "'"


When I run it a box comes up and whatever I have in Studyholder it has up at
the top of the little box. Then when I type something it updates the field.
What am I doing to make this box come up it should just update on its own
without being prompted.

Patient_Registry is the table which needs the update
StudyTitle is the field in Patient_Registy that needs the update
Studyholder is the text I would like to be updated
The current form is NewStudy
ScreenID is the specific place I would like it to be updated at.
Click to expand...
 
Is the value for Studyholder a string? Try

DoCmd.RunSQL "UPDATE Patient_Registry SET Patient_Registry.[StudyTitle]
= '" & Me.Studyholder & "' WHERE Patient_Registry.ScreenID = '" &
Forms![NewStudy]![ScreenID] & "'"
Click to expand...


pokdbz said:
I have this statement:
DoCmd.RunSQL "UPDATE Patient_Registry SET Patient_Registry.[StudyTitle]
= " & Me.Studyholder & " WHERE Patient_Registry.ScreenID = '" &
Forms![NewStudy]![ScreenID] & "'"


When I run it a box comes up and whatever I have in Studyholder it has up at
the top of the little box. Then when I type something it updates the field.
What am I doing to make this box come up it should just update on its own
without being prompted.

Patient_Registry is the table which needs the update
StudyTitle is the field in Patient_Registy that needs the update
Studyholder is the text I would like to be updated
The current form is NewStudy
ScreenID is the specific place I would like it to be updated at.
Click to expand...
 
This should work for you:
'********Code Begin

Dim strQry As String

strQry = "UPDATE Patient_Registry SET Patient_Registry.StudyTitle =
Forms!NewStudy.Studyholder WHERE Patient_Registry.ScreenID =
Forms!NewStudy.ScreenID"

DoCmd.RunSQL strQry

'********Code End

Make sure to put the strQry= line all on one line or break it up with
underscores
 
This should work for you:
'********Code Begin

Dim strQry As String

strQry = "UPDATE Patient_Registry SET Patient_Registry.StudyTitle =
Forms!NewStudy.Studyholder WHERE Patient_Registry.ScreenID =
Forms!NewStudy.ScreenID"

DoCmd.RunSQL strQry

'********Code End

Make sure to put the strQry= line all on one line or break it up with
underscores
 
The way you posted it:

DoCmd.RunSQL "UPDATE Patient_Registry SET Patient_Registry.[StudyTitle]
= " & Me.Studyholder & " WHERE Patient_Registry.ScreenID = '" &
Forms![NewStudy]![ScreenID] & "'"

The way it should work:

DoCmd.RunSQL "UPDATE Patient_Registry SET Patient_Registry.[StudyTitle]
= '" & Me.Studyholder & "' WHERE Patient_Registry.ScreenID = '" &
Forms![NewStudy]![ScreenID] & "'"

The only difference is putting the single-quotes in around Me.Studyholder,
as you did with ScreenID's comparison to Forms![NewStudy]![ScreenID].

If StudyTitle is a string field, and Me.Studyholder as well, this should
work.
 
The way you posted it:

DoCmd.RunSQL "UPDATE Patient_Registry SET Patient_Registry.[StudyTitle]
= " & Me.Studyholder & " WHERE Patient_Registry.ScreenID = '" &
Forms![NewStudy]![ScreenID] & "'"

The way it should work:

DoCmd.RunSQL "UPDATE Patient_Registry SET Patient_Registry.[StudyTitle]
= '" & Me.Studyholder & "' WHERE Patient_Registry.ScreenID = '" &
Forms![NewStudy]![ScreenID] & "'"

The only difference is putting the single-quotes in around Me.Studyholder,
as you did with ScreenID's comparison to Forms![NewStudy]![ScreenID].

If StudyTitle is a string field, and Me.Studyholder as well, this should
work.
 
even better(?)

Dim Db AS DAO.Database

Set Db = Access.CurrentDb
Db.Execute "....", DAO.DbSeeChanges ' for paranoid <g> backend's
MSSQL/Oracle


Pieter
(and yes I prefer QueryDefs with parameters et al)
 
even better(?)

Dim Db AS DAO.Database

Set Db = Access.CurrentDb
Db.Execute "....", DAO.DbSeeChanges ' for paranoid <g> backend's
MSSQL/Oracle


Pieter
(and yes I prefer QueryDefs with parameters et al)
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Field changed from number to text, how do I change my search? 3
Searching for record using query 8
Form value not being passed 1
Search for 2 fields get one result 13
How to combine first and last names 5
field inserted after rest of field, needs to be at same time 1
Updating tables when you input new data 0
Windows 11 Windows 11 update 5

Back
Top