add parameters in a form// edit table called with a query // general HELP

[brazoayeye]

Hi, i'm sorry for confused title but i really can't understand the most simple way to solve this problem. Let's start with a relation screen:



I have a main form based on the "item" table. In this mask i have a subform with "items need" fields; i linked master/slave fields like shown before and all works well. The problem comes when i try to let the user to link a need to a mind (using NM table).


Look at the image above: i want that the user can select a need from the first combobox (where he can see only the need written just above) and a mind from the second combobox (minds are built in another page, but this field is simple and working).

My first try is to set the query for the form this way

and the 1st combobox this way

linking master=item_id ,slave=i_id in the subform field.


Set this way the form selection (form query) is correct but i can sell all entry in the first combobox instead of only the selectedItem-related.
If i try to add a new field i have the error 3341.

I noticed i can add a "parameter" in the query but i can't find how to set the parameter in the form/subform proprieties to avoid the popup so i didn't try this way.

Thanks for help.

[June7]

What are 'mask' and 'submask' - do you mean 'form' and 'subform'?

Based on the relationship image, options for data entry:

1. single form bound to NM_links with comboboxes to select need and mind

2. main form bound to Minds and subform bound to NM_links with combobox to select need

3. main form bound to items_need and subform bound to NM_links with combobox to select mind

[brazoayeye]

What are 'mask' and 'submask' - do you mean 'form' and 'subform'?

You are right, i fixed it.

1. single form bound to NM_links with comboboxes to select need and mind

The user must know the item to understand the need, so how can i set?

2. main form bound to Minds and subform bound to NM_links with combobox to select need

Already done, but it's not what i'm trying to do now (easier to program, but harder to use)

3. main form bound to items_need and subform bound to NM_links with combobox to select mind

I prefer not to use this option to avoid form proliferation in the project.

[June7]

Not sure what you mean by 'know the item'.

The image does not look like the form/subform as suggested.

Perhaps you need form/subform/subsubform.

[brazoayeye]

Not sure what you mean by 'know the item'.

The user must read the item name to understand the need.

The image does not look like the form/subform as suggested.
Perhaps you need form/subform/subsubform.

The image description.png has 2 subforum, both in continuous form. Unfortunately i can't add a subsubForm in continuous form. (and how should i link master-slave fields?)

IS theren't a way to use the form's query as row description for comboboxes (or pass parameter through masks)?

[brazoayeye]

Ok, i partially solved:

add a text field, hidden, linked to i_id named i_id_field
in the need combobox query i add the parameter "i_id="&[i_id_field].Value
in the mind combobox, on event "on disabled" i add a macro with "Execute code" = needCombobox.Requery

Solved problem:
In existing fields i can change the need combobox only in current items' need.

Existing problems:
I can select the need combobox for newer entry (when there are no entry) only after the choice of the "mind combobox".
I have the Error 3341 when i select a need on new entry (WHY?)

Thanks

[brazoayeye]

If i set RecordsetType=Dynaset (Inconsistent Updates) i don't have the error 3341 but access try to make a new entry in the need table (even if the comobobox is selecting in NM_links an n_id existing in items_need). Is theren't a way to manual set how to insert new data?

[June7]

Sounds like you want a dependent (cascading) combobox - combobox with RowSource filtered by value in another control. Which you seem to have accomplished with the dynamic parameter.

But not understanding description of this latest issue. What is the exact error message?

If you want to provide db for analysis, follow instructions at bottom of my post.

[brazoayeye]

I reached the solution with a subsubform design
(red arrows shows master-slave parameter in the subform)


PS: to hide the left arrow i used a macro in "current" event with "if [CurrentRecord]=1", how can i check if the record is the last one?

The text of the erorr is similar to this:
http://www.solvusoft.com/it/errors/e...ss-error-3341/
(i think that the form query try to insert the data in the need table even if it's selected a row from the combobox)

[June7]

Issue resolved?

Hide what left arrow?

What do you mean by 'last' record?

[brazoayeye]

Issue dodged (i updated the thread title)

Looking at the previous img you can see the "Change record n_id" write pointing to the left and right arrow (used to change record in the first subbmask). To avoid problems i hide the left arrow when you are viewing the first record, you can move to the second one with the right arrow and so it will appear the left arrow to let you to come back to the first record... I want to make the right arrow invisible when the last record is selected (2of2 or 3of3).

[June7]

You mean these are command buttons for navigating records (next and previous)?

Test the form's RecordsetClone object to see if at beginning or end of dataset. Example from my code:

Private Sub btnNext_Click()
Me.btnViewLabData.SetFocus
Call ViewData("Next")
End Sub

Private Sub btnPrevious_Click()
Me.btnViewLabData.SetFocus
Call ViewData("Previous")
End Sub

Public Sub ViewData(strDirection)
'called by forms used to view and navigate records
If Not IsNull(Form_SampleInfo.tbxViewDataFormNAME) Then
    DoCmd.Close acForm, Form_SampleInfo.tbxViewDataFormNAME, acSaveNo
End If
With Form_SampleInfo
.RecordsetClone.Bookmark = .Bookmark
Select Case strDirection
    Case "Quit"
        DoCmd.Close acForm, "SampleInfo", acSaveNo
    Case "Next"
        .RecordsetClone.MoveNext
        If Not .RecordsetClone.EOF Then
            DoCmd.GoToRecord acForm, "SampleInfo", acNext
        Else
            .RecordsetClone.MoveLast
            MsgBox "Last record."
            .btnNext.Enabled = False
        End If
        .btnPrevious.Enabled = True
    Case "Previous"
        .RecordsetClone.MovePrevious
        If Not .RecordsetClone.BOF Then
            DoCmd.GoToRecord acForm, "SampleInfo", acPrevious
        Else
            .RecordsetClone.MoveFirst
            MsgBox "First record."
            .btnPrevious.Enabled = False
        End If
        .btnNext.Enabled = True
End Select
End With
End Sub