Drill Down Functionality Needed

[Han]

I'm not new to web development or databases, but am a newbie when it comes
to creating Access forms.

I need to create one or more forms that will allow the user to drill down to
a particular record. The first form would display a search tool for last
name look-up. Any matching records would displayed in a sub-form. Each of
these records would be linked (clickable) to another form, which would
display details of that record.

This kind of thing would be a breeze in ASP or PHP. Anyway, any help would
be greatly appreciated.

Thanks,
Han

 

[Han]

Albert, thanks for the excellent info.

I'll get started using the examples you showed.

Best regards,
John

 

[Han]

Albert, I'm unable to get your example working. I really like the design in
your .gif and would like to do something similar. Could you provide me with
more detail on how to achieve this?

Here's what I've done so far.

I decide a search button is unnecessary. I created an unbound form. I then
created a text box called txtLastName and a list box called lstResults. In
txtLastName's properties, I added the following code to the On Enter event:

Private Sub txtLastName_Enter()
Dim strSQL As String

strSQL = "SELECT [FirstName], [LastName], [Company], [TypeName] FROM
People INNER JOIN PeopleTypes ON [People].[TypeID]=[PeopleTypes].[TypeID]
WHERE LastName LIKE """ & Me.txtLastName & "*"""

Me.lstResults
End Sub

My current dilemma is with the last line (before End Sub). I'm unable to set
the RecordSource because Access doesn't recognize my form. I look at the
form properties, but there doesn't seem to be a way to name it. I'm
obviously missing something (most likely easy, no doubt.)

Any suggestions would be greatly appreciated.

Thanks,
Han

 

[Albert D. Kallal]

Just as side note, if you do not want to use the search button, then you
would use the after update event of text box. (as mentioned, you are working
your way through this step by step). The on-enter event fires when the
cursor moves into the textbox (ie: the text box is entered by keyboard
movement, or the mouse). That is defiantly not the event we want to use.

The on enter event is rather like the got focus event (as mentioned, we have
about double the number of events as compared to VB controls..and this makes
ms-access more difficult to master). For sake of clarity, the On Enter event
is VERY much like the Got Focus event. However, you can switch forms, and
the cursor can stay in the control, so the enter events don't fire when you
come back, but the got focus events do. (so, they have different uses, but
are very similar!).

If you read my notes in the search example link I posted:
http://www.attcanada.net/~kallal.msn/Search/index.html

Then you will figure out that I am a big fan of using the keyboard (came
from a lot of old dos programs!). I think also worthy to note in that link
is the repeating command button that I used for details in that sub form. It
is more obvious then the first example I showed you, since users instantly
realise that they can click on that button. In the double drill down
example, I did not uses buttons, but I plan to add buttons, since users can
"see" the button and this further reduces training.

So, if you double click, or hit enter on the left side (in the list box),
then I launch the form to edit that main customer data. If you hit tab key,
then the cursor jumps to the right side of the screen (in the drill down sub
form - which as mentioned COULD just as well be a listbox). again little the
enter key on this right side grid will launch the DETAILS FORM in this case.
So, it is nice and easy interface for the users. You can also see the add
button on the bottom of the screen if the user fails to find what they are
looking for. I also added some code to make the up / down arrow keys
navigate correctly in that sub-form.

The code in the list box that runs is fired in the listbox in the after
update event. (this is the event to use in the listbox, since the listbox
represents one control, and when you move up/down, or click on the any value
in the listbox, then the field changes, so the after update event. ON change
event. The code in the after update event that fires and sets the details in
the right side is:

Private Sub List51_AfterUpdate()

Dim subSql As String
If IsNull(Me.List51) = False Then
If Me.List51 <> "" Then
subSql = "SELECT * from OldGroupTrips " & _
"WHERE [Gid] = " & Me.List51 & _
" order by TripDate DESC"


Me.OldGroupTrips_subform.Form.RecordSource = subSql
Me.OldGroupTrips_subform.Visible = True
End If
End If

End Sub

In the above case, I start the form with the sub-form on the right side
hidden.

And looking at my code, what a horrible name I used for the list box! My
apologies!

As mentioned, if the user hits the Enter key while in that list box on the
left side of the screen, then I launch the main form to that record. The
code to do this had to go into the KeyDown event for the listbox. So, that
code is:

Private Sub List51_KeyDown(KeyCode As Integer, Shift As Integer)

If KeyCode = 13 Then

KeyCode = 0
If IsNull(Me.List51) = False Then
On Error Resume Next
DoCmd.OpenForm "frmGroups", , , "[id] = " & Me.List51
End If
End If

End Sub

Now, as mentioned, I used the after update event of the text box at the top.
You
could just stuff in the sql into the listbox, but I did actually add a few
other
things, so I put the code into a separate routine (still in the forms code).

BuildSeach simply just builds the sql string, and stuffs it into the listbox

Here is the code I used in that one.

Private Sub txtSGroupName_AfterUpdate()

Call buildsearch
If GotOne = True Then
Me.List51.SetFocus

Me.List51.Selected(1) = True
Me.List51 = Me.List51.Column(0)

Call List51_AfterUpdate

End If


End Sub

That bit of weird code of needs some explain:

Me.List51.Selected(1) = True
Me.List51 = Me.List51.Column(0)

The reason why I did the above two lines, is that I wanted to have the list
box entry pre-selected for the user when the cursor gets moved there via the
setfocus. So, had to both highlight the list box value with the selected(1)
= True command. In addition, I had to SET THE VALUE of the control to
whatever the first value in the listbox was with column(0). You remove that
extra code...but it is just some small touches I added over time.

Note that the listbox assumes the "headings" are to yes. If you don't use
headings, then you need to use selected(0) = True. (listbox values are zero
based, or 1 based depending on if headings are shown...)

 

[Han]

Albert, thanks for your detailed message. It has saved me a great deal of
time.

One remaining issue. The following line errors:

Me.subform.Form.RecordSource = strSQL

"Method or data member not found."

Subform is the name of my form.

I don't have access to a form methods and properties in the VB editor. Is
there a reference that needs to be enabled?

Just as side note, if you do not want to use the search button, then you
would use the after update event of text box. (as mentioned, you are working
your way through this step by step). The on-enter event fires when the
cursor moves into the textbox (ie: the text box is entered by keyboard
movement, or the mouse). That is defiantly not the event we want to use.

The on enter event is rather like the got focus event (as mentioned, we have
about double the number of events as compared to VB controls..and this makes
ms-access more difficult to master). For sake of clarity, the On Enter event
is VERY much like the Got Focus event. However, you can switch forms, and
the cursor can stay in the control, so the enter events don't fire when you
come back, but the got focus events do. (so, they have different uses, but
are very similar!).

If you read my notes in the search example link I posted:
http://www.attcanada.net/~kallal.msn/Search/index.html

Then you will figure out that I am a big fan of using the keyboard (came
from a lot of old dos programs!). I think also worthy to note in that link
is the repeating command button that I used for details in that sub form. It
is more obvious then the first example I showed you, since users instantly
realise that they can click on that button. In the double drill down
example, I did not uses buttons, but I plan to add buttons, since users can
"see" the button and this further reduces training.

So, if you double click, or hit enter on the left side (in the list box),
then I launch the form to edit that main customer data. If you hit tab key,
then the cursor jumps to the right side of the screen (in the drill down sub
form - which as mentioned COULD just as well be a listbox). again little the
enter key on this right side grid will launch the DETAILS FORM in this case.
So, it is nice and easy interface for the users. You can also see the add
button on the bottom of the screen if the user fails to find what they are
looking for. I also added some code to make the up / down arrow keys
navigate correctly in that sub-form.

The code in the list box that runs is fired in the listbox in the after
update event. (this is the event to use in the listbox, since the listbox
represents one control, and when you move up/down, or click on the any value
in the listbox, then the field changes, so the after update event. ON change
event. The code in the after update event that fires and sets the details in
the right side is:

Private Sub List51_AfterUpdate()

Dim subSql As String
If IsNull(Me.List51) = False Then
If Me.List51 <> "" Then
subSql = "SELECT * from OldGroupTrips " & _
"WHERE [Gid] = " & Me.List51 & _
" order by TripDate DESC"


Me.OldGroupTrips_subform.Form.RecordSource = subSql
Me.OldGroupTrips_subform.Visible = True
End If
End If

End Sub

In the above case, I start the form with the sub-form on the right side
hidden.

And looking at my code, what a horrible name I used for the list box! My
apologies!

As mentioned, if the user hits the Enter key while in that list box on the
left side of the screen, then I launch the main form to that record. The
code to do this had to go into the KeyDown event for the listbox. So, that
code is:

Private Sub List51_KeyDown(KeyCode As Integer, Shift As Integer)

If KeyCode = 13 Then

KeyCode = 0
If IsNull(Me.List51) = False Then
On Error Resume Next
DoCmd.OpenForm "frmGroups", , , "[id] = " & Me.List51
End If
End If

End Sub

Now, as mentioned, I used the after update event of the text box at the top.
You
could just stuff in the sql into the listbox, but I did actually add a few
other
things, so I put the code into a separate routine (still in the forms code).

BuildSeach simply just builds the sql string, and stuffs it into the listbox

Here is the code I used in that one.

Private Sub txtSGroupName_AfterUpdate()

Call buildsearch
If GotOne = True Then
Me.List51.SetFocus

Me.List51.Selected(1) = True
Me.List51 = Me.List51.Column(0)

Call List51_AfterUpdate

End If


End Sub

That bit of weird code of needs some explain:

Me.List51.Selected(1) = True
Me.List51 = Me.List51.Column(0)

The reason why I did the above two lines, is that I wanted to have the list
box entry pre-selected for the user when the cursor gets moved there via the
setfocus. So, had to both highlight the list box value with the selected(1)
= True command. In addition, I had to SET THE VALUE of the control to
whatever the first value in the listbox was with column(0). You remove that
extra code...but it is just some small touches I added over time.

Note that the listbox assumes the "headings" are to yes. If you don't use
headings, then you need to use selected(0) = True. (listbox values are zero
based, or 1 based depending on if headings are shown...)

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn

 

[Han]

Albert, thank you for your response and continued help.

Ok, my dilemma is much more fundamental.

In the VB editor, I begin to construct the line:

Me.

At this point, a listbox is presented with a bunch of controls. I then
select my form name from the list:

Me.Detail.

At this point, another listbox is presented with a bunch more items. "Form"
is NOT included in this list. I proceed to hard code the line anyway:

Me.Detail.Form.RecordSource = strSQL

This fails as expected with the following error:

"Method or data member not found."

This leads me to believe that there might be a reference (i.e.
Tools/References from the VB editor menu) missing. Any idea which reference
that is? Or is the problem something different?

Thanks,
Han

 

[Han]

Ok, I'm getting closer. My detail form is displaying the correct record
(sort of--see below.)

In my list box are four columns:

First Name, Last Name, Company, Type

My listbox select statement returns the above fields plus the contactID:

SELECT [FirstName], [LastName], [Company], [Type], [ContactID]

The order is fussy here and here's my problem. If ContactID is placed first,
it will show up in the First Name column, move everything over one and bump
Type off. However, the detail page happily shows the correct record. If
ContactID is not first, the value of Me.lstResults is First Name and the
detail form then requests the value of First Name.

Bottom line, how do you reference a field value other than the first?

 

[Albert D. Kallal]

In the VB editor, I begin to construct the line:

Me.

At this point, a listbox is presented with a bunch of controls. I then
select my form name from the list:

Me.Detail.

Unfortunately, Detail is a reserved word, and in fact represents the actual
detail properties of the form. (so, you should not use, or place a control
on a form called detail, as it will conflict with the forms detail property.
The solution is of course to use a different control name.

me.MySubDetail.Form.RecordSouce

Note that you can still continue to use the form name of Detail (since as
mentoend, you have to set what form the sub-form contorl uses. However, I
would probably change it to frmDetail to advoied confusion in the future.

 

[Albert D. Kallal]

I almost always have the first field of the listbox as the key "id".

You can grab other values from that listbox like:

lstMyList.Column(1)

The above would return the 2nd value (it is zero based).

If you make the format length of the first column (id) 0, then it will be
hidden.

The wizard usually does this for you anyway...assuming you are using the
wizard to crate the list box.

 

[Han]

Yup, I stumbled across the Column attribute too. Very useful.

Building forms in Access kinda grows on you once you conform your thinking
to the Microsoft way of doing things.

In any event, you saved me a great deal of time--very much appreciated.

Regards,
Han

 

[Han]

Yup, I stumbled across the Column attribute too. Very useful.

Building forms in Access kinda grows on you once you conform your thinking
to the Microsoft way of doing things.

In any event, you saved me a great deal of time--very much appreciated.

Regards,
Han

 

[Han]

Yup, I stumbled across the Column attribute too. Very useful.

Building forms in Access kinda grows on you once you conform your thinking
to the Microsoft way of doing things.

In any event, you saved me a great deal of time--very much appreciated.

Regards,
Han