OpenArgs property between project form and equipment list forms

[lavey]

Hey all,
Fairly new to Access (2010) and VBA so bare with me

I am creating a database for an equipment list relevant to specific projects. I have created all the forms etc and all I have left to do is for the user to be able to open the equipment list specific to the project that they have selected.

This is what I've envisioned:
- User selects project number from main menu
- will link to form with all project details
- Click "equipment list" button and will open up the equipment list form
- User can enter data, and display all equipment data in listbox in this form
- User needs to enter equipment data for a different project - goes back to main menu and selects other project number and then clicks "equipment List for that project"
- Now I want this form to only be specific to that project ID

So each Project has its own information - kind of hard to explain.

I have scoured the net and I presume i should use the OpenArgs property to pass the value of the project ID from the projects for to the equipment form. Someone said to have a hidden textbox on the equipment form that has the foreign key bound and use the forms OnLoad event to execute the value of the OpenArgs property.

That bit really confuses me and I can't manage to write the code for the OpenArgs property. Will someone be able to help me out with writing a sample code for this procedure?

Please see attached photo of my relationships etc.

Thanks very much!

 

[namliam]

Why wouldnt you have Equipment "simply" as a subform of your project?

To fill you box, you can fetch your open args simply my using Me.OpenArgs
Or pass your project ID as the "Where" clause of the form.
Which would be something like:

docmd.openform "yourform",acNormal,,"[Yourfield]= " & ProjectID

 

[lavey]

I think it just looks neater opening a new form, in my opinion.

Uh this may sound sound dumb, but what do you mean by the [YourField] part in the code?

 

[namliam]

as in the Fieldname that is your projectID, Guessing p_projectID but that is guessing, would rather leave that up to you to be certain.
Just like yourform would be the form name you are trying to open.

 

[lavey]

Yes thought so, thank you

DoCmd.OpenForm "Equipment List", acNormal, , "[p_ProjectID]= " & Me.p_ProjectID

I have this code for my button. Now for the invisible text box on the equipment list, what code should I write ? theres also no "open" property just onClick and before update etc, which one of these should I write the code into?

 

[namliam]

Using the where clause option, there wouldnt be any need for a hidden text box...
All records visible on the form Equipment list should only be related to that Project_id

fyi, right or write are two different things

You would write the code in the "On open" of the form....
If you want the hidden box, is the originating form still open?
If yes, you can fetch the projectid on demand from that form
If no, add the ProjectID to the openargs and simply fill it with the code I gave you earlier.

Note: Naming Conventions!!!
Using spaces in form names (or any object/controls) is a bad idea.
And it is a GOOD idea to prefix your objects by their type, in this case frm or otherwize tbl/qry/etc.
frmEquipmentList would be a more "good practice" kind off name.

 

[lavey]

Haha! I re-read my post and noticed that, I edited 1 minute before your post

Thank you for the help a great deal.

And yes, Naming Conventions! I will change all of them appropriately, I definitely am starting to get mixed up with what is what etc.

Thanks again

 

[lavey]

While I have your attention Mailman, could you help me with the following.

When a user clicks/double clicks on one of the fields in the List box, how can I get all of the information associated to that item to display in the fields above it. I can't work out any code for that and cannot find anything specific on the net. I want the information for the item that comes up to be editable ie. the user can alter the information

- I have tried using something along the lines of:
=[SearchResults].[column](5)

This however only displays exactly what is from that column, whereas I have close to 30 columns and I think I can only use/display 16 in my query? Additionally, using this method I wasn't able to edit the information.

Many thanks

 

[namliam]

Decent thread about naming conventions:
http://www.access-programmers.co.uk/forums/showthread.php?t=225837&highlight=naming+convention

 

[namliam]

What are you trying to do? You completely lost me in your question, sorry.

 

[lavey]

Sorry let me clarify.

See in the attached picture of the first post the equipment list.

There is a list box and there is currently a row highlighted in it. I want the highlighted rows fields (in this instance "345" & "Heat Exchanger") to appear in their corresponding text boxes on the form. So that the user can scroll through the list box and click on a specific equipment, then edit its attributes in the boxes above it.
I hope that was easier to understand

 

[namliam]

Typicaly I would put such a search list on the top of my form(s) but I guess that is a matter of personal preverence.

Regardless this is something I would resolve using a Subform to display the selected record.
Alternatively you can create code to write the data into the currently unbound controls and then write code to save the changes to the table, but that is about equivalent to going from Amsterdam to Rome by way of Moscow IMHO