VB & ACCESS How to create a report based on Form Input

[campbere]

I want to creat a report in MS Excel. The data is in a MS ACCESS table called Journal_Status. I want to select all the data to display in Excel but restrict it by input from combo boxes on forms. The combo boxes are Month, Year, and Journal Title. So I would like to Select * from Journal_Status and then use the data in then appropriate combo boxes to form the where clause. I use a check box next to the combo box to for the user to indicate that is what they want to restrict by. So if they select a month from the Month combo box and have the check box marked it will restrict by that. If the check box isn't checked then nothing. Can someone help me with this? I tried looking at VB Help connecting to Access but it looked like they were only discussing one shot queries with an a specified value as a restriction.

Thanks

 

[gcole]

When you finish the form, have a query run that merges all of your data and creates a table with it. Here is a sample:

Private Sub cmdsum_Click()
'create personal copy of accts
sql_def = "SELECT accts.sm_id,ACCTS.Account, ACCTS.state, ACCTS.city, ACCTS.street into sm_acct FROM ACCTS INNER JOIN holdsm ON ACCTS.sm_id = holdsm.sm_id ORDER BY ACCTS.Account;"

DoCmd.SetWarnings (False)
'turn overwrite warnings off
DoCmd.RunSQL (sql_def)
DoCmd.SetWarnings (True)
End sub

 

[campbere]

All the data is in one table Journal_Status. Is it neccessary to create a new table? How do I connect the VB application to MS ACCESS?

Finally after I create the new table or result set how do I access it?

 

[patil]

as far as i understand your problem your concern is with building the SQL depending on whether the user checks some of the boxes. you can use something like the code below.

mySQL = "select * from journal_status"
if chkmonth.value then
mySQL = mySQL & " where month = " & 'monthvalue determined
from the combobox as int or string
end if

if chkyear.value then
if chkmonth.value then
mySQL = mySQL & " and year = " & 'year value determined
from the combobox as int or string
else
mySQL = mySQL & " where year = " & 'year value determined
from the combobox as int or string
end if
end if

if chkname.value then
if chkmonth.value or chkyear.value then
mySQL = mySQL & " and name = " & 'year value determined
from the combobox as string
else
mySQL = mySQL & " where name = " & 'name value determined
from the combobox as string
end if
end if


and as far as the connection to the ms access database is concerned that can be done very easily using the jet driver which is easier to use as compared to the ODBC driver.

regards,
manish.