If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
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 |
#22
|
|||
|
|||
"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 |
#23
|
|||
|
|||
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 |
#24
|
|||
|
|||
Integrating the code for a max.date function with a check box controlling it and
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 |
#25
|
|||
|
|||
Integrating the code for a max.date function with a check box controlling it and
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 |
#26
|
|||
|
|||
Integrating the code for a max.date function with a check box controlling it and
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 |
#27
|
|||
|
|||
Integrating the code for a max.date function with a check box controlling it and
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 |
#28
|
|||
|
|||
Integrating the code for a max.date function with a check box controlling it and
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 |
#29
|
|||
|
|||
Integrating the code for a max.date function with a check box controlling it and
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 |
#30
|
|||
|
|||
Integrating the code for a max.date function with a check box controlling it and
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
More Duplicate E-mails Outlook 2003 | John Smith | General Discussion | 36 | January 8th, 2007 05:36 PM |
Attn: Ken Snell - Displaying chemical structures in Access | Michele | General Discussion | 11 | May 13th, 2005 07:19 PM |
Automatically up date time in a cell | Mark | General Discussion | 5 | May 12th, 2005 12:26 AM |
*Another* OLK 2002 sendmail/SMTP problem | steviegb | General Discussion | 11 | March 23rd, 2005 12:59 AM |
OLE Object- the real question | Michelle | Using Forms | 18 | February 28th, 2005 04:04 AM |