How to insert into table from a temp recordset

[W.Dnx.W]

Hi all,

I have a form with around 10 checkboxes which serve as a filter option...now, when I hit my cmdFilter button it works well with a simple MsgBox !Ime showing all the filtered names...now, I want to put the results into a table tblTemp so that I could show the results in my subform. I've tried with making a sql string something like "INSERT INTO tblTemp..." but it's still empty.
Since this table will serve as a one time data, I will need to delete all records when I hit the Filter button next time...so, how do I send my recordset data into my table.

I hope this sounds understandable...

Thanks a lot,

Daniel

 

[llkhoutx]

Use the Query By Example window to select your tables and fields, then select the "Make Table Query" button, then view your result in the SQL view.

 

[W.Dnx.W]

OK, I've tried that, but it seems I0m not getting it rigth...
here is the part of the code that I have after couple of If thens

strSQL1 = str1 & str2
Set rs = db.OpenRecordset(strSQL1)


With rs

If .RecordCount > 0 Then
.MoveFirst

Do Until .EOF

MsgBox !Ime

.MoveNext

Loop

so, this MsgBox was just here to show me if I'm getting the right data out. Now that I0m sure that I am, how can I "export" the results of this recorset into a new table.

thanks a lot

Daniel

 

[Pat Hartman]

Do not create temp tables for this purpose. Create an SQL string and use the that as the RecordSource for the subform.

 

[W.Dnx.W]

So, if I got it right, I should use my strSQL1 string and put it as a Subform.SourceControl=strSQL1
or is there something that I'm missing?
Thanks for helping out...
D

 

[Pat Hartman]

If strSQL1 is a select query, yes.

 

[Daniel.W]

Sorry, I had to re-register...
anyways...is there anything else I need to do when I assign my strSQL1 to .SourceControl...some kind of refresh or something

thx

D

 

[W.Dnx.W]

.........
when I do put my string as an ControlSource, still nothing happens

 

[Pat Hartman]

How about:

Me.YourSubForm.RecordSource = strSQL1
Me.YourSubForm.Requery

Try it first without the requery. You may not need it.

 

[W.Dnx.W]

It seems that still nothing is happeneing...could it be that I have placed Me. at the wrong spot, since I'm not getting MsgBox anymore...

D
---------------------------------------

Me.Svi_korisnici.SourceObject = strSQL1

With rs

If .RecordCount > 0 Then
.MoveFirst

Do Until .EOF

MsgBox !Ime
.MoveNext
Loop

Else
MsgBox ("Nema podataka po traženom kriteriju! Promjeni filter.")
Exit Sub
End If
.Close

End With

 

[Pat Hartman]

I don't understand where you're going with this code loop that does nothing.

 

[W.Dnx.W]

well, you are right, that loop was here just to help me see if my strSQL1 was set up right with all those IF THENs I put up. So I had this MsgBox to prompt me if the right records were chosen...
Now, I will remove this code, but only when I get my data into subform...which I couldn't do so far.
Thanks a lot for helping me on this one.

Daniel

 

[W.Dnx.W]

Still no progress...

 

[llkhoutx]

Do you have a question at this point, or are you stuck on with the question in your original post in this thread?

 

[W.Dnx.W]

I'm still stuck with what Pat gave me last. I've tried that and it seems I can't make it right. At one point my Subform went blank after I inserted Me.subform.sourceControl part but now it seems not to show any change at all.

 

[llkhoutx]

Verify that your strSQL1 is good by putting it in the QBE sql window and executing it (with the Bang icon [the exclamation point icon]).

Also, make sure that you requery your subform after changing the Record Source.

Bad sql with produce what you're getting. Can you post your sql string, i.e. srtSQL1?

 

[W.Dnx.W]

I'm kind pretty sure that it is a good string since I had this small part of code with MsgBox showing me if strSQL1 was giving me te correct stuff. So now that I was sure that my SQL string was right, insted of using MsgBox to show results, I wanted Subform to do that.

 

[W.Dnx.W]

I'm kinda pretty sure that it is a good string since I had this small part of code with MsgBox showing me if strSQL1 was giving me the correct stuff. So now that I was sure that my SQL string was right, insted of using MsgBox to show results, I wanted Subform to do that.

 

[llkhoutx]

I built a zipped sample 2000 mdb illustrating what I think you want. Note that I use a recordset as opposed to a query.

I tried to build a query & had a hard time and couldn't put any more time into it.

The attached works. I can't imagine what this is for. Note that I'm young and inexperienced.

Note that frmFilter is modal, i.e. once it's opened it has to be closed before control/focus can pass to another object. The MainForm navigation buttons indicate that there is just one record.

tblTemp is emptied before frmFilter is opened.

 

[W.Dnx.W]

Thanks for that example, but it's not really what I needed...you see, my checkboxes are different categories people can be part of (like: member, board member, staff...) with note that you can be in multiple categories or in none.
With what you gave me, you are assigning values to the tblTemp, and I need to get the data back upon this filter.
Now, in my DB I have 10 ppl with different categories, and I've made 10chks for 10 different categories, so that I could sort them out in all possible combinations (member + staff, member+board+staff, or if none is selected then show all records). SO I have managed to build up my SQL Select string based upon this check boxes.

Hope this explain it a bit more. Could be that I just made all this to complicated for my own level of Access knowledge so I'm just spinning around.

D