Open form from listbox selection

[MrGrumpy]

I am building a facilities maintenance database, and have a "Maintenance Requested" list box which displays all work requested. I need to create a function where I can highlight a record in the list box, and click one of 2 buttons (Scheduled Maintenance", or "Unscheduled Maintenance"). These buttons open a "Create Work Order" form (which are slightly different).

I have found threads that do this in the past, but now I need the knowledge, I can't find them anywhere! By the way, I know enough to amend basic VB, but have no idea where to start writing VB from scratch. You could say I know just enough to do the job wrong!

Can someone please help me with this wee problem? It should be a simple task for fine programmers such as yourselves! If you can solve my problem, I will give you a day off work (just tell your Boss I said it was OK)!

 

[DCrake]

I assume you mean you want to create a new rcord for the selected order in the list box.

When you have opened the form you can refer back to the calling form by using the following syntax. (There are several variations on the same theme).

Me.YourControl = Forms("FrmUnderlyingForm")("TxtControlName")

David

 

[MrGrumpy]

I don't want to create a new record David. User number 1 creates a "Maintenance Request", which ends up in a list with all other maintenance requests. The Facilities staff turn a selected "Maintenance Request" into a work order.

The "Work_Request_List" form has a list box with all maintenance requests (which you are also helping me to prioritize in a different thread). I want to highlight a record in the list box, and click a button which opens up the Work_Order_Step3 form. I would like the Work_Order_Step3 form to display the highlighted record from the list box in the "Work_Request_List" form

And to answer your question... NO. It does not take 9 women 1 month to make a baby. My friend tried something similar to this, and he ended up with a whole heap of babies! Please don't try it! My wife tried a similar experiment with cooking. Instead of cooking a chicken at 100 degrees for 3 hours, she cooked it at 200 degrees for 1.5 hours. All of our smoke alarms went off, and we had take-aways for dinner that night!

 

[DCrake]

So assumming that the details form has an underlying query that contains the matching data for the highlighted record in the list box on the calling form, you can still use the same methodology.

On the OnLoad event of the form

Me.Filter = "[FilterField]=" & Forms("OtherForm")("ListBox")
Me.FilterOn = True

This will then read the bound column in the listbox on the first form and apply the filter to the form being opened.

Give that a try.

BTY another interesting analogy is that if you use a Sat Nav in your car and the screen is pointing towards the windscreen does it think you are going in reverse?

David

 

[MrGrumpy]

I entered the data into VB as follows :

Private Sub Form_Load()
Me.Filter = "[FilterField]=" & Forms("Work_Requested_List_Step2")("ListBox")
Me.FilterOn = True
End Sub

Should [filterfield] and "ListBox" be titled as they are, or should I change the titles to suit some tables (or something) from the DB?

I changed "listbox" to "listbox19", and I go to the correct form, but the first record is displayed, not the one selected in the listbox19.

I changed [filterfield] to [title] (the Work Order title field in the "Work" table), but nothing happened.

Any ideas on what I'm doing wrong?

 

[DCrake]

When offering solutions many people use made up names that hopefully will become apparant to the user that they need changing to reflect the table and fields names in the posters mdb. This is known as aircode. And as such cannot be tested. If you replace the ones offered with actual names for the fields, tables, forms, etc what results do you get.


You also need to make sure that the underlying query/table that is bound to the form contains the fields that you have named in the filterering condition.

David

 

[MrGrumpy]

Yeah... I have noticed that. Unfortunately, not all airnames are apparent to noobs who don't know all of the commands that Access accepts. I know this is a problem, so I experiment with several variations of coding that is given to me. If I still come up with a blank, I will provide as much details as I can think of, and ask more questions.

In this case, I have changed the [filterfield] to [title] (a field in the bound table). I suspect I might need to somehow tie the bound query with and the field together. Only, I don't know the format to do this.

Also, it might be a good idea to tell you that I have never used filters before (I have never needed to).

 

[DCrake]

Here is an alternative approach to passing variables between objects. If you follow the directions closely you should grasp the logic.

David
Link