using vb code to replace a table's data with form's recordsource

[mpfohl]

I have a search form that uses the user's input to build an SQL query
and then sets that as the form's recordsource.

I want to store the ID column ("histID") of the records I found in to a
table I created ("zTable") so I can then open various reports using
those pre-found record IDs.

I think I want to find a way through VB code to run a Make Table query
and pass my form's recordsource (just the "histID" column) into my
table... I'm not really sure how to tell it to do this.

any ideas? Thanks

 

[Albert D.Kallal]

so I can then open various reports using
those pre-found record IDs.

Why not just save the "where" clause, and pass that to the form, or report
in question? (that is what the where clause is for!!).

The normal approach is to build a un-bound form (a un-bound form is a form
that is NOT attached to a table - these forms are typicaly desiged for user
interface face stuff like promtps, print buttions etc).

The following screen shots are all un-bound forms, and they simply prompt
the user for informaton.

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The above shold give you some ideas

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query. And, NO FORMS conditions are
placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause.

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade. In a nut shell, I am saying to advoid forms expressions in the
actual sql....

For a date range, we could put two calendar contorls on the screen. The code
could be:


dim strWhere as string
dim strStartDate as string
dim strEndDate as string


strStartDtae = "#" & format(me.StartDateContorl,"mm/dd/yyyy") & "#"
strEndDate = "#" & format(me.EndDateContorl,"mm/dd/yyyy") & "#"

strWhere = "InvoiceDate is between " & strStartDate & " and " & strEndDate

docmd.openReport "InvoiceReport",acViewPreview,,strWhere

 

[mpfohl]

Thanks for the help, but I think it won't work in this case.

The reason I wanted to dump into one static table was because my query
built by the SQL has many different tables involved depending on what
the user wants to search by. In the end, what the search returns does
not include all of the subtables and fields I want in my reports, so I
can't build reports directly from that form. Also, I want it to dump
into one table because the user could either enter the ID's by hand
into this "ztable" OR, by using this form based 'make table' SQL.

So in the end, i need to find some way to say via code: "Take the ID
field from my form generated recordset and dump it into my zTable".

Is there a way to do this?

so I can then open various reports using

those pre-found record IDs.

Why not just save the "where" clause, and pass that to the form, or report
in question? (that is what the where clause is for!!).

The normal approach is to build a un-bound form (a un-bound form is a form
that is NOT attached to a table - these forms are typicaly desiged for user
interface face stuff like promtps, print buttions etc).

The following screen shots are all un-bound forms, and they simply prompt
the user for informaton.

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The above shold give you some ideas

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query. And, NO FORMS conditions are
placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause.

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade. In a nut shell, I am saying to advoid forms expressions in the
actual sql....

For a date range, we could put two calendar contorls on the screen. The code
could be:


dim strWhere as string
dim strStartDate as string
dim strEndDate as string


strStartDtae = "#" & format(me.StartDateContorl,"mm/dd/yyyy") & "#"
strEndDate = "#" & format(me.EndDateContorl,"mm/dd/yyyy") & "#"

strWhere = "InvoiceDate is between " & strStartDate & " and " & strEndDate

docmd.openReport "InvoiceReport",acViewPreview,,strWhere

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[Albert D.Kallal]

Yes, you can use that table.....

just make the were clause use a sub-query.

dim strWhere as string

strWhere = "id in (select id from temptable)"

docmd.openReport "myReprot",acviewPreview,,strWhere

So in the end, i need to find some way to say via code: "Take the ID
field from my form generated recordset and dump it into my zTable".

Is there a way to do this?

Remember, the standard where clause I talk about is a VALID SQL where
clause. It is perfectly legal to use a sub-query as I out line above. The
ONLY common factor you need is the id...the "additional" fields do not have
to be common between the two tables....

So, yes...you can well use the above syntax to restrict a form, or report to
a given list of "id" in another table...