Integrating the code for a max.date function with a check box controlling it and

Hi Tom

Did you happen to see look at the other post that I gave you. I received an
interesting response from Dennis Snelgrove which I think may work well,
except I am unable to be able to alter the bullion field to -1. If I was able
to do that we may be able to do it without creating a temporary table
altogether.

Also it may be worth keeping in mind that I don’t foresee multiple users at
one time being a problem as the database is department specific, meaning that
only three people will use it; so multiple users can be avoided if that makes
a significant difference to the development of this feature.

The fact that it is to be used by a small number of people is after all the
reason why I am building it, if it were to be used by a large portion of the
company, the company would pay a more qualified and competent person to do
the job; such as yourself.

A challenge defiantly, do you think it can be done? Is there a way of editing,
even temporarily?


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200510/1

"Tom Ellison" wrote in message
...
Dear Chris:

Your next project sounds somewhat interesting.

I believe I am leaning toward having a local table to store the user's
selected check boxes (booleans). This local table would be created or
cleared when the form is entered. It would need whatever columns uniquely
identify the row being selected.

You would clear and repopulate this table when the form is entered, or
whenever the set of rows displayed on the form changes, depending on how
the
design functions in this respect.

By using a local table, two different users could create reports on
different computers independently.

I concur with Tom. This is how I usually handle what you want to do, Chris.

--

Ken Snell
MS ACCESS MVP

Hey, Ken,

As was said so well by Bartles and James:

thank you for your support!

Tom


"Ken Snell [MVP]" wrote in message
...
"Tom Ellison" wrote in message
...
Dear Chris:

Your next project sounds somewhat interesting.

I believe I am leaning toward having a local table to store the user's
selected check boxes (booleans). This local table would be created or
cleared when the form is entered. It would need whatever columns
uniquely
identify the row being selected.

You would clear and repopulate this table when the form is entered, or
whenever the set of rows displayed on the form changes, depending on how
the
design functions in this respect.

By using a local table, two different users could create reports on
different computers independently.

I concur with Tom. This is how I usually handle what you want to do,
Chris.

--

Ken Snell
MS ACCESS MVP

Hi Ken and Tom

Ok if you guys think that is the better option sounds like a winner to me!!

The only problem is with my newbie status I am not sure that I follow exactly
what you mean. The ‘local table’ you referred to, how does it work and more
importantly how do I build it to get it to work for me?

I am guessing that I build a normal table based of the query, or should it
just include all the same fields.

When you said that it must contain all the fields that uniquely identify the
records contained in the query. The query contains numerous foreign key
fields and a primary key field for the transaction table, would I need to
include all these fields or just the attached fields and the link is through
the query.

For example: if in the query there is manufacturer name searched for by the
manufacturer id (primary key for the manufacturer table) in the local table
do I include both the Id and the manufacturer name.

I am just sure exactly how to go about this. I really appreciate the help, I
am certainly a willing student if you are a willing teacher.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200510/1

Ok I have worked out how to get it to send the results of the query to a
temporary table, by placing an INTO TestTempTable after the SELECT portion of
the SQL statement.

Now when I try to open the form linked to the query it says that the form can
not be used a Row Source.

Also I need to get the button that launches the query to now open the table
instead on the ON CLICK event function. Also the bullion field that now
exists in the transaction table ‘Report’ that will be used to select those
records that the user desires to report is instead of being a check box it is
-1 or 0, how do I get it to be displayed as a check box.

Also I still need to work out how to get it to delete itself when it is
closed and refresh if the criteria of the query is changed on the form.

Ounce I have done that and got the report to display only the records with a
postive value for record, or be based of another query based on the temporary
table, it’s done.

I am starting to believe that this could be possible!!

If you have any words of wisdom for these few challenges or recommend other
posting I would really appreciate it. Where would people like me be without
people like you guys.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200510/1

Some general comments:

(1) I don't use a "make table" query to generate a temporary table. (That's
what a "SELECT INTO" query is.) Instead, have the table already in existence
in the database, and use a delete query to empty it and then an append query
to add the records you want to it. When you're all done, run the delete
query again to empty the table.

(2) Not sure what you mean by saying that the form cannot be used as a Row
Source? Do you mean that the form whose record source is the query that you
have modified into the "SELECT INTO" query is giving an error saying that
the query cannot be used as its RecordSource? That would be a valid and
expected error, as a form cannot use an action query (append, delete,
make-table, or update query) as its Recordsource. Instead, change the form
to use the temporary table as its record source.

(3) I generally use various VBA functions/subroutines that I write for doing
the various steps (delete the data from the table, append the data to the
table, etc.), and call the code from other code that then opens the form
that is desired.

(4) If data are updated in the form, you'll then need to have a query that
updates the data in the original table from what is in the temporary table.
Again, I use VBA code to do all of this.

--

Ken Snell
MS ACCESS MVP



"Chris W via AccessMonster.com" u12677@uwe wrote in message
news:55cdaf0262fd2@uwe...
Ok I have worked out how to get it to send the results of the query to a
temporary table, by placing an INTO TestTempTable after the SELECT portion
of
the SQL statement.

Now when I try to open the form linked to the query it says that the form
can
not be used a Row Source.

Also I need to get the button that launches the query to now open the
table
instead on the ON CLICK event function. Also the bullion field that now
exists in the transaction table 'Report' that will be used to select those
records that the user desires to report is instead of being a check box it
is
-1 or 0, how do I get it to be displayed as a check box.

Also I still need to work out how to get it to delete itself when it is
closed and refresh if the criteria of the query is changed on the form.

Ounce I have done that and got the report to display only the records with
a
postive value for record, or be based of another query based on the
temporary
table, it's done.

I am starting to believe that this could be possible!!

If you have any words of wisdom for these few challenges or recommend
other
posting I would really appreciate it. Where would people like me be
without
people like you guys.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200510/1

Hi Ken, Thanks keeping an eye on the thread I appreciate it

I have created the table with its own primary key, rather than maintain the
primary key as the primary key from the transaction table, I hope that that
is correct.

2) Not sure what you mean by saying that the form cannot be used as a Row
Source?

This was an error that was occurring when I conducted a make table query, it
no longer occurs, so if your not worried I’m not.

(4) If data are updated in the form, you'll then need to have a query that
updates the data in the original table from what is in the temporary table.

Do you think it could be done in the on event criteria of a button “Launch
query”. Currently I have button that when it is clicked the query opens. I
would, ideally, like to repeat this where it opens the table instead. Could
you have attached to this button on the ‘on click’ event have it update the
query.

(3) I generally use various VBA functions/subroutines that I write for doing
the various steps (delete the data from the table, append the data to the
table, etc.)

Do you think you could tell me the code or step me through it, because I have
never written in VBA before.

Thanks mate


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200510/1

This was an error that was occurring when I conducted a make table query, it no longer occurs, so if your not worried I’m not.

Ok I am worried, it is now giving the error “query can not be used as a row
source” when I try to open up the form through which you enter the criteria
for the query.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200510/1

Sounds as if you have a combo box or list box on the form that is trying to
use your 'make table' query as a row source. You'll need to change the Row
Source to a query that will give the correct data to the control.


--

Ken Snell
MS ACCESS MVP

"Chris W via AccessMonster.com" u12677@uwe wrote in message
news:55cf210bb8abc@uwe...
This was an error that was occurring when I conducted a make table
query, it no longer occurs, so if your not worried I'm not.

Ok I am worried, it is now giving the error "query can not be used as a
row
source" when I try to open up the form through which you enter the
criteria
for the query.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200510/1

A function that would delete records from a specific table would be this
(pass the name of the table to the function):

Public Function DeleteRecordsFromTable(strTableName As String) As Boolean
Dim dbs As DAO.Database
On Error GoTo Err_Handle
Set dbs = CurrentDb
dbs.Execute "DELETE * FROM [" & strTableName & "];", dbFailOnError
DeleteRecordsFromTable = True
Exit_TheFunction:
dbs.Close
Set dbs = Nothing
Exit Function
Err_Handle:
DeleteRecordsFromTable = False
Resume Exit_TheFunction
End Function


A function that would run your 'append query' would be this (pass the name
of the query to the function) -- note that this same function will work if
you pass the name of a 'make table' query to the function:

Public Function AppendRecordsToTable(strQueryName As String) As Boolean
Dim dbs As DAO.Database
On Error GoTo Err_Handle
Set dbs = CurrentDb
dbs.Execute strQueryName, dbFailOnError
AppendRecordsToTable = True
Exit_TheFunction:
dbs.Close
Set dbs = Nothing
Exit Function
Err_Handle:
AppendRecordsToTable = False
Resume Exit_TheFunction
End Function


--

Ken Snell
MS ACCESS MVP



"Chris W via AccessMonster.com" u12677@uwe wrote in message
news:55cf0609a4d54@uwe...
Hi Ken, Thanks keeping an eye on the thread I appreciate it

I have created the table with its own primary key, rather than maintain
the
primary key as the primary key from the transaction table, I hope that
that
is correct.

2) Not sure what you mean by saying that the form cannot be used as a Row
Source?

This was an error that was occurring when I conducted a make table query,
it
no longer occurs, so if your not worried I'm not.

(4) If data are updated in the form, you'll then need to have a query
that
updates the data in the original table from what is in the temporary
table.

Do you think it could be done in the on event criteria of a button "Launch
query". Currently I have button that when it is clicked the query opens. I
would, ideally, like to repeat this where it opens the table instead.
Could
you have attached to this button on the 'on click' event have it update
the
query.

(3) I generally use various VBA functions/subroutines that I write for
doing
the various steps (delete the data from the table, append the data to the
table, etc.)

Do you think you could tell me the code or step me through it, because I
have
never written in VBA before.

Thanks mate


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200510/1