Copy ticket number from a report to a form by clicking a link

[David79]

Hello everyone,



I have an Access database that is used by loaders to track loading tickets. In the database they run a report named "Picker Start _ Multiple Query2", the report then asks for the loader number. They enter the loader number then click ok. The next screen they see is the report showing the loader number and the ticket number being loaded. The loading number field is clickable and when clicked should take them to the "Picker Start _ Multiple" form where they can complete and close out the ticket. The "Picker Start _ Multiple" form reads data from the "Picker Start _ Multiple" table.

The problem I am having is that when I click the ticket number on the "Picker Start _ Multiple Query2" report it takes me to the "Picker Start _ Multiple" form but displays the ticket number from the very first record in the associated "Picker Start _ Multiple" table where the data is stored.

How can I set this up so when I click the ticket number in the report it opens up the corresponding record in the "Picker Start _ Multiple" form instead of the first record in the table? Right now I have the following event procedure set up to execute when the ticket number is clicked:
Private Sub PT_Number_Start_Multiple1_Click()
DoCmd.OpenForm "Picker Start _ Multiple"
End Sub

This code causes the desired form to open like it should, it just doesn't copy over the ticket number specified in the report.

Any help is greatly appreciated! I have been searching and unable to find a solution to my problem.

-David

[June7]

Why do you open a report as an intermediate step instead of a form? Is the report opened in Report View?

Not understanding relationship between loader number and ticket number. In one statement you said you are clicking loading number and another statement says you want to click ticket number.

What is the code that opens the "Loader Start _ Multiple" form?

[David79]

Yes, the report opens in Report View. Running the report is a way for them to log in with their loader number then they can view tickets assigned to them. Once they see the tickets assigned to them they can click the one they wish to close, this is where the "Picker Start _ Multiple" form should open and carry over the ticket number they clicked on but instead it shows the first ticket number in the table.

There must be an addition to my code that will tell the report view to carry over the ticket number I clicked to the "Picker Start _ Multiple" form so they can simply close out the ticket.

[Dal Jeanis]

There are two easy methods.

The first would be to pass the ticket number to the form within the OpenForm command, via the openargs parameter, using code like the following link: http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

The second would be to store the desired value into a TempVars variable before executing the Openform command.

Private Sub PT_Number_Start_Multiple1_Click()
   TempVars.Add ("TmpTicket", Me.MyTicketControl.Value)
   DoCmd.OpenForm "Picker Start _ Multiple"
End Sub

In the Form_Open event, you would check the TempVars variable or the OpenArgs parameter and act accordingly. Here's the TempVars version, assuming the ticket number is alphanumeric.

Private Sub Form_Open(Cancel As Integer)
    If Not IsNull([TempVars].Item("TmpTicket")) Then
        Dim RS As DAO.Recordset
        Set RS = Me.RecordsetClone
        RS.FindFirst "[TicketFieldName] = '" & [TempVars].Item("TmpTicket") & "'"
        If Not RS.NoMatch Then
            Me.Bookmark = RS.Bookmark
        End If
    End If
End Sub

Items in red will need to be adjusted to your control and field names.

Here's a sample writeup of how to use TempVars. https://www.accessforums.net/code-re...ars-36353.html

[David79]

Dal,

Thank you for the reply. I am a novice using Microsoft Access. I replaced "MyTicketControl.Value" with the name of the field I want to copy the ticket number from. I tried using the code specified above, when I tried the first option I get the following error: Compile error: Expected: list separator or )


Private Sub PT_Number_Start_Multiple1_Click()
TempVars.Add ("TmpTicket", Me.PT Number Start Multiple1.Value)
DoCmd.OpenForm "Picker Start _ Multiple"
End Sub


When I tried the second code I replaced "TicketFieldName" with the name of the field I want to copy the ticket number from (PT Number Start Multiple1)


Private Sub Form_Open(Cancel As Integer)
If Not IsNull([TempVars].Item("TmpTicket")) Then
Dim RS As DAO.Recordset
Set RS = Me.RecordsetClone
RS.FindFirst "[PT Number Start Multiple1] = '" & [TempVars].Item("TmpTicket") & "'"
If Not RS.NoMatch Then
Me.Bookmark = RS.Bookmark
End If
End If
End Sub


The second code accepted my change without error but the ticket number in the report becomes unclickable. What am I doing wrong?

[David79]

Just an update. I was able to get the code to work. I changed the name of the form referenced in the code from "PT Number Start Multiple1" to "PTNumberStartMultiple1" then it worked, must not have liked the spaces.

Thank you for your help!

[Dal Jeanis]

Correct. When you use spaces in control names and field names, you have to use square braces [] in certain places. It's much better practice to avoid spaces in names. Use CamelCase or Underscored_Names instead.

Please mark thread solved. Top of page, under thread tools.