ADD method syntax for collections

[david]

If through ignorance of group netiqette I cause offence, I apologise, and
ask you to calmly point out my mistake. I barely understand email, let
alone newsgroups.

I'm hoping to get advice about writing a line of code to Add a member to a
Collection in Access 97, running under XP.

Quotes here enclose the actual names used. The database is split into
"Database.mdb" and "Database data.mdb", both in folder "C\Dart\ActDev".
Last week, any database record was capable of displaying three photographs;
a double click on any photograph would open the file containing it. I
wanted a fourth photo, and carefully went through both databases copying
everything connected with "Photo3", pasted it in what is probably the right
position, then changed any mention of "Photo3" to "Photo4". There are three
small text fields, each with a label, associated with each photograph, e.g.
"Photo3Source"; these were copied/pasted/edited too.

When I run the database, I get "Photo4" highlighted in a line "If
IsNull(Me.Photo4) Then" in Private Sub Form_Current() in "Form_ItemsNew"'s
Class Module, with a message "Method or Data Member not found". In the
Object Browser I found that whilst all the original photo-related fields,
and all the unchanging things I'd created on the form, were listed as
members of "form_ItemsNew", none of the four items relating to "Photo4" on
"Database data.mdb"'s "Items" table were listed. Deleting the links to the
table and re-establishing them did not help. I've checked for typos.

Access Help says that members can be added using Add Method giving the
example "Application.VBE.VBProjects(1).VBComponents.Add(vbext_ct_StdModule)"
But I don't begin to understand Add Method's method. If you think it would
solve the problem described, I'm hoping you will write out the line of code
I'll need to type in (where? The Immediate pane, perhaps, since I assume
this is a once-only job?) to get "Photo4" made a member. If that works, then
I assume that I can simply change "Photo4" for the names of the nonmember
text fields, adding them one by one.

Thank you for your attention and any advice you can give me.

 

[Dirk Goldgar]

If through ignorance of group netiqette I cause offence, I apologise, and
ask you to calmly point out my mistake. I barely understand email, let
alone newsgroups.

Don't worry, most of us don't bite. See the rest of my response at the
bottom of your quoted message.

I'm hoping to get advice about writing a line of code to Add a member to a
Collection in Access 97, running under XP.

Quotes here enclose the actual names used. The database is split into
"Database.mdb" and "Database data.mdb", both in folder "C\Dart\ActDev".
Last week, any database record was capable of displaying three
photographs;
a double click on any photograph would open the file containing it. I
wanted a fourth photo, and carefully went through both databases copying
everything connected with "Photo3", pasted it in what is probably the
right
position, then changed any mention of "Photo3" to "Photo4". There are
three
small text fields, each with a label, associated with each photograph,
e.g.
"Photo3Source"; these were copied/pasted/edited too.

When I run the database, I get "Photo4" highlighted in a line "If
IsNull(Me.Photo4) Then" in Private Sub Form_Current() in "Form_ItemsNew"'s
Class Module, with a message "Method or Data Member not found". In the
Object Browser I found that whilst all the original photo-related fields,
and all the unchanging things I'd created on the form, were listed as
members of "form_ItemsNew", none of the four items relating to "Photo4" on
"Database data.mdb"'s "Items" table were listed. Deleting the links to the
table and re-establishing them did not help. I've checked for typos.

Access Help says that members can be added using Add Method giving the
example
"Application.VBE.VBProjects(1).VBComponents.Add(vbext_ct_StdModule)"
But I don't begin to understand Add Method's method. If you think it
would
solve the problem described, I'm hoping you will write out the line of
code
I'll need to type in (where? The Immediate pane, perhaps, since I assume
this is a once-only job?) to get "Photo4" made a member. If that works,
then
I assume that I can simply change "Photo4" for the names of the nonmember
text fields, adding them one by one.

I don't think you need to worry about the Add method, or anythin to do with
adding anything to a collection yourself. If I've understood your
description, you're getting in deeper than you need to. If you do things
correctly in the table and form design, Access takes care of managing the
relevant collections all by itself. I don't have quite enough information
yet to see what has gone wrong, though.

Let's step back a bit and talk about the tables and fields. What is the
name of the table that contains these Photo fields? What are the names of
the relevant fields i that table, and what data types are they? Is that
table in the back-end, with a link in the front-end to point to it? If you
open that linked table directly, by double-clicking on it in the database
window, does it open and display all the fields as expected?

Next, let's get some information about the form that isn't working right.
What is the name of the form? What is the RecordSource property of the
form? Is it the table itself, or is it a query (whether a stored query or
an in-line SQL statement)? If it's a query, please post the SQL of that
query.

On that form, I guess you have controls that are, or are supposed to be,
bound to the Photo fields. Each of those controls has a name and a
ControlSource property, which may or may not be the same. Please post the
names and corresponding ControlSource of each of those controls. I don't
need this for every control on the form, just those related to the photos.

With the answers to all of these questions, I expect we can solve your
problem.

 

[david]

"On that form, I guess you have controls that are, or are supposed to be,

bound to the Photo fields. Each of those controls has a name and a
ControlSource property, which may or may not be the same. Please post the
names and corresponding ControlSource of each of those controls. I don't
need this for every control on the form, just those related to the photos.

With the answers to all of these questions, I expect we can solve your
problem.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)"

Thank you very much indeed, Dirk Goldgar, for your painstaking reply. You
have solved the problem.

Exploring your questions about RecordSource, I found that my 4
nonfunctioning fields were not included amongst the other 30 in a SELECT
DISTINTROW list. Sometimes Access is beginner-friendly; I was able to add
them in the Query Builder, and start on work which has been delayed for a
year.

You have done this dabbler a great kindness, and I am very grateful. Now I
must try not to embarrass you by posting this in the wrong place.

David