How to insert into table from a temp recordset

[llkhoutx]

Note that a checkbox has three states Null (the gray look when form is first opened, etc.), False (a white background), and True (a check).

In your query, what state do you build on? Just True, or all states?

Using 3 check boxes for example
me!chk1=true
me!chk2=False
me!chk3=False

'If only using the true state
dim sWHERE as strng
sWHEREr=""
if me!chk1 = true then sWHERE = "[Staff]=" & -1
if me!chk2 = true then sWHERE = sWHERE &" AND " & "[Member]=" & -1
if me!chk3 = true then sWHERE = sWHERE &" AND " & "[BoardMember]=" & -1
'PICK OFF A LEADING " AND" IF IT EXISTS
IF LEFT(sWHERE,5) = " AND " THEN sWHERE = RIGHT(sWHERE,Len(sWHERE)-5)

'If only using the true & false states
dim sWHERE as strng
sWHEREr=""
if me!chk1 & "" <> "" true then sWHERE = "[Staff]=" & me!ch1
if me!chk2 & "" <> "" then sWHERE = sWHERE &" AND " & "[Member]=" & me!ch2
if me!chk3 & "" <> "" then sWHERE = sWHERE &" AND " & "[BoardMember]=" & me!ch3
'PICK OFF A LEADING " AND" IF IT EXISTS
IF LEFT(sWHERE,5) = " AND " THEN sWHERE = RIGHT(sWHERE,lENsWHERE)-5)

'I doubt that you're all using the Null state
'no code included for that state


'then whichever method is used
if sWHERE & "" <> "" then
strSQL1 = "(whatever) WHERE " & sWHERE
ELSE
strSQL1 = "(whatever)" ' WITHOUT THE FILTERING
END if

 

[W.Dnx.W]

Well, I'm not checking for Null because you can have a recordset with no check box selected and it would still be a valid entry. My rpoblem isn't in building the SQL string, as I said, I is done (and it is a good one, I am getting the right results in all possible ways combined)...the thing is that I am not able to assign that same strSQL1 string to my subform.SourceControl property and then show my results in it.

So far my subform is showing another form which is based on a SQL statement as well...but I cant find a way to use a newly built SQL chackbox dependant statement with my subform
D

 

[llkhoutx]

To store and use the sql string is the subform recordsource, use:

Forms!MainFormName!subformNam.Form.RecordSource = strSQL1
Forms!MainFormName!subformNam.Form.Requery

 

[W.Dnx.W]

Oh, oh...I did it...with all your help, of course...thanks a lot guys...
well, it seems that my SQL string had a small incosistence...I had an ORDER by tbl.userID and my SQL SELECT statement didn't have that part at all...
anyways..thanks a lot everyone...I've learned a lot

Daniel

 

[W.Dnx.W]

One more question...is there a way to execute SQL with docmd.RunSQL but without the confirmation dialog box...
I need to insert some data into table and I'm sure that I want to do it...how can I skip to answer "I'm about to append 1 row..."

Thnks

 

[llkhoutx]

The warning message won't apperar when you use

dim db as dao.database
set db=currentdb
docmd.setwarnings false ' turn warnings off
db.execute sql
docmd.setwarning true ' turn warning back on
db.close
set db=nothing

 

[W.Dnx.W]

Thanks...that was what I needed...I was wondering...how come that in Excell VBA there is a application.DisplayAlert and in Access isn't ?!?!

 

[llkhoutx]

Docmd.SetWarnings True and False serve the same purpose. Maybe different programming groups wrote Access and Excel and only certain only certain common functions were used. Of course, Access VBA in rooted in Basic.

Bill Gates knows.

 

[W.Dnx.W]

It seems so...anyways, thanks a lot for all your help guys...see ya around