click a textbox, a form pops up and goes to a specific record

[Jeffrey Tan]

Hi. I am wondering if this is possible.

I have a main form (opens on startup) and another form that contains problem tickets/records.

I have a label on the bottom left of the form that says
"You are currently logged on as: " [user-name] (takes the username for computer) This is a problem tracking db and is used by 4 people

I have added another label that says "You currently have" # " tickets opened."

I would like to do the following:
1) change the "#" to the actual number of tickets the user has (based on who logged on). so let's say I opened 4 tickets, it will say "You currently have 4 tickets opened." if my friend opened 6 tickets yesterday, when he logs on from his computer and opens the db, it will show on his screen "You current have 6 tickets opened."
2) make the # clickable so that it opens up a listbox that shows the opened tickets they have. based on what they click on the listbox, it will open up form #2 that goes to that specific record.

thanks. hope this isn't to complicated.

[Rabbit]

You can use DLookup to find out how many tickets are open. Then in the On Click event, you can use the where parameter of DoCmd.OpenForm to limit it to the specific user.

[NeoPa]

I would suggest, assuming your main form is a menu and doesn't otherwise use a record source, that you build the menu bound to a record source showing the open tickets filtered by the current user (and move all existing controls to the form's footer). This has the bonus that the form can be requeried at any time to show the updated value correctly.

You could even design it with a Detail section that listed the individual records. This could be hidden by default, but triggered to open on whichever trigger you like. Once a record is selected it can be opened directly on another form, and the Detail section optionally hidden again. The other form can trigger a requery of the main form if the record changes status.

[Jeffrey Tan]

again, sorry for not updating this thread.

my supervisor wanted something else and i managed to solve it.

what i did instead of having a label was create a separate subform with a listbox that queries the table for those that have "OPEN". I then inserted a hyper link on the main form and linked it to the subform. the hyperlink was also updated to show the DCount of "OPEN" on form load.


so when i click on the # that shows open tickets, it opens up the subform listbox. from there, when i select an item from the listbox, it automatically opens up the User Problem Log form and goes to that specific record based on the autonumber.

problem solved. :)

again, thanks for the help Rabbit and NeoPa. I truly appreciate it. :)

[NeoPa]

Pleased to hear it Jeffrey - and thanks for the update :-)