2 tables=2 forms but need one to open to specific record of the ma

[Matt73]

I have a form with personal information on it. I'm trying to create a command
button which will open my other form which will keep notes. Every time I try
the "Open Form" command button I try and select "Match fields to show
specific data to display" and my Notes form shows my fields but my main form
shows nothing. I guess in short its all I want to do is open a record on an
individual, click a button to open the other form and enter notes in the
individuals respective form.

 

[Jeanette Cunningham]

Hi Matt,
the forms wizard will create a form and subform for you.
You won't even need a button to open the form for notes.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Bill]

Assuming you have a normalized database, i.e., in this
case there would be a relationship between the records
in your 1st form and the related table. If so, in your first
form, capture the RecordID presumably the point of
relationship and open the 2nd form passing the RecordID
as either a argument or as a filter expression that the
2nd form will have to filter or otherwise manipulate
such that the desired record for the 2nd form will
display as desired.

If this doesn't match what you're attempting to accomplish,
post back with the specifics of the relationship between
the two tables.

Bill

 

[Bill]

Yes, Jeanette's suggestion is simpler, provided you
have a normalized database.
Bill

 

[Matt73]

I'm trying to keep each window to a minimum. I already have a lot of
information on my main form that is why I was trying to create another form
for notes. I'm just trying to make it so when I click a button it will
automatically open my notes form and display the field I can enter notes in
along with the individuals name which will automatically be there when the
notes form opens.

 

[Bill]

Matt,
Think pop-up when you think about a subform.
Bill

 

[Matt73]

O.k. lets see if I can explain this a little better. I have a table that
tracks personal information and a table that I would like to store notes in.
They both have a Personal_ID # assigned and both are primary keys. The main
table I have fields such as Last_Name, First_Name, and Personal_ID. My Notes
table has only two fields Notes, and Personal_ID. I would like to be able be
in my main form, find an individual, click a button and enter notes that are
specific to my main form. For example, I look up the record that holds "John
Doe's" information, click on the notes button and enter, "02 Jan 09 Did not
show up for work". Then a couple of days later I need to enter another note,
so I would find his specific record in the main page and click on the notes
to enter the new note but at the same time the note I entered a few days
earlier would be listed there. I plan on using a "Memo" field for the notes.

 

[Bill]

Okay, you have a one-to-one relationship between your
personnel record and the associated notes table. Based
on your description, my first question is why the second
table at all. Just include the notes field as part of the
personnel record, but exclude it from display on your
primary form. Then, when you pop-up a sub-form, you
can simply display the note field in the ONLY text box
defined in the sub-form whose source is the current (got
the focus) record.

Bill
It's late in California, so I'll look in again in the AM.

 

[Rick Brandt]

O.k. lets see if I can explain this a little better. I have a table that
tracks personal information and a table that I would like to store notes
in. They both have a Personal_ID # assigned and both are primary keys.
The main table I have fields such as Last_Name, First_Name, and
Personal_ID. My Notes table has only two fields Notes, and Personal_ID.
I would like to be able be in my main form, find an individual, click a
button and enter notes that are specific to my main form. For example, I
look up the record that holds "John Doe's" information, click on the
notes button and enter, "02 Jan 09 Did not show up for work". Then a
couple of days later I need to enter another note, so I would find his
specific record in the main page and click on the notes to enter the new
note but at the same time the note I entered a few days earlier would be
listed there. I plan on using a "Memo" field for the notes.

Using one memo field in a single record for all notes per-person is not a
good idea. Give the notes table a surrogate PK like an AutoNumber and
that will allow multiple notes per person. That also allows you to
automatically date-stamp each note with no added effort by adding a date/
time field defaulted to Now().

The pop-up can be made to work by using the WHERE clause of the OpenForm
command to filter on the specified ID (the button wizard will write that
code for you) and by setting the default value of the ID to reference the
parent form. Simpler though would be to just use a subform. Add a
TabControl and your form-space issue is solved.

 

[Bill]

Matt,
Rick is right about how you structure your tables
and how best to keep a "set" of notes for each
employee. With a one-to-many relationship between
an employee and the collection of accumulative notes,
you can easily refer to the set, time-stamped as Rick
suggested.
Bill