Question A table selection combo box

[Coldsteel]

Hello All,

I am new to access and I have been trying to figure out a way to do this. I am trying to build a combo box that would prompt the user to select a table and then apply the selected table to a report. Is that possible? For example the tables would be June Sales/ July Sales/ Aug Sales etc. If the user selects Aug Sales then the control source for the report would use that table's infomation. Any help would be greatly appreciated

Thanks
Mike

 

[HiTechCoach]

Mike,

Welcome to AWF!

For example the tables would be June Sales/ July Sales/ Aug Sales etc.

If all the tables are identical, then they probably should all the in the same table with a field for the period. All you should need to do is select the desired period from a combo box. The report's query can reference the combo box on the for to get the criteria.

I have never had the need to do this with all the databases I have created.

If you really wanted to do what you ask, then

The only way this will work is if all the tables have the same exact design. ( **** If they are all the same, this indicates that is shousl be a singl table, not multiple tables).


I would use a form to select the desire table. Once selected use VBA code to modify the QueryDef for the report to the SQL to select he desired data. Once the query has been updated, then your can open the report.

Note: If designed properly, all you would have to do is select the desired period from a form using a combo box. The report's query can reference the combo box on the for to get the criteria.

example form reference:

forms!frmGetPeriods.cboPeriod


Also see the attached example:

 

[Coldsteel]

Thanks for the help. I was wandering are there any examples of building a form to allow a user to select a table which I may learn from?

Thanks,
Mike

 

[HiTechCoach]

Thanks for the help. I was wandering are there any examples of building a form to allow a user to select a table which I may learn from?

Thanks,
Mike

Mike,

I am sure it is possible to get all the table names and load them into a combo box's row source. I current do it with reports and queries and forms. I have never had a need to do it with tables.

Before I possibly lead you astray, are all the tables identical?

 

[Coldsteel]

thanks for the reply. Yeah the tables have the same data. What I am trying to build is a sales database that uses three forms. Form 1 allows a user to input data, form 2 allows a user to edit data, and form 3 will be a page to run reports. I want to have a table selection cbo, because I have a query that tranfers a table monthly to another database so the users of form 2 cant alter the data entered from prev. months. But if there is a easier way to manage it this I am open to suggestions. I can post my database if someone can tell me how to compress the file

Thanks,
Mike

 

[HiTechCoach]

thanks for the reply. Yeah the tables have the same data. What I am trying to build is a sales database that uses three forms. Form 1 allows a user to input data, form 2 allows a user to edit data, and form 3 will be a page to run reports. I want to have a table selection cbo, because I have a query that tranfers a table monthly to another database so the users of form 2 cant alter the data entered from prev. months. But if there is a easier way to manage it this I am open to suggestions. I can post my database if someone can tell me how to compress the file

Thanks,
Mike

Mike,

Why do you not have all the data in a single table? This would sure make things sooooo much easier.

 

[Coldsteel]

Hitech,

Thanks for the help. I have put together all my tables into one called tbl_referral for reporting. What I would like to do is build a text box that would save the time in a table when the user selects a option for a cbo. Example in the form i have 2 opitions pending and closed in a status cbo, when the user selects closed i would like the time it was done and have it saved to the record know any examples on how to do this? Would a iff statment do?