transfer key value to 2nd frm;open new blank form

[tomsoter]

I built a combobox in a main form whose selection would open one of ten secondary forms (not subforms). I need help. I'm using Access 2007. My db is a main child death identification table (TableMain: pk=MAIN_ID) and form (TableMainFrm) with name,DOB, address, etc. The combobox lists 10 (COD)Causes of Death such as (Infant Natural, Child Natural, Homicide, Suicide and 6 others. Each COD has a separate table and form such as InfNatTab (fk=InfNatID) and InfNatFrm, ChdNatTab (fk=ChdNatID) and ChdNatFrm, HomTab (fk=HomID) and HomFrm, etc. This would be just for entering NEW data. The main table combobox AfterUpdate code looks like this:

Private Sub Combo125_AfterUpdate()
Dim strFormName As String

Select Case Me.Combo125.Column(0)
Case "Natural_Infant"
strFormName = "InfNatFrm"


Case "Natural_Child"
strFormName = "ChdNatFrm"
Case "Homicide"
strFormName = "HomFrm"
Case "Suicide"
strFormName = "SuiFrm"
'etc
End Select

DoCmd.OpenForm "strFormName"
End Sub

This works fine but I must insert the same MAIN_ID pk value into its corresponding secondary InfNatID or ChdNatID or other secondary fk when it opens. I figure that I have to do this in order to create a separate data EDIT system consisting of a form based on a query of main & COD tables showing the complete linked main & COD data of each record just for editing.

I also need a button at the end of each COD form that will take user back to a new blank Main form to enter the next new main record.

This is also supposed to be a multi-user data entry system, maybe 3 users. Will Access's db splitter tool be enough or will more code be needed.

As you can see I'm a novice. If you have a better, simpler way, please let me know.

[jzwp11]

I think the problem lies more in your table structure. Why would you need a separate table for each cause of death? What would happen if you add a new cause of death? You would have to add a new corresponding table and form and then edit your code accordingly. This is a very inefficient approach. So can you provide us more detail on your table structure and why you have a table for each cause of death?

[pk2317]

First off, I agree that you probably don't need separate tables/forms for each type. But that's your decision. I can answer your question, it is possible to send a value to another form when you open it. The code for your first form would look something like this:

DoCmd.OpenForm "frmForm", , , , , , intInteger

Then, in the Form_Load section of your secondary form, you would want something like this:

Private Sub Form_Load()
    
    If Not IsNull(Me.OpenArgs) Then
        Me.txtTextBox = OpenArgs
    End If
 
End Sub

[tomsoter]

The COD's are general categories that won't change. But the variables for each COD are very different in form & content. I didn't think I should put all the various COD-specific variables in one very long table for all records.

I wanted a form that as easy to comprehend so I primarily used option buttons rather than list boxes (as well as other elements). The cost is that option buttons use more space and I didn't think there'd be room for subforms without needing to scroll the screen, which I'd like to avoid. And the examples I saw of subforms in Access Help just show subforms like tables displaying multiple rows of multiple records. I'd want subforms like the main form with only one record showing at a time. I'll take another look at subforms to see if there is an advantage.

Thanks for the code. I'll give it a try.

[jzwp11]

Looking beyond just data entry forms, I believe that your current table structure will force you to write a query for each type of COD when you start trying to do data analysis at some point rather than just using 1 query and filtering by COD. Reporting also might be cumbersome for the same reason. Might I suggest the following table structure:

A table to hold the causes of death:
tblCOD
-pkCODID primary key autonumber field
-txtCOD text field holding the cause of death


Assuming that some of the variables you mention might be shared for multiple causes of death, I would have a table that holds the variables:

tblVariables
pkVarID primary key, autonumber
txtVariable text field that holds the description of the variable

Now, a table that relates specific variables applicable to each COD

tblCODVariables
-pkCODVarID primary key, autonumber
-fkCODID foreign key to tblCOD
-fkVarID foreign key to tblVariables

You would then use your main table that holds the particulars of the person who died and then a table joined to that main table that holds the details.


tblMain
-pkMainID primay key, autonumber
-fkCODID foreign key to tblCOD
other fields

The detail table would link back to tblMain, but will also be populated with the corresponding variables associated with the specific COD. You would then have a couple of fields to capture the variable data. I included a numeric field to capture your option box type data and then a text field if you want to capture free-form text variable data.

tblMainCODVariableDetail
-pkMainCODVarDetailID primary key, autonumber
-fkMainID foreign key to tblMain
-fkCODVarID foreign key to tblCODVariables
-numericresponsefield
-txtresponsefield

Having a normalized table structure is more important than the forms, but I think there should be a way to preserve your form rules even with the table structure I have presented.