Re: new recordset entry on top

One of the really nice features of the Subform Control is that it
automatically populates the table in the subform with the appropriate key
value from the Mainform (assuming the key field is the field used in the
Link Parent/Child properties. Since you are using Chart Number as your
linking field, you shouldn't need to worry about assigning it. In fact, you
don't even need to show that field in the unbound fields at the top. It
will be automatically assigned.

If you really want to, you can display it as you're doing, but you should
NOT write this value to the bound foreign key field in the subform as you
are doing with the other fields. As I said, it will get assigned
automatically.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

"Kathy" <Kathy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3DA78360-5052-4802-B8A8-B11599A2AA23@xxxxxxxxxxxxxxxx
Hello again...thank you for answering as I am still hacking away...I am
pretty "green" in this area, so I am not confident if I am doing things
correctly, but anyway, here is the latest...
The Main form and subform are linked via the Chart Number. The subform is
the form with the record on top and has the Add/New button. All is working
well. I would like that the chart number from the main form be
automatically
added to the unbound Chart Number control in the subform. So I guessed
around
and added this code to the Add/New button code, right after Me.Requery:

Me.NewChartNumber = Forms!frmAnemiaManagementMain.Form!
txtChartNumber

So far this is working, each time the button is clicked, the new record is
added, then clears the unbound controls, then fills the current chart
number
in the first unbound control.
The dangerous problem is when the user moves to the next patient record,
as
the chart number doesn't update to the new record. So if the user doesn't
change the chart number, it will be saved with the previous record. Thus I
have been trying to work with the Main form with the OnCurrent event. But
so
far, no luck. I hope I am not totally out in left field, but your help is
again greatly appreciated.
--
Kbelo


"Roger Carlson" wrote:

I don't think really want to do this. If chart number is the field
linking
the form and subform (Parent/Child link properties), then it will
automatically be added by the subform control and you can just use a
bound
form (locked). If you are linking on some other field, then you will
just
be storing redundant information in your medication table. You can
easily
get the chart number any time by joining the two tables.

Have I misunderstood?


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



"Kathy" <Kathy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A758EC33-D31E-428D-B337-FAEF4CB198DD@xxxxxxxxxxxxxxxx
Thank you so much again...I am a big chicken and you are right that if
the
end-users made a mistake and wanted to change the medication, then it
could
get "ugly"...I am keeping the combo boxes.
I am so close to finishing this form, I hope it will be OK to ask
another
question. Here goes: The form has a main form (patient information) and
a
subform (the medication record which now has the record on top). I
would
like
that the patient chart number from the main form fills in the unbound
text
box on the medication record as the end-user moves from chart to chart.
Thus
when they entered the next patient record, the chart number would
automatically show up in the unbound chart number box for the
medication
record, then when the Add/New button is clicked, the information goes
into
the detail section and the unbound controls are nulled. Then moves to
the
next patient and their chart number would automatically fill in....and
etc.
I have tried all kinds of combinations of code, but either nothing
happens
or it says I cannot enter a value in that field....and so on. Anyway, I
am
up
a creek again and any help is gratefully accepted.
--
Kbelo


"Roger Carlson" wrote:

No, this is not a dumb question. It's actually fascinating.

Yes, it is possible to do what you want, but you have to be *careful*.
You
should ONLY do it if your detail section is *locked*, that is if the
records
cannot me changed. I'll explain why in a minute.

First of all, here's how to do it:

Instead of having just your main table as the record source for your
form,
create a query that joins the main table with the lookup table(s),
displaying both the medication ID number and the medication name in
the
field list. (Make sure you add the ID field from the *main table* and
not
from the linked table.) Leave your unbound combos as they are. Now,
instead of displaying the medication ID in the field of the form, just
display the medication name field. That ought to do it.

Now, here's why you only want to do this if the form records are
locked.
If
you go back and change a medication name, it won't be changed in the
main
table, but it will be changed in the lookup table! That means that
every
single instance of that medication ID will be changed to the new name.
Not
what you want, probably.

So if you want your user to be able to change the selection (say if
they
made a mistake and chose the wrong medication), you should leave them
as
combo boxes.

Does that help?
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

"Kathy" <Kathy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:66A2DA74-74BC-4EA2-A7A8-9F404AABE07A@xxxxxxxxxxxxxxxx
I'm sorry that I probably did not give a very good explanation. I am
pretty
sure that I am using the correct combo-box construction and it does
work
perfectly in the "unbound controls Record on Top" model. Also in the
detail
section when the AddNew button is clicked, the corresponding bound
control
shows the correct Medication name as long as I keep the control as a
combo
box. Since once the medication is given, there would no longer be
any
choice
of medication, I wanted to make the corresponding box just show the
Medication name that was selected in the unbound control. However
when
I
changed it to a plain text box, then I only get the value or
Medication
ID.
Is there a way to change it to text or should I just leave it as a
combo
box?
Sorry if this is really dumb. And again your help is much
appreciated.
--
Kbelo


"Roger Carlson" wrote:

I don't have a lot of experience with lookup fields. We (MVPs and
other
experienced Access users) discourage their use. The reason is that
lookup
fields mask what is really happening in the table. See this
http://www.mvps.org/access/lookupfields.htm for more information
about
the
evils of lookup fields.

A better way would be get rid of the lookup field property (in
table
design
view, change Lookup to text) and then use a combo box on your form
to
create
the lookup. This would work with my sample.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


"Kathy" <Kathy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:83DE7D67-A41D-431F-956E-D87BF2541DED@xxxxxxxxxxxxxxxx
Yes. I have two tables...one tblMedication and the other tblDoses
which
are
linked by the Medication ID. Thank you for your response.
--
Kbelo


"Roger Carlson" wrote:

Are you using Lookup fields in your table?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

"Kathy" <Kathy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7C96FBFB-5E8F-4C75-BD9B-1971549D32E1@xxxxxxxxxxxxxxxx
Hello again...I did download from your library and reconstruct
my
form
according to that model...thank you for your help!...But I
have
one
problem
that I have not been able to correct. In the form header that
has
the
unbound
controls, I have two combo boxes that are linked so that the
selection
in
the
first box determines the selection in the second box.
Everything
works
beautifully except that when I add the new record, the record
that
appears
in
the detail section shows the ID of the combo box 1 selection
and
not
the
name. (In this example, the first combo box is a list of
Medications
and
the
second combo is a list of standard dosages. So when a
medication
is
selected,
then only standard doses for this medication shows in the
second
combo.
When
saved, the medication ID shows up and not the name of the
medication.)
Is
there some way I can correct this, so the name is shown?
Again,
many
thanks,
Kathy
--
Kbelo


"Roger Carlson" wrote:

On my website (www.rogersaccesslibrary.com), is a small
Access
database
sample called "ContinuousFormWithNewRecordOnTop.mdb" which
illustrates
how
to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


"Kathy" <Kathy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3556EA9B-BD0C-441F-83D8-7DC42613AA25@xxxxxxxxxxxxxxxx
Hello, I am trying to have a new recordset on a subform
open
at
the
top,
instead of the bottom. I noticed a similiar posting and the
reply
suggested
using a one-line subform. If this is the best way to
accomplish
this
design,
could you please offer a little more information about how
to
do
it?
Many
thanks.
--
Kbelo

















.

Quantcast