Dbl click a form field to open another form based on value

[mindbenderz]

I found this thread and its close to what I need (http://bytes.com/topic/access/answers/208888-open-form-based-field-entry) but doesnt completely answer my issue. I have a field on a form (subfrm_xmit_do cs.Path) which, when dbl clicked should open a different form and go to the record that correspondes to the value in the field that was clicked on. The field in the 2nd form is frm_hardcopies. Filename. Also, using the process, is there a way to prevent an error if the field being dbl clicked is empty, or it just doesnt load the 2nd form at all if null?

[patjones]

What code do you have so far?

Pat

[mindbenderz]

I have this attached to the dblclick event, but it doesnt open frm_hardcopies and jump to the correct record. It just opens the form and goes to a new record.

Private Sub Path_DblClick(Cancel As Integer)

On Error GoTo Err_Path_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frm_hardcopies"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Path_Click:
    Exit Sub

Err_Path_Click:
    MsgBox Err.Description
    Resume Exit_Path_Click
    
End Sub

[patjones]

Given the code that you've posted, I would not expect the form to do anything other than open up blank. When a form opens up, you need to tell it where to get its data from. For a bound form, this usually means setting the form's recordsource property to the proper table name, query name, or SQL string in the Form Open event.

You can filter the form's recordsource and thereby get to a particular record or records by using the OpenForm Filter argument, or even the OpenArgs argument, but you have not assigned anything to stLinkCriteria. ..so as you have posted it, the filter is not going to do anything.

Pat

[beacon]

You haven't set a value for the stLinkCriteria variable, so at best the form will open, but nothing will display. However, this is likely causing an error.

You'd need to add something like:

Dim stLinkCriteria As String

stLinkCriteria = Me.Path.Value 'Assuming that the value you want is in the Path field

If you want to make sure there's a value in the field, you can do this in the same subroutine by using the following after assigning the value of Path to the variable:

If (IsNull(stLinkCriteria)) Or (stLinkCriteria = "") Then
     MsgBox "A value must be entered to launch the new form"
     Cancel = True
End If

Then you can use DoCmd.OpenForm to open the form because it will have passed the test to determine if a value was entered in the field.

Hope this helps,
beacon

[mindbenderz]

ok, what if I wanted to have it do something like...

when I double click the Path field on a filename where that value isnt currently a record that appears in the table linked to frm_hardcopies, it asks "There is no record associated with this filename. Do you want to create one?" Click yes, and it copies the value from subfrm_xmit_doc s.path to frm_hardcopies. filename in a new record.

Also, when I use the current code with your addition for the stLinkCriteria, I get the error "Syntax error (missing operator) in query expression. Here is what I have right now

Private Sub Path_DblClick(Cancel As Integer)

On Error GoTo Err_Path_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    stLinkCriteria = Me.Path.Value 'Assuming that the value you want is in the Path field


    stDocName = "frm_hardcopies"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Path_Click:
    Exit Sub

Err_Path_Click:
    MsgBox Err.Description
    Resume Exit_Path_Click
    
End Sub

[patjones]

You're a little all over the place here, and not giving us the right information that we need in order to answer your question.

What exactly is Me.Path.Value? If you run Debug.Print for it, what does it show you? What is the form's recordsource? What line of code does the error occur at?

I'm asking these questions because the error is arising either from a problem with Me.Path.Value, or a problem with the second form's recordsource.

[mindbenderz]

ok, Me.Path.Value is the Path field in subfrm_xmit_doc s. the field that it should link to on the other form is frm_hardcopies. Filename

the goal is: if I double click on the field "Path" in a record in subfrm_xmit_doc s where the value is XYZ, I want frm_hardcopies to load where "Filename" is equal to XYZ. Its a jump to. I dont want to make field "Filename" in frm_hardcopies something like =Forms!subfrm_x mit_docs!Path , since it is a normal form used for data entry and Filename has a source already.

frm_hardcopies is linked to tbl_hardcopies
subfrm_xmit_doc s is linked to tbl_xmit_docs

[patjones]

This is a question of defining your filter properly, which I don't think you've done. When you define a filter, it has to look just like the WHERE clause of a SQL statement. For instance:

stLinkCriteria = "Filename = '" & Me.Path & "'"

This tells Access to open the second form and populate it with the record where Filename is equal to the value in your path control. The value for Me.Path must be enclosed in ' ' since it's a string, which is the reason for the concatenation.

If this works for you then we can address null values.

[mindbenderz]

ok, that worked almost perfect. on frm_hardcopies, I have it so onload it goes to a new record. when I do my dbl click, it goes to the new record and I have to navigate back 1 record to view the one I want. Is there a way to have it prevent the 'go to new record' function when I use this dbl click event?

null might not be an issue if it just goes to new record on frm_hardcopies when it opens. not sure, though

[patjones]

on frm_hardcopies, I have it so onload it goes to a new record

Whatever it is you're doing to make it go to a new record on load, don't do it!

[mindbenderz]

ok. so Ill remove that then.