creating forms for pass-through queries

[Cire via AccessMonster.com]

Hi all, i'm pretty new to access, picked up a book that doesnt have much on
forms thankfully its from the library so i can return it, recommendations
appreciated. anyway back to my questions.

Is forms creation for pass-through queries very sluggish as in slow?
comparing to linked tables, it is so slow..it seems the query is actually run
in the background while you are creating the form...is there a way not to run
the query but still create the form? or access has to "see" the results from
the query before allowing creation. If this is the case, the workaround will
be to manually key in data into combo/list down boxes right? if this is so
can anyone refer me to a good book that covers this? and here's an example to
illustrate my point.

i've got multiple tables that the pass-through query returns. but i want the
user only to select certain options to act as filters. i.e. name,
organisation, status and reference_number

so in 1 combo/list down box you have all the organisations, 1 has all types
of status and so on. so when the user selects his desired criteria, the pass-
through query will run with these parameters and goes back to the server to
extract the data. if this is not possible will i have manually have to create
a table in access to store the data than run the forms to query that table?
or are there other alternatives?

Thanks
Eric

 

[strive4peace]

Make a temporary table with limited records to use for
designing the form. Once the form is done, then change the
RecordSource to the Pass-Through

You can do a Make-Table query on your pass-through and set
the TopValues property to be something like 50, which will
give you 5o records to work with while you are testing --
then you don't have to worry about changing real data also.

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[Cire via AccessMonster.com]

Make a temporary table with limited records to use for
designing the form. Once the form is done, then change the
RecordSource to the Pass-Through

You can do a Make-Table query on your pass-through and set
the TopValues property to be something like 50, which will
give you 5o records to work with while you are testing --
then you don't have to worry about changing real data also.

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

Hi all, i'm pretty new to access, picked up a book that doesnt have much on
forms thankfully its from the library so i can return it, recommendations

[quoted text clipped - 22 lines]

Thanks
Eric

Thanks, btw any good books to recommend on form creation?

 

[Cire via AccessMonster.com]

ok more questions: the filter button works fine now but the export to excel
doesn't. apparently the export command button still exports the whole list of
data instead of the list after the filter had been applied.
i used a marcro: transfer spreadsheet and linked the command button to the
macro. Heres the code of the button:

Private Sub exportExcel_Click()
On Error GoTo Err_exportExcel_Click

Dim stDocName As String

stDocName = "exportExcel"
DoCmd.RunMacro stDocName

Exit_exportExcel_Click:
Exit Sub

Err_exportExcel_Click:
MsgBox Err.Description
Resume Exit_exportExcel_Click

End Sub

so how do i make the action export only the list that has the user's filter
applied to? i'm veryyy raw at VB code and this code was generated when i used
the command button wizard...
will appreciate advice and solutions. thanks

 

[Cire via AccessMonster.com]

oh the filter doesn't work! i still have to manually click "filter by
form" icon on the menu bar before entering the parameters then clicking on
apply filter icon than click on datasheet view to see my filtered results.
Can all this be done on the form itself and not via the menubar? i.e. the
form opens up in default "filter by form" view, you select and/or enter the
required criteria. access builds the sql statement, sends it as a pass-
through to the server, retrieves output, displays in datasheet form, then
have the option to export to a excel document.
but there is possibly a problem. I do not want to display all the criteria
on the form as some are mandatory and cannot be adjusted. so do i display
only the criteria that can be altered, and make the mandatory fields non-
visible? so access still sees the entire statement

and ano question, how do u open the excel file automatically after the
exporting of the data is completed?

sorry for the many questions, i'm just trying to design a good user interface
if all else fails, i guess i have to train my users to do it manually..

 

[Cire via AccessMonster.com]

oh the filter doesn't work! i still have to manually click "filter by
form" icon on the menu bar before entering the parameters then clicking on
apply filter icon than click on datasheet view to see my filtered results.
Can all this be done on the form itself and not via the menubar? i.e. the
form opens up in default "filter by form" view, you select and/or enter the
required criteria. access builds the sql statement, sends it as a pass-
through to the server, retrieves output, displays in datasheet form, then
have the option to export to a excel document.
but there is possibly a problem. I do not want to display all the criteria
on the form as some are mandatory and cannot be adjusted. so do i display
only the criteria that can be altered, and make the mandatory fields non-
visible? so access still sees the entire statement

and ano question, how do u open the excel file automatically after the
exporting of the data is completed?

sorry for the many questions, i'm just trying to design a good user interface
if all else fails, i guess i have to train my users to do it manually..

anyone? maybe i explained it too complicated. to summarize my point:

1. i need a form to open in the filter by form view-by default when u launch
the form
2. i need combo boxes in the filter by form view for some of the criteria, to
enable users to select from a list of options
3. all these will create a sql statement together with other mandatory
criteria which i will include in boxes that are not visible.
4. this sql statement is a pass-through in which access will send to the sql
backend server and retrive the data
5. access will display the data in datasheet form

i hope this is clear enough.
Thanks

 

[strive4peace]

here is one idea:

it sounds like there are 3 things going on here:

1. construct the WHERE clause for the SQL statement

2. save the pass-thru using the Where clause in your
pass-through SQL as a query

3. then open the form based on that query

'------------------------------ ConstructSQLAndOpenForm

Private Function ConstructSQLAndOpenForm()

dim mWhere as string, mSQL as string
mWhere = ""

If not IsNull(me.text_controlname ) Then
mWhere = "([TextFieldname]= '" &
me.controlname_for_text & "')"
end if

'subsequent criteria needs to concatenate AND

If not IsNull(me.date_controlname ) Then
if len(mWhere) > 0 then mWhere = mWhere & " AND "
mWhere = mWhere & "([DateFieldname]= #" &
me.controlname_for_date & "#)"
end if

If not IsNull(me.numeric_controlname ) Then
if len(mWhere) > 0 then mWhere = mWhere & " AND "
mWhere = mWhere & "([NumericFieldname]= " &
me.controlname_for_number & ")"
end if

'etc

if len(mWhere) > 0 then
mWhere = " WHERE " & mwhere
end if

'construct SQL for the pass-through query
mSQL = "SELECT . . ."

'make query
MakeQuery mSQL,

'open the form
DoCmd.OpenForm "formname"

End Function

'------------------------------

Then, in a general module

'------------------------------ MakeQuery
Sub MakeQuery(ByVal pSql As String, ByVal qName As String)
'modified 10-22-05
On Error GoTo MakeQuery_error

Dim mStr As String, mBooMake As Boolean

'if query already exists, update the SQL
'if not, create the query

mBooMake = True

DoCmd.Echo False
DoCmd.SetWarnings False
On Error Resume Next
Err.Number = 0
mStr = CurrentDb.QueryDefs(qName).Name
If Err.Number = 0 Then mBooMake = False
On Error GoTo MakeQuery_error
DoCmd.Echo True
DoCmd.SetWarnings True

If mBooMake Then
CurrentDb.CreateQueryDef qName, pSql
Else
CurrentDb.QueryDefs(qName).sql = pSql
End If

MakeQuery_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

MakeQuery_error:
MsgBox Err.Description, , "ERROR " & Err.Number & "
MakeQuery"
DoCmd.Echo True
DoCmd.SetWarnings True
Stop
'Press F8 to step through code and find problem
'comment out when program is debugged
Resume
Resume MakeQuery_exit
End Sub
'------------------------------

your form would then be based on the query

If you want to change the filter, close the form and go back
to the form where you construct the SQL and make the query.


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[Cire via AccessMonster.com]

here is one idea:

it sounds like there are 3 things going on here:

1. construct the WHERE clause for the SQL statement

2. save the pass-thru using the Where clause in your
pass-through SQL as a query

3. then open the form based on that query

'------------------------------ ConstructSQLAndOpenForm

Private Function ConstructSQLAndOpenForm()

dim mWhere as string, mSQL as string
mWhere = ""

If not IsNull(me.text_controlname ) Then
mWhere = "([TextFieldname]= '" &
me.controlname_for_text & "')"
end if

'subsequent criteria needs to concatenate AND

If not IsNull(me.date_controlname ) Then
if len(mWhere) > 0 then mWhere = mWhere & " AND "
mWhere = mWhere & "([DateFieldname]= #" &
me.controlname_for_date & "#)"
end if

If not IsNull(me.numeric_controlname ) Then
if len(mWhere) > 0 then mWhere = mWhere & " AND "
mWhere = mWhere & "([NumericFieldname]= " &
me.controlname_for_number & ")"
end if

'etc

if len(mWhere) > 0 then
mWhere = " WHERE " & mwhere
end if

'construct SQL for the pass-through query
mSQL = "SELECT . . ."

'make query
MakeQuery mSQL,

'open the form
DoCmd.OpenForm "formname"

End Function

'------------------------------

Then, in a general module

'------------------------------ MakeQuery
Sub MakeQuery(ByVal pSql As String, ByVal qName As String)
'modified 10-22-05
On Error GoTo MakeQuery_error

Dim mStr As String, mBooMake As Boolean

'if query already exists, update the SQL
'if not, create the query

mBooMake = True

DoCmd.Echo False
DoCmd.SetWarnings False
On Error Resume Next
Err.Number = 0
mStr = CurrentDb.QueryDefs(qName).Name
If Err.Number = 0 Then mBooMake = False
On Error GoTo MakeQuery_error
DoCmd.Echo True
DoCmd.SetWarnings True

If mBooMake Then
CurrentDb.CreateQueryDef qName, pSql
Else
CurrentDb.QueryDefs(qName).sql = pSql
End If

MakeQuery_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

MakeQuery_error:
MsgBox Err.Description, , "ERROR " & Err.Number & "
MakeQuery"
DoCmd.Echo True
DoCmd.SetWarnings True
Stop
'Press F8 to step through code and find problem
'comment out when program is debugged
Resume
Resume MakeQuery_exit
End Sub
'------------------------------

your form would then be based on the query

If you want to change the filter, close the form and go back
to the form where you construct the SQL and make the query.

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com
[quoted text clipped - 29 lines]

i hope this is clear enough.
Thanks

thanks for the help, unfortunately my vba knowledge is very limited, i'll
have to do lots of reading up to know how to use the code u have given. any
good vba for access books to recommend?

 

[strive4peace]

here are a couple good ones:

Grover Park George
http://www.amazon.com/exec/obidos/t...urchase/ref=pd_sxp_prev_0/104-0304104-4068752

John Viescas
http://www.bestwebbuys.com/John_Viescas-mcid_2355664.html?isrc=b-authorsearch

and, here is a link to more books:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#Books

Additionally, if you send me an email, I will send you the
first 3 chapters of a book I am writing on VBA as well as
some information on using SQL -- just put VBA, SQL in your
message to jog my memory

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

here is one idea:

it sounds like there are 3 things going on here:

1. construct the WHERE clause for the SQL statement

2. save the pass-thru using the Where clause in your
pass-through SQL as a query

3. then open the form based on that query

'------------------------------ ConstructSQLAndOpenForm

Private Function ConstructSQLAndOpenForm()

dim mWhere as string, mSQL as string
mWhere = ""

If not IsNull(me.text_controlname ) Then
mWhere = "([TextFieldname]= '" &
me.controlname_for_text & "')"
end if

'subsequent criteria needs to concatenate AND

If not IsNull(me.date_controlname ) Then
if len(mWhere) > 0 then mWhere = mWhere & " AND "
mWhere = mWhere & "([DateFieldname]= #" &
me.controlname_for_date & "#)"
end if

If not IsNull(me.numeric_controlname ) Then
if len(mWhere) > 0 then mWhere = mWhere & " AND "
mWhere = mWhere & "([NumericFieldname]= " &
me.controlname_for_number & ")"
end if

'etc

if len(mWhere) > 0 then
mWhere = " WHERE " & mwhere
end if

'construct SQL for the pass-through query
mSQL = "SELECT . . ."

'make query
MakeQuery mSQL,

'open the form
DoCmd.OpenForm "formname"

End Function

'------------------------------

Then, in a general module

'------------------------------ MakeQuery
Sub MakeQuery(ByVal pSql As String, ByVal qName As String)
'modified 10-22-05
On Error GoTo MakeQuery_error

Dim mStr As String, mBooMake As Boolean

'if query already exists, update the SQL
'if not, create the query

mBooMake = True

DoCmd.Echo False
DoCmd.SetWarnings False
On Error Resume Next
Err.Number = 0
mStr = CurrentDb.QueryDefs(qName).Name
If Err.Number = 0 Then mBooMake = False
On Error GoTo MakeQuery_error
DoCmd.Echo True
DoCmd.SetWarnings True

If mBooMake Then
CurrentDb.CreateQueryDef qName, pSql
Else
CurrentDb.QueryDefs(qName).sql = pSql
End If

MakeQuery_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

MakeQuery_error:
MsgBox Err.Description, , "ERROR " & Err.Number & "
MakeQuery"
DoCmd.Echo True
DoCmd.SetWarnings True
Stop
'Press F8 to step through code and find problem
'comment out when program is debugged
Resume
Resume MakeQuery_exit
End Sub
'------------------------------

your form would then be based on the query

If you want to change the filter, close the form and go back
to the form where you construct the SQL and make the query.

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

oh the filter doesn't work! i still have to manually click "filter by
form" icon on the menu bar before entering the parameters then clicking on

[quoted text clipped - 29 lines]

i hope this is clear enough.
Thanks

thanks for the help, unfortunately my vba knowledge is very limited, i'll
have to do lots of reading up to know how to use the code u have given. any
good vba for access books to recommend?

 

[Cire via AccessMonster.com]

here are a couple good ones:

Grover Park George
http://www.amazon.com/exec/obidos/t...urchase/ref=pd_sxp_prev_0/104-0304104-4068752

John Viescas
http://www.bestwebbuys.com/John_Viescas-mcid_2355664.html?isrc=b-authorsearch

and, here is a link to more books:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#Books

Additionally, if you send me an email, I will send you the
first 3 chapters of a book I am writing on VBA as well as
some information on using SQL -- just put VBA, SQL in your
message to jog my memory

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com
[quoted text clipped - 124 lines]

have to do lots of reading up to know how to use the code u have given. any
good vba for access books to recommend?

between a quick question, what does "me." mean? izzit a vba syntax or part of
the control name? and where do i put the function code in? within the form
code itself? than after placing the text/combo/list boxes, i place their
names in btw the round brackets?

 

[strive4peace]

"Me" refers to the form or report that you are behind when
you are writing VBA code

It is not part of the control name, it is a reference to the
form/report where the control resides

for instance:

If not IsNull(me.text_controlname ) Then
mWhere = "([TextFieldname]= '" &
me.controlname_for_text & "')"
end if

me.text_controlname

is a control containing a text value on the form that the
code is behind

I use Me. in code so that Access prompts me for properties
and methods that are applicable. You do not have to use Me
or a form reference -- Access will assume that is what you
mean if it doesn't interpret it another way -- it just makes
things more clear

make a command button to execute the code -- use the OnClick
event

if you use Me. when you type code, Access will prompt you --
you can type letters (or arrow keys) to re-position the list
and the press TAB when what you want is highlighted


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com


Cire via AccessMonster.com wrote:

 

[Cire via AccessMonster.com]

"Me" refers to the form or report that you are behind when
you are writing VBA code

It is not part of the control name, it is a reference to the
form/report where the control resides

for instance:

If not IsNull(me.text_controlname ) Then
mWhere = "([TextFieldname]= '" &
me.controlname_for_text & "')"
end if

me.text_controlname

is a control containing a text value on the form that the
code is behind

I use Me. in code so that Access prompts me for properties
and methods that are applicable. You do not have to use Me
or a form reference -- Access will assume that is what you
mean if it doesn't interpret it another way -- it just makes
things more clear

make a command button to execute the code -- use the OnClick
event

if you use Me. when you type code, Access will prompt you --
you can type letters (or arrow keys) to re-position the list
and the press TAB when what you want is highlighted

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

between a quick question, what does "me." mean? izzit a vba syntax or part of
the control name? and where do i put the function code in? within the form
code itself? than after placing the text/combo/list boxes, i place their
names in btw the round brackets?

Ah i see, crystal, in ur code i have to manually write the sql statement in
it, rather tedious seeing that i've to make sure the statements are
adequately joined together. I've already created a pass-through query in the
query window, is it possible to modify the code to just recall that statement
itself, then add the user input of WHERE conditions to it