I am a newbie to Access & VBA.
I have a strange problem. I am writing a database for a political candidate
in a ward with 24 precincts. There are over 13,000 residents in a table.
The candidate wants to make seperate sets of mailing labels via precincts
(about 400 residents per precinct). I have to put the precinct number in the
header (not the mailing labels) and give the candidate the option to choose
which avery label number. I chosed to used a query that will received the
precinct number from a combobox and a text box to give the avery label number
to the mail merge module.
The database is a secure database with a workgroup file (mdw). The database
is in Microsoft Access 2000 format (mdb) and runs on Microsoft Access 2003
from Microsoft Office 2003.
In addition, I am running the dialog form from the switchboard. (The problem
only happens from the switchboard after the user logs in)
In the code under opendatasource if I used the
SQLStatement:="SELECT * FROM [MyFormQuery]"
I get a login prompt (which I do not want, because the user will already be
logged in on the database). In addition, I get a parameter prompt for the
precinct (but I have already chosed the precinct from the dialog form's
To get around from having to be prompt by the login box I chosed to change
the connection to:
Connection:="DSN=MS Access Database;DBQ=" & CurrentDb.Name & ";"
"SystemDB=C:WorkGroupSecurity1.mdw;UID=UserName; PWD=userPassword;FIL=RedISAM;", _
SQLStatement:="SELECT * FROM [ResidentTable]", _
But now I cannot used my form query!!!
In addition, when I am using my form query I get results back within one
minute but even when I figured out how to used the SQLStatement to get the
precincts using a variable named "UserPrecinct" it took 30 whole minutes.
And, the precinct number had to be put in the mailing label for it to work.
The candidate do not want the precinct number inside the mailing labels (but
he wants it in the header).
How do I get my form query to work from the switchboard on a secure
workgroup database, without being prompt with a login box and a parameter box?
Note: If I do not run the form query from the switchboard everything works
just find. Only when I run it from the switchboard that I have problems.
The subroutines header in the module is:
Public Sub MakeLabels(HeadingTitle As String, UserPrecinct As String,
AveryNumber As String, MyFormQuery As String)
Please help me the best you can.
I am stumped!