DoCmd.OpenForm problem

[salamander]

Hi there,

There is a list of records I have (based ona query). User double clicks a list item to go to that record on another form.

Code invoked is:

Dim stDocName As String
    Dim stLinkCriteria As String
    stLinkCritera = "[ID] = " & Forms!frmNAME!lstNAME.Column(4)
stDocName = "frmNAME"
DoCmd.OpenForm stDocName, , , stLinkCritera

This works fine. However, the resulting form is filtered. I do not want it to be filtered, I just want it to jump to that record in the form, not filter it for that record.

One idea I had is to apply Filter = "" on the new form on current, but I still want users to apply filters through the right click and not have it removed when they move to another record.

Any ideas?

Thanks

 

[RuralGuy]

Pass the [ID] value in the OpenArgs argument of the OpenForm command and then move to the record you want in the OnLoad event of the next form.

 

[missinglinq]

In your calling form:

Private Sub lstName_DblClick(Cancel As Integer)
  DoCmd.OpenForm "frmName", , , , , , Me.lstNAME.Column(4)	
End Sub

In frmName, where NameField holds the value you're passing:

Private Sub Form_Load()
  If Len(Nz(Me.OpenArgs, "")) > 0 Then
    NameField.SetFocus
    DoCmd.FindRecord Me.OpenArgs
  End If
End Sub

 

[salamander]

In your calling form:

Private Sub lstName_DblClick(Cancel As Integer)
  DoCmd.OpenForm "frmName", , , , , , Me.lstNAME.Column(4)	
End Sub

In frmName, where NameField holds the value you're passing:

Private Sub Form_Load()
  If Len(Nz(Me.OpenArgs, "")) > 0 Then
    NameField.SetFocus
    DoCmd.FindRecord Me.OpenArgs
  End If
End Sub

Works but still filters the form. Adding Me.Filter = "" still does not help.

Any ideas?

 

[salamander]

Works but still filters the form. Adding Me.Filter = "" still does not help.

Any ideas?

Ok managed to fix it using RS;

  If Len(Nz(Me.OpenArgs, "")) > 0 Then
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Me.OpenArgs
Me.Bookmark = rs.Bookmark
     End If

However, this only works if the form is not open. If the form is already open, the OpenArgs value is held constant as the one which was used to open the form originally. For example, I click on a list item which has ID 458 on Form A. This opens Form B at Record ID = 458. I then go back to Form A (without closing FormB) and click at ID 460. Form B opens Record ID = 458.

Is there anyway of combatting this?

 

[Dennisk]

Set form B to be modal that way you have to close it to return to form A

 

[salamander]

Set form B to be modal that way you have to close it to return to form A

I'd rather not do that as I would like both forms to be able to be on the screen (some users would prefer to have both on the screen)

 

[missinglinq]

To begin with, the code I gave does not filter the form, it merely moves to the record in question; I checked that and had no problem moving thru the record set and editing/adding records.

Obviously, OpenArgs is going to remain constant until FormB is closed and then reopened. If you want both forms to always be open all the time, and at the corresponding record, then you need to set up FormB as a subform of FormA. This is what subforms were invented for.

 

[salamander]

It definately filters on mines, and Subform is not an option for cosmetic reasons. Going to do it alternatively with public defined vars now.

 

[salamander]

Has anyone got any ideas how to jump to a record on another form without filtering?

 

[namliam]

Check out this thread

http://www.access-programmers.co.uk/forums/showthread.php?t=147757

Where I am helping Slayboy... His code should help you too

 

[darbid]

I am using this old thread to do the same thing.

I am opening a form with:

Dim strfilter As String
Dim stropenargs As String
stropenargs = Me.[LuT Aktenzeichen]
strfilter = "[tbl_LuT Vorgang].[Bearbeiter LuT]='BOW' or [tbl_LuT Vorgang].[LuT Aktenzeichen]= '" & Me.[LuT Aktenzeichen] & "'"
 
 
    DoCmd.OpenForm "frm_Hauptfromular_norm", acNormal, , strfilter, , acHidden, stropenargs
    DoCmd.Close acForm, "frm_Hauptfromular"
    Forms("frm_Hauptfromular_norm").Visible = True

The on the load function:

If Len(Nz(Me.OpenArgs, "")) > 0 Then
 
    Me.[LuT Aktenzeichen].SetFocus
 
    DoCmd.FindRecord Me.OpenArgs
  End If

The problem that I have is that in the second form I am not using a text box for this field. This Field is simple a label.

Thus my question is how can I get the second form to go to a specific record?

 

[namliam]

I presume you have [LuT Aktenzeichen] in the recordset of the new form??

You need it in the recordset (atleast) to be able to search for it.

stropenargs = Me.[LuT Aktenzeichen]
strfilter = "[tbl_LuT Vorgang].[Bearbeiter LuT]='BOW' or [tbl_LuT Vorgang].[LuT Aktenzeichen]= '" & Me.[LuT Aktenzeichen] & "'"

Why thought are your pqassing [LuT Aktenzeichen] both in the filter AND in the Openargs?? That doesnt make sence.

If you change your OR to AND in the filter, the form should only show records with [LuT Aktenzeichen] ... and do exactly what you want it to do.

 

[darbid]

I presume you have [LuT Aktenzeichen] in the recordset of the new form??.

yes

Why thought are your pqassing [LuT Aktenzeichen] both in the filter AND in the Openargs?? That doesnt make sence..

Ok so there are two things that should happen when the new form is opened.

1. First is that the user can see the record that they have choosen on the first form.
2. Second that they can see all of their records.

This is why I have two things in the "where" the [LuT Aktenzeichen] is the record they want to see and [Bearbeiter LuT] is this particular user.

A user could choose a record that they are not the user of thus the record set number would be their user ID +1. Thus I thought "OR" is correct.

I then noticed that it did not go to the record that they had choosen which is the problem I am currently trying to solve.

So I would like to open the new form with this record set and then find a record. The only unique field is [LuT Aktenzeichen] but in the new form it is a lable only.

Edit: I have actually solved my problem by using

If Len(Nz(Me.OpenArgs, "")) > 0 Then
   
    Set rs = Me.RecordsetClone
    rs.FindFirst "[LuT Aktenzeichen] = '" & Me.OpenArgs & "'"
      If Not rs.EOF Then Me.Bookmark = rs.Bookmark
     End If