Combo Box Drop Down List 1

[ryanmc]

Hi,

I am trying to create a dorp down combo box that lists all the tables in my databse that follows particular criteria (month and year - June2000). I will be adding table to the database at the end of every month, so I want the drop down combo box to automatically add the tabel to the list if it follows the criteria I mentioned earlier. Does anyone know how I would set this up?

Thank you, Ryan.

 

[ryanmc]

I really need help with this. If anyone could offer some assistance, I would really appreciate it.

thanks, Ryan

 

[dzshanno]

Ryan,

Populating a combobox with a list of values determined at run-time is not easy.

Unfortunately access is not designed so you can add or delete items from the combobox quickly

It is possible however and most good VBA books will describe the process, It fairly long winded

An alternative may be to create a table populated by the tabledefs collection and use that table as the source of the combo box

the idea would be something like

A some point before the form containing the combo box is opened
*****************************************

dim tdf as tabledef
dim db as dao.database

set db = currentdb()

for each tdf in db.tabledefs
if tdf.datecreated = <the date you want>
update tblList_of_tables
set table_name = tdf.name
end if
next tdf

**************************************

for the source code for the combobox itself use tblList_of_tables

**************************************

hope that helps

 

[braindead2]

set the comboboxes source to

select name from msysobjects where right([name],3) in(jan,feb,mar,apr,may,jun,jul,aug,sept,oct,nov,dec)
refresh as needed
me.refresh

 

[ryanmc]

Thanks guys,

Braindead2, I think what you've suggested sounds like the easiest option. Can you please explain in more detail how I set my combo box source as you suggested. You'll have to excuss me, I'm a junior programmer and I'm really having a lot of problems with this one.

Anyone else who can offer any assistance, I would really appreciate it as well.

Thanks, Ryan.

 

[braindead2]

under the comboboxes properties set the row source type to table/query
under the comboboxes row source paste in
SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((Left([Name],3)) In (&quot;Jan&quot;,&quot;FEB&quot;,&quot;Mar&quot;,&quot;APR&quot;,&quot;MAY&quot;,&quot;JUN&quot;,&quot;JUL&quot;,&quot;AUG&quot;,&quot;SEPT&quot;,&quot;OCT&quot;,&quot;NOV&quot;,&quot;DEC&quot));

This will then display objects whos names start with the above.

In Access there is a system table (&quot;msysobjects&quotthat contains the objects just unhide the table by clicking tools,options,view,system objects then build the query using the grid just as you would any other table.
good luck

 

[ryanmc]

Braindead2,

Thanks you so much! You are amazing and I really, really appreciate your help.

Thanks again,
Ryan.

 

[ryanmc]

Braindead2 or anyone esle who might be able to help,

Now that I've created my combo box that lists all the tables by month and year (ie:June2001), I want to perform calculations to the table that the user selects. What I want to do is, according to building name within infomation from the table selctected, I want to assign either a P for Primary or S for Secondary accordingly. How do I set up my query to do this? Mind you, it's completely dependant on the table the user selects in the combo box.

Thanks again everyone,
Ryan.

 

[braindead2]

USE VBA
dim strtblname as string, strsql as string

strtblname = nz(me.combobox1.value)
strsql = &quot;update &quot; & strtblname 'need more info to complete query
docmd.runsql(strsql)

the above code won't work as I dont have enough information to proceed
&quot;I want to assign either a P for Primary or S for Secondary accordingly&quot;

what do you want to assign the P or S to?
According to what?
based on what event?

a more detailed description of exactly what you want to do and the fields in your table would be most helpful
Good luck