I'm sorry to be bothersome, but I really need help

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

Guest

I'm reposting this because I really need help with the sql statement below. I
apologize for the reposting but I'm desperate. Please forgive me and help me
if you can. Thanks, Rob

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Private Sub lstTemplates_DblClick(Cancel As Integer)
Dim sql As String

sql = "INSERT INTO tblDictation ( fldDescriptionOfProcedure, fldVisitNo,
fldCreator ) " & _
"SELECT ( fldDescriptionOfProcedure , " & " Me.txtVisitNO, " & " &
Me.fldCreator ) " & _
"FROM tblDictationLU " & _
"WHERE tblDictationLU.fldDictationLUno = " & Me.lstTemplates.Column(0)
Me.Form.Refresh


DoCmd.RunSQL sql
End Sub
 
Try this:

sql = "INSERT INTO tblDictation (fldDescriptionOfProcedure, fldVisitNo,
fldCreator) " & _
"SELECT (fldDescriptionOfProcedure , '" & Me.txtVisitNO & "', " &
Me.fldCreator & ") " & _
"FROM tblDictationLU " & _
"WHERE tblDictationLU.fldDictationLUno = " & Me.lstTemplates.Column(0)

Assuming txtVisitNo is a text value and fldCreator is a number...

Steve
 
In
RobUCSD said:
I'm reposting this because I really need help with the sql statement
below. I apologize for the reposting but I'm desperate. Please
forgive me and help me if you can. Thanks, Rob

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Private Sub lstTemplates_DblClick(Cancel As Integer)
Dim sql As String

sql = "INSERT INTO tblDictation ( fldDescriptionOfProcedure,
fldVisitNo, fldCreator ) " & _
"SELECT ( fldDescriptionOfProcedure , " & " Me.txtVisitNO, " & " &
Me.fldCreator ) " & _
"FROM tblDictationLU " & _
"WHERE tblDictationLU.fldDictationLUno = " & Me.lstTemplates.Column(0)
Me.Form.Refresh


DoCmd.RunSQL sql
End Sub
Click to expand...

Looks to me like you need:

Dim sql As String

sql = _
"INSERT INTO tblDictation(" & _
"fldDescriptionOfProcedure, fldVisitNo, fldCreator ) " & _
"SELECT ( fldDescriptionOfProcedure , " & Me.txtVisitNO & _
", " & Me.fldCreator & " ) " & _
"FROM tblDictationLU " & _
"WHERE tblDictationLU.fldDictationLUno = " & _
Me.lstTemplates.Column(0)

DoCmd.RunSQL sql

Me.Refresh
 
Thanks you guys for your response. Here's what I have now. I realized I need
to get the fldDescriptionOfProcedure text from my list box, I think.

I get the following message:

Syntax error (comma) in query experession '(Me.lstTemplates.column(5), '796,
rwcass)'

As you can see, I'm getting the proper values in from txtVisitNO and
Me.fldCreator (wich is a text value.)

Any additional help would be greatly appreciated. Thanks, Rob

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Dim sql As String

sql = "INSERT INTO tblDictation (fldDescriptionOfProcedure, fldVisitNo,
fldCreator) " & _
"SELECT (Me.lstTemplates.column(5) , '" & Me.txtVisitNO & "', " &
Me.fldCreator & ") " & _
"FROM tblDictationLU " & _
"WHERE tblDictationLU.fldDictationLUno = " & Me.lstTemplates.Column(0)

DoCmd.RunSQL sql
 
The Me.lstTemplates.column(5) needs to be *outside* the quotes in the SQL=
statement, but the resulting value needs to be *inside* quotes in the
resulting SQL text:


sql = "INSERT INTO tblDictation (fldDescriptionOfProcedure, fldVisitNo,
fldCreator) " & _
"SELECT ('" & Me.lstTemplates.column(5) & "', " & Me.txtVisitNO & ", '" &
Me.fldCreator & "') " & _
"FROM tblDictationLU " & _
"WHERE tblDictationLU.fldDictationLUno = " & Me.lstTemplates.Column(0)

Since rwcass is a text string (I think) it has to be in quotes in the SQL
text as well, so that you get this:

('column5text', '796, 'rwcass')

I think we're close.

John



Thanks you guys for your response. Here's what I have now. I realized I need
to get the fldDescriptionOfProcedure text from my list box, I think.

I get the following message:

Syntax error (comma) in query experession '(Me.lstTemplates.column(5), '796,
rwcass)'

As you can see, I'm getting the proper values in from txtVisitNO and
Me.fldCreator (wich is a text value.)

Any additional help would be greatly appreciated. Thanks, Rob

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Dim sql As String

sql = "INSERT INTO tblDictation (fldDescriptionOfProcedure, fldVisitNo,
fldCreator) " & _
"SELECT (Me.lstTemplates.column(5) , '" & Me.txtVisitNO & "', " &
Me.fldCreator & ") " & _
"FROM tblDictationLU " & _
"WHERE tblDictationLU.fldDictationLUno = " & Me.lstTemplates.Column(0)

DoCmd.RunSQL sql
[quoted text clipped - 31 lines]
Me.Refresh
Click to expand...
Click to expand...
 
Thanks John, I'm going into a meeting right now but as soon as I get back
I'll try it and post back. Thanks for your help.

J_Goddard via AccessMonster.com said:
The Me.lstTemplates.column(5) needs to be *outside* the quotes in the SQL=
statement, but the resulting value needs to be *inside* quotes in the
resulting SQL text:


sql = "INSERT INTO tblDictation (fldDescriptionOfProcedure, fldVisitNo,
fldCreator) " & _
"SELECT ('" & Me.lstTemplates.column(5) & "', " & Me.txtVisitNO & ", '" &
Me.fldCreator & "') " & _
"FROM tblDictationLU " & _
"WHERE tblDictationLU.fldDictationLUno = " & Me.lstTemplates.Column(0)

Since rwcass is a text string (I think) it has to be in quotes in the SQL
text as well, so that you get this:

('column5text', '796, 'rwcass')

I think we're close.

John



Thanks you guys for your response. Here's what I have now. I realized I need
to get the fldDescriptionOfProcedure text from my list box, I think.

I get the following message:

Syntax error (comma) in query experession '(Me.lstTemplates.column(5), '796,
rwcass)'

As you can see, I'm getting the proper values in from txtVisitNO and
Me.fldCreator (wich is a text value.)

Any additional help would be greatly appreciated. Thanks, Rob

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Dim sql As String

sql = "INSERT INTO tblDictation (fldDescriptionOfProcedure, fldVisitNo,
fldCreator) " & _
"SELECT (Me.lstTemplates.column(5) , '" & Me.txtVisitNO & "', " &
Me.fldCreator & ") " & _
"FROM tblDictationLU " & _
"WHERE tblDictationLU.fldDictationLUno = " & Me.lstTemplates.Column(0)

DoCmd.RunSQL sql
I'm reposting this because I really need help with the sql statement
below. I apologize for the reposting but I'm desperate. Please
Click to expand...
[quoted text clipped - 31 lines]
Me.Refresh
Click to expand...
Click to expand...
Click to expand...
 
Hello John, we're getting close. Here's the sql statement. I get the proper
values for all the fields, but I'm still getting a Run Time error '3075':
Syntax error(comma) in query expression '(Pt. Arrived via gurney.....rest of
memo field

I don't know if it makes any difference, but Me.lstTemplates.column(5) is a
memo field.

Thanks for your great help.

J_Goddard via AccessMonster.com said:
The Me.lstTemplates.column(5) needs to be *outside* the quotes in the SQL=
statement, but the resulting value needs to be *inside* quotes in the
resulting SQL text:


sql = "INSERT INTO tblDictation (fldDescriptionOfProcedure, fldVisitNo,
fldCreator) " & _
"SELECT ('" & Me.lstTemplates.column(5) & "', " & Me.txtVisitNO & ", '" &
Me.fldCreator & "') " & _
"FROM tblDictationLU " & _
"WHERE tblDictationLU.fldDictationLUno = " & Me.lstTemplates.Column(0)

Since rwcass is a text string (I think) it has to be in quotes in the SQL
text as well, so that you get this:

('column5text', '796, 'rwcass')

I think we're close.

John



Thanks you guys for your response. Here's what I have now. I realized I need
to get the fldDescriptionOfProcedure text from my list box, I think.

I get the following message:

Syntax error (comma) in query experession '(Me.lstTemplates.column(5), '796,
rwcass)'

As you can see, I'm getting the proper values in from txtVisitNO and
Me.fldCreator (wich is a text value.)

Any additional help would be greatly appreciated. Thanks, Rob

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Dim sql As String

sql = "INSERT INTO tblDictation (fldDescriptionOfProcedure, fldVisitNo,
fldCreator) " & _
"SELECT (Me.lstTemplates.column(5) , '" & Me.txtVisitNO & "', " &
Me.fldCreator & ") " & _
"FROM tblDictationLU " & _
"WHERE tblDictationLU.fldDictationLUno = " & Me.lstTemplates.Column(0)

DoCmd.RunSQL sql
I'm reposting this because I really need help with the sql statement
below. I apologize for the reposting but I'm desperate. Please
Click to expand...
[quoted text clipped - 31 lines]
Me.Refresh
Click to expand...
Click to expand...
Click to expand...
 
I don't know if it makes any difference, but Me.lstTemplates.column(5) is a
memo field.
Click to expand...

Putting a memo field into a Listbox or a Combo Box will truncate it to 255
bytes. Why do you feel that you need to copy a memo field to another table,
storing it redundantly? What are these two tables; how are they related; and
why do you feel that you need to store the same information twice?

John W. Vinson [MVP]
 
The table tblDictationLU (LU=lookup) contains canned physician dictation
templates. The MD dbl clicks on the list box and with the query we're working
on the tblDictation gets populated with the dictation template he/she
chooses. Then, in the form they can edit the note as needed for the specific
patient and procedure.

I could make it so the list box doesn't contain the memo field, but then
we'll have to include the table in the query process; like
tblDictationLU.fldDescriptionOfProcedure instead of getting it from the list
box. I'm not sure how to do this.

Thanks John for hanging in there with me. Rob
 
I could make it so the list box doesn't contain the memo field, but then
we'll have to include the table in the query process; like
tblDictationLU.fldDescriptionOfProcedure instead of getting it from the list
box. I'm not sure how to do this.
Click to expand...

A third alternative would be to include tblDictationLU's Primary Key in the
listbox, and use either DLookUp or VBA code to retrieve the memo field and
push it into the bound control. I take it that the description is subject to
editing after it's been selected? Otherwise there's still no reason to store
it redundently.

The code would be in the listbox's AfterUpdate event - something like

Private Sub lstDescription_AfterUpdate()
Me!txtMemoFieldName = DLookUp("[fldDescriptionOfProcedure]", _
"[tblDictationLU]", "[ProcedureID] = " & Me!lstDescription)
End Sub


John W. Vinson [MVP]
 
Back
Top