Need MVP help

M

Mark Andrews

I have been stumping people on this newsgroup but I haven't given up yet.
Hopefully someone will be willing to try and understand the question and
help me out.

I am designing a system that has the user navigate to a form with a listbox.
The user can filter the listbox and change the underlying query that drives
the list box in various ways. Once they have filtered and sorted the
listbox how they
want they double click on a row and a new form opens which shows detail
records and any related records in subforms.

So The listbox might start out with 4000 records and the user filters it
down to 45 and then clicks on number 23 and the new form opens on record 23
of 45 and allows the user to edit details.

My issue is that I want to allow the form with the list box to have queries
that pull data from multiple tables (example: list accounts and show sum of
sales in one column and sort descending by sales) and allow filtering on
various columns (example filter where sales > 50000) and once I do that I
need to figure out the best way to show something like (when the user clicks
on record 23 I would like the detail form to show the underlying 45 accounts
sorted by sales descending). However the user would only be editing details
on accounts and see individual related sales records.

I made it work using this method:
- I created a table tblIDTemp (stored in front end, with two columns
(autonumber and column to hold ID number)
When user clicks on row:
- delete recs in tblIDTemp
- add new recs to tblIDTemp (based on current recs in listbox)
- query that drives the detail form always joins to tblIDTemp and sorts by
Autonumber column (this achieves the same filtering and sorting that the
listbox had)

Question is:
Any better ways to make this happen? Don't like all this deleting and
adding of records but can't figure any other way that would work. Guessing
my front end will grow like crazy.

Thanks in advance,
Mark
 
K

Klatuu

Rather than a List Box, I would suggest a datasheet subform and related
combo boxes to identify the filtering.

Here is an example of how I do this:

'---------------------------------------------------------------------------------------
' Procedure : SetInitFilters
' DateTime : 2/10/2008 14:15
' Author : Dave Hargis
' Purpose : Sets the form filter for each combo search box
'---------------------------------------------------------------------------------------
'
Private Function SetInitFilters() As String
Dim strFilter As String 'Used by Form Filtering procedures

On Error GoTo SetInitFilters_Error

With Me

'Build The Filter String
If .cboPriority <> "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([OrigReleaseQtr], '@@@@-@') =
""" & _
.cboOrigQtr & """"
End If

If .cboCurrQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([CurrReleaseQtr], '@@@@-@') =
""" & _
.cboCurrQtr & """"
End If

If .cboInitStatus <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

If .cboInitType <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitType] = " & .cboInitType
End If

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CcID] = " & Me.txtCostCenter

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[cenpID] = " & Me.txtProduct

If Len(strFilter) > 0 Then
.subInitiative.Form.Filter = strFilter
.subInitiative.Form.FilterOn = True
.subInitiative.Form.Requery
Else
.subInitiative.Form.FilterOn = False
End If

End With 'Me

SetInitFilters = strFilter

SetInitFilters_Exit:
On Error GoTo 0
Exit Function

SetInitFilters_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure SetInitFilters of VBA Document Form_frmDashBoard",
vbExclamation, conMsgTitle
GoTo SetInitFilters_Exit

End Function

'---------------------------------------------------------------------------------------
' Procedure : AddAnd
' DateTime : 2/9/2008 17:26
' Author : Dave Hargis
' Purpose : Adds the word AND to a filtering string when needed
'---------------------------------------------------------------------------------------
'
Public Function AddAnd(strFilterString) As String

On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If

AddAnd_Exit:
On Error GoTo 0

Exit Function

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of Module modUtilities", vbExclamation,
conMsgTitle
GoTo AddAnd_Exit

End Function

Then in the Double Click event of the subform, I open a detail form like
this:

Private Sub txtDescription_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmInitiative", , , , , , Me.txtInitID
End Sub
 
M

Mark Andrews

Thanks for the code, that might help me in the future. I think a subform
vs. a list box there are pros and cons of each and I like your method of
filtering.

The main issue which I think got missed (and that's what I need help on) is:

When you call frmInitiative using this code:
Private Sub txtDescription_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmInitiative", , , , , , Me.txtInitID
End Sub
Click to expand...

I assume you are opening frmInitiative to ONE record and using OpenArgs to
pass the value (from how it looks).

I would like to:
- filter based on related record SUM (example: Sum of sales dollars > 50000)
- datasheet subform or list box shows 45 account records (accountid,
accountname, sumofsales)
- double click on row 13
- open frmInitiative and show 45 account records and navigate to record 13
(however this form needs to be based on a query that is updatable)

The problem being "how do I get the detail form to show the 45 records
(accounts with sales > 50000 sorted by Sales DESC)"???????????

Hope this makes sense,
Any help is appreciated,
Mark

Klatuu said:
Rather than a List Box, I would suggest a datasheet subform and related
combo boxes to identify the filtering.

Here is an example of how I do this:

'---------------------------------------------------------------------------------------
' Procedure : SetInitFilters
' DateTime : 2/10/2008 14:15
' Author : Dave Hargis
' Purpose : Sets the form filter for each combo search box
'---------------------------------------------------------------------------------------
'
Private Function SetInitFilters() As String
Dim strFilter As String 'Used by Form Filtering procedures

On Error GoTo SetInitFilters_Error

With Me

'Build The Filter String
If .cboPriority <> "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([OrigReleaseQtr], '@@@@-@') =
""" & _
.cboOrigQtr & """"
End If

If .cboCurrQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([CurrReleaseQtr], '@@@@-@') =
""" & _
.cboCurrQtr & """"
End If

If .cboInitStatus <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

If .cboInitType <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitType] = " & .cboInitType
End If

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CcID] = " & Me.txtCostCenter

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[cenpID] = " & Me.txtProduct

If Len(strFilter) > 0 Then
.subInitiative.Form.Filter = strFilter
.subInitiative.Form.FilterOn = True
.subInitiative.Form.Requery
Else
.subInitiative.Form.FilterOn = False
End If

End With 'Me

SetInitFilters = strFilter

SetInitFilters_Exit:
On Error GoTo 0
Exit Function

SetInitFilters_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure SetInitFilters of VBA Document Form_frmDashBoard",
vbExclamation, conMsgTitle
GoTo SetInitFilters_Exit

End Function

'---------------------------------------------------------------------------------------
' Procedure : AddAnd
' DateTime : 2/9/2008 17:26
' Author : Dave Hargis
' Purpose : Adds the word AND to a filtering string when needed
'---------------------------------------------------------------------------------------
'
Public Function AddAnd(strFilterString) As String

On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If

AddAnd_Exit:
On Error GoTo 0

Exit Function

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of Module modUtilities", vbExclamation,
conMsgTitle
GoTo AddAnd_Exit

End Function

Then in the Double Click event of the subform, I open a detail form like
this:

Private Sub txtDescription_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmInitiative", , , , , , Me.txtInitID
End Sub


Mark Andrews said:
I have been stumping people on this newsgroup but I haven't given up yet.
Hopefully someone will be willing to try and understand the question and
help me out.

I am designing a system that has the user navigate to a form with a
listbox.
The user can filter the listbox and change the underlying query that
drives
the list box in various ways. Once they have filtered and sorted the
listbox how they
want they double click on a row and a new form opens which shows detail
records and any related records in subforms.

So The listbox might start out with 4000 records and the user filters it
down to 45 and then clicks on number 23 and the new form opens on record
23
of 45 and allows the user to edit details.

My issue is that I want to allow the form with the list box to have
queries
that pull data from multiple tables (example: list accounts and show sum
of
sales in one column and sort descending by sales) and allow filtering on
various columns (example filter where sales > 50000) and once I do that I
need to figure out the best way to show something like (when the user
clicks
on record 23 I would like the detail form to show the underlying 45
accounts
sorted by sales descending). However the user would only be editing
details
on accounts and see individual related sales records.

I made it work using this method:
- I created a table tblIDTemp (stored in front end, with two columns
(autonumber and column to hold ID number)
When user clicks on row:
- delete recs in tblIDTemp
- add new recs to tblIDTemp (based on current recs in listbox)
- query that drives the detail form always joins to tblIDTemp and sorts
by
Autonumber column (this achieves the same filtering and sorting that the
listbox had)

Question is:
Any better ways to make this happen? Don't like all this deleting and
adding of records but can't figure any other way that would work.
Guessing
my front end will grow like crazy.

Thanks in advance,
Mark
Click to expand...
Click to expand...
 
L

Larry Kahm

Mark,

You get the "detail" record to show all 45 records by displaying either a
continuous form or a form that is set to display in datasheet mode.

The OpenArgs parameter that is passed is usually an individual record. You
could use it as the parameter in the WHERE clause of the OpenForm statement.
That would drive the query in your form's recordsource to pull up all 45
records.

Hope that helps!

Larry

Mark Andrews said:
Thanks for the code, that might help me in the future. I think a subform
vs. a list box there are pros and cons of each and I like your method of
filtering.

The main issue which I think got missed (and that's what I need help on)
is:

When you call frmInitiative using this code:
Private Sub txtDescription_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmInitiative", , , , , , Me.txtInitID
End Sub
Click to expand...

I assume you are opening frmInitiative to ONE record and using OpenArgs to
pass the value (from how it looks).

I would like to:
- filter based on related record SUM (example: Sum of sales dollars >
50000)
- datasheet subform or list box shows 45 account records (accountid,
accountname, sumofsales)
- double click on row 13
- open frmInitiative and show 45 account records and navigate to record 13
(however this form needs to be based on a query that is updatable)

The problem being "how do I get the detail form to show the 45 records
(accounts with sales > 50000 sorted by Sales DESC)"???????????

Hope this makes sense,
Any help is appreciated,
Mark

Klatuu said:
Rather than a List Box, I would suggest a datasheet subform and related
combo boxes to identify the filtering.

Here is an example of how I do this:

'---------------------------------------------------------------------------------------
' Procedure : SetInitFilters
' DateTime : 2/10/2008 14:15
' Author : Dave Hargis
' Purpose : Sets the form filter for each combo search box
'---------------------------------------------------------------------------------------
'
Private Function SetInitFilters() As String
Dim strFilter As String 'Used by Form Filtering procedures

On Error GoTo SetInitFilters_Error

With Me

'Build The Filter String
If .cboPriority <> "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([OrigReleaseQtr], '@@@@-@') =
""" & _
.cboOrigQtr & """"
End If

If .cboCurrQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([CurrReleaseQtr], '@@@@-@') =
""" & _
.cboCurrQtr & """"
End If

If .cboInitStatus <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

If .cboInitType <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitType] = " & .cboInitType
End If

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CcID] = " & Me.txtCostCenter

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[cenpID] = " & Me.txtProduct

If Len(strFilter) > 0 Then
.subInitiative.Form.Filter = strFilter
.subInitiative.Form.FilterOn = True
.subInitiative.Form.Requery
Else
.subInitiative.Form.FilterOn = False
End If

End With 'Me

SetInitFilters = strFilter

SetInitFilters_Exit:
On Error GoTo 0
Exit Function

SetInitFilters_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure SetInitFilters of VBA Document Form_frmDashBoard",
vbExclamation, conMsgTitle
GoTo SetInitFilters_Exit

End Function

'---------------------------------------------------------------------------------------
' Procedure : AddAnd
' DateTime : 2/9/2008 17:26
' Author : Dave Hargis
' Purpose : Adds the word AND to a filtering string when needed
'---------------------------------------------------------------------------------------
'
Public Function AddAnd(strFilterString) As String

On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If

AddAnd_Exit:
On Error GoTo 0

Exit Function

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of Module modUtilities", vbExclamation,
conMsgTitle
GoTo AddAnd_Exit

End Function

Then in the Double Click event of the subform, I open a detail form like
this:

Private Sub txtDescription_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmInitiative", , , , , , Me.txtInitID
End Sub


Mark Andrews said:
I have been stumping people on this newsgroup but I haven't given up yet.
Hopefully someone will be willing to try and understand the question and
help me out.

I am designing a system that has the user navigate to a form with a
listbox.
The user can filter the listbox and change the underlying query that
drives
the list box in various ways. Once they have filtered and sorted the
listbox how they
want they double click on a row and a new form opens which shows detail
records and any related records in subforms.

So The listbox might start out with 4000 records and the user filters it
down to 45 and then clicks on number 23 and the new form opens on record
23
of 45 and allows the user to edit details.

My issue is that I want to allow the form with the list box to have
queries
that pull data from multiple tables (example: list accounts and show sum
of
sales in one column and sort descending by sales) and allow filtering on
various columns (example filter where sales > 50000) and once I do that
I
need to figure out the best way to show something like (when the user
clicks
on record 23 I would like the detail form to show the underlying 45
accounts
sorted by sales descending). However the user would only be editing
details
on accounts and see individual related sales records.

I made it work using this method:
- I created a table tblIDTemp (stored in front end, with two columns
(autonumber and column to hold ID number)
When user clicks on row:
- delete recs in tblIDTemp
- add new recs to tblIDTemp (based on current recs in listbox)
- query that drives the detail form always joins to tblIDTemp and sorts
by
Autonumber column (this achieves the same filtering and sorting that the
listbox had)

Question is:
Any better ways to make this happen? Don't like all this deleting and
adding of records but can't figure any other way that would work.
Guessing
my front end will grow like crazy.

Thanks in advance,
Mark
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

You are correct. I am using the OpenArgs to open the form to a specific
record. Normally I would use the Where argument, but in this case there is
a reason.

As to wanting to show multiple records and naviage to a specifi record, here
is how you could do that.
1. Use the Where argument of the OpenForm to filter the records. It will
filter to multiple records that match the criteria. If you want the form to
open in datasheet view, you will have to use that in the acFormDS option for
the View argument

2. Now, the next step. To go to a specific record.

Here we use the OpenArgs argument to position to a specific record.
So, as a quick test to make sure this all works, Here is what I did:

docmd.OpenForm "frmclientds",acFormDS,,"[iscorporate] = false",,,"31"
'the "31" was just a test value, you woul need to put in an actual value for
the field you are matching on

Then, to get to the specific record:

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
With Me.RecordsetClone
.FindFirst "[ClientID] = " & Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub

So, I still believe you should seriously consider subforms rather than a
list box. The list box properties are a bit more clugy to work with. I do
agree there a times to use a list box, but I don't think this is one of
them.



Mark Andrews said:
Thanks for the code, that might help me in the future. I think a subform
vs. a list box there are pros and cons of each and I like your method of
filtering.

The main issue which I think got missed (and that's what I need help on)
is:

When you call frmInitiative using this code:
Private Sub txtDescription_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmInitiative", , , , , , Me.txtInitID
End Sub
Click to expand...

I assume you are opening frmInitiative to ONE record and using OpenArgs to
pass the value (from how it looks).

I would like to:
- filter based on related record SUM (example: Sum of sales dollars >
50000)
- datasheet subform or list box shows 45 account records (accountid,
accountname, sumofsales)
- double click on row 13
- open frmInitiative and show 45 account records and navigate to record 13
(however this form needs to be based on a query that is updatable)

The problem being "how do I get the detail form to show the 45 records
(accounts with sales > 50000 sorted by Sales DESC)"???????????

Hope this makes sense,
Any help is appreciated,
Mark

Klatuu said:
Rather than a List Box, I would suggest a datasheet subform and related
combo boxes to identify the filtering.

Here is an example of how I do this:

'---------------------------------------------------------------------------------------
' Procedure : SetInitFilters
' DateTime : 2/10/2008 14:15
' Author : Dave Hargis
' Purpose : Sets the form filter for each combo search box
'---------------------------------------------------------------------------------------
'
Private Function SetInitFilters() As String
Dim strFilter As String 'Used by Form Filtering procedures

On Error GoTo SetInitFilters_Error

With Me

'Build The Filter String
If .cboPriority <> "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([OrigReleaseQtr], '@@@@-@') =
""" & _
.cboOrigQtr & """"
End If

If .cboCurrQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([CurrReleaseQtr], '@@@@-@') =
""" & _
.cboCurrQtr & """"
End If

If .cboInitStatus <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

If .cboInitType <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitType] = " & .cboInitType
End If

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CcID] = " & Me.txtCostCenter

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[cenpID] = " & Me.txtProduct

If Len(strFilter) > 0 Then
.subInitiative.Form.Filter = strFilter
.subInitiative.Form.FilterOn = True
.subInitiative.Form.Requery
Else
.subInitiative.Form.FilterOn = False
End If

End With 'Me

SetInitFilters = strFilter

SetInitFilters_Exit:
On Error GoTo 0
Exit Function

SetInitFilters_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure SetInitFilters of VBA Document Form_frmDashBoard",
vbExclamation, conMsgTitle
GoTo SetInitFilters_Exit

End Function

'---------------------------------------------------------------------------------------
' Procedure : AddAnd
' DateTime : 2/9/2008 17:26
' Author : Dave Hargis
' Purpose : Adds the word AND to a filtering string when needed
'---------------------------------------------------------------------------------------
'
Public Function AddAnd(strFilterString) As String

On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If

AddAnd_Exit:
On Error GoTo 0

Exit Function

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of Module modUtilities", vbExclamation,
conMsgTitle
GoTo AddAnd_Exit

End Function

Then in the Double Click event of the subform, I open a detail form like
this:

Private Sub txtDescription_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmInitiative", , , , , , Me.txtInitID
End Sub


Mark Andrews said:
I have been stumping people on this newsgroup but I haven't given up yet.
Hopefully someone will be willing to try and understand the question and
help me out.

I am designing a system that has the user navigate to a form with a
listbox.
The user can filter the listbox and change the underlying query that
drives
the list box in various ways. Once they have filtered and sorted the
listbox how they
want they double click on a row and a new form opens which shows detail
records and any related records in subforms.

So The listbox might start out with 4000 records and the user filters it
down to 45 and then clicks on number 23 and the new form opens on record
23
of 45 and allows the user to edit details.

My issue is that I want to allow the form with the list box to have
queries
that pull data from multiple tables (example: list accounts and show sum
of
sales in one column and sort descending by sales) and allow filtering on
various columns (example filter where sales > 50000) and once I do that
I
need to figure out the best way to show something like (when the user
clicks
on record 23 I would like the detail form to show the underlying 45
accounts
sorted by sales descending). However the user would only be editing
details
on accounts and see individual related sales records.

I made it work using this method:
- I created a table tblIDTemp (stored in front end, with two columns
(autonumber and column to hold ID number)
When user clicks on row:
- delete recs in tblIDTemp
- add new recs to tblIDTemp (based on current recs in listbox)
- query that drives the detail form always joins to tblIDTemp and sorts
by
Autonumber column (this achieves the same filtering and sorting that the
listbox had)

Question is:
Any better ways to make this happen? Don't like all this deleting and
adding of records but can't figure any other way that would work.
Guessing
my front end will grow like crazy.

Thanks in advance,
Mark
Click to expand...
Click to expand...
Click to expand...
 
A

Albert D. Kallal

Question is:
Any better ways to make this happen? Don't like all this deleting and
adding of records but can't figure any other way that would work.
Guessing
my front end will grow like crazy.
Click to expand...

I would not "transfer" the data. You should be able to build the query, and
then have the listbox (or subform) display that query (data).

You can "stuff" the sql right into a listbox, or right into a sub-form to
display the results.

I see no reason to pull data into a temp table....

As for using a listbox, perhaps a continues form would be better?

Here is some screen shots and ideas of what I mean:

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

The above might give you some ideas....note how some forms have "side" by
"side" sub-forms that that gives a nice drill down...
 
M

Mark Andrews

Thanks for your continued help, however I'm still unclear on how to get my
detail form to show the 45 records.

My underlying query for the detail form is something like this:
Select accountid, accountname, other accountfields from tblAccount

What I need to display the 45 records is
SELECT tblAccount.AccountID, tblAccount.AccountName, other account fields
FROM tblAccount LEFT JOIN tblSales ON tblAccount.AccountID =
tblSales.AccountID
GROUP BY tblAccount.AccountID, tblAccount.AccountName,
tblAccount.AccountStatus
HAVING Sum(tblSales.SalesDollars) > 50000
ORDER BY Sum(tblSales.SalesDollars) DESC

Which is a non updateable query (and I need an updatable query). So my
current approach involves creating a temp table with the 45 accountIDs
and using an autonumber field to sort the records (since they are inserted
into the temp table in the order of the list box).
My query then becomes:
SELECT tblAccount.AccountID, tblAccount.AccountName, other account fields
FROM tblAccount INNER JOIN tblIDTemp ON tblAccount.AccountID =
tblIDTemp.IDValue
ORDER BY tblIDTemp.AutoNumberID;

and tblIDTemp just so happens to have 45 records with the IDs of the 45
accounts with sales over 50,000 and the autonumberID helps with the sorting
aspect (in this case to sort descending by sales dollars).

What you have been saying will work fine for easy filtering (such as
filtering off fields in the tblAccount), on those I can just change the
where
clause of the detail form and problem solved. It's when I want to get
filtering an sorting situations on related data (such as sales) that I have
the issue.

Thanks in advance for any other help you can give,
Mark




Select accountid, accountname, other accountfields from tblAccount INNER
JOIN tblSales on (tblAccount.AccountID = tblSales.AccountID)
Klatuu said:
You are correct. I am using the OpenArgs to open the form to a specific
record. Normally I would use the Where argument, but in this case there
is a reason.

As to wanting to show multiple records and naviage to a specifi record,
here is how you could do that.
1. Use the Where argument of the OpenForm to filter the records. It will
filter to multiple records that match the criteria. If you want the form
to open in datasheet view, you will have to use that in the acFormDS
option for the View argument

2. Now, the next step. To go to a specific record.

Here we use the OpenArgs argument to position to a specific record.
So, as a quick test to make sure this all works, Here is what I did:

docmd.OpenForm "frmclientds",acFormDS,,"[iscorporate] = false",,,"31" 'the
"31" was just a test value, you woul need to put in an actual value for
the field you are matching on

Then, to get to the specific record:

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
With Me.RecordsetClone
.FindFirst "[ClientID] = " & Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub

So, I still believe you should seriously consider subforms rather than a
list box. The list box properties are a bit more clugy to work with. I
do agree there a times to use a list box, but I don't think this is one of
them.



Mark Andrews said:
Thanks for the code, that might help me in the future. I think a subform
vs. a list box there are pros and cons of each and I like your method of
filtering.

The main issue which I think got missed (and that's what I need help on)
is:

When you call frmInitiative using this code:
Private Sub txtDescription_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmInitiative", , , , , , Me.txtInitID
End Sub
Click to expand...

I assume you are opening frmInitiative to ONE record and using OpenArgs
to pass the value (from how it looks).

I would like to:
- filter based on related record SUM (example: Sum of sales dollars >
50000)
- datasheet subform or list box shows 45 account records (accountid,
accountname, sumofsales)
- double click on row 13
- open frmInitiative and show 45 account records and navigate to record
13 (however this form needs to be based on a query that is updatable)

The problem being "how do I get the detail form to show the 45 records
(accounts with sales > 50000 sorted by Sales DESC)"???????????

Hope this makes sense,
Any help is appreciated,
Mark

Klatuu said:
Rather than a List Box, I would suggest a datasheet subform and related
combo boxes to identify the filtering.

Here is an example of how I do this:

'---------------------------------------------------------------------------------------
' Procedure : SetInitFilters
' DateTime : 2/10/2008 14:15
' Author : Dave Hargis
' Purpose : Sets the form filter for each combo search box
'---------------------------------------------------------------------------------------
'
Private Function SetInitFilters() As String
Dim strFilter As String 'Used by Form Filtering procedures

On Error GoTo SetInitFilters_Error

With Me

'Build The Filter String
If .cboPriority <> "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([OrigReleaseQtr], '@@@@-@') =
""" & _
.cboOrigQtr & """"
End If

If .cboCurrQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([CurrReleaseQtr], '@@@@-@') =
""" & _
.cboCurrQtr & """"
End If

If .cboInitStatus <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

If .cboInitType <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitType] = " & .cboInitType
End If

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CcID] = " & Me.txtCostCenter

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[cenpID] = " & Me.txtProduct

If Len(strFilter) > 0 Then
.subInitiative.Form.Filter = strFilter
.subInitiative.Form.FilterOn = True
.subInitiative.Form.Requery
Else
.subInitiative.Form.FilterOn = False
End If

End With 'Me

SetInitFilters = strFilter

SetInitFilters_Exit:
On Error GoTo 0
Exit Function

SetInitFilters_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure SetInitFilters of VBA Document
Form_frmDashBoard", vbExclamation, conMsgTitle
GoTo SetInitFilters_Exit

End Function

'---------------------------------------------------------------------------------------
' Procedure : AddAnd
' DateTime : 2/9/2008 17:26
' Author : Dave Hargis
' Purpose : Adds the word AND to a filtering string when needed
'---------------------------------------------------------------------------------------
'
Public Function AddAnd(strFilterString) As String

On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If

AddAnd_Exit:
On Error GoTo 0

Exit Function

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of Module modUtilities", vbExclamation,
conMsgTitle
GoTo AddAnd_Exit

End Function

Then in the Double Click event of the subform, I open a detail form like
this:

Private Sub txtDescription_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmInitiative", , , , , , Me.txtInitID
End Sub


I have been stumping people on this newsgroup but I haven't given up
yet.
Hopefully someone will be willing to try and understand the question
and help me out.

I am designing a system that has the user navigate to a form with a
listbox.
The user can filter the listbox and change the underlying query that
drives
the list box in various ways. Once they have filtered and sorted the
listbox how they
want they double click on a row and a new form opens which shows detail
records and any related records in subforms.

So The listbox might start out with 4000 records and the user filters
it
down to 45 and then clicks on number 23 and the new form opens on
record 23
of 45 and allows the user to edit details.

My issue is that I want to allow the form with the list box to have
queries
that pull data from multiple tables (example: list accounts and show
sum of
sales in one column and sort descending by sales) and allow filtering
on
various columns (example filter where sales > 50000) and once I do that
I
need to figure out the best way to show something like (when the user
clicks
on record 23 I would like the detail form to show the underlying 45
accounts
sorted by sales descending). However the user would only be editing
details
on accounts and see individual related sales records.

I made it work using this method:
- I created a table tblIDTemp (stored in front end, with two columns
(autonumber and column to hold ID number)
When user clicks on row:
- delete recs in tblIDTemp
- add new recs to tblIDTemp (based on current recs in listbox)
- query that drives the detail form always joins to tblIDTemp and sorts
by
Autonumber column (this achieves the same filtering and sorting that
the
listbox had)

Question is:
Any better ways to make this happen? Don't like all this deleting and
adding of records but can't figure any other way that would work.
Guessing
my front end will grow like crazy.

Thanks in advance,
Mark
Click to expand...
Click to expand...
Click to expand...
 
M

Mark Andrews

Thanks for the link (lots of good examples!!!). I tried to explain why I
can't just use the sql in my latest post.

My app is very similar to the one with the first form using a sub form for
filtering, user filters records by sales > 50000 and gets 45 records then
the user
clicks the glasses icon on record 13 and I want the form that opens to show
45 records on record 13.
My issue is the filtering and sorting are based on related tables, so I can
have the detail form open to just ONE record with no problems but can't have
the
detail form open to 45 records (because I can't do the join without making
the query non updateable).

Maybe the only answer is to keep everything on one form, or only allow the
popup detail form to show one record?

Any other help is appreciated,
Mark
 
K

Klatuu

No Group By query is ever updatable. Each row in a Group By can represent
the accumulation of multiple records, so there is no way to know which
record to update.

So, even if you do create a temp table to use, how will you know wich of the
individual records in the original table to update?

If you can describe the business need, perhaps we can offer a better
suggestion.


Mark Andrews said:
Thanks for your continued help, however I'm still unclear on how to get my
detail form to show the 45 records.

My underlying query for the detail form is something like this:
Select accountid, accountname, other accountfields from tblAccount

What I need to display the 45 records is
SELECT tblAccount.AccountID, tblAccount.AccountName, other account fields
FROM tblAccount LEFT JOIN tblSales ON tblAccount.AccountID =
tblSales.AccountID
GROUP BY tblAccount.AccountID, tblAccount.AccountName,
tblAccount.AccountStatus
HAVING Sum(tblSales.SalesDollars) > 50000
ORDER BY Sum(tblSales.SalesDollars) DESC

Which is a non updateable query (and I need an updatable query). So my
current approach involves creating a temp table with the 45 accountIDs
and using an autonumber field to sort the records (since they are inserted
into the temp table in the order of the list box).
My query then becomes:
SELECT tblAccount.AccountID, tblAccount.AccountName, other account fields
FROM tblAccount INNER JOIN tblIDTemp ON tblAccount.AccountID =
tblIDTemp.IDValue
ORDER BY tblIDTemp.AutoNumberID;

and tblIDTemp just so happens to have 45 records with the IDs of the 45
accounts with sales over 50,000 and the autonumberID helps with the
sorting
aspect (in this case to sort descending by sales dollars).

What you have been saying will work fine for easy filtering (such as
filtering off fields in the tblAccount), on those I can just change the
where
clause of the detail form and problem solved. It's when I want to get
filtering an sorting situations on related data (such as sales) that I
have the issue.

Thanks in advance for any other help you can give,
Mark




Select accountid, accountname, other accountfields from tblAccount INNER
JOIN tblSales on (tblAccount.AccountID = tblSales.AccountID)
Klatuu said:
You are correct. I am using the OpenArgs to open the form to a specific
record. Normally I would use the Where argument, but in this case there
is a reason.

As to wanting to show multiple records and naviage to a specifi record,
here is how you could do that.
1. Use the Where argument of the OpenForm to filter the records. It will
filter to multiple records that match the criteria. If you want the form
to open in datasheet view, you will have to use that in the acFormDS
option for the View argument

2. Now, the next step. To go to a specific record.

Here we use the OpenArgs argument to position to a specific record.
So, as a quick test to make sure this all works, Here is what I did:

docmd.OpenForm "frmclientds",acFormDS,,"[iscorporate] = false",,,"31"
'the "31" was just a test value, you woul need to put in an actual value
for the field you are matching on

Then, to get to the specific record:

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
With Me.RecordsetClone
.FindFirst "[ClientID] = " & Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub

So, I still believe you should seriously consider subforms rather than a
list box. The list box properties are a bit more clugy to work with. I
do agree there a times to use a list box, but I don't think this is one
of them.



Mark Andrews said:
Thanks for the code, that might help me in the future. I think a
subform vs. a list box there are pros and cons of each and I like your
method of filtering.

The main issue which I think got missed (and that's what I need help on)
is:

When you call frmInitiative using this code:

Private Sub txtDescription_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmInitiative", , , , , , Me.txtInitID
End Sub

I assume you are opening frmInitiative to ONE record and using OpenArgs
to pass the value (from how it looks).

I would like to:
- filter based on related record SUM (example: Sum of sales dollars >
50000)
- datasheet subform or list box shows 45 account records (accountid,
accountname, sumofsales)
- double click on row 13
- open frmInitiative and show 45 account records and navigate to record
13 (however this form needs to be based on a query that is updatable)

The problem being "how do I get the detail form to show the 45 records
(accounts with sales > 50000 sorted by Sales DESC)"???????????

Hope this makes sense,
Any help is appreciated,
Mark

Rather than a List Box, I would suggest a datasheet subform and related
combo boxes to identify the filtering.

Here is an example of how I do this:

'---------------------------------------------------------------------------------------
' Procedure : SetInitFilters
' DateTime : 2/10/2008 14:15
' Author : Dave Hargis
' Purpose : Sets the form filter for each combo search box
'---------------------------------------------------------------------------------------
'
Private Function SetInitFilters() As String
Dim strFilter As String 'Used by Form Filtering procedures

On Error GoTo SetInitFilters_Error

With Me

'Build The Filter String
If .cboPriority <> "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([OrigReleaseQtr], '@@@@-@')
= """ & _
.cboOrigQtr & """"
End If

If .cboCurrQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([CurrReleaseQtr], '@@@@-@')
= """ & _
.cboCurrQtr & """"
End If

If .cboInitStatus <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

If .cboInitType <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitType] = " & .cboInitType
End If

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CcID] = " & Me.txtCostCenter

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[cenpID] = " & Me.txtProduct

If Len(strFilter) > 0 Then
.subInitiative.Form.Filter = strFilter
.subInitiative.Form.FilterOn = True
.subInitiative.Form.Requery
Else
.subInitiative.Form.FilterOn = False
End If

End With 'Me

SetInitFilters = strFilter

SetInitFilters_Exit:
On Error GoTo 0
Exit Function

SetInitFilters_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure SetInitFilters of VBA Document
Form_frmDashBoard", vbExclamation, conMsgTitle
GoTo SetInitFilters_Exit

End Function

'---------------------------------------------------------------------------------------
' Procedure : AddAnd
' DateTime : 2/9/2008 17:26
' Author : Dave Hargis
' Purpose : Adds the word AND to a filtering string when needed
'---------------------------------------------------------------------------------------
'
Public Function AddAnd(strFilterString) As String

On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If

AddAnd_Exit:
On Error GoTo 0

Exit Function

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of Module modUtilities", vbExclamation,
conMsgTitle
GoTo AddAnd_Exit

End Function

Then in the Double Click event of the subform, I open a detail form
like this:

Private Sub txtDescription_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmInitiative", , , , , , Me.txtInitID
End Sub


I have been stumping people on this newsgroup but I haven't given up
yet.
Hopefully someone will be willing to try and understand the question
and help me out.

I am designing a system that has the user navigate to a form with a
listbox.
The user can filter the listbox and change the underlying query that
drives
the list box in various ways. Once they have filtered and sorted the
listbox how they
want they double click on a row and a new form opens which shows
detail
records and any related records in subforms.

So The listbox might start out with 4000 records and the user filters
it
down to 45 and then clicks on number 23 and the new form opens on
record 23
of 45 and allows the user to edit details.

My issue is that I want to allow the form with the list box to have
queries
that pull data from multiple tables (example: list accounts and show
sum of
sales in one column and sort descending by sales) and allow filtering
on
various columns (example filter where sales > 50000) and once I do
that I
need to figure out the best way to show something like (when the user
clicks
on record 23 I would like the detail form to show the underlying 45
accounts
sorted by sales descending). However the user would only be editing
details
on accounts and see individual related sales records.

I made it work using this method:
- I created a table tblIDTemp (stored in front end, with two columns
(autonumber and column to hold ID number)
When user clicks on row:
- delete recs in tblIDTemp
- add new recs to tblIDTemp (based on current recs in listbox)
- query that drives the detail form always joins to tblIDTemp and
sorts by
Autonumber column (this achieves the same filtering and sorting that
the
listbox had)

Question is:
Any better ways to make this happen? Don't like all this deleting and
adding of records but can't figure any other way that would work.
Guessing
my front end will grow like crazy.

Thanks in advance,
Mark
Click to expand...
Click to expand...
Click to expand...
 
M

Mark Andrews

The temp table method does work. The temp table stores the IDs of the
records from the list box so joining with it works fine.
The only issues are:
- the front end will grow because of the constant creating of this temp
table
- might be slow with thousands of records.

Business need:
- Creating a system similar to Microsoft CRM (if you have seen that one)
where each entity in the system (accounts, contacts, activities, invoices
etc...) has a consistant navigation path of
1. List view of entity (allows, sorting, filtering etc...)
2. On selecting an item in #1 a detail view pops up showing the filtered set
from #1 with the same record selected

In #1 typical senerios are that the list views have limited columns and some
summary columns
Example: for accounts I might want to show:
- Account Name
- Address
- Account Number
- Sales for 2008
- Opportunity Revenue for 2008

and allow the user various filtering and sorting options

I prefer to have two forms (list view and detail) because the list views and
detail both require a good deal of screen real estate.

Once the user filters down the records (or they don't have to filter) they
click on a record and
a detail form shows the same records they were looking at except they would
see and be able to edit all fields.
Example: a detail form for accounts would have 30 account fields, and 4 tabs
showing subforms for contacts, opportunities, activities and invoices
related to the account.

They might want to quickly edit the filtered set of accounts (thus the
desire for the entire filtered set to appear in the detail form), navigating
using form view
in the detail is the normal way. Example: I filter accounts down to the 10
top accounts and quickly browse through them.

I'm guessing the answer is to have the listbox next to the detail records.
Possibly even a hidden listbox. I think all this discussion has helped me
out.

Thanks for everyone's help,
If you think of some unique approach let me know,
Mark





Klatuu said:
No Group By query is ever updatable. Each row in a Group By can represent
the accumulation of multiple records, so there is no way to know which
record to update.

So, even if you do create a temp table to use, how will you know wich of
the individual records in the original table to update?

If you can describe the business need, perhaps we can offer a better
suggestion.


Mark Andrews said:
Thanks for your continued help, however I'm still unclear on how to get
my detail form to show the 45 records.

My underlying query for the detail form is something like this:
Select accountid, accountname, other accountfields from tblAccount

What I need to display the 45 records is
SELECT tblAccount.AccountID, tblAccount.AccountName, other account fields
FROM tblAccount LEFT JOIN tblSales ON tblAccount.AccountID =
tblSales.AccountID
GROUP BY tblAccount.AccountID, tblAccount.AccountName,
tblAccount.AccountStatus
HAVING Sum(tblSales.SalesDollars) > 50000
ORDER BY Sum(tblSales.SalesDollars) DESC

Which is a non updateable query (and I need an updatable query). So my
current approach involves creating a temp table with the 45 accountIDs
and using an autonumber field to sort the records (since they are
inserted into the temp table in the order of the list box).
My query then becomes:
SELECT tblAccount.AccountID, tblAccount.AccountName, other account fields
FROM tblAccount INNER JOIN tblIDTemp ON tblAccount.AccountID =
tblIDTemp.IDValue
ORDER BY tblIDTemp.AutoNumberID;

and tblIDTemp just so happens to have 45 records with the IDs of the 45
accounts with sales over 50,000 and the autonumberID helps with the
sorting
aspect (in this case to sort descending by sales dollars).

What you have been saying will work fine for easy filtering (such as
filtering off fields in the tblAccount), on those I can just change the
where
clause of the detail form and problem solved. It's when I want to get
filtering an sorting situations on related data (such as sales) that I
have the issue.

Thanks in advance for any other help you can give,
Mark




Select accountid, accountname, other accountfields from tblAccount INNER
JOIN tblSales on (tblAccount.AccountID = tblSales.AccountID)
Klatuu said:
You are correct. I am using the OpenArgs to open the form to a specific
record. Normally I would use the Where argument, but in this case there
is a reason.

As to wanting to show multiple records and naviage to a specifi record,
here is how you could do that.
1. Use the Where argument of the OpenForm to filter the records. It
will filter to multiple records that match the criteria. If you want
the form to open in datasheet view, you will have to use that in the
acFormDS option for the View argument

2. Now, the next step. To go to a specific record.

Here we use the OpenArgs argument to position to a specific record.
So, as a quick test to make sure this all works, Here is what I did:

docmd.OpenForm "frmclientds",acFormDS,,"[iscorporate] = false",,,"31"
'the "31" was just a test value, you woul need to put in an actual value
for the field you are matching on

Then, to get to the specific record:

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
With Me.RecordsetClone
.FindFirst "[ClientID] = " & Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub

So, I still believe you should seriously consider subforms rather than a
list box. The list box properties are a bit more clugy to work with. I
do agree there a times to use a list box, but I don't think this is one
of them.



Thanks for the code, that might help me in the future. I think a
subform vs. a list box there are pros and cons of each and I like your
method of filtering.

The main issue which I think got missed (and that's what I need help
on) is:

When you call frmInitiative using this code:

Private Sub txtDescription_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmInitiative", , , , , , Me.txtInitID
End Sub

I assume you are opening frmInitiative to ONE record and using OpenArgs
to pass the value (from how it looks).

I would like to:
- filter based on related record SUM (example: Sum of sales dollars >
50000)
- datasheet subform or list box shows 45 account records (accountid,
accountname, sumofsales)
- double click on row 13
- open frmInitiative and show 45 account records and navigate to record
13 (however this form needs to be based on a query that is updatable)

The problem being "how do I get the detail form to show the 45 records
(accounts with sales > 50000 sorted by Sales DESC)"???????????

Hope this makes sense,
Any help is appreciated,
Mark

Rather than a List Box, I would suggest a datasheet subform and
related combo boxes to identify the filtering.

Here is an example of how I do this:

'---------------------------------------------------------------------------------------
' Procedure : SetInitFilters
' DateTime : 2/10/2008 14:15
' Author : Dave Hargis
' Purpose : Sets the form filter for each combo search box
'---------------------------------------------------------------------------------------
'
Private Function SetInitFilters() As String
Dim strFilter As String 'Used by Form Filtering procedures

On Error GoTo SetInitFilters_Error

With Me

'Build The Filter String
If .cboPriority <> "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([OrigReleaseQtr], '@@@@-@')
= """ & _
.cboOrigQtr & """"
End If

If .cboCurrQtr <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([CurrReleaseQtr], '@@@@-@')
= """ & _
.cboCurrQtr & """"
End If

If .cboInitStatus <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

If .cboInitType <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitType] = " & .cboInitType
End If

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CcID] = " & Me.txtCostCenter

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[cenpID] = " & Me.txtProduct

If Len(strFilter) > 0 Then
.subInitiative.Form.Filter = strFilter
.subInitiative.Form.FilterOn = True
.subInitiative.Form.Requery
Else
.subInitiative.Form.FilterOn = False
End If

End With 'Me

SetInitFilters = strFilter

SetInitFilters_Exit:
On Error GoTo 0
Exit Function

SetInitFilters_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure SetInitFilters of VBA Document
Form_frmDashBoard", vbExclamation, conMsgTitle
GoTo SetInitFilters_Exit

End Function

'---------------------------------------------------------------------------------------
' Procedure : AddAnd
' DateTime : 2/9/2008 17:26
' Author : Dave Hargis
' Purpose : Adds the word AND to a filtering string when needed
'---------------------------------------------------------------------------------------
'
Public Function AddAnd(strFilterString) As String

On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If

AddAnd_Exit:
On Error GoTo 0

Exit Function

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of Module modUtilities", vbExclamation,
conMsgTitle
GoTo AddAnd_Exit

End Function

Then in the Double Click event of the subform, I open a detail form
like this:

Private Sub txtDescription_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmInitiative", , , , , , Me.txtInitID
End Sub


I have been stumping people on this newsgroup but I haven't given up
yet.
Hopefully someone will be willing to try and understand the question
and help me out.

I am designing a system that has the user navigate to a form with a
listbox.
The user can filter the listbox and change the underlying query that
drives
the list box in various ways. Once they have filtered and sorted the
listbox how they
want they double click on a row and a new form opens which shows
detail
records and any related records in subforms.

So The listbox might start out with 4000 records and the user filters
it
down to 45 and then clicks on number 23 and the new form opens on
record 23
of 45 and allows the user to edit details.

My issue is that I want to allow the form with the list box to have
queries
that pull data from multiple tables (example: list accounts and show
sum of
sales in one column and sort descending by sales) and allow filtering
on
various columns (example filter where sales > 50000) and once I do
that I
need to figure out the best way to show something like (when the user
clicks
on record 23 I would like the detail form to show the underlying 45
accounts
sorted by sales descending). However the user would only be editing
details
on accounts and see individual related sales records.

I made it work using this method:
- I created a table tblIDTemp (stored in front end, with two columns
(autonumber and column to hold ID number)
When user clicks on row:
- delete recs in tblIDTemp
- add new recs to tblIDTemp (based on current recs in listbox)
- query that drives the detail form always joins to tblIDTemp and
sorts by
Autonumber column (this achieves the same filtering and sorting that
the
listbox had)

Question is:
Any better ways to make this happen? Don't like all this deleting
and
adding of records but can't figure any other way that would work.
Guessing
my front end will grow like crazy.

Thanks in advance,
Mark
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Any better ways? 5
Need some PWA help 0
Adding data to MS Access from csv file 0
Word OLE to check spelling in text box 1
OLE Word spelling 0
Need Help with Macro Security 1
Requery listbox 1
Outlook Desktop (latest) stopped syncing IMAP accpounts from selfowned Mail server. 0

Top