I have 2 tables that are linked.  One table holds the Main Data - a 
collection of cost totals, the second holds additional data about one of the 
items (a breakdown of one of the cost totals) from the Main Data.  

I created the main form, then I created the second form and used a command 
button to open the second form.  On historical data this worked perfect.  

Now I tried to add a new record starting with the main form.  When I hit the 
command button to open the linked form, the form would not open.  After that, 
I then realized that I can't even open the second form by itself and add a 
new record - the button is "grayed" out.  I can still open the table and add 
a new record, but not using the form.

So, I actually have 2 problems.  
I cannot add a new record to the second form I created.  
When I add a new record to the main form, I need the linked form to open in 
"add" mode for new records, but for existing records, I need it to open to 
the current linked record.

Unfortunately, I don't know how to use code - so if that is my solution, 
I'll need the "dummies" version on exactly how to do this, if anyone is 
willing to put in the effort to explain it to me.

Thanks for the help - this website has helped me through a lot of obstacles 
in the design of this db!

-- 
JackieM

If I follow it appears as though you have a table with a one-to-many 
relationship between records stored in that table and records stored in a 
second table.  In other words for every record in your main table there may 
be a number of records in your second table.  If that's the case, instead of 
the button, you might try playing with a subform command tool.  the subform 
will allow you to place your second form right on the first form and maintain 
the link between them (regardless of whether this is a new record or an old 
record).

To illustrate, if the main table contains a list of teachers:

John
Jane
Bob

and the second table contains a list of the student initials in each 
teacher's class:

John has AB, DS, CF, GD, KA
Jane has RT, PP, SM, NT
Bob has ML, RF, BC

a form with a subform would show Jane in the form and RT, PP, SM, and NT in 
the subform. Does that make sense?  I'm happy to help further. Let me know.


"JackieM" wrote:

> I have 2 tables that are linked.  One table holds the Main Data - a 
> collection of cost totals, the second holds additional data about one of the 
> items (a breakdown of one of the cost totals) from the Main Data.  
> 
> I created the main form, then I created the second form and used a command 
> button to open the second form.  On historical data this worked perfect.  
> 
> Now I tried to add a new record starting with the main form.  When I hit the 
> command button to open the linked form, the form would not open.  After that, 
> I then realized that I can't even open the second form by itself and add a 
> new record - the button is "grayed" out.  I can still open the table and add 
> a new record, but not using the form.
> 
> So, I actually have 2 problems.  
> I cannot add a new record to the second form I created.  
> When I add a new record to the main form, I need the linked form to open in 
> "add" mode for new records, but for existing records, I need it to open to 
> the current linked record.
> 
> Unfortunately, I don't know how to use code - so if that is my solution, 
> I'll need the "dummies" version on exactly how to do this, if anyone is 
> willing to put in the effort to explain it to me.
> 
> Thanks for the help - this website has helped me through a lot of obstacles 
> in the design of this db!
> 
> -- 
> JackieM

Thank you for your time in responding to me.  I have used subforms before, 
but in this instance it would be too much on one screen - I have about 5 of 
these linked forms.  So is it possible for this to work properly the way I am 
trying to do this?  

The relationships between the tables are actually set up as one to one.  We 
have one unique job number - I'm using this as the linked field.  The main 
form holds the totals for all costs on the job.  The "linked" forms are 
breakdowns of 5 of the major costs.  

Is it possible that because the relationship is one-to-one, that is why I 
cannot add a new record in the linked table?

Please don't tell me I set this all up incorrectly.  I thought I was getting 
very near to completion on this database!
-- 
JackieM


"ArielZusya" wrote:

> If I follow it appears as though you have a table with a one-to-many 
> relationship between records stored in that table and records stored in a 
> second table.  In other words for every record in your main table there may 
> be a number of records in your second table.  If that's the case, instead of 
> the button, you might try playing with a subform command tool.  the subform 
> will allow you to place your second form right on the first form and maintain 
> the link between them (regardless of whether this is a new record or an old 
> record).
> 
> To illustrate, if the main table contains a list of teachers:
> 
> John
> Jane
> Bob
> 
> and the second table contains a list of the student initials in each 
> teacher's class:
> 
> John has AB, DS, CF, GD, KA
> Jane has RT, PP, SM, NT
> Bob has ML, RF, BC
> 
> a form with a subform would show Jane in the form and RT, PP, SM, and NT in 
> the subform. Does that make sense?  I'm happy to help further. Let me know.
> 
> 
> "JackieM" wrote:
> 
> > I have 2 tables that are linked.  One table holds the Main Data - a 
> > collection of cost totals, the second holds additional data about one of the 
> > items (a breakdown of one of the cost totals) from the Main Data.  
> > 
> > I created the main form, then I created the second form and used a command 
> > button to open the second form.  On historical data this worked perfect.  
> > 
> > Now I tried to add a new record starting with the main form.  When I hit the 
> > command button to open the linked form, the form would not open.  After that, 
> > I then realized that I can't even open the second form by itself and add a 
> > new record - the button is "grayed" out.  I can still open the table and add 
> > a new record, but not using the form.
> > 
> > So, I actually have 2 problems.  
> > I cannot add a new record to the second form I created.  
> > When I add a new record to the main form, I need the linked form to open in 
> > "add" mode for new records, but for existing records, I need it to open to 
> > the current linked record.
> > 
> > Unfortunately, I don't know how to use code - so if that is my solution, 
> > I'll need the "dummies" version on exactly how to do this, if anyone is 
> > willing to put in the effort to explain it to me.
> > 
> > Thanks for the help - this website has helped me through a lot of obstacles 
> > in the design of this db!
> > 
> > -- 
> > JackieM

Maybe this will help:

Private Sub Open_DBMat_Click()
On Error GoTo Err_Open_DBMat_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Database Materials"
    
    stLinkCriteria = "[Database Materials_Proposal #]=" & "'" & Me![Proposal 
#] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Open_DBMat_Click:
    Exit Sub

Err_Open_DBMat_Click:
    MsgBox Err.Description
    Resume Exit_Open_DBMat_Click
    
End Sub

I think all I need is a command line that says if there is no matching 
record, go to add a new record.  But being that I have never written code, I 
have no idea what to write, where to write it, etc.

Thank you for anyone will to put in the time to help me!

-- 
JackieM


"ArielZusya" wrote:

> If I follow it appears as though you have a table with a one-to-many 
> relationship between records stored in that table and records stored in a 
> second table.  In other words for every record in your main table there may 
> be a number of records in your second table.  If that's the case, instead of 
> the button, you might try playing with a subform command tool.  the subform 
> will allow you to place your second form right on the first form and maintain 
> the link between them (regardless of whether this is a new record or an old 
> record).
> 
> To illustrate, if the main table contains a list of teachers:
> 
> John
> Jane
> Bob
> 
> and the second table contains a list of the student initials in each 
> teacher's class:
> 
> John has AB, DS, CF, GD, KA
> Jane has RT, PP, SM, NT
> Bob has ML, RF, BC
> 
> a form with a subform would show Jane in the form and RT, PP, SM, and NT in 
> the subform. Does that make sense?  I'm happy to help further. Let me know.
> 
> 
> "JackieM" wrote:
> 
> > I have 2 tables that are linked.  One table holds the Main Data - a 
> > collection of cost totals, the second holds additional data about one of the 
> > items (a breakdown of one of the cost totals) from the Main Data.  
> > 
> > I created the main form, then I created the second form and used a command 
> > button to open the second form.  On historical data this worked perfect.  
> > 
> > Now I tried to add a new record starting with the main form.  When I hit the 
> > command button to open the linked form, the form would not open.  After that, 
> > I then realized that I can't even open the second form by itself and add a 
> > new record - the button is "grayed" out.  I can still open the table and add 
> > a new record, but not using the form.
> > 
> > So, I actually have 2 problems.  
> > I cannot add a new record to the second form I created.  
> > When I add a new record to the main form, I need the linked form to open in 
> > "add" mode for new records, but for existing records, I need it to open to 
> > the current linked record.
> > 
> > Unfortunately, I don't know how to use code - so if that is my solution, 
> > I'll need the "dummies" version on exactly how to do this, if anyone is 
> > willing to put in the effort to explain it to me.
> > 
> > Thanks for the help - this website has helped me through a lot of obstacles 
> > in the design of this db!
> > 
> > -- 
> > JackieM