Frustrated Please Help!!!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi I trying to set up a command button to create a new record while
populating three of the fileds from one of my tables. I do have them link in
a query. I am new at this but nothing right now seems to be working.
thanks,
keri
 
Hi I trying to set up a command button to create a new record while
populating three of the fileds from one of my tables. I do have them link in
a query. I am new at this but nothing right now seems to be working.
thanks,
keri
Click to expand...

Keri, I think it would help a lot if you could describe your application in a
bit more detail.

- What are you trying to accomplish?
- What Tables do you have? What is each table's Primary Key; what other
important (i.e. linking) fields are there; how are the tables related?
- What's the Recordsource of the form (and subforms???) that you're using?
- What's the code behind the command button?
- What happens (or doesn't) when you click the button?
- What do you WANT to happen?

Bear in mind - you're close to the database, you know what you want, you know
the structure. We don't!

John W. Vinson [MVP]
 
The tables primary key is what access assign to it.
One table has fixed information Project Name, Project Number, Project Manager.
The second Table has Mth/Yr Sent, Date Recevied, Income Earned,Retained,
Owed, Paid, Check Number.
I have them link through a query using the Primiary ID for the linking source.
I have one form no subforms. I'm not sure how to get those to work.
I have made a command button to copy to a new record. It copies everything
to a new record only if I'm on a particular record.
What I want to happen is for the fixed information from my first table to
automaly repeat so I may update the information for the second table. not
have the whole thing copied.
Does this make any sense. I'm sorry if it doesn't I'm new to access I have
only used the toutorials online. my work gives us the software but now IT
Support for questions like this.

Thanks,
Keri
 
The tables primary key is what access assign to it.
One table has fixed information Project Name, Project Number, Project Manager.
The second Table has Mth/Yr Sent, Date Recevied, Income Earned,Retained,
Owed, Paid, Check Number
Click to expand...

So each project will have multiple records in the second table?
I have them link through a query using the Primiary ID for the linking source.
Click to expand...

If you're linking PrimaryID to PrimaryID it *WON'T WORK*. That's not how
linking works! You need a Foreign Key - a Long Integer number field, if the
project table's primary key is an Autonumber - and use THAT for the linking.
Better might be to make Project Number the primary key of the project table -
if it's unique (only one record has that number), stable (not something you're
changing all the time), and short (a number or a small text field) it would be
a good primary key. If you do so, then you should add a matching Project
Number field in the second table (to indicate which project the check is for).
I have one form no subforms. I'm not sure how to get those to work.
Click to expand...

Base the main Form on the Project table, and the subform on the Checks table.
If there is a relationship defined from Projects to Checks (on ProjectNumber,
or ProjectID) Access will automatically use these fields as the master/child
link field.
I have made a command button to copy to a new record. It copies everything
to a new record only if I'm on a particular record.
Click to expand...

It should NEVER - under *ANY* circumstances - be necessary to copy the same
data to a second record.
What I want to happen is for the fixed information from my first table to
automaly repeat so I may update the information for the second table. not
have the whole thing copied.
Click to expand...

You don't need to - and shouldn't!!! - make a second copy of the project
information. Store it *once*, and then link many Checks records to it. That's
what the subform does for you. You can find a project on the mainform, and
then enter as many checks as you like in the subform. You don't need *ANY*
code or queries to do this.
Does this make any sense. I'm sorry if it doesn't I'm new to access I have
only used the toutorials online. my work gives us the software but now IT
Support for questions like this.
Click to expand...

Check out some (perhaps better) tutorials, particularly the Database Design
101 link on Jeff's page, and Cheryl's tutorial:

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]
 
Hi John,
Thank you very much for your help. I have a form with a subform it works so
much better. I'm not sure why I made it more complcate then it really was.
I had just one last question. As I am adding new records at the end of a
particular table how do I add information in addmatily in the form. For
example Project Name, Project Number, and PM. Should I make them a bound
form or not?? Do you understand what I mean?

thanks,
Keri
 
Hi John,
Thank you very much for your help. I have a form with a subform it works so
much better. I'm not sure why I made it more complcate then it really was.
I had just one last question. As I am adding new records at the end of a
particular table how do I add information in addmatily in the form. For
example Project Name, Project Number, and PM. Should I make them a bound
form or not?? Do you understand what I mean?
Click to expand...

I'm not sure, but I'm *guessing* that you want to store the project name,
project number, and PM redundantly in your second table. If so... *don't*.
Relational databases use the "grandmother's pantry principle": "A place - ONE
place! - for everything, everything in its place". The Project Name should be
stored in the Project table, and *only* in the project table.

If I'm misunderstanding, please explain what you're adding to where.

John W. Vinson [MVP]
 
Hi John

Does this mean only one table instead of spliting the infomration into two
tables?
 
Hi John

Does this mean only one table instead of spliting the infomration into two
tables?
Click to expand...


No.

Here's my understanding of your data. My understanding may be wrong; if so I'd
be interested to hear what your data actually is.

Projects are one type of entity.

Payments are a DIFFERENT KIND of entity.

One Project can involve many Payments. Each Payment applies to one Project.

Each kind of entity gets *its own table*.

Projects go in one table; all information about the project as a whole, as an
entity, goes in fields in that table. These would include things like the
project name, the identity of the person in charge, whatever else applies to
the project as a whole.

Payments go in A DIFFERENT table: you said "The second Table has Mth/Yr Sent,
Date Recevied, Income Earned,Retained, Owed, Paid, Check Number." These appear
to be attributes of a Payment. A Payment isn't a Project - a Project doesn't
have a check number!!!

You need a Project No field in the second table - the payments table - to
indicate which project this payment applies to. If a given project has 20
payments, you would have *one* record in the projects table for that project,
and 20 records in the payments table, one for each payment; all of them would
reference the same project.

But you DON'T need the project name, or the name of the person in charge, in
the payments table. You have the Project No in the payments table; this gives
you a link to the projects table which contains that information.

Does that make any more sense?

John W. Vinson [MVP]
 
Hi John,

Thank you very much for you help. I think I have figure out how to solve
the last little problem I have. I'm going to assign default values to the
information that I want to automaticly assign to a record when I'm updating
my form. I have formed a relationship between the Properties table and the
Finanical table. Based on the information I read I think I confused before
about the repeating of records vs. adding new records to the finanical data
table. I'll let you know what happens.
thanks
keri
 
Back
Top