Sticky List Box

[oldblindpew]

This has probably been asked before, but I didn't find a thread on it,
probably due to not knowing how to search.

The user is using a form to do maintenance on Firms table. A list box on a
dialog form is used to choose the desired firm, by name. The list box always
appears in alphabetical order, starting at the top of the list. How can I
bookmark the list box so as to return to the last firm chosen, rather than
always going back to the top of the list? I want the list box to be "sticky"
or to remember where the user last searched.

Thanks

 

[Mr. B]

oldblindpew,

The simple answer is save the latest value selected in the list box to a
varaible and then set the listbox value to that variable upon returning to
the list box.

I am somewhat confused because normally a listbox will maintain the selected
value until it is requeried or it is set to some other value by code. Do you
have other code that is setting the value of your list box?

HTH
Mr. B
askdoctoraccess dot com

 

[Jeff Boyce]

To follow on Mr. B's question, is that listbox bound to any field in your
underlying table?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dale Fye]

It sounds like you are popping up your search form from a main form, and
then closing it again when you are done with the search. Is that accurate?
If so, then the record in your main form should have the information for the
company you last selected, shouldn't it?

If so, you could use the search forms load event to set the value of the
list box to the value from the appropriate field in your main form.
Something like:

Private Sub Form_Load

if currentproject.allforms(MainFormName).isloaded = false then
'don't know how you would get here
msgbox "Main form is not loaded"
elseif len(forms("MainFormName").txtCompanyID & "") = 0 then
me.lstCompanies = NULL
else
me.lstCompanies = forms("MainFormName").txtCompanyID
endif

end sub

HTH
Dale

 

[oldblindpew]

Hi Dale, and thanks for replying.

Yes, the search form is called from main form and then closed when Firm is
selected. So yes, the main form is aware of the last firm selected.

There is an additional complication: the value of the list box in my search
form is the firmID number for the selected firm, which is an autonumber key.
The main form does not contain a control for this field, since the whole idea
of a surrogate key is that you don't have to see it or worry about it.

Would I actually have to put the key field on the main form and make it
invisible?! It seems like surrogate keys were an afterthought in Acces, and
no tools were added or enhanced to handle them. For instance, I just learned
that FindRecord, an otherwise simple and powerful method, becomes useless
under these same circumstances.

Also, I'm getting compile errors. In the "If" statement, my main form name
is not being recognized; it is flagged as an undefined variable. The
"elseif" statement is also failing because Access expected end of statement.
I've tried monkeying with the syntax, but have gotten nowhere.

The thought occurred to me to simply make the search form invisible instead
of closing it, and then make it visible again. There are probably a host of
reasons why this won't work. One thing we have to plan for is the difference
between the first time the search form loads, where it should go to the top
of the list, and subsequent loads, where it should go to the specified firm.

--o.b.p.

 

[Dale Fye]

Although it is not required, I will generally put the Autonumber field in a
textbox on the form, but will set it's visible property to false (actually I
have it setup so that when I am using the application in my debugging mode
it sets the visible property to True, otherwise, it sets the visible
property to False). You could also include the autonumber field in the
forms record source, but not actually bind it to a control. If you do that,
then you can refer to it as me.[ID] instead of me.txt_ID (assumes you name
the textbox using a naming convention).

I'm not sure why you state that surrogate keys were an afterthought, or what
tools you would like to have to deal with them. I'm also not sure what you
mean about the FindRecord method being useless "under these circumstances".

Post the SQL you are using for the Load event of your search form. I'll
take a look and see if I can figure out what I did wrong (or whether you
just didn't translate my control and field names to what you have in your
table/form).

Dale

 

[BruceM]

There was a fairly extensive discussion about what seems to be the same
topic, at least in part, in a thread started March 3, entitled "Somebody
please help me". I had suggested a way to use the search form to find a
record on any other form by passing the form name via OpenArgs. This would
require that the search form be closed in between a search on one form and a
search on another one. It should also be possible to pass the value by way
of a custom function, I suppose, but it seemed more complicated than was
necessary to do it that way. I may have overlooked a simpler approach.

The OP's original approach was to use FindRecord and GoToRecord, which
apparently require that a control bound to the matching field (the ID field,
for instance) have the focus. I suggested using RecordSetClone and
Bookmark. I think the OP was headed in that direction, but I think I also
suggested that a new thread might attract more attention. I'm just trying
to fill in some missing information that may make the whole thing a little
clearer.

Although it is not required, I will generally put the Autonumber field in
a textbox on the form, but will set it's visible property to false
(actually I have it setup so that when I am using the application in my
debugging mode it sets the visible property to True, otherwise, it sets
the visible property to False). You could also include the autonumber
field in the forms record source, but not actually bind it to a control.
If you do that, then you can refer to it as me.[ID] instead of me.txt_ID
(assumes you name the textbox using a naming convention).

I'm not sure why you state that surrogate keys were an afterthought, or
what tools you would like to have to deal with them. I'm also not sure
what you mean about the FindRecord method being useless "under these
circumstances".

Post the SQL you are using for the Load event of your search form. I'll
take a look and see if I can figure out what I did wrong (or whether you
just didn't translate my control and field names to what you have in your
table/form).

Dale

Hi Dale, and thanks for replying.

Yes, the search form is called from main form and then closed when Firm
is
selected. So yes, the main form is aware of the last firm selected.

There is an additional complication: the value of the list box in my
search
form is the firmID number for the selected firm, which is an autonumber
key.
The main form does not contain a control for this field, since the whole
idea
of a surrogate key is that you don't have to see it or worry about it.

Would I actually have to put the key field on the main form and make it
invisible?! It seems like surrogate keys were an afterthought in Acces,
and
no tools were added or enhanced to handle them. For instance, I just
learned
that FindRecord, an otherwise simple and powerful method, becomes useless
under these same circumstances.

Also, I'm getting compile errors. In the "If" statement, my main form
name
is not being recognized; it is flagged as an undefined variable. The
"elseif" statement is also failing because Access expected end of
statement.
I've tried monkeying with the syntax, but have gotten nowhere.

The thought occurred to me to simply make the search form invisible
instead
of closing it, and then make it visible again. There are probably a host
of
reasons why this won't work. One thing we have to plan for is the
difference
between the first time the search form loads, where it should go to the
top
of the list, and subsequent loads, where it should go to the specified
firm.

--o.b.p.

 

[oldblindpew]

Hi Dale:
Thanks for helping, and thanks to BruceM. I'd really be up the creek
without this forum.

By adding an invisible text box to my main form, to hold the value of the
FirmID key, I've now gotten the dialog form to behave in the "sticky"
fashion, so I'm almost there, but have a couple more questions.

I use a public function to open many of my forms. This way, the same bit of
code performs the same kind of job using different values, plus I can run the
code directly from an on-click event without having to go thru a private sub.
This function now has two arguments: the name of the form to open, and the
name of the calling form. The name of the calling form is passed to the
opening form via OpenArgs.

Here is the code for the On Load event of my dialog form, minus error
handling:

Private Sub Form_Load()
'Show last Firm searched.
Dim strArgs As String
strArgs = Me.OpenArgs
Me.lstFirm = Forms(strArgs).txbFirmID
Exit Sub
End Sub

This just makes the value of the list box in the dialog form equal to the
FirmID of the current record in the main form.

Question1: When the main form is first opened, it automatically shows the
first record in the table. How can I change this? When first opened, I want
the main form to either be blank, or to go to the first firm in alphabetical
order, not to record no. 1.

Question2: After the "sitcky" dialog form pops up, is there a way to make
it's list box scroll down a little so the chosen value is centered in the
list box, rather than being at the bottom? I want the user to be able to see
firm names before and after the current firm.

In reply to your question: As BruceM indicated, FindRecord searches the
field that is bound to the control that has focus in the form. It's like
FindRecord assumes that the key field will be present in the form, which
harkens back to the days of user-controlled key values. Using an invisible
text box doesn't work because it can't be given focus. So FindRecord works
well with visible keys, but not with invisible, i.e. autonumbering keys.
This lack of utility leads to RecordsetClones and Bookmarks. Access VBA 2007
Programmer's Reference, p. 255, admits: "To reposition the form, you use a
RecordsetClone. This is a strange concept to developers when they first use
it."

Although it is not required, I will generally put the Autonumber field in a
textbox on the form, but will set it's visible property to false (actually I
have it setup so that when I am using the application in my debugging mode
it sets the visible property to True, otherwise, it sets the visible
property to False). You could also include the autonumber field in the
forms record source, but not actually bind it to a control. If you do that,
then you can refer to it as me.[ID] instead of me.txt_ID (assumes you name
the textbox using a naming convention).

I'm not sure why you state that surrogate keys were an afterthought, or what
tools you would like to have to deal with them. I'm also not sure what you
mean about the FindRecord method being useless "under these circumstances".

Post the SQL you are using for the Load event of your search form. I'll
take a look and see if I can figure out what I did wrong (or whether you
just didn't translate my control and field names to what you have in your
table/form).

Dale

Hi Dale, and thanks for replying.

Yes, the search form is called from main form and then closed when Firm is
selected. So yes, the main form is aware of the last firm selected.

There is an additional complication: the value of the list box in my
search
form is the firmID number for the selected firm, which is an autonumber
key.
The main form does not contain a control for this field, since the whole
idea
of a surrogate key is that you don't have to see it or worry about it.

Would I actually have to put the key field on the main form and make it
invisible?! It seems like surrogate keys were an afterthought in Acces,
and
no tools were added or enhanced to handle them. For instance, I just
learned
that FindRecord, an otherwise simple and powerful method, becomes useless
under these same circumstances.

Also, I'm getting compile errors. In the "If" statement, my main form
name
is not being recognized; it is flagged as an undefined variable. The
"elseif" statement is also failing because Access expected end of
statement.
I've tried monkeying with the syntax, but have gotten nowhere.

The thought occurred to me to simply make the search form invisible
instead
of closing it, and then make it visible again. There are probably a host
of
reasons why this won't work. One thing we have to plan for is the
difference
between the first time the search form loads, where it should go to the
top
of the list, and subsequent loads, where it should go to the specified
firm.

--o.b.p.

 

[BruceM]

Regarding Question 1, make a query based on the table. Sort and arrange the
data as you prefer, and use that query as the form's Record Source.

Regarding Question 2, I don't think so, but I'm not absolutely certain.

I think someone suggested in the other thread that you can make a visible
list box very small (.01 x .01), with its Special Effect property set to
Flat, no border, text color and back color the same as the background, and
tucked away in a corner of the form. You can do this with an autonumber
field as well as any other field.

I have not had a reason to use GoToRecord or FindRecord, and am answering
here because it has been a while since anybody else has added to this
thread. That RecordSetClone is (in the quoted author's view) a strange
concept at first does not argue against its use. There are strange concepts
with many machines, software products, economic philosophies, or just about
anything you care to name. Maybe there is a way to use GoToRecord or
FindRecord to do what you need, but the three or four lines of code used by
RecordSetClone and Bookmark are for me the simplest. Please pardon my
saying so, but it almost seems to me as if you are searching for a way to
use a method that does not involve RecordSetClone because the concept
bothers you.

Hi Dale:
Thanks for helping, and thanks to BruceM. I'd really be up the creek
without this forum.

By adding an invisible text box to my main form, to hold the value of the
FirmID key, I've now gotten the dialog form to behave in the "sticky"
fashion, so I'm almost there, but have a couple more questions.

I use a public function to open many of my forms. This way, the same bit
of
code performs the same kind of job using different values, plus I can run
the
code directly from an on-click event without having to go thru a private
sub.
This function now has two arguments: the name of the form to open, and the
name of the calling form. The name of the calling form is passed to the
opening form via OpenArgs.

Here is the code for the On Load event of my dialog form, minus error
handling:

Private Sub Form_Load()
'Show last Firm searched.
Dim strArgs As String
strArgs = Me.OpenArgs
Me.lstFirm = Forms(strArgs).txbFirmID
Exit Sub
End Sub

This just makes the value of the list box in the dialog form equal to the
FirmID of the current record in the main form.

Question1: When the main form is first opened, it automatically shows the
first record in the table. How can I change this? When first opened, I
want
the main form to either be blank, or to go to the first firm in
alphabetical
order, not to record no. 1.

Question2: After the "sitcky" dialog form pops up, is there a way to make
it's list box scroll down a little so the chosen value is centered in the
list box, rather than being at the bottom? I want the user to be able to
see
firm names before and after the current firm.

In reply to your question: As BruceM indicated, FindRecord searches the
field that is bound to the control that has focus in the form. It's like
FindRecord assumes that the key field will be present in the form, which
harkens back to the days of user-controlled key values. Using an
invisible
text box doesn't work because it can't be given focus. So FindRecord
works
well with visible keys, but not with invisible, i.e. autonumbering keys.
This lack of utility leads to RecordsetClones and Bookmarks. Access VBA
2007
Programmer's Reference, p. 255, admits: "To reposition the form, you use
a
RecordsetClone. This is a strange concept to developers when they first
use
it."

Although it is not required, I will generally put the Autonumber field in
a
textbox on the form, but will set it's visible property to false
(actually I
have it setup so that when I am using the application in my debugging
mode
it sets the visible property to True, otherwise, it sets the visible
property to False). You could also include the autonumber field in the
forms record source, but not actually bind it to a control. If you do
that,
then you can refer to it as me.[ID] instead of me.txt_ID (assumes you
name
the textbox using a naming convention).

I'm not sure why you state that surrogate keys were an afterthought, or
what
tools you would like to have to deal with them. I'm also not sure what
you
mean about the FindRecord method being useless "under these
circumstances".

Post the SQL you are using for the Load event of your search form. I'll
take a look and see if I can figure out what I did wrong (or whether you
just didn't translate my control and field names to what you have in your
table/form).

Dale

Hi Dale, and thanks for replying.

Yes, the search form is called from main form and then closed when Firm
is
selected. So yes, the main form is aware of the last firm selected.

There is an additional complication: the value of the list box in my
search
form is the firmID number for the selected firm, which is an autonumber
key.
The main form does not contain a control for this field, since the
whole
idea
of a surrogate key is that you don't have to see it or worry about it.

Would I actually have to put the key field on the main form and make it
invisible?! It seems like surrogate keys were an afterthought in
Acces,
and
no tools were added or enhanced to handle them. For instance, I just
learned
that FindRecord, an otherwise simple and powerful method, becomes
useless
under these same circumstances.

Also, I'm getting compile errors. In the "If" statement, my main form
name
is not being recognized; it is flagged as an undefined variable. The
"elseif" statement is also failing because Access expected end of
statement.
I've tried monkeying with the syntax, but have gotten nowhere.

The thought occurred to me to simply make the search form invisible
instead
of closing it, and then make it visible again. There are probably a
host
of
reasons why this won't work. One thing we have to plan for is the
difference
between the first time the search form loads, where it should go to the
top
of the list, and subsequent loads, where it should go to the specified
firm.

--o.b.p.
:

It sounds like you are popping up your search form from a main form,
and
then closing it again when you are done with the search. Is that
accurate?
If so, then the record in your main form should have the information
for
the
company you last selected, shouldn't it?

If so, you could use the search forms load event to set the value of
the
list box to the value from the appropriate field in your main form.
Something like:

Private Sub Form_Load

if currentproject.allforms(MainFormName).isloaded = false then
'don't know how you would get here
msgbox "Main form is not loaded"
elseif len(forms("MainFormName").txtCompanyID & "") = 0 then
me.lstCompanies = NULL
else
me.lstCompanies = forms("MainFormName").txtCompanyID
endif

end sub

HTH
Dale

This has probably been asked before, but I didn't find a thread on
it,
probably due to not knowing how to search.

The user is using a form to do maintenance on Firms table. A list
box
on
a
dialog form is used to choose the desired firm, by name. The list
box
always
appears in alphabetical order, starting at the top of the list. How
can I
bookmark the list box so as to return to the last firm chosen,
rather
than
always going back to the top of the list? I want the list box to be
"sticky"
or to remember where the user last searched.

Thanks

 

[oldblindpew]

BruceM,
Thanks for bothering with this.

I've done an ugly little workaround where On Load of the main form, I get to
a record at or near the top of the list like so:

Set rst = Me.RecordsetClone
rst.FindFirst "tNameFirm Like 'A *'"
If Not rst.EOF Then
Me.Bookmark = rst.Bookmark
End If

This feels pretty shaky and will probably blow up on me some day. I'll look
into your advice, which is undoubtedly correct, of using a query to create
the record source, rather than just the plain unvarnished table (no pun
intended).

Regarding FindRecord, I am NOT saying RecordsetClone, FindFirst, and
Bookmark don't work well or shouldn't be used. I'm just saying, as a novice,
it seems unreasonably awkward, complex, and arcane. Programming is just
harder now than it was back in the Fortan days, and I guess I just came at
this with the idea that things had been improved.

Regarding Question 1, make a query based on the table. Sort and arrange the
data as you prefer, and use that query as the form's Record Source.

Regarding Question 2, I don't think so, but I'm not absolutely certain.

I think someone suggested in the other thread that you can make a visible
list box very small (.01 x .01), with its Special Effect property set to
Flat, no border, text color and back color the same as the background, and
tucked away in a corner of the form. You can do this with an autonumber
field as well as any other field.

I have not had a reason to use GoToRecord or FindRecord, and am answering
here because it has been a while since anybody else has added to this
thread. That RecordSetClone is (in the quoted author's view) a strange
concept at first does not argue against its use. There are strange concepts
with many machines, software products, economic philosophies, or just about
anything you care to name. Maybe there is a way to use GoToRecord or
FindRecord to do what you need, but the three or four lines of code used by
RecordSetClone and Bookmark are for me the simplest. Please pardon my
saying so, but it almost seems to me as if you are searching for a way to
use a method that does not involve RecordSetClone because the concept
bothers you.

Hi Dale:
Thanks for helping, and thanks to BruceM. I'd really be up the creek
without this forum.

By adding an invisible text box to my main form, to hold the value of the
FirmID key, I've now gotten the dialog form to behave in the "sticky"
fashion, so I'm almost there, but have a couple more questions.

I use a public function to open many of my forms. This way, the same bit
of
code performs the same kind of job using different values, plus I can run
the
code directly from an on-click event without having to go thru a private
sub.
This function now has two arguments: the name of the form to open, and the
name of the calling form. The name of the calling form is passed to the
opening form via OpenArgs.

Here is the code for the On Load event of my dialog form, minus error
handling:

Private Sub Form_Load()
'Show last Firm searched.
Dim strArgs As String
strArgs = Me.OpenArgs
Me.lstFirm = Forms(strArgs).txbFirmID
Exit Sub
End Sub

This just makes the value of the list box in the dialog form equal to the
FirmID of the current record in the main form.

Question1: When the main form is first opened, it automatically shows the
first record in the table. How can I change this? When first opened, I
want
the main form to either be blank, or to go to the first firm in
alphabetical
order, not to record no. 1.

Question2: After the "sitcky" dialog form pops up, is there a way to make
it's list box scroll down a little so the chosen value is centered in the
list box, rather than being at the bottom? I want the user to be able to
see
firm names before and after the current firm.

In reply to your question: As BruceM indicated, FindRecord searches the
field that is bound to the control that has focus in the form. It's like
FindRecord assumes that the key field will be present in the form, which
harkens back to the days of user-controlled key values. Using an
invisible
text box doesn't work because it can't be given focus. So FindRecord
works
well with visible keys, but not with invisible, i.e. autonumbering keys.
This lack of utility leads to RecordsetClones and Bookmarks. Access VBA
2007
Programmer's Reference, p. 255, admits: "To reposition the form, you use
a
RecordsetClone. This is a strange concept to developers when they first
use
it."

Although it is not required, I will generally put the Autonumber field in
a
textbox on the form, but will set it's visible property to false
(actually I
have it setup so that when I am using the application in my debugging
mode
it sets the visible property to True, otherwise, it sets the visible
property to False). You could also include the autonumber field in the
forms record source, but not actually bind it to a control. If you do
that,
then you can refer to it as me.[ID] instead of me.txt_ID (assumes you
name
the textbox using a naming convention).

I'm not sure why you state that surrogate keys were an afterthought, or
what
tools you would like to have to deal with them. I'm also not sure what
you
mean about the FindRecord method being useless "under these
circumstances".

Post the SQL you are using for the Load event of your search form. I'll
take a look and see if I can figure out what I did wrong (or whether you
just didn't translate my control and field names to what you have in your
table/form).

Dale

Hi Dale, and thanks for replying.

Yes, the search form is called from main form and then closed when Firm
is
selected. So yes, the main form is aware of the last firm selected.

There is an additional complication: the value of the list box in my
search
form is the firmID number for the selected firm, which is an autonumber
key.
The main form does not contain a control for this field, since the
whole
idea
of a surrogate key is that you don't have to see it or worry about it.

Would I actually have to put the key field on the main form and make it
invisible?! It seems like surrogate keys were an afterthought in
Acces,
and
no tools were added or enhanced to handle them. For instance, I just
learned
that FindRecord, an otherwise simple and powerful method, becomes
useless
under these same circumstances.

Also, I'm getting compile errors. In the "If" statement, my main form
name
is not being recognized; it is flagged as an undefined variable. The
"elseif" statement is also failing because Access expected end of
statement.
I've tried monkeying with the syntax, but have gotten nowhere.

The thought occurred to me to simply make the search form invisible
instead
of closing it, and then make it visible again. There are probably a
host
of
reasons why this won't work. One thing we have to plan for is the
difference
between the first time the search form loads, where it should go to the
top
of the list, and subsequent loads, where it should go to the specified
firm.

--o.b.p.
:

It sounds like you are popping up your search form from a main form,
and
then closing it again when you are done with the search. Is that
accurate?
If so, then the record in your main form should have the information
for
the
company you last selected, shouldn't it?

If so, you could use the search forms load event to set the value of
the
list box to the value from the appropriate field in your main form.
Something like:

Private Sub Form_Load

if currentproject.allforms(MainFormName).isloaded = false then
'don't know how you would get here
msgbox "Main form is not loaded"
elseif len(forms("MainFormName").txtCompanyID & "") = 0 then
me.lstCompanies = NULL
else
me.lstCompanies = forms("MainFormName").txtCompanyID
endif

end sub

HTH
Dale

This has probably been asked before, but I didn't find a thread on
it,
probably due to not knowing how to search.

The user is using a form to do maintenance on Firms table. A list
box
on
a
dialog form is used to choose the desired firm, by name. The list
box
always
appears in alphabetical order, starting at the top of the list. How
can I
bookmark the list box so as to return to the last firm chosen,
rather
than
always going back to the top of the list? I want the list box to be
"sticky"
or to remember where the user last searched.

Thanks

 

[Dale Fye]

"improved" is a subjective assessment

Object and event based programming is certainly more complicated than the
straight Fortran code many of us wrote back in the 70s and 80s, but the
results sure are a lot simpler. I can remember having to tab through an
entire screen, just to update a single field in the bottom right corner.

Dale

BruceM,
Thanks for bothering with this.

I've done an ugly little workaround where On Load of the main form, I get
to
a record at or near the top of the list like so:

Set rst = Me.RecordsetClone
rst.FindFirst "tNameFirm Like 'A *'"
If Not rst.EOF Then
Me.Bookmark = rst.Bookmark
End If

This feels pretty shaky and will probably blow up on me some day. I'll
look
into your advice, which is undoubtedly correct, of using a query to create
the record source, rather than just the plain unvarnished table (no pun
intended).

Regarding FindRecord, I am NOT saying RecordsetClone, FindFirst, and
Bookmark don't work well or shouldn't be used. I'm just saying, as a
novice,
it seems unreasonably awkward, complex, and arcane. Programming is just
harder now than it was back in the Fortan days, and I guess I just came at
this with the idea that things had been improved.

Regarding Question 1, make a query based on the table. Sort and arrange
the
data as you prefer, and use that query as the form's Record Source.

Regarding Question 2, I don't think so, but I'm not absolutely certain.

I think someone suggested in the other thread that you can make a visible
list box very small (.01 x .01), with its Special Effect property set to
Flat, no border, text color and back color the same as the background,
and
tucked away in a corner of the form. You can do this with an autonumber
field as well as any other field.

I have not had a reason to use GoToRecord or FindRecord, and am answering
here because it has been a while since anybody else has added to this
thread. That RecordSetClone is (in the quoted author's view) a strange
concept at first does not argue against its use. There are strange
concepts
with many machines, software products, economic philosophies, or just
about
anything you care to name. Maybe there is a way to use GoToRecord or
FindRecord to do what you need, but the three or four lines of code used
by
RecordSetClone and Bookmark are for me the simplest. Please pardon my
saying so, but it almost seems to me as if you are searching for a way to
use a method that does not involve RecordSetClone because the concept
bothers you.

Hi Dale:
Thanks for helping, and thanks to BruceM. I'd really be up the creek
without this forum.

By adding an invisible text box to my main form, to hold the value of
the
FirmID key, I've now gotten the dialog form to behave in the "sticky"
fashion, so I'm almost there, but have a couple more questions.

I use a public function to open many of my forms. This way, the same
bit
of
code performs the same kind of job using different values, plus I can
run
the
code directly from an on-click event without having to go thru a
private
sub.
This function now has two arguments: the name of the form to open, and
the
name of the calling form. The name of the calling form is passed to
the
opening form via OpenArgs.

Here is the code for the On Load event of my dialog form, minus error
handling:

Private Sub Form_Load()
'Show last Firm searched.
Dim strArgs As String
strArgs = Me.OpenArgs
Me.lstFirm = Forms(strArgs).txbFirmID
Exit Sub
End Sub

This just makes the value of the list box in the dialog form equal to
the
FirmID of the current record in the main form.

Question1: When the main form is first opened, it automatically shows
the
first record in the table. How can I change this? When first opened,
I
want
the main form to either be blank, or to go to the first firm in
alphabetical
order, not to record no. 1.

Question2: After the "sitcky" dialog form pops up, is there a way to
make
it's list box scroll down a little so the chosen value is centered in
the
list box, rather than being at the bottom? I want the user to be able
to
see
firm names before and after the current firm.

In reply to your question: As BruceM indicated, FindRecord searches
the
field that is bound to the control that has focus in the form. It's
like
FindRecord assumes that the key field will be present in the form,
which
harkens back to the days of user-controlled key values. Using an
invisible
text box doesn't work because it can't be given focus. So FindRecord
works
well with visible keys, but not with invisible, i.e. autonumbering
keys.
This lack of utility leads to RecordsetClones and Bookmarks. Access
VBA
2007
Programmer's Reference, p. 255, admits: "To reposition the form, you
use
a
RecordsetClone. This is a strange concept to developers when they
first
use
it."

:

Although it is not required, I will generally put the Autonumber field
in
a
textbox on the form, but will set it's visible property to false
(actually I
have it setup so that when I am using the application in my debugging
mode
it sets the visible property to True, otherwise, it sets the visible
property to False). You could also include the autonumber field in
the
forms record source, but not actually bind it to a control. If you do
that,
then you can refer to it as me.[ID] instead of me.txt_ID (assumes you
name
the textbox using a naming convention).

I'm not sure why you state that surrogate keys were an afterthought,
or
what
tools you would like to have to deal with them. I'm also not sure
what
you
mean about the FindRecord method being useless "under these
circumstances".

Post the SQL you are using for the Load event of your search form.
I'll
take a look and see if I can figure out what I did wrong (or whether
you
just didn't translate my control and field names to what you have in
your
table/form).

Dale

Hi Dale, and thanks for replying.

Yes, the search form is called from main form and then closed when
Firm
is
selected. So yes, the main form is aware of the last firm selected.

There is an additional complication: the value of the list box in my
search
form is the firmID number for the selected firm, which is an
autonumber
key.
The main form does not contain a control for this field, since the
whole
idea
of a surrogate key is that you don't have to see it or worry about
it.

Would I actually have to put the key field on the main form and make
it
invisible?! It seems like surrogate keys were an afterthought in
Acces,
and
no tools were added or enhanced to handle them. For instance, I
just
learned
that FindRecord, an otherwise simple and powerful method, becomes
useless
under these same circumstances.

Also, I'm getting compile errors. In the "If" statement, my main
form
name
is not being recognized; it is flagged as an undefined variable.
The
"elseif" statement is also failing because Access expected end of
statement.
I've tried monkeying with the syntax, but have gotten nowhere.

The thought occurred to me to simply make the search form invisible
instead
of closing it, and then make it visible again. There are probably a
host
of
reasons why this won't work. One thing we have to plan for is the
difference
between the first time the search form loads, where it should go to
the
top
of the list, and subsequent loads, where it should go to the
specified
firm.

--o.b.p.
:

It sounds like you are popping up your search form from a main
form,
and
then closing it again when you are done with the search. Is that
accurate?
If so, then the record in your main form should have the
information
for
the
company you last selected, shouldn't it?

If so, you could use the search forms load event to set the value
of
the
list box to the value from the appropriate field in your main form.
Something like:

Private Sub Form_Load

if currentproject.allforms(MainFormName).isloaded = false then
'don't know how you would get here
msgbox "Main form is not loaded"
elseif len(forms("MainFormName").txtCompanyID & "") = 0 then
me.lstCompanies = NULL
else
me.lstCompanies = forms("MainFormName").txtCompanyID
endif

end sub

HTH
Dale

message
This has probably been asked before, but I didn't find a thread
on
it,
probably due to not knowing how to search.

The user is using a form to do maintenance on Firms table. A
list
box
on
a
dialog form is used to choose the desired firm, by name. The
list
box
always
appears in alphabetical order, starting at the top of the list.
How
can I
bookmark the list box so as to return to the last firm chosen,
rather
than
always going back to the top of the list? I want the list box to
be
"sticky"
or to remember where the user last searched.

Thanks

 

[BruceM]

I think it will be OK as long as there is a firm with a name beginning with
the letter "A". However, a query is absolutely the way to go. It can be as
simple as adding every field from the table, and sorting by a chosen field,
or by more than one field. There are other advantages. For instance, in a
database of names you can sort by last, first, middle if you wish; you can
also combine the names into a single calculated field:
FullName: [FirstName] & " " & [MiddleInitial] & ". " & [LastName]

or

LastFirst: [LastName] & ", " & [FirstName]

Bind a text box to FullName or LastFirst as you would to a table field.

You can use functions such as DateAdd to calculate a date ten days or three
months or two years or whatever before or after a stored date value; or
combine City, State, Zip into a single line; or select the top ten records
in the recordset or within groups; and so forth and so on. If you are not
using queries you are losing out on a very large part of the data management
capabilities Access offers.

BruceM,
Thanks for bothering with this.

I've done an ugly little workaround where On Load of the main form, I get
to
a record at or near the top of the list like so:

Set rst = Me.RecordsetClone
rst.FindFirst "tNameFirm Like 'A *'"
If Not rst.EOF Then
Me.Bookmark = rst.Bookmark
End If

This feels pretty shaky and will probably blow up on me some day. I'll
look
into your advice, which is undoubtedly correct, of using a query to create
the record source, rather than just the plain unvarnished table (no pun
intended).

Regarding FindRecord, I am NOT saying RecordsetClone, FindFirst, and
Bookmark don't work well or shouldn't be used. I'm just saying, as a
novice,
it seems unreasonably awkward, complex, and arcane. Programming is just
harder now than it was back in the Fortan days, and I guess I just came at
this with the idea that things had been improved.

Regarding Question 1, make a query based on the table. Sort and arrange
the
data as you prefer, and use that query as the form's Record Source.

Regarding Question 2, I don't think so, but I'm not absolutely certain.

I think someone suggested in the other thread that you can make a visible
list box very small (.01 x .01), with its Special Effect property set to
Flat, no border, text color and back color the same as the background,
and
tucked away in a corner of the form. You can do this with an autonumber
field as well as any other field.

I have not had a reason to use GoToRecord or FindRecord, and am answering
here because it has been a while since anybody else has added to this
thread. That RecordSetClone is (in the quoted author's view) a strange
concept at first does not argue against its use. There are strange
concepts
with many machines, software products, economic philosophies, or just
about
anything you care to name. Maybe there is a way to use GoToRecord or
FindRecord to do what you need, but the three or four lines of code used
by
RecordSetClone and Bookmark are for me the simplest. Please pardon my
saying so, but it almost seems to me as if you are searching for a way to
use a method that does not involve RecordSetClone because the concept
bothers you.

Hi Dale:
Thanks for helping, and thanks to BruceM. I'd really be up the creek
without this forum.

By adding an invisible text box to my main form, to hold the value of
the
FirmID key, I've now gotten the dialog form to behave in the "sticky"
fashion, so I'm almost there, but have a couple more questions.

I use a public function to open many of my forms. This way, the same
bit
of
code performs the same kind of job using different values, plus I can
run
the
code directly from an on-click event without having to go thru a
private
sub.
This function now has two arguments: the name of the form to open, and
the
name of the calling form. The name of the calling form is passed to
the
opening form via OpenArgs.

Here is the code for the On Load event of my dialog form, minus error
handling:

Private Sub Form_Load()
'Show last Firm searched.
Dim strArgs As String
strArgs = Me.OpenArgs
Me.lstFirm = Forms(strArgs).txbFirmID
Exit Sub
End Sub

This just makes the value of the list box in the dialog form equal to
the
FirmID of the current record in the main form.

Question1: When the main form is first opened, it automatically shows
the
first record in the table. How can I change this? When first opened,
I
want
the main form to either be blank, or to go to the first firm in
alphabetical
order, not to record no. 1.

Question2: After the "sitcky" dialog form pops up, is there a way to
make
it's list box scroll down a little so the chosen value is centered in
the
list box, rather than being at the bottom? I want the user to be able
to
see
firm names before and after the current firm.

In reply to your question: As BruceM indicated, FindRecord searches
the
field that is bound to the control that has focus in the form. It's
like
FindRecord assumes that the key field will be present in the form,
which
harkens back to the days of user-controlled key values. Using an
invisible
text box doesn't work because it can't be given focus. So FindRecord
works
well with visible keys, but not with invisible, i.e. autonumbering
keys.
This lack of utility leads to RecordsetClones and Bookmarks. Access
VBA
2007
Programmer's Reference, p. 255, admits: "To reposition the form, you
use
a
RecordsetClone. This is a strange concept to developers when they
first
use
it."

:

Although it is not required, I will generally put the Autonumber field
in
a
textbox on the form, but will set it's visible property to false
(actually I
have it setup so that when I am using the application in my debugging
mode
it sets the visible property to True, otherwise, it sets the visible
property to False). You could also include the autonumber field in
the
forms record source, but not actually bind it to a control. If you do
that,
then you can refer to it as me.[ID] instead of me.txt_ID (assumes you
name
the textbox using a naming convention).

I'm not sure why you state that surrogate keys were an afterthought,
or
what
tools you would like to have to deal with them. I'm also not sure
what
you
mean about the FindRecord method being useless "under these
circumstances".

Post the SQL you are using for the Load event of your search form.
I'll
take a look and see if I can figure out what I did wrong (or whether
you
just didn't translate my control and field names to what you have in
your
table/form).

Dale

Hi Dale, and thanks for replying.

Yes, the search form is called from main form and then closed when
Firm
is
selected. So yes, the main form is aware of the last firm selected.

There is an additional complication: the value of the list box in my
search
form is the firmID number for the selected firm, which is an
autonumber
key.
The main form does not contain a control for this field, since the
whole
idea
of a surrogate key is that you don't have to see it or worry about
it.

Would I actually have to put the key field on the main form and make
it
invisible?! It seems like surrogate keys were an afterthought in
Acces,
and
no tools were added or enhanced to handle them. For instance, I
just
learned
that FindRecord, an otherwise simple and powerful method, becomes
useless
under these same circumstances.

Also, I'm getting compile errors. In the "If" statement, my main
form
name
is not being recognized; it is flagged as an undefined variable.
The
"elseif" statement is also failing because Access expected end of
statement.
I've tried monkeying with the syntax, but have gotten nowhere.

The thought occurred to me to simply make the search form invisible
instead
of closing it, and then make it visible again. There are probably a
host
of
reasons why this won't work. One thing we have to plan for is the
difference
between the first time the search form loads, where it should go to
the
top
of the list, and subsequent loads, where it should go to the
specified
firm.

--o.b.p.
:

It sounds like you are popping up your search form from a main
form,
and
then closing it again when you are done with the search. Is that
accurate?
If so, then the record in your main form should have the
information
for
the
company you last selected, shouldn't it?

If so, you could use the search forms load event to set the value
of
the
list box to the value from the appropriate field in your main form.
Something like:

Private Sub Form_Load

if currentproject.allforms(MainFormName).isloaded = false then
'don't know how you would get here
msgbox "Main form is not loaded"
elseif len(forms("MainFormName").txtCompanyID & "") = 0 then
me.lstCompanies = NULL
else
me.lstCompanies = forms("MainFormName").txtCompanyID
endif

end sub

HTH
Dale

message
This has probably been asked before, but I didn't find a thread
on
it,
probably due to not knowing how to search.

The user is using a form to do maintenance on Firms table. A
list
box
on
a
dialog form is used to choose the desired firm, by name. The
list
box
always
appears in alphabetical order, starting at the top of the list.
How
can I
bookmark the list box so as to return to the last firm chosen,
rather
than
always going back to the top of the list? I want the list box to
be
"sticky"
or to remember where the user last searched.

Thanks