Error 2046 when trying to go to Form record in Navigation Pane

[RagonichaFulva]

Hello,

I have created a navigation pane form (Init_Form2) which contains a subform (Store_Down_Subform) which includes a subform (Store_Down_Subform_Datasheet).

Both, Store_Down_Subform and Store_Down_Subform_Datasheet show the data from the same table. the idea is to create a sort of split form (split forms unfortunately don't work in navigation pane forms). What I need to do is that when a user sets focus on a certain record in Store_Down_Subform_Datasheet, Store_Down_Subform moves to the corresponding record.

I am using the following code:

Private Sub Store_Down_GotFocus()
    Call sendtorecord
End Sub


Private Sub sendtorecord()
    Dim recNum As Long
    Dim formName As String
    formName = "Store_Down_Subform"
    recNum = Me.CurrentRecord
    'DoCmd.SelectObject acForm, formName, True
    'On Error Resume Next
    Forms!Init_Form2.Store_Down_subform.Requery
    DoCmd.GoToRecord aForm, formName, acGoTo, recNum
    'On Error GoTo 0
End Sub

The original code works with Store_Down_Subform form open, but it doesn't when it's in the navigation pane, so I have tried different possibilities.

I included the code with all the options I have used, but none works (select object, requery, etc.)

I would much appreciate your assistance.

[rpeare]

Is there a specific reason you're using a datasheet for your selection? usually this type of operation is handled by list boxes, where you could see a list of items then in the ON CLICK event of the list box bring up all the details associated with the item you clicked on.

[RagonichaFulva]

Is there a specific reason you're using a datasheet for your selection? usually this type of operation is handled by list boxes, where you could see a list of items then in the ON CLICK event of the list box bring up all the details associated with the item you clicked on.

Hello rpeare,

I wish to create a sort of split form in my navigation form (in a certain tab of the navigation form) so the user can edit each record from the upper part (form view - one record at a time) and see the whole table in the lower part (datasheet view and locked). I must use this system because split forms don't work in navigation forms. My idea is that the user only modidfies data with the form view in the upper area but can go quickly to certain record clicking on the record in the datasheet view.

I look forward for your feedback.

Thanks for your thoughts.

[rpeare]

As I said in my original post, is there a specific reason you need to use a datasheet view instead of a list box to do your selection.

You can show the exact same data elements in a list box that you can in a datasheet view but, in my opinion, list boxes are a lot easier to work with in terms of retrieving data and displaying it. On top of that you would only need *1* subform on your main form to handle the operation.

[RagonichaFulva]

I need to use a datasheet view in order to be able to use filters in it, etc.

Any chance of doing this with a listbox?

Thanks for your assistance.

[rpeare]

Depending on what your filters are yes, if you're just search for a text value in a specific field, yes, if you're searching for a string in any field yes, if you're searching for two different things in two different fields, yes, you but it just depends on how you set up your database. Basically the same query that is driving your datasheet view would drive the list box. Then in your ON CLICK event of the list box you can have your subform go to the selected record assuming you have a primary key for the items listed in the list box.

[RagonichaFulva]

Actually, I need the user to dynamically apply filters according to his/her criteria: less than, contains, etc. on one, two or every field. That's why I felt that I needed the datasheet view. I lock it in order to avoid editions, but I want to offer these possibilities.

[rpeare]

If your datasheet is showing you what you want now with the way you have it set up, you can show that exact same data in a list box, as long as the dataset has a primary key you can click on an item in a list box and have it bring up the detail associated with that record.

[RagonichaFulva]

Thanks for your feedback, but I have tried with a listbox after your suggestion and it lacks the possibility of using dynamic filters. I can achieve the detail associated with the record, but not a table view that users can manipulate.

I know this is possible to do it with a datasheet, because I have done it when the form is opened alone. the problem is when it's included in a navigation tab.

[rpeare]

You can apply any filter to a list box that you can to a datasheet view so I'm not sure what you mean when you say you can't apply a dynamic filter to a list box (I do it all the time) However, if you are switching the order of the columns in your datasheet view that is something that is definitely NOT easy to do with a list box.

That's what I mean when I ask if you have a specific reason you want to use a datasheet view. If you're moving columns around that's a functionality that's much easier to deal with in a datasheet view. Perhaps if you made a copy of your database and put in some garbage data then zipped it and uploaded it to this site it might be easier to get a grip on what you're doing but I think we're talking at cross purposes.

Are you moving your columns around in the datasheet view portion of your form. You said they both show the same data and you also said you were trying to use the current record of the datasheet view to select a record for the other subform which made me think you were just using it as a selection criteria and nothing else.

[RagonichaFulva]

You can apply any filter to a list box that you can to a datasheet view so I'm not sure what you mean when you say you can't apply a dynamic filter to a list box (I do it all the time) However, if you are switching the order of the columns in your datasheet view that is something that is definitely NOT easy to do with a list box.

That's what I mean when I ask if you have a specific reason you want to use a datasheet view. If you're moving columns around that's a functionality that's much easier to deal with in a datasheet view. Perhaps if you made a copy of your database and put in some garbage data then zipped it and uploaded it to this site it might be easier to get a grip on what you're doing but I think we're talking at cross purposes.

Are you moving your columns around in the datasheet view portion of your form. You said they both show the same data and you also said you were trying to use the current record of the datasheet view to select a record for the other subform which made me think you were just using it as a selection criteria and nothing else.

Hi,

It seems that the problem is that I don't know how to enable filtering in the listbox.

I can't send the database to you at present, because today I fell ill, but maybe you happen to have a small dummy example I could use to study while I am in bed?

Maybe a link to an article to learn how to work correctly with list boxes?

Thanks for your assistance and my apologies for being a nuisance.

[rpeare]

RagonichaFulva.zip

Short example of using list boxes to navigate to existing records and filtering list boxes based on user input

[RagonichaFulva]

Hello rpeare,

Thanks for the example.

I can see possibilities in it, although I might need to introduce multiple search boxes to combine different search criteria and i must foresee how many criteria the user might need to combine. Consequently, for this particular situation it's not exactly what I need, although it will be useful for other forms I am designing.

I'll continue investigating about how to reference from one subform to the other. I have several walkthroughs in mind. Not very elegant, but that might work.

Thanks for your assistance.

[rpeare]

I've seen a lot of solutions where people apply filters to sets of data using user input. I prefer building a SQL statement based on what the user enters. So for instance in your setup you could ask them for a criteria for each of the fields you intend them to be able to search for (or even allow them to choose the columns themselves) AND enter the criteria (or multiple criteria). After the statement is constructed, you can make the sql statement the row source, record source, control source of whatever the final product is (list box, combo box, form, record source, report, etc)