SUBFORMS: Adding a Third Table Isn't Successful

Hello, I'm using Access 2003 and am a novice.

I'm creating a very simple (I hope) relational DVD collection
database, and am encountering a problem when I add a third table and
try to join it to the other tables.

Let me show you what my three tables consist of, before I explain the
problem.

Table 1 (Main):
MovieID (Primary Key)
Title
Director
Running Time
Location
Comments

Table 2 (Actors):
MovieID
ActorID (Primary Key)
Last Name
First Name

Table 3 (Genre):
MovieID
GenreID (Primary Key)
Genre

(Genre is movie type, such as Drama, Comedy, Foreign, etc.)

My goal is to have a repeating panel for the Actors, so I can type in
an unlimited number of Actors' names per record.

In addition, I want to enter an unlimited number of genres to a
record.  (For example, Spaceballs is both a comedy and science
fiction.   Star Wars is science fiction, adventure, and fantasy.)

I want to be able to delete a record.  As I understand things, when
you create a relationship, you use the Enforce Integrity/Delete
Cascade option for this.

I successfully joined the first two tables via MovieID, and
successfully created a form with a subform for Actors.  I've found no
problems yet with that form.  I can add actors' names, and can delete
entire records via the Delete Cascade option.

The problem arises when I try to incorporate the GENRE table.  I've
tried joining it to the MovieID field in Table 1, but I'm clearly not
joining the right things together, because when I use the Form Wizard,
it doesn't let me create a subform.

So, I'm not 100% sure I created Table 3 correctly.  Did I select the
proper primary key?  
Should I be doing the joining differently?


If you need further information from me, please let me know.  I'm not
sure if I provided enough details to analyze the problem.

Also, please keep in mind that I am somewhat of a novice.   Thank you
for your assistance.

0
T
3/22/2008 12:46:34 AM
access.forms 6864 articles. 2 followers. Follow

10 Replies
657 Views

Similar Articles

[PageSpeed] 32

You design is a bit off.

1. Genre is an attribute of the movie, not the other way around. So, remove 
the MovieID from Genre, and add the GenreID to the main Movie table.

2. There is a many to many relationship between movies and actors.  (1 movie 
can have many actors and 1 actor can play in many movies). So, you need to 
add another table, call it tblMoviesActors which has 2 fields, with a 
compound primary key.

tblMoviesActors
MovieID - PK
ActorID - PK

The GenreID is displayed as a combo box in the main form, and the subform 
has 2 controls, a hidden textbox for MovieID (the link field) and a combo 
box for ActorID.

If you need a sample, look at the relationship of Orders and Products in the 
Northwind sample database that came with Access.
-- 
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"T. Hulot" <tatihulot@gmail.com> wrote in message 
news:rhl8u3pksfb7g3fhmk8qu1imalj8eul7lr@4ax.com...
> Hello, I'm using Access 2003 and am a novice.
>
> I'm creating a very simple (I hope) relational DVD collection
> database, and am encountering a problem when I add a third table and
> try to join it to the other tables.
>
> Let me show you what my three tables consist of, before I explain the
> problem.
>
> Table 1 (Main):
> MovieID (Primary Key)
> Title
> Director
> Running Time
> Location
> Comments
>
> Table 2 (Actors):
> MovieID
> ActorID (Primary Key)
> Last Name
> First Name
>
> Table 3 (Genre):
> MovieID
> GenreID (Primary Key)
> Genre
>
> (Genre is movie type, such as Drama, Comedy, Foreign, etc.)
>
> My goal is to have a repeating panel for the Actors, so I can type in
> an unlimited number of Actors' names per record.
>
> In addition, I want to enter an unlimited number of genres to a
> record.  (For example, Spaceballs is both a comedy and science
> fiction.   Star Wars is science fiction, adventure, and fantasy.)
>
> I want to be able to delete a record.  As I understand things, when
> you create a relationship, you use the Enforce Integrity/Delete
> Cascade option for this.
>
> I successfully joined the first two tables via MovieID, and
> successfully created a form with a subform for Actors.  I've found no
> problems yet with that form.  I can add actors' names, and can delete
> entire records via the Delete Cascade option.
>
> The problem arises when I try to incorporate the GENRE table.  I've
> tried joining it to the MovieID field in Table 1, but I'm clearly not
> joining the right things together, because when I use the Form Wizard,
> it doesn't let me create a subform.
>
> So, I'm not 100% sure I created Table 3 correctly.  Did I select the
> proper primary key?
> Should I be doing the joining differently?
>
>
> If you need further information from me, please let me know.  I'm not
> sure if I provided enough details to analyze the problem.
>
> Also, please keep in mind that I am somewhat of a novice.   Thank you
> for your assistance.
> 


0
Arvin
3/22/2008 1:48:24 AM
Hi Arvin (and Original Poster),

The OP wants a 1-many relationship between movie and genre, so suggestion 1 
doesn't quite cut it here.  He really needs something like:

tblGenres
GenreID - PK
Genre - text entry for each possible genres

and

tblMovieGenres
GenreID - FK for tblGenres
MovieID - FK for tblMovies

If he wants to prevent duplicates, he could set the combination of both 
fields as the PK for this table.

Rob


"Arvin Meyer [MVP]" <a@m.com> wrote in message 
news:eapsV77iIHA.5088@TK2MSFTNGP02.phx.gbl...
> You design is a bit off.
>
> 1. Genre is an attribute of the movie, not the other way around. So, 
> remove the MovieID from Genre, and add the GenreID to the main Movie 
> table.
>
> 2. There is a many to many relationship between movies and actors.  (1 
> movie can have many actors and 1 actor can play in many movies). So, you 
> need to add another table, call it tblMoviesActors which has 2 fields, 
> with a compound primary key.
>
> tblMoviesActors
> MovieID - PK
> ActorID - PK
>
> The GenreID is displayed as a combo box in the main form, and the subform 
> has 2 controls, a hidden textbox for MovieID (the link field) and a combo 
> box for ActorID.
>
> If you need a sample, look at the relationship of Orders and Products in 
> the Northwind sample database that came with Access.
> -- 
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
>
> "T. Hulot" <tatihulot@gmail.com> wrote in message 
> news:rhl8u3pksfb7g3fhmk8qu1imalj8eul7lr@4ax.com...
>> Hello, I'm using Access 2003 and am a novice.
>>
>> I'm creating a very simple (I hope) relational DVD collection
>> database, and am encountering a problem when I add a third table and
>> try to join it to the other tables.
>>
>> Let me show you what my three tables consist of, before I explain the
>> problem.
>>
>> Table 1 (Main):
>> MovieID (Primary Key)
>> Title
>> Director
>> Running Time
>> Location
>> Comments
>>
>> Table 2 (Actors):
>> MovieID
>> ActorID (Primary Key)
>> Last Name
>> First Name
>>
>> Table 3 (Genre):
>> MovieID
>> GenreID (Primary Key)
>> Genre
>>
>> (Genre is movie type, such as Drama, Comedy, Foreign, etc.)
>>
>> My goal is to have a repeating panel for the Actors, so I can type in
>> an unlimited number of Actors' names per record.
>>
>> In addition, I want to enter an unlimited number of genres to a
>> record.  (For example, Spaceballs is both a comedy and science
>> fiction.   Star Wars is science fiction, adventure, and fantasy.)
>>
>> I want to be able to delete a record.  As I understand things, when
>> you create a relationship, you use the Enforce Integrity/Delete
>> Cascade option for this.
>>
>> I successfully joined the first two tables via MovieID, and
>> successfully created a form with a subform for Actors.  I've found no
>> problems yet with that form.  I can add actors' names, and can delete
>> entire records via the Delete Cascade option.
>>
>> The problem arises when I try to incorporate the GENRE table.  I've
>> tried joining it to the MovieID field in Table 1, but I'm clearly not
>> joining the right things together, because when I use the Form Wizard,
>> it doesn't let me create a subform.
>>
>> So, I'm not 100% sure I created Table 3 correctly.  Did I select the
>> proper primary key?
>> Should I be doing the joining differently?
>>
>>
>> If you need further information from me, please let me know.  I'm not
>> sure if I provided enough details to analyze the problem.
>>
>> Also, please keep in mind that I am somewhat of a novice.   Thank you
>> for your assistance.
>>
>
> 

0
Rob
3/22/2008 7:30:02 AM
The design should look like this if you only want to assign 1 genre to each
movie:
Table 1 (Main):
MovieID (Primary Key)
GenreID (in the relationships window, link this from GenreID in the
Genre Table)
Title
DirectorID (linked to DirectorID in the Directors table - *if you are only
going to assign 1 director per movie*)
Running Time
Location
MovieComments

Table2 - Genre
GenreID (autonumber, primary key)
Genre
GenreComments (don't use the same name, Comments, in this table as in the
other one - it will cause confusion eventually

Table3-Actors
ActorID (autonumber, primary key)
ActFName
ActSurName
other actor details
ActorComments

Table4 - Directors
DirectorID (Autonumber, Primary Key)
DirFName
DirSurname
other director details
DirectorComments

Table5 - MovieActor table
MovActID (autonumber, PK)
MovieID (linked from the Movies table)
ActID (linked from the Actor table
Role (the role that actor played in that movie)

Lets say you want to be able to assign several directors to a movie (some
films have more than one, don't they?)
Then you would take DirectorID out of the Movies table and have

Table6 MovieDirector
MovDirID (PK)
MovieID (linked from the movies table)
DirectorID (linked from the Directors table)
Any other details which apply only to that director in that movie


Once you have your table design right, then we can start working on that
form :)
Evi








"T. Hulot" <tatihulot@gmail.com> wrote in message
news:rhl8u3pksfb7g3fhmk8qu1imalj8eul7lr@4ax.com...
> Hello, I'm using Access 2003 and am a novice.
>
> I'm creating a very simple (I hope) relational DVD collection
> database, and am encountering a problem when I add a third table and
> try to join it to the other tables.
>
> Let me show you what my three tables consist of, before I explain the
> problem.
>
> Table 1 (Main):
> MovieID (Primary Key)
> Title
> Director
> Running Time
> Location
> Comments
>
> Table 2 (Actors):
> MovieID
> ActorID (Primary Key)
> Last Name
> First Name
>
> Table 3 (Genre):
> MovieID
> GenreID (Primary Key)
> Genre
>
> (Genre is movie type, such as Drama, Comedy, Foreign, etc.)
>
> My goal is to have a repeating panel for the Actors, so I can type in
> an unlimited number of Actors' names per record.
>
> In addition, I want to enter an unlimited number of genres to a
> record.  (For example, Spaceballs is both a comedy and science
> fiction.   Star Wars is science fiction, adventure, and fantasy.)
>
> I want to be able to delete a record.  As I understand things, when
> you create a relationship, you use the Enforce Integrity/Delete
> Cascade option for this.
>
> I successfully joined the first two tables via MovieID, and
> successfully created a form with a subform for Actors.  I've found no
> problems yet with that form.  I can add actors' names, and can delete
> entire records via the Delete Cascade option.
>
> The problem arises when I try to incorporate the GENRE table.  I've
> tried joining it to the MovieID field in Table 1, but I'm clearly not
> joining the right things together, because when I use the Form Wizard,
> it doesn't let me create a subform.
>
> So, I'm not 100% sure I created Table 3 correctly.  Did I select the
> proper primary key?
> Should I be doing the joining differently?
>
>
> If you need further information from me, please let me know.  I'm not
> sure if I provided enough details to analyze the problem.
>
> Also, please keep in mind that I am somewhat of a novice.   Thank you
> for your assistance.
>


0
Evi
3/22/2008 9:01:21 AM
I don't think this is what I want, if I understand you correctly.
If I understand you correctly, then what you are doing limits the
number of genres and actors.  Unless I'm totally misunderstanding you.

By adding a third table with a compound primary key (which I learned
about only this past week), and by creating a combo box, the user
cannot enter his own genres.  The author of the database puts in a
predetermined set of genres and actors.  The combo box would let the
user choose from a list.  

Am I understanding you correctly or incorrectly?

I want the user to enter any genre he wants, and any actor he wants.

I've been told this causes "consistent data entry" problems, and there
is no possibility for "normalization."   I've figured out what all of
this means, but I can live without consistent data entry.  It might
not be sound from a database engingeering point of view, but it's what
I want to do.

By the way...about ten minutes after posting my message, I figured out
what I wanted to do.  I haven't tested the search/query aspect yet, so
what I did may turn out wrong, but as of right now, after adding a
second subform via Table Control, it seems to be operating properly.



>You design is a bit off.
>
>1. Genre is an attribute of the movie, not the other way around. So, remove 
>the MovieID from Genre, and add the GenreID to the main Movie table.
>
>2. There is a many to many relationship between movies and actors.  (1 movie 
>can have many actors and 1 actor can play in many movies). So, you need to 
>add another table, call it tblMoviesActors which has 2 fields, with a 
>compound primary key.
>
>tblMoviesActors
>MovieID - PK
>ActorID - PK
>
>The GenreID is displayed as a combo box in the main form, and the subform 
>has 2 controls, a hidden textbox for MovieID (the link field) and a combo 
>box for ActorID.
>
>If you need a sample, look at the relationship of Orders and Products in the 
>Northwind sample database that came with Access.

0
T
3/22/2008 11:36:30 AM
You are mistaken if you think that the number of genres or actors will be
limited using a combo box. You are thinking about the sort of combo that is
limited to stuff you type into it when you create it.
The sort of combo Arvin is referring to, is usually based on a table or
query. So you would have a form based on your Genres table and you can enter
as many Genres as you like, any time you like.
When you add a combo to your Movies table, using a wizard, it will give you
the option to base it on an existing table in your database. You would use
that option and base the combo on your Genres table.
If you want to add a new Genre you can open up your Genres form and enter a
new one to your Genres table where it will become available to your combo.
You can equally (*but only once your database design is correct*) add a
subform so that you don't have to switch to a new form. It depends how much
space you have on your screen - you can even drag your Genres form into your
existing form in Design View and link it by *GenreID* (details available if
this doesn't make sense).

But please don't waste your time creating forms and subforms until your
database design is right. Even if it looks right, you will see what we are
talking about very soon.
It does take some getting your head around to understand relational database
design and many of the books which you read can leave you feeling more
puzzed than before but your sort of project is the ideal way to learn. If
anyone here using 'jargon' which you don't understand, please ask and ask
again. Most of the people here don't want to puzzle or confuse anyone.
Evi



"T. Hulot" <tatihulot@gmail.com> wrote in message
news:cfr9u3tnkhr1dkmqbu01kj8njogb7estat@4ax.com...
> I don't think this is what I want, if I understand you correctly.
> If I understand you correctly, then what you are doing limits the
> number of genres and actors.  Unless I'm totally misunderstanding you.
>
> By adding a third table with a compound primary key (which I learned
> about only this past week), and by creating a combo box, the user
> cannot enter his own genres.  The author of the database puts in a
> predetermined set of genres and actors.  The combo box would let the
> user choose from a list.
>
> Am I understanding you correctly or incorrectly?
>
> I want the user to enter any genre he wants, and any actor he wants.
>
> I've been told this causes "consistent data entry" problems, and there
> is no possibility for "normalization."   I've figured out what all of
> this means, but I can live without consistent data entry.  It might
> not be sound from a database engingeering point of view, but it's what
> I want to do.
>
> By the way...about ten minutes after posting my message, I figured out
> what I wanted to do.  I haven't tested the search/query aspect yet, so
> what I did may turn out wrong, but as of right now, after adding a
> second subform via Table Control, it seems to be operating properly.
>
>
>
> >You design is a bit off.
> >
> >1. Genre is an attribute of the movie, not the other way around. So,
remove
> >the MovieID from Genre, and add the GenreID to the main Movie table.
> >
> >2. There is a many to many relationship between movies and actors.  (1
movie
> >can have many actors and 1 actor can play in many movies). So, you need
to
> >add another table, call it tblMoviesActors which has 2 fields, with a
> >compound primary key.
> >
> >tblMoviesActors
> >MovieID - PK
> >ActorID - PK
> >
> >The GenreID is displayed as a combo box in the main form, and the subform
> >has 2 controls, a hidden textbox for MovieID (the link field) and a combo
> >box for ActorID.
> >
> >If you need a sample, look at the relationship of Orders and Products in
the
> >Northwind sample database that came with Access.
>


0
Evi
3/22/2008 12:38:19 PM
I would also move the director to a seperate table setup like actors.  No 
reason to have to add same director many times.

"Evi" <evwool@TakeThisOutntlworld.com> wrote in message 
news:%47Fj.10653$Ge4.3508@newsfe1-win.ntli.net...
> You are mistaken if you think that the number of genres or actors will be
> limited using a combo box. You are thinking about the sort of combo that 
> is
> limited to stuff you type into it when you create it.
> The sort of combo Arvin is referring to, is usually based on a table or
> query. So you would have a form based on your Genres table and you can 
> enter
> as many Genres as you like, any time you like.
> When you add a combo to your Movies table, using a wizard, it will give 
> you
> the option to base it on an existing table in your database. You would use
> that option and base the combo on your Genres table.
> If you want to add a new Genre you can open up your Genres form and enter 
> a
> new one to your Genres table where it will become available to your combo.
> You can equally (*but only once your database design is correct*) add a
> subform so that you don't have to switch to a new form. It depends how 
> much
> space you have on your screen - you can even drag your Genres form into 
> your
> existing form in Design View and link it by *GenreID* (details available 
> if
> this doesn't make sense).
>
> But please don't waste your time creating forms and subforms until your
> database design is right. Even if it looks right, you will see what we are
> talking about very soon.
> It does take some getting your head around to understand relational 
> database
> design and many of the books which you read can leave you feeling more
> puzzed than before but your sort of project is the ideal way to learn. If
> anyone here using 'jargon' which you don't understand, please ask and ask
> again. Most of the people here don't want to puzzle or confuse anyone.
> Evi
>
>
>
> "T. Hulot" <tatihulot@gmail.com> wrote in message
> news:cfr9u3tnkhr1dkmqbu01kj8njogb7estat@4ax.com...
>> I don't think this is what I want, if I understand you correctly.
>> If I understand you correctly, then what you are doing limits the
>> number of genres and actors.  Unless I'm totally misunderstanding you.
>>
>> By adding a third table with a compound primary key (which I learned
>> about only this past week), and by creating a combo box, the user
>> cannot enter his own genres.  The author of the database puts in a
>> predetermined set of genres and actors.  The combo box would let the
>> user choose from a list.
>>
>> Am I understanding you correctly or incorrectly?
>>
>> I want the user to enter any genre he wants, and any actor he wants.
>>
>> I've been told this causes "consistent data entry" problems, and there
>> is no possibility for "normalization."   I've figured out what all of
>> this means, but I can live without consistent data entry.  It might
>> not be sound from a database engingeering point of view, but it's what
>> I want to do.
>>
>> By the way...about ten minutes after posting my message, I figured out
>> what I wanted to do.  I haven't tested the search/query aspect yet, so
>> what I did may turn out wrong, but as of right now, after adding a
>> second subform via Table Control, it seems to be operating properly.
>>
>>
>>
>> >You design is a bit off.
>> >
>> >1. Genre is an attribute of the movie, not the other way around. So,
> remove
>> >the MovieID from Genre, and add the GenreID to the main Movie table.
>> >
>> >2. There is a many to many relationship between movies and actors.  (1
> movie
>> >can have many actors and 1 actor can play in many movies). So, you need
> to
>> >add another table, call it tblMoviesActors which has 2 fields, with a
>> >compound primary key.
>> >
>> >tblMoviesActors
>> >MovieID - PK
>> >ActorID - PK
>> >
>> >The GenreID is displayed as a combo box in the main form, and the 
>> >subform
>> >has 2 controls, a hidden textbox for MovieID (the link field) and a 
>> >combo
>> >box for ActorID.
>> >
>> >If you need a sample, look at the relationship of Orders and Products in
> the
>> >Northwind sample database that came with Access.
>>
>
> 


0
Pete
3/22/2008 2:13:54 PM
On Sat, 22 Mar 2008 10:13:54 -0400, "Pete D." <pduffy211AT@coxDOT.net> wrote:

>I would also move the director to a seperate table setup like actors.  No 
>reason to have to add same director many times.

An alternative - perhaps a better one - would be to make the Actors table more
general: rather than just Actors, have a table which would list all the people
involved, with an additional field indicating the nature of their involvement
(actor, producer, director, assistant key grip, whatever).
-- 

             John W. Vinson [MVP]
0
John
3/22/2008 6:53:57 PM
This is so true.  I promise to read this thread carefully after Easter
weekend, and I'll take the time to try to absorb it.  I feel like I'm
hacking away at this like a lumberjack with a tree stump.  But at
least I am understanding things little by little.



>It does take some getting your head around to understand relational database
>design and many of the books which you read can leave you feeling more
>puzzed than before but your sort of project is the ideal way to learn. If
>anyone here using 'jargon' which you don't understand, please ask and ask
>again. Most of the people here don't want to puzzle or confuse anyone.

0
T
3/22/2008 8:20:29 PM
This is an interesting idea that I never thought of.

Sort of like a Cast and Crew table.  I could even include
"screenwriter" and "music composer" or "cinematographer."

I like this idea...and it might make the database easier. 


>
>An alternative - perhaps a better one - would be to make the Actors table more
>general: rather than just Actors, have a table which would list all the people
>involved, with an additional field indicating the nature of their involvement
>(actor, producer, director, assistant key grip, whatever).

0
T
3/22/2008 8:22:10 PM
On Sat, 22 Mar 2008 16:20:29 -0400, T. Hulot <tatihulot@gmail.com> wrote:

>This is so true.  I promise to read this thread carefully after Easter
>weekend, and I'll take the time to try to absorb it.  I feel like I'm
>hacking away at this like a lumberjack with a tree stump.  But at
>least I am understanding things little by little.

There are lots of good resources to help get you up the steep and rocky
learning slope... try some of these, and the references therein:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials
-- 

             John W. Vinson [MVP]
0
John
3/23/2008 5:37:02 AM
Reply:

Similar Artilces:

Update one table from another
I am trying to update one table that has one record for each employee(table 1) with available vacation time. The other table records every time off request(table 2) and how much time they want off. I have the update query and it works fine. The problem is that everytime it is ran every requested time off amount(from table2) is subtracted from the available time(table1) again and again. I want the records for requested time(table2) to update the employee available time off(table1) only once, but keep the records on the table as that is the basis for a report. Thank You, Brett ----=...

what is the function and name is of the symbol in each table cell.
Under Paragraph I clicked the Show/Hide Symbol icon so I can now see a symbol at the end of each text within a table cell. I wondered what that is so I tried to use Help to find out. I did find help that mapped a word (like paragraph) into a symbol. But I can't find anywhere where if I know the symbol it will tell me the meaning. Can you tell me how to find such info? Or maybe you can tell me what the function and name is of the symbol in each table cell. Thanks I'm sorry, I meant to sent this to the Word group. Of course, I wouldn't mind getting the info...

Creating a table
There's probably an easier way to do it but... I have a series of numbers in column A (150 in all) and a series of letters in column B (22 in all). In total there are 4800 rows. What I am trying to do is create a table with the number down the left hand side and the letters across the top. In each cell within the table I need to count how many times the combination of number/letter appears. Eg: A B C 201 0 2 2 202 3 0 1 203 3 6 4 I tried combining the number/letter into a single text field using the CONCATENATE function then filtering ...

Adding Contacts Folder as Address Book in Exchange Server
I am trying to add my contacts folder to my address book in Outlook XP and don't see the Tools, Options I used to see in Outlook 2000. How can I add this folder to my address book? Is that an option you can turn on or off for a profile in Exchange Server? Thanks!! -- Remove 'spam' from email address to contact me directly Right click on your Contacts folder, go to properties, outlook address book. Can you tick the box there? If not, go to tools, e-mail accounts, view/change address books, and make sure you have the Outlook Address Book option there. Then try right-clicking...

Finding all queries which use a table
Hi, Does anyone know of a tool that can scan all queries in a database and find if a certain table is used? I have a table called tblCustomerRollup which is old and outdated. I want to see which of the 500 queries in my database use this table without opeing every single one of them? Thanks, -- Chuck W Chuck Sounds like a variation on Search/Replace. Try searching online for "Database Documenter" as a starting point. A couple of the commercial tools I've used include FMS, Inc.'s Total Access Analyzer and Black Moshannon's Speed Ferret. There are a lot of fr...

Keeping a table in a form editable and checking that fields are filled in before allowing a save
Firstly apologies if this is the incorrect forum but I was looking for a general word forum and could not find one. Please point me to one if one exists. I am trying to create a form where I want to specify what items need to be filled in. (Review minutes from design reviews). I want to make certain fields mandatory like the date, attendees and check list used and want to block saving of the document with a warning until they are filled in. Is there a way of doing this? Also as a part of the review actions are filled in to a table. depending on how many actions there are the table...

How to write a new entry in a combo box to its underlying table
Dear Access 2007 VBA Gurus, I have a assets database (rather uncreatively named "Assets"). I use a form (named "Asset Acquisition Input Form New") to enter new assets. The "Manufacturer" field (combo box name "Manufacturers_ID) on this form is a lookup to a Manufacturers table. What I want to happen is when I enter an item that is not in the lookup list, I want a message box to prompt me to add the new entry to the underlying table, or to cancel and select an item from the list. I have no trouble with the MsgBox command itself. What I don&...

Adding an "I'm out of the Office" Message
Re Outlook Express. I can't find anywhere on the index about how to send one of those "I will be out of the office from July blah blah to August blah blah." Anyone help, please? thanks-- Richard Hi - This is a group to support Outlook from the Office group of programs. Outlook Express is a part of Internet Explorer and is a quite different program, despite its similar name.. You will probably get a faster and more expert answer if you post this to an Outlook Express news group. Try posting in one of these newsgroups: microsoft.public.windows.inetexplorer.ie5.outlookexp...

Right click in Pivot Table or on Entire Column
I have added items to the right-click menu that popups up when you have a cell or cells selected. But when you are in a Pivot Table or have an entire column selected the right-click popup is different. Is there a way to add an item to the right-click popup menu when you are in a Pivot Table or have an entire column selected? Thank you for your help. Steven Never mind. This one was right in the help section. I should have looked first. Thank you, Steven "Steven" wrote: > I have added items to the right-click menu that popups up when you have a ...

FP: Couldnt close table
We have been receiving this error on two separate PCs after a recent upgrade to 7.5. Our version is 7.50g43 (service pack 5). At first we thought it was isolated to one PC now a second PC is having the same error. On the first PC, I ran new network cable, installed a different network card with no help. This PC is Win98, 64MB RAM, 600Mhz. The second PC has 128MB RAM also Win98. I don't believe switching to XP is an option right now. This message has appeared in the payables module on both machines. But it has also appeared in receivables as well. Not in GL or Payroll. I...

Subform question 04-09-10
I have a form (Form1) that contains a subform (Subform1). Within this subform I have a combo box which, depending on what is chosen, pops up another form (Popup1)for additional information. I need this additional information in the form that pops up to be 'linked' with the subform. The problem I am running into is that when the user enters information in Popup1, the table has not been populated witht he data that is in the subform so there is no record to 'link' to. What is the best way to force te esubform to pass its information to the table? Thanks i...

Same Table cannot be the child table in two nexted relations...
I've been setting up a schema with the XML Designer in VS .NET 2003. The designer lets me set up a lot of things including a complex type that contains an unnamed complex type called modified. When I try to preview the dataset, I get an error described as "The same table (modified) cannot be the child table in two nested relations. I've run into this before with a different complex type, and I've changed the name of the instance of the type in the various elements it's used, and the problem goes away. In this case, problem is an unnamed complex type, so it only a...

Pivot tables and Macros
I was looking to be able to manipulate (ie change selections from the drop downs, not change the fields in the table or anything)and print from a pivot table using a macro. So how do I go about doing that? When I set up a macro by recording the actions that I want to do, I always get an error message when I try to run it as a macro. What specific steps are you recording, and what error message do you get? Can you click the Debug button, and see the line of code that is causing the problem? Dust For Eyes wrote: > I was looking to be able to manipulate (ie change selections from the >...

How to copy aQuery to a new Table?
I have a database in a Table, a report based on that same Table and a Query based on that Report. After two months or so I like, after some new data input, to save the Table into a new Object Table. What is the best way for the Report and Query to follow the new Table whitout recreating the original Report & Query? Thankyou for your comments. I use MS Office Access 2007. Joe T >>I have a database in a Table, a report based on that same Table and a Query based on that Report. Your phrasing is wrong when it comes to the elements of an Access database. A dat...

Adding a button with a function on protected sheets.
How do i add a button to 'reset/clear' the data on a worksheet that is protected and uses VLookup data (from another worksheet). Everyday this table will have data chosen from combo boxes or manually entered in allowable editable fields and at the end of the day after the files is saved, I need to clear that data for use on the next day. How is this Reset button applied with allowable edit ranges, VLookup data and a protected worksheet? Thanks There's several ways to do this. 1. Instead of straight vlookups, use =IF(ISBLANK(VLOOKUP(....)),"",VLOOKUP(....)) wher...

Pivot Table in Excel
hi, I have a problem using the pivot Table in excel 2000. Earlier the location of the pivot table in excel was pointing to say c:\sales.mdb. Now the location has changed to D:\Sales.mdb. someone please tell me the place to change in the excel to reflect the same. So that upon refreshing the document i can see the latest data. Currently i am getting a error message when i try to refresh, but its not prompting to change to alternative location. If i edit the excel file in a notepad...i can see the location pointing to c:\sales.mdb. Thanks in advance for any help regarding this..expect...

obtaining data in text form from a table
Hi all, I like to be able to obtain the dates in a text format from the table below. ie Test2 8-Feb Test5 4-Feb,8-Feb Test6 4-Feb,5-Feb, 9-Feb Do I need to do this by macros and if so, any help would be appreciated. Table Care Recipient Surname 4-Feb 5-Feb 8-Feb 9-Feb Test1 Test2 8-Feb Test3 Test4 Test5 4-Feb 8-Feb Test6 4-Feb 5-Feb 9-Feb Vlookup should do what you want, as in: =vlookup(a2,Table,2,false) Adjust the ranges t...

pvt table field settings!
hi! i am receiving data every day from 10 different places as under.! for example: DATE 11.01.2009 PLACE "A" TOTAL NUMBER OF RECEIPTS 10 TOTAL OF RECEIPTS AMOUNT 1000 DATE 11.01.2009 PLACE "B" TOTAL NUMBER OF RECEIPTS 50 TOTAL OF RECEIPTS AMOUNT 1500 DATE 11.02.2009 PLACE "A" TOTAL NUMBER OF RECEIPTS 15 TOTAL OF RECEIPTS AMOUNT 1000 DATE 11.03.2009 PLACE "C" TOTAL NUMBER OF RECEIPTS 10 TOTAL OF RECEIPTS AMOUNT 1000 -likewise i'm receiving daily data from all the 10 places..! what i want is a pivot table report on a...

Adding GAL users into a custom form
Hi, I have created a custom form, and am required to add a series of approvers. What I am trying to achieve is: * Add users from my GAL into a text field, so that when the form is sent, they do not get initially CC'ed the form. Is this achievable, and if so, how do I do it (if you could help by including any appropriate code, that would be great). Regards, Rick ...

Non AD emails going to 1 user
I have an Exchange 2003 server running on SBS 2003 the issues is one user is getting all the emails sent to him that look like they are coming from his domain. For example his email is user@mydomain.com but in his inbox he is getting XYZ@mydomain.com but XYZ is not in the AD or has a email box set up on this server. Why is getting this non AD email and how can I stop it. Thanks in advance Are you sure it's not a SPAM where the spammer may have simply put in 123abc@mydomain.com and BCC it to all possible conceivable names @mydomain.com?? R Green "LaOVis" <LaOVis@discuss...

HTML Table import
Hi, I have a HTML document which contains a large data of data. When this html file is imported into excel some of the columns get out of whack. eg. Some columns are removed completely. This html renders ok in IE but get corrupted or misread when imported into excel, mainly when the table gets big. eg. It generally happens after the 10000 row mark. Any suggestions? Do you import it through data>import external data>new web query? -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "Ryan Hafey" <Ryan Hafey@discussion...

copying column width in tables
Hi, I created a table in Publisher with 6 columns. I would columns #1, 2, 4 and 5 to have the same width and columns 3 and 6 to have a different width. I'd like to set the width of column 1 and then paste that size to the other four so they all have exactly the same width. Presently, I am eyeballing it, but it takes too much time and is not exact. Any ideas on how to do this? The only way is to create guides. Arrange, ruler guides. You could try creating a table in Word, copy/paste. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsof...

subform not refreshing
Hi there, I have a main form with a subform embedded to be able to scroll through a list of records. There is no linking field between the two. The subform displays all the records properly. One of the fields in the subform is a checkbox to 'close' the record indicating that the info is no longer current and shouldn't be displayed. This works fine. I should also mention that the subform is based on a query. I have a button on the main form which opens another modal form based on the same query as the subform that will let me uncheck the checkbox in case the record was 'clo...

look up tables and IF statements
=VLOOKUP(G2,$C$30:$F$369,2) I have managed to create a look up table using the office assistant, and the formula above dumps information into cell G3. If G2 is empty or has a zero I don't want G3 to have anything in it. At the moment it just keeps coming up with #N/A Can anyone tell me what formula I should be using please Hi Rick! Would this modification do? =IF(OR(G2<>0,G2<>""),VLOOKUP(G2,$C$30:$F$369,2),"") Hope this helps! -- Thanks and kind regards "Rick" wrote: > =VLOOKUP(G2,$C$30:$F$369,2) > I have managed to create a lo...

Pivot Table from two worksheets
I am trying to create a pivot table from two worksheets. Each workshee represents different accounts from which I am tracking expenses. I hav these columns on both sheets: Type of Expense (labeled A, B, C, D) Date, Description, and Amount. I want the pivot table to organize all of the data from both sheet according to Type of Expense and show the Date, Description, and Amoun under each Type of Expense. I can get it to organize it by Type o Expense and show a total for each, but how do I get it to show thi additional information? Thanks -- Message posted from http://www.ExcelForum.com ...