Update through form

[RemyO]

For me all this is just a training excercise in Access. The case i took is a friends case.
I could have given a example where i want to look up a personid thought its first and last name and combine it with some random key from another table.



I just dont know how to create a form using 2 input parameters from table 1 returning an ID and 1 input parameter from table 2

Being a novice in Access I want to explore the possibilities of Access. I have no desire to build a functional application at the moment.

So though the case might be invalid but the technical question still remains and is something i might encounter in the future.

PS i tested another datamodel where i put location in machine and that worked. But there i missed some lookup functionality.

PS2. Orange the small little form you created is exactly what i want but cant create. I dont know how to code that form manually

[orange]

RemyO,

Attached is the database (2000 format) that I created based on your post.

Look at the design view, Events and properties of the Forms and you'll see some of the code.

Post if you have specific questions.


There are free video tutorials at this site. here is one on combo boxes and forms.
http://www.datapigtechnologies.com/f...combobox1.html
This pdf has some very good Access learning links. There is a section on Forms.
http://www.sfubusiness.ca/areas/mis/...ons/forms1.pdf

Good luck

[goodguy]

@RemyO: Please have a look at my attached db which is built on database design principles and normalization, as opposed to yours. Study the tables and their relationships, the forms, queries and reports and how they have been designed and you will get a good idea of what good design is all about, e.g.
1. All lookup tables have a single text column only which is also the PK. There is no rule that says that the PK must be an Autonumber. Using the text field saves you a lot of trouble with writing SQL and joins when looking up values.
2. One machine can only be in one location so Location column is an FK on Machines table. Likewise Category column.
3. Devices and Parts are M:M so we have junction tables for Machines+Devices and Devices+Parts.

[RemyO]

Thanks Orange. Your forms got me going.
Though i am not quite there yet

How did you manage your form to only show 1 new record. I would like my form to act like a data entry form only, so changing data is not allowed.

Goodguy: I like the way you normalised everything. I know my model isnt perfect. But i intentionally created something with some complexity i want to resolve using access. I want to learn what Access can do and where and when i have to to use VBA. So where your model has the benefits of resolving a particular functional problem my interest at the moment is resolving technical issues.

[goodguy]

Wrong approach: you won't learn better by ignoring the principles of good database design. In fact, database design is equal parts art and science. If you master that, you are past halfway home!

[orange]

I agree with Goodguy. You are only fooling yourself if you think you can ignore proper structuring of your tables (normalization) and can solve your issues with programming.
Relational database is getting the structures (tables and relationships) correct and then using the tools SQL, VBA etc appropriately.

[goodguy]

Orange, thumbs up!

[RemyO]

I can only partially agree with your statements. What you are basically saying is: "Come up with a good (valid) example which will have the given complexity and only then we can help you".

My small (though invalid example) is a very simple db. Easy to overview and still holding enough complexity so i can fool around with VBA, inserting records through buttons, catching errors, checking for duplicates and much more.

While it might not hold any meaning in the real world it can help me understand stuff in access that i may encounter.

[goodguy]

Best luck in your self-guided adventures!

[orange]

I can only partially agree with your statements. What you are basically saying is: "Come up with a good (valid) example which will have the given complexity and only then we can help you".

My small (though invalid example) is a very simple db. Easy to overview and still holding enough complexity so i can fool around with VBA, inserting records through buttons, catching errors, checking for duplicates and much more.

While it might not hold any meaning in the real world it can help me understand stuff in access that i may encounter.

Well RemyO that may be your interprretation of what we are saying, but it is not what I said nor meant. Let me be perfectly clear:

You posted a scenario and asked for assistance. We saw what you posted -All the facts we have to work with - and offered advice. You seem to be blaming us because your sample (not a real problem) was either ill conceived or not truly reflective of the issue you wanted to "play " with.

My response, which you have chosen to ignore or manipulate, is GET YOUR DATA STRUCTURES ORGANIZED TO TRULY REPRESENT THE BUSINESS PROBLEM/OPPORTUNITY YOU ARE DEALING WITH; MODEL THE DATA; GET TEST DATA TO EXERCISE THE MODEL; THOROUGHLY TEST THE MODEL; RECONCILE EVERY ISSUE THAT EVOLVES; ADJUST THE MODEL AS NEEDED.

Underlying data structures are the key to database design and applications involving that database.

I think your situation is lacking details. You seem to want to solve an issue with code and ignore tables and relationships. The analogy that comes to mind is
I have a new tool (let's say automated nail gun) and I want to build a house. "What kind of house do you want to build, RemyO ?" "Do you have plans for your house?" No, not really I just want to use the nail gun!

So go do some practice exercises in vba, forget data base design and normalization. When you convince yourself that it's structure, not vba, that is the root of your problem, then you can google Normalization and Data Base design for the details which you seem intent on ignoring at the moment.


Good luck with your project.