INSERT INTO Statement trouble

[ScubaSteve]

This board has been incredibly helpful with my database, so I thought I
would give this another shot.

I've got an insert into statement that I don't understand fully. I
want items selected in a listbox, on dblclick, to be inserted into a
table. Here is my statement.

Private Sub lstProblem_DblClick(Cancel As Integer)
Dim strSQL As String

strSQL = "INSERT INTO tPatientDiagnoses([DiagnosisID],
[DiagnosisName]) VALUES (" _
& Me.LstProblem.Column(0) & ", " _
& Me.LstProblem.Column(1) & ")"

CurrentDb.Execute strSQL, dbFailOnError
End Sub

This is on a subform (linked to tpatientDiagnoses) and the items in the
listbox are items available that should be appended to the record using
CaseID and PatientID (composite). The master and child fields are set
up correctly in the subform as far as I know.

tpatientDiagnoses fields look like this:
CaseID : PatientID : DiagnosisID :
DiagnosisName

The listbox this is coming from has multiple columns (4) since it based
on a selection in a combobox.

Eventually I want the items appended to the table to appear in another
listbox.

Thanks!

 

[ScubaSteve]

Oh, i get the error "Too few Parameters. Expected 1" Runtime error 3061

 

[Jeff Boyce]

If you are attempting to insert a text value, you need to remind Access of
this by delimiting it with double-quotes.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[ScubaSteve]

Okay, I took out the text value just to test it and it inserted the
number into the table correctly. However, the CaseID and PatientID
didn't follow so they were 0's. Do I have to include the case and
patientid in the SQL statement? I thought by having it in the subform,
that would do it.

Still figuring out where to put the quotes, but I'll get it - thanks

If you are attempting to insert a text value, you need to remind Access of
this by delimiting it with double-quotes.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

This board has been incredibly helpful with my database, so I thought I
would give this another shot.

I've got an insert into statement that I don't understand fully. I
want items selected in a listbox, on dblclick, to be inserted into a
table. Here is my statement.

Private Sub lstProblem_DblClick(Cancel As Integer)
Dim strSQL As String

strSQL = "INSERT INTO tPatientDiagnoses([DiagnosisID],
[DiagnosisName]) VALUES (" _
& Me.LstProblem.Column(0) & ", " _
& Me.LstProblem.Column(1) & ")"

CurrentDb.Execute strSQL, dbFailOnError
End Sub

This is on a subform (linked to tpatientDiagnoses) and the items in the
listbox are items available that should be appended to the record using
CaseID and PatientID (composite). The master and child fields are set
up correctly in the subform as far as I know.

tpatientDiagnoses fields look like this:
CaseID : PatientID : DiagnosisID :
DiagnosisName

The listbox this is coming from has multiple columns (4) since it based
on a selection in a combobox.

Eventually I want the items appended to the table to appear in another
listbox.

Thanks!

 

[Jeff Boyce]

If you don't explicitly tell Access to INSERT a field (and provide it a
value), it has no reason to second-guess you.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

Okay, I took out the text value just to test it and it inserted the
number into the table correctly. However, the CaseID and PatientID
didn't follow so they were 0's. Do I have to include the case and
patientid in the SQL statement? I thought by having it in the subform,
that would do it.

Still figuring out where to put the quotes, but I'll get it - thanks

If you are attempting to insert a text value, you need to remind Access of
this by delimiting it with double-quotes.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

This board has been incredibly helpful with my database, so I thought I
would give this another shot.

I've got an insert into statement that I don't understand fully. I
want items selected in a listbox, on dblclick, to be inserted into a
table. Here is my statement.

Private Sub lstProblem_DblClick(Cancel As Integer)
Dim strSQL As String

strSQL = "INSERT INTO tPatientDiagnoses([DiagnosisID],
[DiagnosisName]) VALUES (" _
& Me.LstProblem.Column(0) & ", " _
& Me.LstProblem.Column(1) & ")"

CurrentDb.Execute strSQL, dbFailOnError
End Sub

This is on a subform (linked to tpatientDiagnoses) and the items in the
listbox are items available that should be appended to the record using
CaseID and PatientID (composite). The master and child fields are set
up correctly in the subform as far as I know.

tpatientDiagnoses fields look like this:
CaseID : PatientID : DiagnosisID :
DiagnosisName

The listbox this is coming from has multiple columns (4) since it based
on a selection in a combobox.

Eventually I want the items appended to the table to appear in another
listbox.

Thanks!

 

[ScubaSteve]

Hey Jeff,

Just wanted to say thanks for the guidance. Ended up with...

Private Sub LstProblem_DblClick(Cancel As Integer)

Dim strSQL As String

strSQL = "INSERT INTO tPatientDiagnoses([CaseID],
[PatientID], [DiagnosisID], [DiagnosisName]) VALUES (" _
& Forms![frmcase]![patient_form].Form![txtCaseID] & "," _
& Forms![frmcase]![patient_form].Form![txtPatientID] & ","
_
& Me.lstproblem.Column(0) & ", """ _
& Me.lstproblem.Column(1) & """);"

CurrentDb.Execute strSQL, dbFailOnError

Me.lstPatientProblem.Requery


End Sub