How can I format label name as I loop through records?

[Guest]

I have a subform with a relatively small (under 40) maximum number of records
for each main form record. I'm using a tab control to identify all the
potential subform records, whether present or not (this is a cosmetic feature
- no data is actually stored on the tab control pages). My application is
working fine but I would like to add an indicator above each tab to show
whether or not that particular record exists.

I have created small 'marker' labels above each tab and want to make these
labels visible when the subform record is missing (and vice versa). My plan
is to loop through the recordsetclone of the subform and hide the labels for
all the records I find. All other marker labels would then be made visible.
The labels have been given the names TabFlag0, TabFlag1, TabFlag2 .......
TabFlag38.

My problem is that I can't currently think of a way to automatically select
the correct label as I step through the loop. Obviously I need to concatenate
"TabFlag" and the current loop counter value but I don't know how to create
an executable line of code using this assembled label name.

I suspect that there is a very simple solution ........

 

[Marshall Barton]

I have a subform with a relatively small (under 40) maximum number of records
for each main form record. I'm using a tab control to identify all the
potential subform records, whether present or not (this is a cosmetic feature
- no data is actually stored on the tab control pages). My application is
working fine but I would like to add an indicator above each tab to show
whether or not that particular record exists.

I have created small 'marker' labels above each tab and want to make these
labels visible when the subform record is missing (and vice versa). My plan
is to loop through the recordsetclone of the subform and hide the labels for
all the records I find. All other marker labels would then be made visible.
The labels have been given the names TabFlag0, TabFlag1, TabFlag2 .......
TabFlag38.

My problem is that I can't currently think of a way to automatically select
the correct label as I step through the loop. Obviously I need to concatenate
"TabFlag" and the current loop counter value but I don't know how to create
an executable line of code using this assembled label name.

You're right, it is simple:

Me("TabFlag" & xx).Visible = True

 

[Dirk Goldgar]

I have a subform with a relatively small (under 40) maximum number of
records for each main form record. I'm using a tab control to
identify all the potential subform records, whether present or not
(this is a cosmetic feature - no data is actually stored on the tab
control pages). My application is working fine but I would like to
add an indicator above each tab to show whether or not that
particular record exists.

I have created small 'marker' labels above each tab and want to make
these labels visible when the subform record is missing (and vice
versa). My plan is to loop through the recordsetclone of the subform
and hide the labels for all the records I find. All other marker
labels would then be made visible. The labels have been given the
names TabFlag0, TabFlag1, TabFlag2 ....... TabFlag38.

My problem is that I can't currently think of a way to automatically
select the correct label as I step through the loop. Obviously I need
to concatenate "TabFlag" and the current loop counter value but I
don't know how to create an executable line of code using this
assembled label name.

I suspect that there is a very simple solution ........

I wonder about your design, since it's usually a bad idea to embed data
content in form design, as you appear to be doing with your tabs and
labels. However, the simple answer to your question is to concatenate
the "TabFlag" prefix with your loop counter and use the result as a
string index into the Control collection, like this:

Dim I As Integer

For I = 0 to 38
Me.Controls("TabFlag" & I).Visible = False
Next I

 

[Guest]

Dirk (and Marshall), thanks for pointing out what is indeed a simple solution.

I will respond to your comment about form design issues later. However, I'm
hitting a very strange problem trying to put your solution into effect. My
code is shown below and my very first subform has a total of 8 records. I
therefore expect to go through the For...Next loop 8 times. In fact, it only
goes through the loop once! I have tested the recordcount value in the
immediate window and get the correct value. Substituting the integer 8 for
..RecordCount in the 'For' line makes the loop work so why is the .RecordCount
property not giving me the same result?

Set rstFrames = Me.RecordsetClone
With rstFrames
.MoveFirst
For intI = 1 To .RecordCount
Me.Controls("TabFlag" & .Fields("FrameNo")).Visible = False
.MoveNext
Next intI
End With

 

[Douglas J. Steele]

RecordCount isn't accurate until the recordset has read the last record in
it.

Try putting a .MoveLast before your .MoveFirst statement.

 

[Guest]

Douglas, thanks for that! Inserting a .MoveLast has indeed fixed the problem.

I still puzzled, however. While stepping through my code I checked the value
of .RecordCount just before executing the 'Next' statement. Since I got the
correct value of 8, why did the loop end?

 

[Douglas J. Steele]

Not sure I completely understand your question.

 

[Dirk Goldgar]

Douglas, thanks for that! Inserting a .MoveLast has indeed fixed the
problem.

I still puzzled, however. While stepping through my code I checked
the value of .RecordCount just before executing the 'Next' statement.
Since I got the correct value of 8, why did the loop end?

I'm not certain how you tested, but bear in mind that the expression you
specify for the end of a For loop is evaluated only once, the first time
execution reaches the For statement. But the recordset is loaded with
records asynchronously, and when the .MoveNext method is called, the
recordset object has a chance to update the .RecordCount property with
the number of records loaded so far. Running the following procedure
(substituting a table name of your own) may give you an idea as to
what's going on:

'----- start of demo code -----
Sub testfor()

Dim rs As DAO.Recordset
Dim I As Integer

Set rs = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)

For I = 1 To rs.RecordCount
Debug.Print I, rs.RecordCount
Debug.Print I, rs.RecordCount
rs.MoveNext
Debug.Print I, rs.RecordCount
Next

rs.Close

End Sub
'----- end of demo code -----

 

[Guest]

Dirk, thanks for lifting the lid a little on the internal workings of Access.
You are absolutely right - the .MoveNext method triggered an update to the
..RecordCount property. Prior to the .MoveNext the .RecordCount was only 1.
After the .MoveNext it was 8.

Getting back to the doubts you expressed earlier about my subform design,
let me attempt to clarify why I have used some data dependant elements in my
subform. Firstly, let me say that I take a keen interest in interface design
issues and always try hard to achieve a simple and uncluttered solution where
the functionality of a form is immediately obvious. I also like to provide as
many aids as possible to minimise input errors. These are my objectives even
when, as in this case, I will be the only user.

My intention is to create a database for several hundred rolls of 35mm film
that I have taken during my life, before getting a digital SLR a year ago.
The maximum number of images I have ever got from one roll of film must be
about 38 and I have already made the equivalent of a contact sheet for each
film using a large format (A3) flatbed scanner.

My task is now to record as much information about these images as I can
gather (probably by interrogating my wife, who remembers everything). This
process will take quite a long time and will be random in nature. Some data
will be collected very quickly. Other information will emerge more slowly as
we begin to dig ever deeper into our family history over the past 30 odd
years.

My main form is based on the Films table and is used for info such as film
type, the first usable frame number, the last usable frame number, comments
relating to the entire film, etc. The 'contact print' image is also displayed
on the main form.

The subform is used for data about each frame of the film. It will also
display a thumbnail image of the frame, if available. Right from the
beginning, I liked the idea of using a tab control with one tab for each
frame and because each tab only carries the frame number it is easy to get
the maximum number of 38 tabs on one row. The row of tabs is reformatted for
each film. Where a frame record exists it is displayed. Where a frame record
is missing a dummy record is shown, with an 'Add new record' button. As
previously mentioned, the tab control is only used as a visual device and no
data is stored on the tab pages.

This design gives me an instant method for selecting any specific frame (a
useful feature given the rather random manner in which I expect to input the
data). My use of miniature marker labels above tabs for which no frame data
exists is also a simple way of highlighting the gaps in my data.

Feel free to offer any opinions you like on my design. Be as critical as you
like!Essentially, it's my first attempt for this particular application and,
without doubt, better designs will be possible. One minor issue is that it
does not lend itself to inputting the same data for several frames all at
once, though I'm not yet sure how often this would be useful.

 

[Dirk Goldgar]

Dirk, thanks for lifting the lid a little on the internal workings of
Access. You are absolutely right - the .MoveNext method triggered an
update to the .RecordCount property. Prior to the .MoveNext the
.RecordCount was only 1. After the .MoveNext it was 8.

Getting back to the doubts you expressed earlier about my subform
design, let me attempt to clarify why I have used some data dependant
elements in my subform. Firstly, let me say that I take a keen
interest in interface design issues and always try hard to achieve a
simple and uncluttered solution where the functionality of a form is
immediately obvious. I also like to provide as many aids as possible
to minimise input errors. These are my objectives even when, as in
this case, I will be the only user.

My intention is to create a database for several hundred rolls of
35mm film that I have taken during my life, before getting a digital
SLR a year ago. The maximum number of images I have ever got from one
roll of film must be about 38 and I have already made the equivalent
of a contact sheet for each film using a large format (A3) flatbed
scanner.

My task is now to record as much information about these images as I
can gather (probably by interrogating my wife, who remembers
everything). This process will take quite a long time and will be
random in nature. Some data will be collected very quickly. Other
information will emerge more slowly as we begin to dig ever deeper
into our family history over the past 30 odd years.

My main form is based on the Films table and is used for info such as
film type, the first usable frame number, the last usable frame
number, comments relating to the entire film, etc. The 'contact
print' image is also displayed on the main form.

The subform is used for data about each frame of the film. It will
also display a thumbnail image of the frame, if available. Right from
the beginning, I liked the idea of using a tab control with one tab
for each frame and because each tab only carries the frame number it
is easy to get the maximum number of 38 tabs on one row. The row of
tabs is reformatted for each film. Where a frame record exists it is
displayed. Where a frame record is missing a dummy record is shown,
with an 'Add new record' button. As previously mentioned, the tab
control is only used as a visual device and no data is stored on the
tab pages.

This design gives me an instant method for selecting any specific
frame (a useful feature given the rather random manner in which I
expect to input the data). My use of miniature marker labels above
tabs for which no frame data exists is also a simple way of
highlighting the gaps in my data.

Feel free to offer any opinions you like on my design. Be as critical
as you like!Essentially, it's my first attempt for this particular
application and, without doubt, better designs will be possible. One
minor issue is that it does not lend itself to inputting the same
data for several frames all at once, though I'm not yet sure how
often this would be useful.

Now I'm glad I didn't come out too strongly saying, "You're making a big
mistake in your design!" Under the circumstances, I think your design
makes sense, given that you know maximum (small) number of records
you're ever going to have to deal with.

The first thing that occurred to me when you talked about representing a
contact sheet was a form with a grid of 38-40 subform controls (all
copies of the same subform), each with an image control that would
display the thumbnail of the particular photo. There would have to be
code in the form's Current event to show and hide the subforms
appropriately. Clicking on a particular photo would bring up the
details for that photo, either on the main form or via a popup form.
Would a grid like that be feasible?

 

[Guest]

I'm glad that my explanation has resulted in your approval of my current
design. Your alternative may well have a purpose within my database
application but my design has the edge, I think, for quickly scrolling
through the frame data. Also, the vast majority of my frames have not yet
been individually scanned so thumbnails don't exist. So far, all I have is
proofing scans for entire films at one go plus a mere handful of frame
thumbnails.

While I'm here, let me raise a new problem. Given all the description you
have read about my application it will be easier to explain to you than if I
post a new thread.

As previously stated, whenever I click on a tab that represents a frame for
which there is no data I display a dummy record with a command button for
adding a new record (using DoCmd.GoToRecord , , acNewRec). This works fine,
but I have only just noticed that this new record appears to be added to the
end of the subform's recordset. The subform records were originally in frame
number order but now they are out of sequence. I thought that a Me.Requery
would fix that, but this gives me error 2105 "You can't go to the specified
record" from the AddRecordButton subroutine. I don't have any other code yet
to specify the current row after the requery so it goes to the first record.

I'm somewhat confused. Any explanations for this behaviour?

 

[Dirk Goldgar]

While I'm here, let me raise a new problem. Given all the description
you have read about my application it will be easier to explain to
you than if I post a new thread.

As previously stated, whenever I click on a tab that represents a
frame for which there is no data I display a dummy record with a
command button for adding a new record (using DoCmd.GoToRecord , ,
acNewRec). This works fine, but I have only just noticed that this
new record appears to be added to the end of the subform's recordset.
The subform records were originally in frame number order but now
they are out of sequence. I thought that a Me.Requery would fix that,
but this gives me error 2105 "You can't go to the specified record"
from the AddRecordButton subroutine. I don't have any other code yet
to specify the current row after the requery so it goes to the first
record.

I'm somewhat confused. Any explanations for this behaviour?

I'm don't think I understand the whole setup well enough to answer. Do
you have tables like these:

Films
------
FilmID (pk)

Frames
--------
FilmID (compound pk, fk:Films)
FrameNo (compund pk)

Do you have 38 copies of the subform, one per frame on each page of a
tab control on the main form (which is based on Films), or do you have
one copy of the subform with the tab control on that, and a lot of fancy
footwork to make it work? My first inclination would be to do it the
former way, but I don't know what approach you've taken.

Where does the dummy record come from? What's the code behind the
AddNewRecord button?

 

[Guest]

Yes, my tables are set up as you have guessed. I only have one subform. The
tab control is simply a strip to display frame numbers, supported by coding
to format its appearance and set the active tab. No data is stored on tab
control pages.

The 'dummy record' is created by hiding all the subform controls (except the
tab control) and making visible the AddNewRecord button - which has the
standard Wizard coding for this purpose, i.e. "DoCmd.GoToRecord , ,

acNewRec". After pressing this button, a subform Current Event is triggered where (for a new record only) I make the FrameNo the same as the active tab, the FilmID the same as the active film, reset the subform control visibility - and run the Requery that is causing my problem.

I'm guessing that this standard Wizard code (with its "GoToRecord"
component) may be clashing with my Requery. However, I don't really
understand what's going on here in enough detail to make proper sense of it.

 

[Dirk Goldgar]

Yes, my tables are set up as you have guessed. I only have one
subform. The tab control is simply a strip to display frame numbers,
supported by coding to format its appearance and set the active tab.
No data is stored on tab control pages.

So what are you doing, changing the recordsource of the subform in the
tab control's Change event, so that the subform always shows the record
from the Frames table that matches the selected tab?

The 'dummy record' is created by hiding all the subform controls
(except the tab control) and making visible the AddNewRecord button -
which has the standard Wizard coding for this purpose, i.e.
"DoCmd.GoToRecord , ,
acNewRec". After pressing this button, a subform Current Event is
triggered where (for a new record only) I make the FrameNo the same
as the active tab, the FilmID the same as the active film, reset the
subform control visibility - and run the Requery that is causing my
problem.

I'm guessing that this standard Wizard code (with its "GoToRecord"
component) may be clashing with my Requery. However, I don't really
understand what's going on here in enough detail to make proper sense
of it.

I don't think I'm going to be able to dope this out without seeing all
the code from the form and subform. In particular, I'd need to see how
the subform's recordsource is manipulated. One possible alternate
coding strategy for your AddNewRecord button would be to execute an
append query to add a record for the new frame directly to the Frames
table, and then requerying the subform to show that record.

 

[Guest]

So what are you doing, changing the recordsource of the subform in the
tab control's Change event, so that the subform always shows the record
from the Frames table that matches the selected tab?

After clicking on a tab, I do a .FindFirst in the subform's RecordsetClone
to locate the Frame record that matches the tab and then set the subform to
the same bookmark. However, this is not really relevant to my problem
scenario as I'm not clicking on a tab. I'm simply adding a new record to
match the current active tab (i.e frame no) and not (yet) subsequently
attempting to position the subform at any specific row. As previously stated,
the subform automatically opens at the first record after the requery, albeit
with the error message whose meaning I don't quite understand (which record
is it telling me I can't go to?). Without the requery everything is fine
apart from the subform record order.

I don't think I'm going to be able to dope this out without seeing all
the code from the form and subform. In particular, I'd need to see how
the subform's recordsource is manipulated. One possible alternate
coding strategy for your AddNewRecord button would be to execute an
append query to add a record for the new frame directly to the Frames
table, and then requerying the subform to show that record.

That alternative might well bypass my problem. I'll give it a try. Thanks
for the suggestion.

 

[Guest]

Dirk, I've done what you suggested and my problem has now disappeared! The
code to add a new record to the Frames table was already there for the
special case of an empty subform. I simply had to make this apply in all
cases and eliminate the standard Wizard code for adding a new record. I also
added some code to reposition the subform back at the newly added record
after the requery.

Thanks a bundle for all your help!!!!!!!!!!!!!


:

 

[Dirk Goldgar]

Dirk, I've done what you suggested and my problem has now
disappeared! The code to add a new record to the Frames table was
already there for the special case of an empty subform. I simply had
to make this apply in all cases and eliminate the standard Wizard
code for adding a new record. I also added some code to reposition
the subform back at the newly added record after the requery.
Excellent.

Thanks a bundle for all your help!!!!!!!!!!!!!

You're welcome. And good luck with what sounds like a very interesting
application.