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

[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

 

[Guest]

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

 

[Dirk Goldgar]

In

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

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

 

[Guest]

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

 

[J_Goddard via AccessMonster.com]

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

 

[Guest]

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.

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

[quoted text clipped - 31 lines]

Me.Refresh

 

[Guest]

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.

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

[quoted text clipped - 31 lines]

Me.Refresh

 

[John W. Vinson]

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

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]

 

[Guest]

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

 

[John W. Vinson]

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.

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]