On 10 Jul 2011 01:43:03 GMT, "David-W-Fenton"
<NoEmail (AT) SeeSignature (DOT) invalid> wrote:

I think that is exactly what I am trying to do David. A sort of data
mining form for querying our Access database where the user can select
any combination of what they selected for the controls on the form.

-paulw

See this thread

http://groups.google.com/group/comp....7d9ab97fdb889f

--

AG
Email: npATadhdataDOTcom


"PW" <emailaddyinsig (AT) ifIremember (DOT) com> wrote

PW <emailaddyinsig (AT) ifIremember (DOT) com> wrote in
news:dpqj17dsl1vqilmpp9q4k9953n2tothgfn (AT) 4ax (DOT) com:

Googling "Access query by form" brings back a whole host of possible
approaches to the problem.

I have had three different approaches over the years:

1. the ad hoc approach -- write the SQL on the fly based on the
particulars of the form.

2. use a class module that abstracts the structure so you can use
that to write "dumb" code that does all the work for you (but is
very hard to use and understand).

3. a hybrid between ad hoc and some form of automatic processing of
the results.

Mostly, I use #1.

The first thing I do that it's important is decide that any single
QBF interface will return only one fixed resultset in all cases.
This is very often nothing more than a temp table with a checkbox
field and a PK that connects to the main table in the particular app
(usually PersonID). The QBF search process simply finds the records
that match the requested criteria and appends the PKs to the temp
table. The display of results is usually in a subform of the search
form, and includes a lot more fields (and the display results can
differ depending on the type of search).

This makes things relatively easy, as it means the FROM clause of
the SQL INSERT statement that populates the temp table is static.

So, the only thing that has to be done is to write the WHERE clause.
Here's the function that writes it for one of my more recent apps (I
haven't fixed the formatting for the SQL strings, which I have
unwrapped in the source code):

Private Function GetWhere() As String
Dim strPersonType As String
Dim strTemp As String
Dim varItem As Variant
Dim strConferences As String
Dim strLapsed As String
Dim ctl As Control
Dim strAttendeeInformation As String
Dim strAttendee As String
Dim strField As String
Dim strCriteria As String

Select Case Me!optWho
Case 1
strPersonType = "Attendee"
Case 2
strPersonType = "Exhibitor"
Case 3
strPersonType = "Speaker"
Case 4
' do not filter at all
Case 5
strPersonType = "Prospect"
End Select
If Len(strPersonType) > 0 And strPersonType <> "Prospect" Then
strTemp = strTemp & " AND PersonType = " & STR_QUOTE & _
strPersonType & STR_QUOTE
End If
If (Me!lstConference.ItemsSelected.Count > 0) Then
For Each varItem In Me!lstConference.ItemsSelected
strConferences = strConferences & ", " & _
Me!lstConference.ItemData(varItem)
Next varItem
strConferences = Mid(strConferences, 3)
strTemp = strTemp & " AND ConferenceID IN (" _
& strConferences & ")"
End If
If (Me!optLapsed.Enabled) And (Not IsNull(Me!cmbLapsedStart) _
And Not IsNull(Me!cmbLapsedYear)) Then
Select Case Me!optWho
Case 1, 4
strLapsed = "SELECT DISTINCT
tblAttendeeConference.AttendeeID FROM tblConference INNER
JOIN tblAttendeeConference ON tblConference.ConferenceID =
tblAttendeeConference.ConferenceID WHERE
tblAttendeeConference.AttendeeID Not In (SELECT DISTINCT
tblAttendeeConference.AttendeeID FROM tblConference INNER
JOIN tblAttendeeConference ON tblConference.ConferenceID =
tblAttendeeConference.ConferenceID WHERE
tblConference.Year>=" & Me!cmbLapsedYear & ""
Case 2
strLapsed = "SELECT DISTINCT tblBooth.ExhibitorID FROM
tblConference INNER JOIN tblBooth ON
tblConference.ConferenceID = tblBooth.ConferenceID WHERE
tblBooth.ExhibitorID Not In (SELECT DISTINCT
tblBooth.ExhibitorID FROM tblConference INNER JOIN tblBooth
ON tblConference.ConferenceID = tblBooth.ConferenceID WHERE
tblConference.Year>=" & Me!cmbLapsedYear & ""
Case 3
strLapsed = "SELECT DISTINCT tblSpeakerSessions.AttendeeID
FROM tblConference INNER JOIN (tblSpeakerSessions INNER
JOIN tblSession ON tblSpeakerSessions.SessionID =
tblSession.SessionID) ON tblConference.ConferenceID =
tblSession.ConferenceID WHERE tblSpeakerSessions.AttendeeID
Not In (SELECT DISTINCT tblSpeakerSessions.AttendeeID FROM
tblSpeakerSessions INNER JOIN (tblConference INNER JOIN
tblSession ON tblConference.ConferenceID =
tblSession.ConferenceID) ON tblSpeakerSessions.SessionID =
tblSession.SessionID WHERE tblConference.Year>=" &
Me!cmbLapsedYear & ""
End Select
strLapsed = strLapsed & " AND tblConference.Year>" & _
Me!cmbLapsedStart & " And tblConference.Year<" & _
Me!cmbLapsedYear
strTemp = strTemp & " AND tblPerson.PersonID IN (" _
& strLapsed & ")"
End If
Select Case strPersonType
Case "Attendee", vbNullString
' eliminate anybody who has ever been an Exhibitor
For Each ctl In Me!ctlTab.Pages("pgeAttendees").Controls
If ctl.ControlType = acTextBox Then
If Not IsNull(ctl) Then
If ctl.Name = "txtTypeAttend" Then
strField = ("tblAttendeeConference." & _
Mid(ctl.Name, 4))
strCriteria = Replace((Me("txt" & _
Mid(ctl.Name, 4))), ".", "?")
strCriteria = Replace(strCriteria, ",", "?")
strCriteria = Replace(strCriteria, "+", "?")
If Me("cmb" & Mid(ctl.Name, 4)).Column(1) = 0 Then
strCriteria = "*" & strCriteria & "*"
End If
strAttendee = strAttendee & " AND (" & _
Application.BuildCriteria(strField, dbText, _
strCriteria) & ")"
Else
strField = ("tblAttendeeInformation." & _
Mid(ctl.Name, 4))
strCriteria = Replace((Me("txt" & _
Mid(ctl.Name, 4))), ".", "?")
strCriteria = Replace(strCriteria, ",", "?")
strCriteria = Replace(strCriteria, "+", "?")
strAttendeeInformation = strAttendeeInformation & _
" AND (" & Application.BuildCriteria(strField, _
dbText, strCriteria) & ")"
End If
End If
End If
Next ctl
Set ctl = Nothing
strAttendee = Mid(strAttendee, 6)
If Len(strAttendee) > 0 Then
strTemp = strTemp & " AND " & strAttendee
End If
strAttendeeInformation = Mid(strAttendeeInformation, 6)
If Len(strAttendeeInformation) > 0 Then
strTemp = strTemp & " AND tblPerson.PersonID IN (SELECT
DISTINCT tblAttendeeInformation.AttendeeID FROM
tblAttendeeInformation WHERE " & strAttendeeInformation &
")"
End If
Case "Prospect"
strTemp = strTemp & " AND tblPerson.PersonID IN (SELECT
DISTINCT tblPerson.PersonID FROM (tblPerson LEFT JOIN
tblAttendeeConference ON tblPerson.PersonID =
tblAttendeeConference.AttendeeID) INNER JOIN tblPersonType ON
tblPerson.PersonID = tblPersonType.PersonID WHERE
(((tblPersonType.PersonType)='Attendee') AND
((tblAttendeeConference.AttendeeConferenceID) Is Null))"
Case "Exhibitor"
Select Case Me!optExhibitors
Case 1
strTemp = strTemp & " AND tblPerson.PersonID IN (SELECT
DISTINCT tblPerson.PersonID FROM tblPerson INNER JOIN
(tblConference INNER JOIN tblBooth ON
tblConference.ConferenceID = tblBooth.ConferenceID) ON
tblPerson.PersonID = tblBooth.ExhibitorID WHERE
(((tblConference.ConferenceID)=" & Me!cmbConference &
"))"
Case 2
strTemp = strTemp & " AND tblPerson.PersonID IN (SELECT
DISTINCT tblPerson.PersonID FROM (([SELECT DISTINCT
tblBooth.ExhibitorID As PersonID FROM tblBooth WHERE
(((tblBooth.ConferenceID)=" & GetCurrentConference() &
"));]. AS CurrentExhibitors RIGHT JOIN tblPerson ON
CurrentExhibitors.PersonID = tblPerson.PersonID) INNER
JOIN tblPersonType ON tblPerson.PersonID =
tblPersonType.PersonID) INNER JOIN tblBooth ON
tblPerson.PersonID = tblBooth.ExhibitorID WHERE
(((CurrentExhibitors.PersonID) Is Null) AND
((tblPersonType.PersonType)='Exhibitor') AND
((tblBooth.ConferenceID)<>" & GetCurrentConference() &
"))"
Case 3
strTemp = strTemp & " AND tblPerson.PersonID IN (SELECT
DISTINCT tblPerson.PersonID FROM (tblPerson LEFT JOIN
[SELECT tblBooth.BoothID, tblBooth.ExhibitorID FROM
tblBooth WHERE (((tblBooth.ConferenceID)=" &
GetCurrentConference() & "));]. AS CurrentBooths ON
tblPerson.PersonID = CurrentBooths.ExhibitorID) INNER
JOIN tblPersonType ON tblPerson.PersonID =
tblPersonType.PersonID WHERE (((CurrentBooths.BoothID) Is
Null) AND ((tblPersonType.PersonType)='Exhibitor'))"
Case 4 ' All
' do nothing at all -- doesn't change the criteria
End Select
Case "Speaker"
End Select
GetWhere = Mid(strTemp, 6)
End Function

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On 13 Jul 2011 17:56:21 GMT, "David-W-Fenton"
<NoEmail (AT) SeeSignature (DOT) invalid> wrote:

That is what I did with the original form. A la something modified
from a Getz Access 97 book. It has been working great except I now
have to modify it for a client and the form has been so hacked by
myself and my wife that it is time to rewrite it from scratch because
I can't figure it all out (it's been many years since I wrote it).

Wow. I am going to spend most of the day tomorrow studying what you
have posted and I'll get back to you. Thanks so much David.

I have no idea how to do #2! :-)

-paulw

On Wednesday, July 6, 2011 7:04:33 PM UTC-5, PW wrote:
Although this is an older post I am responding to, I don't get this "automatically saving records" statement at all. About all that happens is that if you are on a new record and add something to a field it will update the autonumber whether or not the record is saved or canceled. Same thing occurs whether opening the table directly or using a table as a form's recordsource. If one has a love affair with sequential autonumbers that might be of concern but a poor reason to abandon bound controls. Could you describe your issue of automatic saves so that I may become aware of the dangers of bound forms.

Patrick Finucane wrote:
I think he's referring to the behavior where navigating to a new/different
record automatically saves any changes to the record from which the
navigation occurred without warning the user or giving him any chance to
change his mind. The user is forced to remember to ctrl-backspace before
tabbing or clicking into the next row.

Yes, the developer can mitigate this but many think it should be the other
way around: the default behavior should be to ask the user if changes should
be saved and it should be up to the developer to modify that behavior.

I imagine it's similar to the distaste for opt-out vs. opt-in.

On Thu, 14 Jul 2011 12:54:49 -0400, "Bob Barrows"
<reb01501 (AT) NOyahooSPAM (DOT) com> wrote:

I agree Bob! And yes Patrick, we are concerned about sequential
record numbers. Our clients use reservation numbers for reference,
for instance, and I bet some will freak out if there is a gap between
them.

-paulw

On Jul 14, 1:14*pm, PW <emailaddyin... (AT) ifIremember (DOT) com> wrote:
I most likely would create a field called Sequential that is updated
when the record is new and saved.

Typically I use autonumbers as primary keys to be used to link to
other tables. It wouldn't cross my mind to use them as a sequential
field.

I would think a navigation buttons would be irrelevant in an unbound
form.

If you have data that makes the form dirty it will run the
BeforeUpdate event thus you can cancel it.

With bound forms, if you hit the X to close the form, and the B4Update
event sets Cancel to True, this will fire off the FORM_ERROR event.
The data error will be 2169.

If you set the response value to the data error to acDataErrDisplay,
you will get a horrible looking and confusing error display from
Microsoft. This horrible looking msgbox is a Yes/No messagebox.
There is no way to display your own custom message and pass back a
value...you must use the horrible MS-generated msgbox.

You can tell the operator that any changes they made weren't saved and
then using acDataErrorContinue but that doesn't address the B4Update
event's Cancel.

This would not be an issue with an unbound form.

Patrick Finucane wrote:
time, navigation buttons are not irrelevant.
Well, yeah! In other words, developers are being forced to "opt-out" of the
undesirable behavior rather than opting in.
.... which I think answers your original question.

On Jul 15, 7:01*am, "Bob Barrows" <reb01... (AT) NOSPAMyahoo (DOT) com> wrote:
Well, my predudices are showing.

I just created a form, added 3 text boxes. Not a recordsource or
controlsource in sight. Standard defaults. Saved form. Open it up.
Got the nav buttons on the bottom, doing nothing. Enter some data
into the text boxes. The nav buttons are mostly greyed out and I
can't more anywhere. There is no forward, back, end or first. It
just is. Where's the relevancy of the nav buttons?

Do I agree with your contention that it should be on MS's nickle and
not on the developer. Sure. I sometimes wonder how many man hours
were wasted by Access deveopers around the world searching for a
FileOpen dialog routine.

But I'll stick to my routine of permitting users to use bound forms
linked to data tables and keep the unbound forms to mundane tasks for
creating reports and filters.