Database Problems

[Crypto]

Hey guys, I am completely new to all sorts of databases. I am trying to develop a test one for students. My problem is, I have 5 or so tables consisting of, personal information, academic information, emergency contacts, and some other stuff... I made a form to input the information but I am assuming I am not getting the relationships right because I get the #Name? value in the input boxes that are outside of the form. I tried to solve this by basing the form raw source off of an extended query that involved all of the tables and when I do that, the form doesn't even show up... strange... Would anyone be willing to help me on this matter? All i want to do is use a single form with tabbed pages to input data into more than one table.

A problem that I will run into down the line, is a one to many relationship that will display a single student takes more than one subject. I can't understand this either but one step at a time.

Any help will be greatly appreciated. If you would like my database attached let me know!.

Thanks in advance!

 

[PNGBill]

Does your query give the data you were expecting?? before you worry about the form.

Your form may well be in at two parts. Main Form and at least one Sub Form.

Student Details in the main form and the subjects taken by that student in the sub form as an example.
You will need to decide which data goes where on the form.

One query may collect all the student details. Name, Age, Class etc for the main form.

Another query will collect the data fo subjects taken and include the studentID.
Your subform uses the 2nd query and displays this data in the subform for which ever studentID is on the main form.

Like an Invoice from a hardware store.
The header (main form) has your name and address, date, delivery details etc.
The body (subform) has the products listed line by line.

 

[HiTechCoach]

Hey guys, I am completely new to all sorts of databases. I am trying to develop a test one for students. My problem is, I have 5 or so tables consisting of, personal information, academic information, emergency contacts, and some other stuff... I made a form to input the information but I am assuming I am not getting the relationships right because I get the #Name? value in the input boxes that are outside of the form. I tried to solve this by basing the form raw source off of an extended query that involved all of the tables and when I do that, the form doesn't even show up... strange... Would anyone be willing to help me on this matter? All i want to do is use a single form with tabbed pages to input data into more than one table.

A problem that I will run into down the line, is a one to many relationship that will display a single student takes more than one subject. I can't understand this either but one step at a time.

Any help will be greatly appreciated. If you would like my database attached let me know!.

Thanks in advance!

You're welcome

I tried to solve this by basing the form raw source off of an extended query that involved all of the tables

Normally a form is bound to a single table.

All i want to do is use a single form with tabbed pages to input data into more than one table.

As PNGBill pointed out, will need to use sub forms. Sub forms are used for the tables that are on the many side of the one-to-many relationship with the table in the main form.

 

[Crypto]

Thanks a lot guys. I got my sub forms made and they are included in my main form and they look the way I want. I believe my relationships are still really messed up. Now when I goto enter information into one of the subforms I get the error "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship." Am I supposed to one-to-one relate all the tables together by the ID? Again what I am trying to do, is input values into numerous tables through sub-forms and have all the information relate to one single ID. I hope I am explaining this correctly. I feel like this is such a noob question haha.

Thanks in advance!

 

[Crypto]

Hey guys, sorry to nag. But I am stuck. It seems way to simple to be having such a problem. I simply want to use numerous tables and input data into those tables through a form that has sub-forms. The question is, how do I relate the tables. Id -> Id etc... or do I go another way, because I am getting this error about duplicate values etc. Again, I hate to nag, but its frustrating.


Thanks in advance!

 

[PNGBill]

Error message - create duplicate primary key may be your clue.

When you add records to a table you can do it two ways (at least?)

Have the form open, by form, query, or datasheet, and type in records.
Have an Update Query append the records you have created or selected to the table.

In both cases you do not type/append the primary key, the table will make it's own.

Most time Primary Keys are Auto and if not, then how do you arrive at the new key without the possibility of a duplicate?

Leave out the Primary Key in your append fields.
Don't include the Primary Key in your SubForm fields, if the subform is the table you want to add records to.

 

[HiTechCoach]

Am I supposed to one-to-one relate all the tables together by the ID?

Depends on the design. Subforms are normally used to display the many side of a one-to-many relationship.


We can probably help you a lot better if you will attach a copy of your database with sample data. nothing confidential. Is that possible?

 

[Crypto]

Hey guys thanks a lot for the help, I have come a long way due to your help.

Now I wanna accomplish something else.

I have two subforms. One that takes in data, and the other shows data. I click a button to add record from the first subform and it does what it needs, but I can only see if it added to the second subform when I reopen the mainform. I looked into an update query that I could use when I click the add record button. Am I looking in the right place? Is there an easier way of doing this?

Thanks in advance!

 

[PNGBill]

Looks like you have done what you want, just can't see it.

Try adding a Refresh Button to the 2nd subform and see if this shows the data. If it does, then see if you can build the code into the button on your first form that you mention.

The code will be slightly different because you are on one form and the action is on another.

 

[Crypto]

Hey, thanks a lot for the advice!!!

I believe I have one last problem. When I enter a record into a subform, there is another subform below it that displays the information input into the 1st subform. When I input data one after another without closing the form, it works fine. But if there contains a record already in the table, the add record inserts it at the 1st position, and then appends after the rest. It basically keeps overewriting the first record unless I do it all at once, what would cause this? I hope I am clear.

Thanks in advance!

 

[PNGBill]

Check the properties of your 2nd subform.

Maybe a change in the Additions, Edits properties will fix what you want to do.

I just did a subform that in one instance needed to accept new data and in another instance, only edit. You can effect the subforms properties by events that can include IfThen to make the change or not.

Another issue could be is the subform continuous or not?

 

[Crypto]

ahhhh THANKS! Okay, I found the problem. Lets say I open a new student record and add n subjects. I then reopen that same student back up later, and I am in fact viewing the first subject the student takes, which is why when I delete the first the subject in the 1st form, I am overwriting it in the 2nd form.

Prevention?

haha, at least we are figuring this out. I apologize for my many problem PNGBill, but you are very helpful!

Thanks in advance!

 

[PNGBill]

Maybe check the table you are storing the records on and ensure you have one or two records.

You enter data in the first form but do you want to see the data in this form that is already in the table?
You don't Overwrite data. You can Edit It or you can Make a Tbale that replaces the existing table but you can't write a new record over an existing record, like you would on paper.

Decide what you want the first subform to do.
Enter data only?? or enter data and view existing data?
If enter only, set properties to Addition yes and edit no - this may solve this issue.
If to both view and enter data then the form should be continuous and allow Addition.

The 2nd subform...

Is this just to view all the data entered? then it will be continuous and additions - no, edit - yes (if you want).

If you want to have different properties for different occurrences then you can set If Then statments on your forms properties that will allow additions if no data present and edits if some data present.

 

[Crypto]

Hey guys, I have a main form with tabs that contain subforms. What I am trying to do, is an if statement.

If a value in a certain field in the main form = "somevalue" then a control on a subform becomes visible.

Where would I code this anyway? I'm guessing in a query.

 

[vbaInet]

We hope that one day we can code such things in a query.

Here's a link:

http://baldyweb.com/ConditionalVisibility.htm