sql help

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

Guest

What I'm trying to do with the below is the following:

I have a form named frmTemplate, on that form is a list box that refers to
the different template available in the tblDictationLU. When an Item is
DblClicked on I want to insert the text from the memo field in
fldDiscriptionOfPro (comming from tblDictationLU) into the fldDesriptionOfPro
of the target table tblDictation.

With the WHERE statement I'm attempting to refer back to the tblDicationLU
and get the proper template for the memo field.

I know I've got the sql all f**d up, that's why I'm asking for help.

Thanks in advance
************************************************************
Private Sub lstTemplates_DblClick(Cancel As Integer)

Dim sql As String

sql = "INSERT INTO tblDictation ( [fldDiscriptionOfPro] )_"
FROM * tblDictationLU([fldDiscriptionOfPro])
WHERE Me.lstTemplates(1) = tblDictationLU([fldDictationLUno])

DoCmd.RunSQL sql
End Sub
 
Assuming that the field has the same name (complete with spelling mistake!)
in both tables:

sql = "INSERT INTO tblDictation ( [fldDiscriptionOfPro] ) " & _
"SELECT [fldDiscriptionOfPro] " & _
"FROM tblDictationLU " & _
"WHERE tblDictationLU.[fldDictationLUno] = " & Me.lstTemplates(1)

That assumes that fldDictationLUno is a numeric field. If it's text, you'll
need quotes in the Where clause:

"WHERE tblDictationLU.[fldDictationLUno] = '" & Me.lstTemplates(1) & "'"

At least, that would be correct if Me.lstTemplates(1) was legitimate. To
what are you trying to refer in that list box? If the list box does not
allow multiselect and you're trying to get the content of the second column
of the selected row, you need Me.lstTemplates.Column(1). If you're trying to
refer to some other value, post back.
 
Hi -

Try this SQL, which assumes that Me.lstTemplates.column(1) is numeric. Note
that in the sql= statement the Me.lstTemplates.column(1) is outside the
quotation marks.

sql= "Insert into tblDictation ([fldDiscriptionofPro]) select tblDictationLU.
fldDiscriptionofPro from tblDictationLU where tblDictationLU.fldDictationLUno
= " & Me.lstTemplates.column(1)

This SQL creates a *new* record in tblDictation - is this what you are
wanting to do?

I think I have everything spelled right - hope this helps.

John


What I'm trying to do with the below is the following:

I have a form named frmTemplate, on that form is a list box that refers to
the different template available in the tblDictationLU. When an Item is
DblClicked on I want to insert the text from the memo field in
fldDiscriptionOfPro (comming from tblDictationLU) into the fldDesriptionOfPro
of the target table tblDictation.

With the WHERE statement I'm attempting to refer back to the tblDicationLU
and get the proper template for the memo field.

I know I've got the sql all f**d up, that's why I'm asking for help.

Thanks in advance
************************************************************
Private Sub lstTemplates_DblClick(Cancel As Integer)

Dim sql As String

sql = "INSERT INTO tblDictation ( [fldDiscriptionOfPro] )_"
FROM * tblDictationLU([fldDiscriptionOfPro])
WHERE Me.lstTemplates(1) = tblDictationLU([fldDictationLUno])

DoCmd.RunSQL sql
End Sub
Click to expand...
 
Hello Doug or others out there. Can you please help me with the syntax of the
sql statement below. I'm terrible at sql syntax (as you can see below.)


Thanks in advance, Rob
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
sql = "INSERT INTO tblDictation ( [fldDescriptionOfProcedure], [fldVisitNo],
[fldCreator] ) " & _
"SELECT [fldDescriptionOfProcedure], " & " [txtVisitNO], " & " &
[Me.fldCreator], " & _
"FROM tblDictationLU " & _
"WHERE tblDictationLU.[fldDictationLUno] = " & Me.lstTemplates.Column(0)



DoCmd.RunSQL sql
End Sub

Douglas J. Steele said:
Assuming that the field has the same name (complete with spelling mistake!)
in both tables:

sql = "INSERT INTO tblDictation ( [fldDiscriptionOfPro] ) " & _
"SELECT [fldDiscriptionOfPro] " & _
"FROM tblDictationLU " & _
"WHERE tblDictationLU.[fldDictationLUno] = " & Me.lstTemplates(1)

That assumes that fldDictationLUno is a numeric field. If it's text, you'll
need quotes in the Where clause:

"WHERE tblDictationLU.[fldDictationLUno] = '" & Me.lstTemplates(1) & "'"

At least, that would be correct if Me.lstTemplates(1) was legitimate. To
what are you trying to refer in that list box? If the list box does not
allow multiselect and you're trying to get the content of the second column
of the selected row, you need Me.lstTemplates.Column(1). If you're trying to
refer to some other value, post back.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


RobUCSD said:
What I'm trying to do with the below is the following:

I have a form named frmTemplate, on that form is a list box that refers to
the different template available in the tblDictationLU. When an Item is
DblClicked on I want to insert the text from the memo field in
fldDiscriptionOfPro (comming from tblDictationLU) into the
fldDesriptionOfPro
of the target table tblDictation.

With the WHERE statement I'm attempting to refer back to the tblDicationLU
and get the proper template for the memo field.

I know I've got the sql all f**d up, that's why I'm asking for help.

Thanks in advance
************************************************************
Private Sub lstTemplates_DblClick(Cancel As Integer)

Dim sql As String

sql = "INSERT INTO tblDictation ( [fldDiscriptionOfPro] )_"
FROM * tblDictationLU([fldDiscriptionOfPro])
WHERE Me.lstTemplates(1) = tblDictationLU([fldDictationLUno])

DoCmd.RunSQL sql
End Sub
Click to expand...
Click to expand...
 
sql = "INSERT INTO tblDictation " & _
"([fldDescriptionOfProcedure], [fldVisitNo], [fldCreator] ) " & _
"SELECT [fldDescriptionOfProcedure], [txtVisitNO], " & _
Me.fldCreator & " FROM tblDictationLU " & _
"WHERE tblDictationLU.[fldDictationLUno] = " & Me.lstTemplates.Column(0)

I'm assuming fldDescriptionOfProcedure and txtVisitNO are both fields in
tblDictationLU, and that you're trying to pick Me.fldCreator from the form.

If fldCreator is a text field, you'll need

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

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


RobUCSD said:
Hello Doug or others out there. Can you please help me with the syntax of
the
sql statement below. I'm terrible at sql syntax (as you can see below.)


Thanks in advance, Rob
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
sql = "INSERT INTO tblDictation ( [fldDescriptionOfProcedure],
[fldVisitNo],
[fldCreator] ) " & _
"SELECT [fldDescriptionOfProcedure], " & " [txtVisitNO], " & " &
[Me.fldCreator], " & _
"FROM tblDictationLU " & _
"WHERE tblDictationLU.[fldDictationLUno] = " & Me.lstTemplates.Column(0)



DoCmd.RunSQL sql
End Sub

Douglas J. Steele said:
Assuming that the field has the same name (complete with spelling
mistake!)
in both tables:

sql = "INSERT INTO tblDictation ( [fldDiscriptionOfPro] ) " & _
"SELECT [fldDiscriptionOfPro] " & _
"FROM tblDictationLU " & _
"WHERE tblDictationLU.[fldDictationLUno] = " & Me.lstTemplates(1)

That assumes that fldDictationLUno is a numeric field. If it's text,
you'll
need quotes in the Where clause:

"WHERE tblDictationLU.[fldDictationLUno] = '" & Me.lstTemplates(1) &
"'"

At least, that would be correct if Me.lstTemplates(1) was legitimate. To
what are you trying to refer in that list box? If the list box does not
allow multiselect and you're trying to get the content of the second
column
of the selected row, you need Me.lstTemplates.Column(1). If you're trying
to
refer to some other value, post back.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


RobUCSD said:
What I'm trying to do with the below is the following:

I have a form named frmTemplate, on that form is a list box that refers
to
the different template available in the tblDictationLU. When an Item is
DblClicked on I want to insert the text from the memo field in
fldDiscriptionOfPro (comming from tblDictationLU) into the
fldDesriptionOfPro
of the target table tblDictation.

With the WHERE statement I'm attempting to refer back to the
tblDicationLU
and get the proper template for the memo field.

I know I've got the sql all f**d up, that's why I'm asking for help.

Thanks in advance
************************************************************
Private Sub lstTemplates_DblClick(Cancel As Integer)

Dim sql As String

sql = "INSERT INTO tblDictation ( [fldDiscriptionOfPro] )_"
FROM * tblDictationLU([fldDiscriptionOfPro])
WHERE Me.lstTemplates(1) = tblDictationLU([fldDictationLUno])

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