By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,247 Members | 1,989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,247 IT Pros & Developers. It's quick & easy.

Using a form/sub form to enter data

P: 4
I have created a data base with 3 tables. The first table has only 2 records and each record is linked to many in table 2.

I have used a form sub form to eneter table 2 data without problems. I just select the name of the record in table 1, and enter everything else in table two. In table two I have used a lookup to table one for the name in table one. This worked and the datasheet in table 2 showed many instances where each of the two names apeared with different equipment atrributed to each one!

For table 3 i wanted to list tasks to be done with each peice of equipment. Say there are 2 cars in table 2, Car 1 for Rob and Car 2 for steve.

I have used the same process, look up fields for Name, and Equipment (looked up from table 2) and created a form/subform where those 2 fields apear on the form, and task details on the sub form.

I have two issues. After entering a heap of data into the sub field under diferent combinations from my query I checked the data sheet for table 3 and noticed the equipment colum was empty.

I also noticed in this view I could enter data using the lookup, but it didnt mater if i had the wrong car with the wrong person. That combination was not a record in table 2.

What have i done wrong?
3 Weeks Ago #1

✓ answered by NeoPa

CwazyRuss:
What have i done wrong?
If I start by saying "Lots." it might have a deleterious effect which isn't deserved. Let's just say that database work is quite different from most other dev work and has quite a steep learning curve at the start. I would suggest you could help yourself on that score by exploring some tutorials (Microsoft Access Tutorials (Strive4Peace)). It would be good use of your time and effort, giving you a bit of a kick-start to get you going on the right track.

The most fundamental problem, I expect, is the design of your tables. Free text fields like names of items should never be used as the primary way of linking or identifying records. Use a Primary Index (or Key - PK) to identify the master table where two link together and also as the Foreign Key (FK) in the table where records need to be linked into the master. Table #3 can be linked into table #1 or table #2, or even to both.

From your explanation we have very little to go on as far as what your database is doing, and I don't think it would be helpful at this stage to discuss what might be happening on your form that's not the best approach. That would only distract from the more fundamental issue of table/database design at this stage. However, when you have that issue resolved we can help further with the form design if you can manage to explain it in terms that indicate what you're doing and what isn't working more clearly. I suggest we leave that for another thread though.

Good luck.

Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,419
CwazyRuss:
What have i done wrong?
If I start by saying "Lots." it might have a deleterious effect which isn't deserved. Let's just say that database work is quite different from most other dev work and has quite a steep learning curve at the start. I would suggest you could help yourself on that score by exploring some tutorials (Microsoft Access Tutorials (Strive4Peace)). It would be good use of your time and effort, giving you a bit of a kick-start to get you going on the right track.

The most fundamental problem, I expect, is the design of your tables. Free text fields like names of items should never be used as the primary way of linking or identifying records. Use a Primary Index (or Key - PK) to identify the master table where two link together and also as the Foreign Key (FK) in the table where records need to be linked into the master. Table #3 can be linked into table #1 or table #2, or even to both.

From your explanation we have very little to go on as far as what your database is doing, and I don't think it would be helpful at this stage to discuss what might be happening on your form that's not the best approach. That would only distract from the more fundamental issue of table/database design at this stage. However, when you have that issue resolved we can help further with the form design if you can manage to explain it in terms that indicate what you're doing and what isn't working more clearly. I suggest we leave that for another thread though.

Good luck.
3 Weeks Ago #2

zmbd
Expert Mod 5K+
P: 5,397
In addition to the tutorial link Neopa gave you I've sent a copy of some resources to your Bytes.com>Inbox
3 Weeks Ago #3

P: 4
Thanks. I think linking to tbl1 and tbl2 will solve alot of my problems. I am new and the curve is steep. I will keep at it though.
3 Weeks Ago #4

NeoPa
Expert Mod 15k+
P: 31,419
CwazyRuss:
I am new and the curve is steep. I will keep at it though.
You can't say fairer than that. Good luck on your journey.
2 Weeks Ago #5

Post your reply

Sign in to post your reply or Sign up for a free account.