Can't add new record to form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JackieM - 12 Dec 2007 21:08 GMT

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!

Signature

JackieM

Reply to this Message

ArielZusya - 12 Dec 2007 21:39 GMT

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.

> 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
[quoted text clipped - 21 lines]
> Thanks for the help - this website has helped me through a lot of obstacles
> in the design of this db!

Reply to this Message

JackieM - 13 Dec 2007 12:42 GMT

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!

Signature

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
[quoted text clipped - 46 lines]
> > Thanks for the help - this website has helped me through a lot of obstacles
> > in the design of this db!

Reply to this Message

JackieM - 13 Dec 2007 13:07 GMT

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!

Signature

JackieM

Reply to this Message