Update Correct Records in Table; Based on Certain ID

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

I am using the following code to make updates to my Table named ‘PatientTable’:
Option Compare Database
Option Explicit
Dim cboOriginator As TextBox

Private Sub Command19_Click()

Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstPatientTable As ADODB.Recordset
Dim strCnn As String

Dim mydb As String
If err < 1 Then

Set cnn1 = New ADODB.Connection
mydb = "C:\RTDA Tool.mdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
cnn1.Open strCnn

Set rstPatientTable = New ADODB.Recordset
rstPatientTable.CursorType = adOpenKeyset
rstPatientTable.LockType = adLockOptimistic
rstPatientTable.Open "PatientTable", cnn1, , , adCmdTable

rstPatientTable.Update

rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update

MsgBox "Patient: " & Me![FirstName] & " " & Me![LastName] & " has
been successfully updated!!"

rstPatientTable.Close
cnn1.Close
Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub


When the macro fires, the code runs and it DOES update the Table, but the
updates always occur on the first record of the Table, it doesn’t update the
correct record. There must be a simple solution for this, but I don’t know
where to begin. On my Form I have a TextBox named ‘MR’; it is an ID. When I
type in an MR number, several TextBoxes on my Form update automatically.
This is fine! I can see a patient’s status, and add some new information via
several TextBoxes, the problem is that when the code fires, the updates
aren’t made to the correct records. How can I ensure that updates are made
to the appropriate record? The appropriate record is the number in the MR
TetxBox?

I’ve been working on this project most of the day; this is the last piece of
the puzzle. I would really appreciate it if someone could help me finish
this off!!

Thanks so much!!
Ryan---
 
Hi,
try this:

rstPatientTable.Open "Select * from PatientTable Where ID=" & me!MR, cnn1
if not rstPatientTable.EOF then
rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update
end if

You can also consider using update query, just one line of code to run it.

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
 
Thanks Alex! I think this is close, but it doesn't quite work; the code
fails here.
rstPatientTable.Open "Select * From PatientTable Where ID = " & Me!MR,
cnn1, , , adCmdTable

I get a message that says 'Syntax error in FROM clause'. What am I doing
wrong? Also ,what is the Update Query that you mentioned before? I've used
Update Queries lots of times before; just doing simple things though. I am
not sure how to implement an Update Query into this code, but I would be very
grateful if you show me how!!

Thanks,
Ryan---



--
RyGuy


Alex Dybenko said:
Hi,
try this:

rstPatientTable.Open "Select * from PatientTable Where ID=" & me!MR, cnn1
if not rstPatientTable.EOF then
rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update
end if

You can also consider using update query, just one line of code to run it.

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

ryguy7272 said:
I am using the following code to make updates to my Table named
‘PatientTable’:
Option Compare Database
Option Explicit
Dim cboOriginator As TextBox

Private Sub Command19_Click()

Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstPatientTable As ADODB.Recordset
Dim strCnn As String

Dim mydb As String
If err < 1 Then

Set cnn1 = New ADODB.Connection
mydb = "C:\RTDA Tool.mdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
cnn1.Open strCnn

Set rstPatientTable = New ADODB.Recordset
rstPatientTable.CursorType = adOpenKeyset
rstPatientTable.LockType = adLockOptimistic
rstPatientTable.Open "PatientTable", cnn1, , , adCmdTable

rstPatientTable.Update

rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update

MsgBox "Patient: " & Me![FirstName] & " " & Me![LastName] & " has
been successfully updated!!"

rstPatientTable.Close
cnn1.Close
Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub


When the macro fires, the code runs and it DOES update the Table, but the
updates always occur on the first record of the Table, it doesn’t update
the
correct record. There must be a simple solution for this, but I don’t
know
where to begin. On my Form I have a TextBox named ‘MR’; it is an ID.
When I
type in an MR number, several TextBoxes on my Form update automatically.
This is fine! I can see a patient’s status, and add some new information
via
several TextBoxes, the problem is that when the code fires, the updates
aren’t made to the correct records. How can I ensure that updates are
made
to the appropriate record? The appropriate record is the number in the MR
TetxBox?

I’ve been working on this project most of the day; this is the last piece
of
the puzzle. I would really appreciate it if someone could help me finish
this off!!

Thanks so much!!
Ryan---
Click to expand...
Click to expand...
 
Hi,
should be:
rstPatientTable.Open "Select * From PatientTable Where ID = " & Me!MR, cnn1

note that adCmdTable is omitted
also check that table/field names are correct

as for update query - run query designer, add PatientTable, change type to
update - then you can define what fields you want to update to form's
control values (expression builder will help here), also add a filter by ID

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


ryguy7272 said:
Thanks Alex! I think this is close, but it doesn't quite work; the code
fails here.
rstPatientTable.Open "Select * From PatientTable Where ID = " & Me!MR,
cnn1, , , adCmdTable

I get a message that says 'Syntax error in FROM clause'. What am I
doing
wrong? Also ,what is the Update Query that you mentioned before? I've
used
Update Queries lots of times before; just doing simple things though. I
am
not sure how to implement an Update Query into this code, but I would be
very
grateful if you show me how!!

Thanks,
Ryan---



--
RyGuy


Alex Dybenko said:
Hi,
try this:

rstPatientTable.Open "Select * from PatientTable Where ID=" & me!MR, cnn1
if not rstPatientTable.EOF then
rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update
end if

You can also consider using update query, just one line of code to run
it.

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

ryguy7272 said:
I am using the following code to make updates to my Table named
‘PatientTable’:
Option Compare Database
Option Explicit
Dim cboOriginator As TextBox

Private Sub Command19_Click()

Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstPatientTable As ADODB.Recordset
Dim strCnn As String

Dim mydb As String
If err < 1 Then

Set cnn1 = New ADODB.Connection
mydb = "C:\RTDA Tool.mdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
cnn1.Open strCnn

Set rstPatientTable = New ADODB.Recordset
rstPatientTable.CursorType = adOpenKeyset
rstPatientTable.LockType = adLockOptimistic
rstPatientTable.Open "PatientTable", cnn1, , , adCmdTable

rstPatientTable.Update

rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update

MsgBox "Patient: " & Me![FirstName] & " " & Me![LastName] & "
has
been successfully updated!!"

rstPatientTable.Close
cnn1.Close
Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub


When the macro fires, the code runs and it DOES update the Table, but
the
updates always occur on the first record of the Table, it doesn’t
update
the
correct record. There must be a simple solution for this, but I don’t
know
where to begin. On my Form I have a TextBox named ‘MR’; it is an ID.
When I
type in an MR number, several TextBoxes on my Form update
automatically.
This is fine! I can see a patient’s status, and add some new
information
via
several TextBoxes, the problem is that when the code fires, the updates
aren’t made to the correct records. How can I ensure that updates are
made
to the appropriate record? The appropriate record is the number in the
MR
TetxBox?

I’ve been working on this project most of the day; this is the last
piece
of
the puzzle. I would really appreciate it if someone could help me
finish
this off!!

Thanks so much!!
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
Hummm, I am definitely doing something wrong here. I keep getting a message
that says run-time error. No value given for one or more parameters.

I tried several things, but couldn’t resolve it.

Also, I am now thinking the Update Query will be my best bet for this task.
I worked with that for a bit this AM, but couldn’t get that working either.

This is my SQL for the Update Query:
UPDATE PatientTable SET PatientTable.SIM_Comments = Forms!SimForm!SIM_Comments
WHERE (([SIM_Comments]<>"") AND ((PatientTable.MR)=[MR]));

Field = SIM_Comments
Table = PatientTable
Update To = Forms!SimForm!SIM_Comments
Criteria = <>â€â€
also
Field = MR
Table = Patient Table
Update To =
Criteria = [MR]

I think this Criteria has to be MR, because I have to reference the
appropriate MR in the Table, based on the MR in the Form and then update the
correct SIM_Comments in the Table, based on this MR. Is that right?

In any event, when I hit the ‘bang’ button, I get prompted for a Parameter,
which doesn’t seem right at all, and then zero records are updated.

Here is my VBA code now:
Private Sub Command19_Click()

Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstPatientTable As ADODB.Recordset
Dim strCnn As String
'Dim mydb As DAO.Database
Dim mydb As String


'if no errors insert data
If err < 1 Then
' Open a connection.
Set cnn1 = New ADODB.Connection
mydb = "C:\RTDA Tool.mdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
cnn1.Open strCnn

Set rstPatientTable = New ADODB.Recordset
rstPatientTable.CursorType = adOpenKeyset
rstPatientTable.LockType = adLockOptimistic
rstPatientTable.Open "Select * From PatientTable Where ID = " & Me!MR,
cnn1
If Not rstPatientTable.EOF Then
rstPatientTable.Update
rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update
End If

MsgBox "Patient: " & Me![FirstName] & " " & Me![LastName] & " has
been successfully updated!!"

rstPatientTable.Close
cnn1.Close


Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub

How can I get the SQL working and then pop that into my VBA code?
This is the last part of this project; I hope I can finish soon.

Thanks,
Ryan---



--
RyGuy


Alex Dybenko said:
Hi,
should be:
rstPatientTable.Open "Select * From PatientTable Where ID = " & Me!MR, cnn1

note that adCmdTable is omitted
also check that table/field names are correct

as for update query - run query designer, add PatientTable, change type to
update - then you can define what fields you want to update to form's
control values (expression builder will help here), also add a filter by ID

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


ryguy7272 said:
Thanks Alex! I think this is close, but it doesn't quite work; the code
fails here.
rstPatientTable.Open "Select * From PatientTable Where ID = " & Me!MR,
cnn1, , , adCmdTable

I get a message that says 'Syntax error in FROM clause'. What am I
doing
wrong? Also ,what is the Update Query that you mentioned before? I've
used
Update Queries lots of times before; just doing simple things though. I
am
not sure how to implement an Update Query into this code, but I would be
very
grateful if you show me how!!

Thanks,
Ryan---



--
RyGuy


Alex Dybenko said:
Hi,
try this:

rstPatientTable.Open "Select * from PatientTable Where ID=" & me!MR, cnn1
if not rstPatientTable.EOF then
rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update
end if

You can also consider using update query, just one line of code to run
it.

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

I am using the following code to make updates to my Table named
‘PatientTable’:
Option Compare Database
Option Explicit
Dim cboOriginator As TextBox

Private Sub Command19_Click()

Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstPatientTable As ADODB.Recordset
Dim strCnn As String

Dim mydb As String
If err < 1 Then

Set cnn1 = New ADODB.Connection
mydb = "C:\RTDA Tool.mdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
cnn1.Open strCnn

Set rstPatientTable = New ADODB.Recordset
rstPatientTable.CursorType = adOpenKeyset
rstPatientTable.LockType = adLockOptimistic
rstPatientTable.Open "PatientTable", cnn1, , , adCmdTable

rstPatientTable.Update

rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update

MsgBox "Patient: " & Me![FirstName] & " " & Me![LastName] & "
has
been successfully updated!!"

rstPatientTable.Close
cnn1.Close
Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub


When the macro fires, the code runs and it DOES update the Table, but
the
updates always occur on the first record of the Table, it doesn’t
update
the
correct record. There must be a simple solution for this, but I don’t
know
where to begin. On my Form I have a TextBox named ‘MR’; it is an ID.
When I
type in an MR number, several TextBoxes on my Form update
automatically.
This is fine! I can see a patient’s status, and add some new
information
via
several TextBoxes, the problem is that when the code fires, the updates
aren’t made to the correct records. How can I ensure that updates are
made
to the appropriate record? The appropriate record is the number in the
MR
TetxBox?

I’ve been working on this project most of the day; this is the last
piece
of
the puzzle. I would really appreciate it if someone could help me
finish
this off!!

Thanks so much!!
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
Something is causing an error; the SQL is the culprit, I guess. Could it be
something like this?
rstPatientTable.Open "Select * FROM PatientTable Where MR = " &
Me!MR & ";", cnn1, , , adCmdTable

....that doesn't work, but looks kind of close as far as I can tell.

The MR in my Query needs to equal the Me!MR on the Form. the logic seems
right; I don't know why it doesn't work though...

Any thoughts?

Thanks,
Ryan---

--
RyGuy


ryguy7272 said:
Hummm, I am definitely doing something wrong here. I keep getting a message
that says run-time error. No value given for one or more parameters.

I tried several things, but couldn’t resolve it.

Also, I am now thinking the Update Query will be my best bet for this task.
I worked with that for a bit this AM, but couldn’t get that working either.

This is my SQL for the Update Query:
UPDATE PatientTable SET PatientTable.SIM_Comments = Forms!SimForm!SIM_Comments
WHERE (([SIM_Comments]<>"") AND ((PatientTable.MR)=[MR]));

Field = SIM_Comments
Table = PatientTable
Update To = Forms!SimForm!SIM_Comments
Criteria = <>â€â€
also
Field = MR
Table = Patient Table
Update To =
Criteria = [MR]

I think this Criteria has to be MR, because I have to reference the
appropriate MR in the Table, based on the MR in the Form and then update the
correct SIM_Comments in the Table, based on this MR. Is that right?

In any event, when I hit the ‘bang’ button, I get prompted for a Parameter,
which doesn’t seem right at all, and then zero records are updated.

Here is my VBA code now:
Private Sub Command19_Click()

Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstPatientTable As ADODB.Recordset
Dim strCnn As String
'Dim mydb As DAO.Database
Dim mydb As String


'if no errors insert data
If err < 1 Then
' Open a connection.
Set cnn1 = New ADODB.Connection
mydb = "C:\RTDA Tool.mdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
cnn1.Open strCnn

Set rstPatientTable = New ADODB.Recordset
rstPatientTable.CursorType = adOpenKeyset
rstPatientTable.LockType = adLockOptimistic
rstPatientTable.Open "Select * From PatientTable Where ID = " & Me!MR,
cnn1
If Not rstPatientTable.EOF Then
rstPatientTable.Update
rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update
End If

MsgBox "Patient: " & Me![FirstName] & " " & Me![LastName] & " has
been successfully updated!!"

rstPatientTable.Close
cnn1.Close


Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub

How can I get the SQL working and then pop that into my VBA code?
This is the last part of this project; I hope I can finish soon.

Thanks,
Ryan---



--
RyGuy


Alex Dybenko said:
Hi,
should be:
rstPatientTable.Open "Select * From PatientTable Where ID = " & Me!MR, cnn1

note that adCmdTable is omitted
also check that table/field names are correct

as for update query - run query designer, add PatientTable, change type to
update - then you can define what fields you want to update to form's
control values (expression builder will help here), also add a filter by ID

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


ryguy7272 said:
Thanks Alex! I think this is close, but it doesn't quite work; the code
fails here.
rstPatientTable.Open "Select * From PatientTable Where ID = " & Me!MR,
cnn1, , , adCmdTable

I get a message that says 'Syntax error in FROM clause'. What am I
doing
wrong? Also ,what is the Update Query that you mentioned before? I've
used
Update Queries lots of times before; just doing simple things though. I
am
not sure how to implement an Update Query into this code, but I would be
very
grateful if you show me how!!

Thanks,
Ryan---



--
RyGuy


:

Hi,
try this:

rstPatientTable.Open "Select * from PatientTable Where ID=" & me!MR, cnn1
if not rstPatientTable.EOF then
rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update
end if

You can also consider using update query, just one line of code to run
it.

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

I am using the following code to make updates to my Table named
‘PatientTable’:
Option Compare Database
Option Explicit
Dim cboOriginator As TextBox

Private Sub Command19_Click()

Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstPatientTable As ADODB.Recordset
Dim strCnn As String

Dim mydb As String
If err < 1 Then

Set cnn1 = New ADODB.Connection
mydb = "C:\RTDA Tool.mdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
cnn1.Open strCnn

Set rstPatientTable = New ADODB.Recordset
rstPatientTable.CursorType = adOpenKeyset
rstPatientTable.LockType = adLockOptimistic
rstPatientTable.Open "PatientTable", cnn1, , , adCmdTable

rstPatientTable.Update

rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update

MsgBox "Patient: " & Me![FirstName] & " " & Me![LastName] & "
has
been successfully updated!!"

rstPatientTable.Close
cnn1.Close
Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub


When the macro fires, the code runs and it DOES update the Table, but
the
updates always occur on the first record of the Table, it doesn’t
update
the
correct record. There must be a simple solution for this, but I don’t
know
where to begin. On my Form I have a TextBox named ‘MR’; it is an ID.
When I
type in an MR number, several TextBoxes on my Form update
automatically.
This is fine! I can see a patient’s status, and add some new
information
via
several TextBoxes, the problem is that when the code fires, the updates
aren’t made to the correct records. How can I ensure that updates are
made
to the appropriate record? The appropriate record is the number in the
MR
TetxBox?

I’ve been working on this project most of the day; this is the last
piece
of
the puzzle. I would really appreciate it if someone could help me
finish
this off!!

Thanks so much!!
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
I just created a query that seems to do what I want. This is the SQL:
rstPatientTable.Open "SELECT * FROM PatientTable WHERE
(((PatientTable.MR)= " & Me!MR & "));", cnn1, , , adCmdTable

I copied/pasted it into a my code and it still doesn't work!!

What am I doing wrong?

--
RyGuy


ryguy7272 said:
Something is causing an error; the SQL is the culprit, I guess. Could it be
something like this?
rstPatientTable.Open "Select * FROM PatientTable Where MR = " &
Me!MR & ";", cnn1, , , adCmdTable

...that doesn't work, but looks kind of close as far as I can tell.

The MR in my Query needs to equal the Me!MR on the Form. the logic seems
right; I don't know why it doesn't work though...

Any thoughts?

Thanks,
Ryan---

--
RyGuy


ryguy7272 said:
Hummm, I am definitely doing something wrong here. I keep getting a message
that says run-time error. No value given for one or more parameters.

I tried several things, but couldn’t resolve it.

Also, I am now thinking the Update Query will be my best bet for this task.
I worked with that for a bit this AM, but couldn’t get that working either.

This is my SQL for the Update Query:
UPDATE PatientTable SET PatientTable.SIM_Comments = Forms!SimForm!SIM_Comments
WHERE (([SIM_Comments]<>"") AND ((PatientTable.MR)=[MR]));

Field = SIM_Comments
Table = PatientTable
Update To = Forms!SimForm!SIM_Comments
Criteria = <>â€â€
also
Field = MR
Table = Patient Table
Update To =
Criteria = [MR]

I think this Criteria has to be MR, because I have to reference the
appropriate MR in the Table, based on the MR in the Form and then update the
correct SIM_Comments in the Table, based on this MR. Is that right?

In any event, when I hit the ‘bang’ button, I get prompted for a Parameter,
which doesn’t seem right at all, and then zero records are updated.

Here is my VBA code now:
Private Sub Command19_Click()

Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstPatientTable As ADODB.Recordset
Dim strCnn As String
'Dim mydb As DAO.Database
Dim mydb As String


'if no errors insert data
If err < 1 Then
' Open a connection.
Set cnn1 = New ADODB.Connection
mydb = "C:\RTDA Tool.mdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
cnn1.Open strCnn

Set rstPatientTable = New ADODB.Recordset
rstPatientTable.CursorType = adOpenKeyset
rstPatientTable.LockType = adLockOptimistic
rstPatientTable.Open "Select * From PatientTable Where ID = " & Me!MR,
cnn1
If Not rstPatientTable.EOF Then
rstPatientTable.Update
rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update
End If

MsgBox "Patient: " & Me![FirstName] & " " & Me![LastName] & " has
been successfully updated!!"

rstPatientTable.Close
cnn1.Close


Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub

How can I get the SQL working and then pop that into my VBA code?
This is the last part of this project; I hope I can finish soon.

Thanks,
Ryan---



--
RyGuy


Alex Dybenko said:
Hi,
should be:
rstPatientTable.Open "Select * From PatientTable Where ID = " & Me!MR, cnn1

note that adCmdTable is omitted
also check that table/field names are correct

as for update query - run query designer, add PatientTable, change type to
update - then you can define what fields you want to update to form's
control values (expression builder will help here), also add a filter by ID

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


Thanks Alex! I think this is close, but it doesn't quite work; the code
fails here.
rstPatientTable.Open "Select * From PatientTable Where ID = " & Me!MR,
cnn1, , , adCmdTable

I get a message that says 'Syntax error in FROM clause'. What am I
doing
wrong? Also ,what is the Update Query that you mentioned before? I've
used
Update Queries lots of times before; just doing simple things though. I
am
not sure how to implement an Update Query into this code, but I would be
very
grateful if you show me how!!

Thanks,
Ryan---



--
RyGuy


:

Hi,
try this:

rstPatientTable.Open "Select * from PatientTable Where ID=" & me!MR, cnn1
if not rstPatientTable.EOF then
rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update
end if

You can also consider using update query, just one line of code to run
it.

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

I am using the following code to make updates to my Table named
‘PatientTable’:
Option Compare Database
Option Explicit
Dim cboOriginator As TextBox

Private Sub Command19_Click()

Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstPatientTable As ADODB.Recordset
Dim strCnn As String

Dim mydb As String
If err < 1 Then

Set cnn1 = New ADODB.Connection
mydb = "C:\RTDA Tool.mdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
cnn1.Open strCnn

Set rstPatientTable = New ADODB.Recordset
rstPatientTable.CursorType = adOpenKeyset
rstPatientTable.LockType = adLockOptimistic
rstPatientTable.Open "PatientTable", cnn1, , , adCmdTable

rstPatientTable.Update

rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update

MsgBox "Patient: " & Me![FirstName] & " " & Me![LastName] & "
has
been successfully updated!!"

rstPatientTable.Close
cnn1.Close
Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub


When the macro fires, the code runs and it DOES update the Table, but
the
updates always occur on the first record of the Table, it doesn’t
update
the
correct record. There must be a simple solution for this, but I don’t
know
where to begin. On my Form I have a TextBox named ‘MR’; it is an ID.
When I
type in an MR number, several TextBoxes on my Form update
automatically.
This is fine! I can see a patient’s status, and add some new
information
via
several TextBoxes, the problem is that when the code fires, the updates
aren’t made to the correct records. How can I ensure that updates are
made
to the appropriate record? The appropriate record is the number in the
MR
TetxBox?

I’ve been working on this project most of the day; this is the last
piece
of
the puzzle. I would really appreciate it if someone could help me
finish
this off!!

Thanks so much!!
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
Hi,
for update query try:
UPDATE PatientTable SET PatientTable.SIM_Comments =
Forms!SimForm!SIM_Comments WHERE not [SIM_Comments] is null AND
PatientTable.MR=Forms!SimForm![MR]

not sure you need "not [SIM_Comments] is null" according to your VBA code,
also you can update 2 fields at once:

docmd.runsql "UPDATE PatientTable SET PatientTable.SIM_Comments =
Forms!SimForm!SIM_Comments, RT_Start_Date = Forms!SimForm!RT_Start_Date
WHERE PatientTable.MR=Forms!SimForm![MR]"


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com




ryguy7272 said:
Hummm, I am definitely doing something wrong here. I keep getting a
message
that says run-time error. No value given for one or more parameters.

I tried several things, but couldn’t resolve it.

Also, I am now thinking the Update Query will be my best bet for this
task.
I worked with that for a bit this AM, but couldn’t get that working
either.

This is my SQL for the Update Query:
UPDATE PatientTable SET PatientTable.SIM_Comments =
Forms!SimForm!SIM_Comments
WHERE (([SIM_Comments]<>"") AND ((PatientTable.MR)=[MR]));

Field = SIM_Comments
Table = PatientTable
Update To = Forms!SimForm!SIM_Comments
Criteria = <>â€â€
also
Field = MR
Table = Patient Table
Update To =
Criteria = [MR]

I think this Criteria has to be MR, because I have to reference the
appropriate MR in the Table, based on the MR in the Form and then update
the
correct SIM_Comments in the Table, based on this MR. Is that right?

In any event, when I hit the ‘bang’ button, I get prompted for a
Parameter,
which doesn’t seem right at all, and then zero records are updated.

Here is my VBA code now:
Private Sub Command19_Click()

Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstPatientTable As ADODB.Recordset
Dim strCnn As String
'Dim mydb As DAO.Database
Dim mydb As String


'if no errors insert data
If err < 1 Then
' Open a connection.
Set cnn1 = New ADODB.Connection
mydb = "C:\RTDA Tool.mdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
cnn1.Open strCnn

Set rstPatientTable = New ADODB.Recordset
rstPatientTable.CursorType = adOpenKeyset
rstPatientTable.LockType = adLockOptimistic
rstPatientTable.Open "Select * From PatientTable Where ID = " & Me!MR,
cnn1
If Not rstPatientTable.EOF Then
rstPatientTable.Update
rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update
End If

MsgBox "Patient: " & Me![FirstName] & " " & Me![LastName] & " has
been successfully updated!!"

rstPatientTable.Close
cnn1.Close


Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub

How can I get the SQL working and then pop that into my VBA code?
This is the last part of this project; I hope I can finish soon.

Thanks,
Ryan---



--
RyGuy


Alex Dybenko said:
Hi,
should be:
rstPatientTable.Open "Select * From PatientTable Where ID = " & Me!MR,
cnn1

note that adCmdTable is omitted
also check that table/field names are correct

as for update query - run query designer, add PatientTable, change type
to
update - then you can define what fields you want to update to form's
control values (expression builder will help here), also add a filter by
ID

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


ryguy7272 said:
Thanks Alex! I think this is close, but it doesn't quite work; the
code
fails here.
rstPatientTable.Open "Select * From PatientTable Where ID = " &
Me!MR,
cnn1, , , adCmdTable

I get a message that says 'Syntax error in FROM clause'. What am I
doing
wrong? Also ,what is the Update Query that you mentioned before? I've
used
Update Queries lots of times before; just doing simple things though.
I
am
not sure how to implement an Update Query into this code, but I would
be
very
grateful if you show me how!!

Thanks,
Ryan---



--
RyGuy


:

Hi,
try this:

rstPatientTable.Open "Select * from PatientTable Where ID=" & me!MR,
cnn1
if not rstPatientTable.EOF then
rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update
end if

You can also consider using update query, just one line of code to run
it.

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

I am using the following code to make updates to my Table named
‘PatientTable’:
Option Compare Database
Option Explicit
Dim cboOriginator As TextBox

Private Sub Command19_Click()

Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstPatientTable As ADODB.Recordset
Dim strCnn As String

Dim mydb As String
If err < 1 Then

Set cnn1 = New ADODB.Connection
mydb = "C:\RTDA Tool.mdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
cnn1.Open strCnn

Set rstPatientTable = New ADODB.Recordset
rstPatientTable.CursorType = adOpenKeyset
rstPatientTable.LockType = adLockOptimistic
rstPatientTable.Open "PatientTable", cnn1, , , adCmdTable

rstPatientTable.Update

rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update

MsgBox "Patient: " & Me![FirstName] & " " & Me![LastName] & "
has
been successfully updated!!"

rstPatientTable.Close
cnn1.Close
Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub


When the macro fires, the code runs and it DOES update the Table,
but
the
updates always occur on the first record of the Table, it doesn’t
update
the
correct record. There must be a simple solution for this, but I don’t
know
where to begin. On my Form I have a TextBox named ‘MR’; it is an
ID.
When I
type in an MR number, several TextBoxes on my Form update
automatically.
This is fine! I can see a patient’s status, and add some new
information
via
several TextBoxes, the problem is that when the code fires, the
updates
aren’t made to the correct records. How can I ensure that updates
are
made
to the appropriate record? The appropriate record is the number in
the
MR
TetxBox?

I’ve been working on this project most of the day; this is the last
piece
of
the puzzle. I would really appreciate it if someone could help me
finish
this off!!

Thanks so much!!
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
Hi,
I wrote you already - if you are using sql select - you don’t need to
specify adCmdTable paramenter:

rstPatientTable.Open "SELECT * FROM PatientTable WHERE (((PatientTable.MR)=
" & Me!MR & "));", cnn1

BTW, if MR is a text field - then value should be in quotes:

rstPatientTable.Open "SELECT * FROM PatientTable WHERE (((PatientTable.MR)=
'" & Me!MR & "'));", cnn1

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


ryguy7272 said:
I just created a query that seems to do what I want. This is the SQL:
rstPatientTable.Open "SELECT * FROM PatientTable WHERE
(((PatientTable.MR)= " & Me!MR & "));", cnn1, , , adCmdTable

I copied/pasted it into a my code and it still doesn't work!!

What am I doing wrong?

--
RyGuy


ryguy7272 said:
Something is causing an error; the SQL is the culprit, I guess. Could it
be
something like this?
rstPatientTable.Open "Select * FROM PatientTable Where MR = " &
Me!MR & ";", cnn1, , , adCmdTable

...that doesn't work, but looks kind of close as far as I can tell.

The MR in my Query needs to equal the Me!MR on the Form. the logic seems
right; I don't know why it doesn't work though...

Any thoughts?

Thanks,
Ryan---

--
RyGuy


ryguy7272 said:
Hummm, I am definitely doing something wrong here. I keep getting a
message
that says run-time error. No value given for one or more parameters.

I tried several things, but couldn’t resolve it.

Also, I am now thinking the Update Query will be my best bet for this
task.
I worked with that for a bit this AM, but couldn’t get that working
either.

This is my SQL for the Update Query:
UPDATE PatientTable SET PatientTable.SIM_Comments =
Forms!SimForm!SIM_Comments
WHERE (([SIM_Comments]<>"") AND ((PatientTable.MR)=[MR]));

Field = SIM_Comments
Table = PatientTable
Update To = Forms!SimForm!SIM_Comments
Criteria = <>â€â€
also
Field = MR
Table = Patient Table
Update To =
Criteria = [MR]

I think this Criteria has to be MR, because I have to reference the
appropriate MR in the Table, based on the MR in the Form and then
update the
correct SIM_Comments in the Table, based on this MR. Is that right?

In any event, when I hit the ‘bang’ button, I get prompted for a
Parameter,
which doesn’t seem right at all, and then zero records are updated.

Here is my VBA code now:
Private Sub Command19_Click()

Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstPatientTable As ADODB.Recordset
Dim strCnn As String
'Dim mydb As DAO.Database
Dim mydb As String


'if no errors insert data
If err < 1 Then
' Open a connection.
Set cnn1 = New ADODB.Connection
mydb = "C:\RTDA Tool.mdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
cnn1.Open strCnn

Set rstPatientTable = New ADODB.Recordset
rstPatientTable.CursorType = adOpenKeyset
rstPatientTable.LockType = adLockOptimistic
rstPatientTable.Open "Select * From PatientTable Where ID = " &
Me!MR,
cnn1
If Not rstPatientTable.EOF Then
rstPatientTable.Update
rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update
End If

MsgBox "Patient: " & Me![FirstName] & " " & Me![LastName] & "
has
been successfully updated!!"

rstPatientTable.Close
cnn1.Close


Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub

How can I get the SQL working and then pop that into my VBA code?
This is the last part of this project; I hope I can finish soon.

Thanks,
Ryan---



--
RyGuy


:

Hi,
should be:
rstPatientTable.Open "Select * From PatientTable Where ID = " &
Me!MR, cnn1

note that adCmdTable is omitted
also check that table/field names are correct

as for update query - run query designer, add PatientTable, change
type to
update - then you can define what fields you want to update to form's
control values (expression builder will help here), also add a
filter by ID

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


Thanks Alex! I think this is close, but it doesn't quite work; the
code
fails here.
rstPatientTable.Open "Select * From PatientTable Where ID = " &
Me!MR,
cnn1, , , adCmdTable

I get a message that says 'Syntax error in FROM clause'. What am
I
doing
wrong? Also ,what is the Update Query that you mentioned before?
I've
used
Update Queries lots of times before; just doing simple things
though. I
am
not sure how to implement an Update Query into this code, but I
would be
very
grateful if you show me how!!

Thanks,
Ryan---



--
RyGuy


:

Hi,
try this:

rstPatientTable.Open "Select * from PatientTable Where ID=" &
me!MR, cnn1
if not rstPatientTable.EOF then
rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update
end if

You can also consider using update query, just one line of code to
run
it.

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

I am using the following code to make updates to my Table named
‘PatientTable’:
Option Compare Database
Option Explicit
Dim cboOriginator As TextBox

Private Sub Command19_Click()

Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstPatientTable As ADODB.Recordset
Dim strCnn As String

Dim mydb As String
If err < 1 Then

Set cnn1 = New ADODB.Connection
mydb = "C:\RTDA Tool.mdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
cnn1.Open strCnn

Set rstPatientTable = New ADODB.Recordset
rstPatientTable.CursorType = adOpenKeyset
rstPatientTable.LockType = adLockOptimistic
rstPatientTable.Open "PatientTable", cnn1, , , adCmdTable

rstPatientTable.Update

rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update

MsgBox "Patient: " & Me![FirstName] & " " & Me![LastName]
& "
has
been successfully updated!!"

rstPatientTable.Close
cnn1.Close
Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub


When the macro fires, the code runs and it DOES update the
Table, but
the
updates always occur on the first record of the Table, it doesn’t
update
the
correct record. There must be a simple solution for this, but I
don’t
know
where to begin. On my Form I have a TextBox named ‘MR’; it is
an ID.
When I
type in an MR number, several TextBoxes on my Form update
automatically.
This is fine! I can see a patient’s status, and add some new
information
via
several TextBoxes, the problem is that when the code fires, the
updates
aren’t made to the correct records. How can I ensure that
updates are
made
to the appropriate record? The appropriate record is the number
in the
MR
TetxBox?

I’ve been working on this project most of the day; this is the
last
piece
of
the puzzle. I would really appreciate it if someone could help
me
finish
this off!!

Thanks so much!!
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
What a difference 1 line of code makes!! I was using this before:
rstPatientTable.Open "PatientTable", cnn1, , , adCmdTable

Now, I’m using this:
rstPatientTable.Open "SELECT * FROM PatientTable WHERE
(((PatientTable.MR)=" & Me!MR & "));", cnn1

Everything is lovely at this moment!!

This DG is invaluable. I swear, I was looking online for an answer for my
problem for several hours today (I’m not just sitting around waiting for
others to solve my problems for me). Google is pretty powerful, but towards
the end of the day I still didn’t find a solution. I went to the local
bookstore and looked at few books; still no solution. Finally, Alex
responded, and the answer was revealed!!

I say ‘Thanks’ to Alex!! Also, ‘Thanks to all who contribute their thoughts
and ideas in this DG!!!’



Regards,
Ryan---



Alex Dybenko said:
Hi,
for update query try:
UPDATE PatientTable SET PatientTable.SIM_Comments =
Forms!SimForm!SIM_Comments WHERE not [SIM_Comments] is null AND
PatientTable.MR=Forms!SimForm![MR]

not sure you need "not [SIM_Comments] is null" according to your VBA code,
also you can update 2 fields at once:

docmd.runsql "UPDATE PatientTable SET PatientTable.SIM_Comments =
Forms!SimForm!SIM_Comments, RT_Start_Date = Forms!SimForm!RT_Start_Date
WHERE PatientTable.MR=Forms!SimForm![MR]"


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com




ryguy7272 said:
Hummm, I am definitely doing something wrong here. I keep getting a
message
that says run-time error. No value given for one or more parameters.

I tried several things, but couldn’t resolve it.

Also, I am now thinking the Update Query will be my best bet for this
task.
I worked with that for a bit this AM, but couldn’t get that working
either.

This is my SQL for the Update Query:
UPDATE PatientTable SET PatientTable.SIM_Comments =
Forms!SimForm!SIM_Comments
WHERE (([SIM_Comments]<>"") AND ((PatientTable.MR)=[MR]));

Field = SIM_Comments
Table = PatientTable
Update To = Forms!SimForm!SIM_Comments
Criteria = <>â€â€
also
Field = MR
Table = Patient Table
Update To =
Criteria = [MR]

I think this Criteria has to be MR, because I have to reference the
appropriate MR in the Table, based on the MR in the Form and then update
the
correct SIM_Comments in the Table, based on this MR. Is that right?

In any event, when I hit the ‘bang’ button, I get prompted for a
Parameter,
which doesn’t seem right at all, and then zero records are updated.

Here is my VBA code now:
Private Sub Command19_Click()

Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstPatientTable As ADODB.Recordset
Dim strCnn As String
'Dim mydb As DAO.Database
Dim mydb As String


'if no errors insert data
If err < 1 Then
' Open a connection.
Set cnn1 = New ADODB.Connection
mydb = "C:\RTDA Tool.mdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
cnn1.Open strCnn

Set rstPatientTable = New ADODB.Recordset
rstPatientTable.CursorType = adOpenKeyset
rstPatientTable.LockType = adLockOptimistic
rstPatientTable.Open "Select * From PatientTable Where ID = " & Me!MR,
cnn1
If Not rstPatientTable.EOF Then
rstPatientTable.Update
rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update
End If

MsgBox "Patient: " & Me![FirstName] & " " & Me![LastName] & " has
been successfully updated!!"

rstPatientTable.Close
cnn1.Close


Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub

How can I get the SQL working and then pop that into my VBA code?
This is the last part of this project; I hope I can finish soon.

Thanks,
Ryan---



--
RyGuy


Alex Dybenko said:
Hi,
should be:
rstPatientTable.Open "Select * From PatientTable Where ID = " & Me!MR,
cnn1

note that adCmdTable is omitted
also check that table/field names are correct

as for update query - run query designer, add PatientTable, change type
to
update - then you can define what fields you want to update to form's
control values (expression builder will help here), also add a filter by
ID

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


Thanks Alex! I think this is close, but it doesn't quite work; the
code
fails here.
rstPatientTable.Open "Select * From PatientTable Where ID = " &
Me!MR,
cnn1, , , adCmdTable

I get a message that says 'Syntax error in FROM clause'. What am I
doing
wrong? Also ,what is the Update Query that you mentioned before? I've
used
Update Queries lots of times before; just doing simple things though.
I
am
not sure how to implement an Update Query into this code, but I would
be
very
grateful if you show me how!!

Thanks,
Ryan---



--
RyGuy


:

Hi,
try this:

rstPatientTable.Open "Select * from PatientTable Where ID=" & me!MR,
cnn1
if not rstPatientTable.EOF then
rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update
end if

You can also consider using update query, just one line of code to run
it.

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

I am using the following code to make updates to my Table named
‘PatientTable’:
Option Compare Database
Option Explicit
Dim cboOriginator As TextBox

Private Sub Command19_Click()

Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstPatientTable As ADODB.Recordset
Dim strCnn As String

Dim mydb As String
If err < 1 Then

Set cnn1 = New ADODB.Connection
mydb = "C:\RTDA Tool.mdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
cnn1.Open strCnn

Set rstPatientTable = New ADODB.Recordset
rstPatientTable.CursorType = adOpenKeyset
rstPatientTable.LockType = adLockOptimistic
rstPatientTable.Open "PatientTable", cnn1, , , adCmdTable

rstPatientTable.Update

rstPatientTable!RT_Start_Date = RT_Start_Date
rstPatientTable!SIM_Comments = SIM_Comments

rstPatientTable.Update

MsgBox "Patient: " & Me![FirstName] & " " & Me![LastName] & "
has
been successfully updated!!"

rstPatientTable.Close
cnn1.Close
Else
MsgBox "An Error has occurred, please check and try again"
End If

End Sub


When the macro fires, the code runs and it DOES update the Table,
but
the
updates always occur on the first record of the Table, it doesn’t
update
the
correct record. There must be a simple solution for this, but I don’t
know
where to begin. On my Form I have a TextBox named ‘MR’; it is an
ID.
When I
type in an MR number, several TextBoxes on my Form update
automatically.
This is fine! I can see a patient’s status, and add some new
information
via
several TextBoxes, the problem is that when the code fires, the
updates
aren’t made to the correct records. How can I ensure that updates
are
made
to the appropriate record? The appropriate record is the number in
the
MR
TetxBox?

I’ve been working on this project most of the day; this is the last
piece
of
the puzzle. I would really appreciate it if someone could help me
finish
this off!!

Thanks so much!!
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
Back
Top