Saving and calling a variable from a field

[snewham]

Ok basically I wont bore you with the reason I need this as it even baffles myself and it would be a very long winded explanation hehe.


Basically, what I want to do is save a variable from a selected field when you click a button that is within a (table) subform and then call on that field (Project ID) to FindRecord (Project ID) within another form after I have changed the control source of the subform that will contain that form. I need to save as a variable for a reason but I wont bore you with that like I said. I can't just use findrecord from that field location

This is the code that I am using at the moment:

Private Sub open_record_button_in_proposed_tab_Click()

    Dim x As String
    
    On Error GoTo Err_openrecord_Click
    If Forms![frm_main]![Sub_Main]![proposed_subform].Form.RecordsetClone.RecordCount = 0 Then
        MsgBox "There are no proposed projects entered for this Client."
    Else
    
        x = "Forms![frm_main]![Sub_Main]![frm_projects].Form![Project ID]"
        Forms("Frm_Main").Sub_Main.SourceObject = "frm_projects"
        DoCmd.FindRecord x
        
        
        
        DoCmd.FindRecord Forms![frm_main]![Sub_Main]![active_subform].Form![Project ID]
        
       
    End If

Exit_openrecord_Click:
    Exit Sub

Err_openrecord_Click:
    MsgBox Err.Description
    Resume Exit_openrecord_Click
End Sub

As you can see, there is also a simple error message that is displayed when there are no records within the table/subform.


Thanks alot for any help
scott

 

[Sprocket]

I think this might be a case where you could use the OpenArgs expression to get the second form to open with a value established on the first form.


This is what the Access/VBA help file says about it

Determines the string expression specified by the OpenArgs argument of the OpenForm method that opened a form. Read/write Variant.

expression.OpenArgs
expression Required. An expression that returns one of the objects in the Applies To list.

Remarks
This property is available only by using a macro or by using Visual Basic with the OpenForm method of the DoCmd object. This property setting is read-only in all views.

To use the OpenArgs property, open a form by using the OpenForm method of the DoCmd object and set the OpenArgs argument to the desired string expression. The OpenArgs property setting can then be used in code for the form, such as in an Open event procedure. You can also refer to the property setting in a macro, such as an Open macro, or an expression, such as an expression that sets the ControlSource property for a control on the form.

For example, suppose that the form you open is a continuous-form list of clients. If you want the focus to move to a specific client record when the form opens, you can set the OpenArgs property to the client's name, and then use the FindRecord action in an Open macro to move the focus to the record for the client with the specified name.

Example
The following example uses the OpenArgs property to open the Employees form to a specific employee record and demonstrates how the OpenForm method sets the OpenArgs property. You can run this procedure as appropriate— for example, when the AfterUpdate event occurs for a custom dialog box used to enter new information about an employee.

Sub OpenToCallahan()
DoCmd.OpenForm "Employees", acNormal, , , acReadOnly, _
, "Callahan"
End Sub


Sub Form_Open(Cancel As Integer)
Dim strEmployeeName As String
' If OpenArgs property contains employee name, find
' corresponding employee record and display it on form. For
' example,if the OpenArgs property contains "Callahan",
' move to first "Callahan" record.
strEmployeeName = Forms!Employees.OpenArgs
If Len(strEmployeeName) > 0 Then
DoCmd.GoToControl "LastName"
DoCmd.FindRecord strEmployeeName, , True, , True, , True
End If
End Sub

The next example uses the FindFirst method to locate the employee named in the OpenArgs property.

Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
Dim strEmployeeName As String
strEmployeeName = Me.OpenArgs
Dim RS As DAO.Recordset
Set RS = Me.RecordsetClone
RS.FindFirst "LastName = '" & strEmployeeName & "'"
If Not RS.NoMatch Then
Me.Bookmark = RS.Bookmark
End If
End If
End Sub

I know you said you want to create a global variable but this is not always necessary. You will have identified the data on "form one" and can use it as is from that form.
"Form two" will have opened using that same value so you can pick it up again within that form.

Worth a look at

Regards... Sprocket

 

[snewham]

Thanks for the help Sprocket, I'll take a look into that later.

scott