Need Help With DLookUp Function in Access (Excel equivalent LookUp)

[rlp12273]

I have attached screen shots from the database that I need help with.



What I am trying to accomplish is in the Form "Assets" that was created from the Table "Assets". I want to populate the Validation Protocol field (below the blue line) and have all of the other information above the line (8 fields) populate automatically. The information that needs to go in these 8 fields is located in the "Asset List" table. How can I add this information automatically based on the entry of the Validation Protocol number?

The reason I need this is because when I add more protocols in the future with the same number, I dont want to have to type all of the information in each time. What will differentiate each record in the form will be the "Vol" field (volume). Also, in the future, I will be adding new assets into the "Asset List" table with each asset having its own protocol number (ie, the next asset will be VP3502, and so on).

I have been searching online for an answer for more than a week, but I cant seem to find out how. Is DLookUp the best way to do this or another way?

Someone, PLEASE HELP!!! I am not VB savy, so I am trying to use similar functions that are used in Excel.

Thanks!!!

[orange]

Can you show us your Tables and Relationships as a jpg?

[rlp12273]

The one table was attached. I have attached both tables as .jpg files.




I dont have any relationships or queries setup yet. Should I?

[rlp12273]

Just to add, I do know how to create relationships. I just have not created any as of yet.

I have attached the Database in Access 2003 format (had to take away the attachments field).Validation Protocols Database (Test).mdb

[June7]

Not understanding data relationship. Why would entry of Validation Protocal control which asset is related? What are PK/FK links for these tables?

Review:
http://forums.aspfree.com/microsoft-...es-208217.html
http://www.developerbarn.com/communi...-database.435/
http://office.microsoft.com/en-us/ac...010098674.aspx

[rlp12273]

I am thinking that the asset number should be the PK now that I have had time to reflect. I dont currently have any PK assigned. The reason for this is because the asset will be unique to the other information. The validation protocol could have more than one asset (similar equipment would be under one protocol). So, if I were to link the asset to completing the other information (7 fields), how would I do it using the DLookUp function? I can have the protocol number assigned to the asset number that can be autofilled as well, but that is not really necessary. Both of the tables have an "asset" field that can pull the information into the Form.

I have attached the DB in my previous post.

Does that information help? The links you sent didnt help me because I am still a novice-sorry. I do know the basics though.

Thanks!!

[orange]

For your own benefit and to help you understand database, you should research Normalization.
In addition, google ms access naming convention and review some of the benefits of using a naiming convention that does NOT allow special characters in field and object names.
You will save yourself endless hours of frustration by
- studying and practicing Normalization,
- designing database tables and relationships following Normalization rules, and
- using a naming convention that limits names to alphanumeric and the "_" character.

Normalization --- read the first 3 topics here http://www.rogersaccesslibrary.com/forum/topic238.html

For practice use the ER Diagramming link to design your own tables and relationships - you will learn from the exercise.

For Naming convention and all round great info on MS Access and usage see http://www.accessmvp.com/strive4peace/

Good luck with your project.

[June7]

The links I referenced are intended for novices. If you know basics then you should know normalization and relationships. Grasping these concepts is at the root of good relational database design. What you have posted so far does not demonstrate this level of understanding.

If protocols are standard sets of attributes that can be associated with assets, then you need a table of these protocols. Then selecting the protocol ID into a field of Asset record can be used to link the tables and view all related information.

[orange]

Read this chapter if nothing else (Normalization and names)
http://www.accessmvp.com/strive4peac...Chapter_03.pdf

[rlp12273]

The links I referenced are intended for novices. If you know basics then you should know normalization and relationships. Grasping these concepts is at the root of good relational database design. What you have posted so far does not demonstrate this level of understanding.

If protocols are standard sets of attributes that can be associated with assets, then you need a table of these protocols. Then selecting the protocol ID into a field of Asset record can be used to link the tables and view all related information.

Yeah, thanks

[rlp12273]

Thank you. I will take a look at the information you provided.