Help with insert Query

[ByronJohnson]

Hey Everyone,

I am stuck on this problem. I am trying to string together three different fields together and insert them into a table all within in the same column, same row.

Here is my code.

Private Sub cmdUpdate_Click()

Dim lacvar As Integer
Dim codonnum As Integer
Dim codonstr As String

lacvar = Forms!Sequence!lacIbase

codonnum = (lacvar Mod 3) + 1

Select Case codonnum
Case 1
codonstr = "Insert Into Sequence (AminoAcid) Select base from LACI where laci = " & lacvar & " or laci = " & lacvar & " + 1 or laci = " & lacvar & " + 2"
Case 2
codonstr = "Insert Into Sequence (AminoAcid) Select base from LACI where laci = " & lacvar & " - 1 or laci = " & lacvar & " or laci = " & lacvar & " + 1"
Case 3
codonstr = "Insert Into Sequence (AminoAcid) Select base from LACI where laci = " & lacvar & " - 2 or laci = " & lacvar & " - 1 or laci = " & lacvar & ""
End Select

lisAmino.RowSource = codonstr

End Sub

I think the problem is that it is not string the values together and it is trying to insert them three new rows and not in the same row.

Hope this makes senese and someone can help me out.
Byron Johnson

 

[braindead2]

Hey Byron,
I am real confused by your posted code. What it looks like you are getting is three records returned that match criteria and then it appends that
To append via SQL it should be as simple as

INSERT INTO firsttbl( field )
SELECT [field1] & [field2] & [field3] AS datatopaste
FROM otherTBL where = your criteria

but what it looks to me is you are trying to append returned records rather then 3 fields, if I'm correct You will need to try to assign the records that are being returned to a variable then append it to your table

something like

dim variables here

build select statement here
open recordset here

do until rst.eof
rst.move first
condonstr = Rst!base & ", "
rst.movenext
loop
rst.close

'append query here
docmd. runsql "Insert Into Sequence (AminoAcid),condonstr "
but the last line really confuses me are you trying to use a select query as the rowsource for a listbox???

 

[ByronJohnson]

Thanks for you help. I am going to make that listbox into a text box. I just wanted to see if my queries were working.