Different subforms by field value (1 Viewer)

[Lanser]

Hi Searched but cant find the correct answer
I have a main form, that contains the product details and two subforms that show targets and results.
I have half a dozen different porduct types that have different criteria and rather than try and cram all the criteria on one subform I want to show different target/results subforms with limited criteria depending on the Product group in the selected record.

So when the operator opens the Main form the subforms adjust to only show the relevent data.

 

[MajP]

In the main forms OnCurrentEvent do something like

select case ProductGroup
  dim subFrm as access.form
  set subFrm = me.YourSubFormControlName
  Case 1
    subFrm.sourceObject = "subFrm1"
  case 2
    subFrm.sourceobject = "subFrm2"
...
end select

 

[Lanser]

Apologies I forgot to say thanks your solution works although it has created a second problem
I an using some code (P_NewRec)i n the subforms module attached to a button on the main form to transfer data from unbound controls into the subform but now I get a runtime error 2105 you can't go to the specified record.

I have tried a few different ways to reference the selected subform but then get a Method or data member not found error

Private Sub Command50_Click()

    If frmRes_sub("uDate") > 0 Then  ' Checks whether any data awaiting entry
        
        frmRes_sub.SetFocus
            'If Me.frmRes_sub.SourceObject = "frmRes_672" Then
            'frmRes_sub.frmRes_672.Form.P_NewRec
            'frmRes_sub.frmRes_672.P_NewRec
        
        frmRes_sub.Form.P_NewRec    ' Procedure for moving contents of unbound text boxes into the data table.
            'End If
    Else
        MsgBox "No Fresh Data"
    End If
    
End Sub

 

[Pat Hartman]

You cannot reference a subform that isn't loaded.

Why are you doing data entry in the main form that is being moved to the subform? Just do the data entry directly into the subform. I don't even see a button that you might be clicking.

And finally, ALWAYS give a control a proper name BEFORE you add code to any of its events.

You will probably need to upload a copy of the database if we can't see the issue in the picture.

 

[Lanser]

Sorry for delay in responding I have been away.
The command button is on the main form but in order to keep the most recent data at the top of the subform I am using unbound controls in the header of the subform.
I've attached the database fe and be with some sample data

 

[MarkK]

It's hard to figure out what you are trying to do here. One thing though, in respect to not being able to go the specified record: the query driving the frmRes_xxx subforms, qryResults, is not updatable. As a result, if you try to add a record, as you seem to be trying to do with the P_NewRec routines, they will fail on that score.

Typically you base a single form or subform on a single table. Joins between your tables are then modelled in the user interface by the relationships between data in the parent form and the subform. If one product row has many result rows, for instance, the parent form shows the single product row, and the subform shows the many related result rows. Your approach does not seem to follow this pattern, so you are fighting against a poor data model.

I think you need to back up a bit and get a better handle on how the data is structured. Don't do much work on the user interface until you have a working data model. The data model should drive the UI.

To get help with that, maybe start a new thread and describe in detail what needs happen with the data.

All the best,

 

[Lanser]

Trying to make the UI as familair as poss to the users, currently they use pen/paper filing card system. with different cards for different product groups. I was trying to avoid having seperate forms for each group

It was working as expected with the single subform but adding the dynamic subform selection based on product group that MajP suggested and the P_NewRec sub just gives the can't go to current record error

 

[Gasman]

Trying to make the UI as familair as poss to the users, currently they use pen/paper filing card system. with different cards for different product groups. I was trying to avoid having seperate forms for each group

Surely that is closer to what they are used to at present, having separate forms?

 

[Lanser]

Surely that is closer to what they are used to at present, having separate forms?

Almost yes, but at the moment they have 3k cards all in alphabetical order by product code, so they just look for the code it doesnt matter what category it is. The cards are set up by a different team. I only have two categories set up in the database so far but to have 7 different ones will confuse them as they'll first have to look up what category the product is in to select the correct form.

 

[gemma-the-husky]

You can have character searches. Enter search string "Green Widget" and find rows that contain "green" and "widget", and let the user choose from the selection. You can't index this but depending how few records you have to search it might be the easiest solution. 3000 isn't many to do a full text search.

I must say I have no idea how web browsers do stuff like this with millions of possibilities.

 

[Lanser]

Thanks Gemma, but the searching is ok was hoping to get my little bit of code to work with adding a new result to the dynamically loaded subform.
Maybe I need to reconsider my original idea of showing/hiding controls on just one subform depending on category

 

[Pat Hartman]

You can control the sort order of the subform so the newest record is on top. Just sort descending by the autonumber.
If you don't want to explain to the user how to get to the "new" record, add a button to move to it. Then in the After Insert event of the subform, requery the subform to move the new record to the top.

 

[Lanser]

Hi Pat,
A variation of what I have aleady except at the moment I am using a button and the P_NewRec sub with unbound controls in the subform header to add data into the subform and then requery to get the latest record at the top.
The issue is no matter how I try to call the P_NewRec sub with the dynamically loaded subform it cant go to the record, but if I have a fixed subform it will