Inserting a sql string into a table using sql. Help please?

[Sean Mc]

I have a form that assembles a sql string based on user choices, then sets
this sql string as listbox rowsource, so the user just sees what table
values they want to see at that moment.

Now what I am trying to do is add "[<< Previous]" and "[Next >>]" buttons,
so that the user can scroll thru the last 10 filters made to the list box,
without having to go thru the filter form process again. My thought was to
create a tblFilters table, and every time a filter sqlString was defined,
save it off to my table for later access when the previous, next buttons
where clicked.

Of course, the way I know how to do it is not going to work, because it
seems that the application is confusing my INSERT/UPDATE sql string with the
sql string value I am trying to put into the table.

Follows is what ISNT working. :

DoCmd.RunSQL "INSERT INTO tblFilter (tblFilter.SqlString, tblFilter.Rank) "
& _
"VALUES ('" & Forms!frmPrts.lstParts.RowSource & "',
" & intRank & ");"

IS there a way I could make the application think the data-value sql string
is just a string value, so I can insert it?

Thanks -
SeanMc

 

[Dale Fye]

Sean,

Assuming you have an autonumber field in your tblFilters, you could try
something like:

Private Sub SaveFilter(SQL as string)

Dim rs as dao.recordset

set rs = db.openrecordset("tblFilters")
rs.addnew
rs("FilterString") = SQL 'replace FilterString with the name of your
field
rs.update
rs.close
set rs = nothing

'Delete the oldest filters if there are more than 10
IF DCOUNT("ID","tblFilters") > 10
currentdb.execute "DELETE * FROM tblFilters WHERE ID = " &
DMIN("ID", "tblFilters")
end if

End Sub

HTH
Dale

 

[Sean Mc]

Sean,

Assuming you have an autonumber field in your tblFilters, you could try
something like:

Private Sub SaveFilter(SQL as string)

Dim rs as dao.recordset

set rs = db.openrecordset("tblFilters")
rs.addnew
rs("FilterString") = SQL 'replace FilterString with the name of your
field
rs.update
rs.close
set rs = nothing

'Delete the oldest filters if there are more than 10
IF DCOUNT("ID","tblFilters") > 10
currentdb.execute "DELETE * FROM tblFilters WHERE ID = " &
DMIN("ID", "tblFilters")
end if

End Sub

HTH
Dale

Thank you Dale, saving it via the recordset method is the way I went, and
it worked great.
Thanks for the reply.
Sean M