Form Auto population

[Evgeny]

i guys,


Im completly new to Access and have been asked to build a database which includes, three main tables customers, buildings and asset register for each building. As you can see from Customer Form, i created a button called Asset Register, which brings up Asset Form based on Macro <OpenForm = Asset, Datasheet, , , , Normal; ApplyFilter = [Asset]![Building ID]=[Forms]![Customer]![Building].[Form]![txt_BuildingID].[Value], >. (Not too sure if its right way, but it works).
The problem is when i open up Asset Register Form - i can input all the information and it will be saved in Asset Register Table, but i have to input Building ID manuly for the same building, and i have about 300 items ;(. How do i automate this proccess, so building ID populates automaitcly for all items when i enter information (and saves it in Asset Register Table)?
Please inform me if i made very stupid mistakes in my database, any critisism will be appreciated, i am trying to learn Access

[pbaldy]

A more common method of displaying data with a one-to-many relationship would be a form/subform. In your case, the form would be based on the building table, and the subform would be based on the assets table. They would be linked on the building ID, and master/child links would automatically populate the building ID for you when entering assets. If you want to stay with your method, you could make the default value of the textbox refer to the form containing the building ID.

I just noticed you have the customer form set up like I mentioned, so you already know about it. You might consider opening a form set up like that when clicking on your asset register button.

I would avoid spaces and symbols in your object names. In the long run, they aren't worth the trouble. Some use underscores "Asset_location"; though personally I hate them and use capitalization "AssetLocation".

[Evgeny]

Thank you for prompt reply. I will give your solution a try. In general, how would you comment on my db structure? am I on the right track? I am self-learner.

[pbaldy]

Other than the previous comments, it looks fine. I'd say you're on the right track.

Welcome to the site by the way!

[Evgeny]

Thank you pbaldy. I played around with solution you suggested, but it only got my db messier lol. Please suggest if you could provide a quick visual example. Really need to get this done, I has been suffering with it for few days now (.

Appreciate your time

[Evgeny]

pbably, i figured out what you were trying to tell me!!! The solution was easire than i thought, spent days writing all sort of weird formulas for nothing lol!!

Thank You, your a legend

[pbaldy]

Excellent! Glad you got it sorted out.

[Evgeny]

As i managed to autopopulate data, i got completly stuck again ((( The code provided above used to work for retrieving particular record based on the filter, however its gone completly mad now (or may be i have ). Please advise on the macro for VIEW ASSET button.


Kind Regards

[Evgeny]

file attached

[pbaldy]

I often use this technique to restrict records on a form/report being opened:

http://www.baldyweb.com/wherecondition.htm

[Evgeny]

hey, thank you. I never used VBA, how do you declare DoCmd.OpenForm ? As i undestand in Private Sub ButtonName_Click()?

[Evgeny]

Nothing seems to work ((( , Tried applying filters and i tried your VBA functions. Completly stuck now.

[pbaldy]

Here's a primer on using VBA:

http://www.baldyweb.com/FirstVBA.htm

My bedtime, so if you're still stuck, post the db so far and I'll take a look in the morning.

[Evgeny]

hey, thank you for reply. I am still stuck unfortunately . I attached the db, please suggest if you have time.

Kind Regards,

Evgeny

[Evgeny]

This code returns run - time "3075" error...


Private Sub Command13_Click()
DoCmd.OpenForm "Building+Assets", , , "txt_BuildingID2 = ''" & Me.txt_BuildingID

End Sub