1-Many with multiple subforms

[Bensta]

Here is what I want to accomplish.
I have two tables: Spec and SpecDetail.
On my Main Form I have a combo box from which user selects an Item # and the corresponding info (coming from Specdetail table) should display on the subforms (Total 3)that are on Tabs. The reason I used tabs and subforms is because I have a lot of fields defined in SpecDetail. I have been having problems with access crashing and also unable to add new records and keep the subforms synchronized.
Example: Item1 can have many history.
Can anyone help me with this please?
I would really appreciate it.
Bensta

 

[ZmrAbdulla]

See Northwind sample database that ships with Access (can be accessed from the help menu)
Open "Orders" form that has a sub form called "Orders Subform"


________________________________________________________
Zameer Abdulla
Help to find Missing people
There’s a world of difference between editorials and advertorials

 

[Bensta]

Actually one of my questions would be: Is it possible to have multiple subforms from the same data source linked to a MasterForm? My subsforms are part of the many relationship side.
If so, can I still directly update the subforms and keep them synchronized?
Thank you
Benstsa

 

[ZmrAbdulla]

You may able to set it.. But most of the time you will get error.
If you need to use fields from a single source then don't use subforms in tabs; use tabs in a single subfrom. I hope you understand what I mean.

________________________________________________________
Zameer Abdulla
Help to find Missing people
There’s a world of difference between editorials and advertorials

 

[TheAceMan1]

How are ya Bensta . . . . .

Better for you would be [blue]one subform[/blue] with the [blue]Tab Control[/blue], and the [blue]approriate controls placed on each tab![/blue]

See Ya! . . . . . .

 

[Bensta]

I am doing fine TheAceMan1. Thank you. How about yourself?
All the fields are coming from the same table. THere too many to keep on one subform. The ideal think is to put them on tabs. That way, it would be nice for users to work with. It keeps to screen clean.
What if I group some of them on one subform and the other on other tabs. HOw can I make sure that the other fields are kept synchronized with the fields on the subform? Again all those field are from the same datasource (table).
On the master form I want to have one combo box ,from which users can select a Item Number and the correspoding historical data will display on the sub forms/Tabs. Also users should be able to add a new entery for the each given Item number selected from the combo box on the masterform
I am sorry I just can't seem to think a simple way to do it.
Thank you TheAceMan1. I hope I was not too confusing

 

[PHV]

All the fields are coming from the same table. THere too many to keep on one subform
Seems like a data design issue, have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

[blue]The ideal think is to put them on tabs . . .[/blue]

This is one of the best uses for a Tab Control, espcially since you can have just about as many tabs as you desire.

I have a form for a Legal DB that has one subform with a Tab Control. There 241 textboxes in the Tabs!

Did you really read my post!

See Ya! . . . . . .

 

[Bensta]

No, I don;t think there is a database design issue. My tables meet the First NOrmal Form. IT is pretty much well normalized. I am One to Many relationship defined my tables (the master Table and the Detail Table).
It just happens that the Detail Table(which actually holds the historical data for the primary key form the master table) has too many fields. I guess from a User INterface Design point of view, how could I represent my fields on the Form?

 

[ZmrAbdulla]

You need to work a little manually on the db.
[ul]
[li] Create a subform with all the fields from the record source[/li]
[li] Place a tabcontrol on the subform [/li]
[li] Right-click on each control and do cut & paste all of them on to the desired tab [/li]
[li] Resize the form's width and height. [/li]
[/ul]

________________________________________________________
Zameer Abdulla
Help to find Missing people
There’s a world of difference between editorials and advertorials

 

[Bensta]

Thank you Guys very much. I will try this approach and get back to you. You guys are wondeful
Than you
Bensta

 

[Bensta]

I created the sub-from with all the fields from the Child table and cut and paste them into the tabs and linked it to the Main Form. It seems to work like charme.
You all deserve a big STAR !!!!
One more question: Is it possible to create a button on the main form when click, it will add a new record to the subform. That way, users won;t have to user the navigation button on the sub form to add a new record.
Thank you very much
Bensta

 

[ZmrAbdulla]

Answered the same at thread702-1121516 by myself



________________________________________________________
Zameer Abdulla
Help to find Missing people
There’s a world of difference between editorials and advertorials

 

[TheAceMan1]

Bensta . . . . .

Perhaps this ([blue]you![/blue] substitute proper names/values in [purple]purple[/purple]):

[blue]   Dim ctl As Control, frm As Form, subName As String
      
   If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord
   
   If Me.NewRecord Then
      MsgBox "Add a record to the MainForm first!"
   Else
      subName = "[purple][b]YourSubformName[/b][/purple]"
      Set ctl = Me(subName)
      Set frm = Me(subName).Form
      
      ctl.SetFocus
      
      If Not frm.NewRecord Then
         DoCmd.RunCommand acCmdRecordsGoToNew
      End If
      
      Set ctl = Nothing
      Set frm = Nothing
   End If[/blue]

See Ya! . . . . . .

 

[Bensta]

TheAceMan1 thank you so much for the code.
The problem I have now is that when I try to add a new record to subform(that is on the many side of the relationship) the actually PartID (foreign key) does not get stored. It is storing zeros.
One partid can have many records. Thus everytime I add a new record I want to store the PartID number in the History table. I just can;t get to work. It keep puting zeros instead of the actual partid number.
Any ideas?
Thank you

 

[TheAceMan1]

Bensta . . . .

Hmmmm . . . a little confusing this. In your prior post you said:

[blue]I created the sub-from with all the fields from the Child table and cut and paste them into the tabs and linked it to the Main Form. [purple]It seems to work like charme.[/purple][/blue]

Which is indicitive of proper linking. [purple]The problem you quote with PartID is the reverse![/purple]

Post back Key fields in the following format for each table:

[blue][tt]TableName
---------
PKname as DataType
FKname as DataType - many side to - TableName.PKname[/tt][/blue]

Example:

[blue][tt]Spec
----
SpecID as Autoumber

SpecDetail
----------
DetailID as Autonumber
SpecID as LongInteger - manyside to Spec.SpecID[/tt][/blue]

Also post back the Master/Child Link properties of the subform.

Special Note: If the forms are linked properly, as soon as you [blue]edit a new record in the mainform[/blue], [purple]PartID[/purple] should immediately update!

See Ya! . . . . . .

 

[Bensta]

Sorry TheAceMan1 for the confusion
It meant to say SPecID.
Spec
----
SpecID as Autoumber

SpecDetail
----------
DetailID as Autonumber
SpecID as LongInteger - manyside to Spec.SpecID

the Master/Child Link properties of the subform.
Master/Child SpecID -> SpecID

There is another strange thing happening here. It seems to be working now, but when I convert the database from 2000 to 2002 this problems seems to happen each time. Are there any version issues between Access 2000 and 2002 that's causing the records from the many side of the relationship not save properly when using subforms?

2)As far using the application in a multi-user enviroment. The backend database resides somewhere on the network. Are there any steps to take or should take as far the way I should design the application such as recordset object. Ex should I use recordset object to update my tables, etc?
Thank you so much
Bensta

 

[Bensta]

I am really hoping that someone will kind enough to help me with this problem.
Now every time I select a record from the combo box the child records in sub-form(which are grouped into tabs) are not filtering correctly. I have to select the same record many time for the corresponding child records to show up/refresh. It is acting really strange. This is the code I am using to display records based on the value selected in the combo box.
Private Sub cboRollID_AfterUpdate()

' Return record(s) that match value selected in RollID combo box.


strSQL = "Select * from tblRollMaintenance where"
strSQL = strSQL & " RollID=" & Me![cboRollID]
Me!Form1.Form.RecordSource = strSQL
'DoCmd.Requery

Me.lblRollNumber.Caption = NullToZero(Me.cboRollID.Column(1))
Thank you in advance.
Bensta