Exactly. I'd see it as a 'New' button which would add the record and take
you to it.
(...and what were you using to trigger the code before?)
well let's start off by seeing how much 'better' it's going to be, however
better is defined.
regarding the existence of a button. it doesn't, however if it'll get the
showboat moving i could create one -- which answers your question wrt what
other code there IS on it -- NONE.
since i'm writing this from home and w/o the aid and comfort of the
database
itself, i'll have to begin it no sooner than manyana morning. but i can
see
that even now i'm unclear as to where you would have this concoction of
ours
belong. i mean you asked if i had a button established .... so as to wrap
it
up in an event trigger/property?
-ted
:
I said that it was a better approach... not that it was easier...
Could I just check something... you have a button that you're clicking
on a
form that is going to be triggering this routine? Does that button also
have any other code included? Anyway...
Basically, the SQL you are going to want to run is going to be of the
form...
INSERT INTO tblTarget ( [Patient Number], [Lesion Number] )
SELECT 1 AS PN, 2 AS LN;
....except for two things. The first one you already have - tblTarget
is
indeed a surrogate for your actual table name. As your table name
contains
spaces it will also need some additional square brackets... so it'd look
like...
INSERT INTO [Lesions: Non-Target] ( [Patient Number], [Lesion Number] )
SELECT 1 AS PN, 2 AS LN;
The second thing is that if you run that SQL it's going to add a new
record
into the table with 1 as the patient number and 2 as the lesion
number...
which isn't what you want. You therefore need to build a string similar
to
that one, but with the actual values you want instead of the 1 and 2.
More
comments on that below.
The last point is that isn't an SQL statement that you save. It's going
to
be built as and when it's needed, with the correct values included, and
then
run, and then discarded.
'Set up variables.
'sql is going to be the command to be run
'pn is going to be the patient number you want to add
'ln is going to be the lesion number you want to add
dim sql as string
dim pn as long
dim ln as long
'Figure out the required values
'pn=.... 'whatever.. set it to the patient number you want
'from looking at your other posts it will be something like...
pn=[Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]
'ln=.... 'whatever... set it to the lesion number you want
'again, from looking at your other posts, something like...
ln=Nz(DMax("[Lesion Number]","[Lesions: Non-Target]","[Patient Number] =
"
& [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]),0) + 1
You could, at this point, set a new breakpoint so that you can check
that
the correct values for pn and ln are being returned, or just drop in a
msgbox pn and/or msgbox ln if you prefer. It's definitely worth doing
that
because if it isn't returning the correct values then the remaining code
is
certainly not going to do what you want it to.
Next, you need to create the SQL statement - but containing the values
you
want
That's done just by joining bits of text together....
sql="INSERT INTO [Lesions: Non-Target] ( [Patient Number], [Lesion
Number] )
"& _
"SELECT "&cstr(pn)&" AS PN, "&cstr(ln)&" AS LN;"
Again, you should check here on what the sql string looks like. It
should
be exactly the same as the one with the 1 and 2 in from before, but with
your values.
Last thing... just run that sql string. That's done like this...
dim db as database
set db=currentdb
db.execute sql
set db=nothing
That, as I mentioned a while ago, will hopefully add the correct new
record.
The last part, which we haven't covered yet, is to move to that newly
added
record, but let's leave that for the moment. For now you will hopefully
just be able to use the navigation buttons to find it.
Good luck!
well, i don't seem to be having all that much luck penetrating your
description of the process that this'd involve either....
here's where it's at:
i wrote the SQL
INSERT INTO tblTarget ( [Patient Number], [Lesion Number] )
SELECT 1 AS PN, 2 AS LN;
and saved it as "qryAppendToNonTargetLesions".
now, the trick seems to be decoding what you're saying.
is tblTarget just used as a surrogate for the real table's name, i.e.
"Lesions: Non-Target"
(btw, "Patient Number" is an eight digit integer valued number and
"Lesion
Number" is a three digit integer valued number that can be no greater
than
999)
when you say "You now need to build that same string but instead of
the
random values 1 and 2....include the actual values you want to use"
this
is
where you start to lose me.
:
In the order that it appeared in the previous post. Step 1 is to
build
the
sql string, step 2 is to run it. This chunk of code is just the
'run
it'
part.
okay -- it's probably worth trying it out (the default method isn'
t
working
out for some reason anyway).
where does this code belong?
dim db as database
set db=currentdb
db.execute sql
set db=nothing
best,
-ted
:
No. Provided that the new record needs to be written with null
values
for
any of the other values (or 0s for numeric fields) then just
leaving
them
out of the query will work fine. The actual format of how you
choose to
display those fields on a form is also immaterial - bear in mind
that
you're
not writing data to the form - that's just a means of viewing
the
data -
it's going into a table.
Which of the methods you feel is simpler is always going to be
down
to
you.
But I guarantee you 100% that the SQL method is a hugely
powerful
technique
that has uses in many places in Access. It *is* much better and
as
I
mentioned before, a great deal easier to debug.
in addition to the pn and ln (which comprise the PK), there
are
five
other
controls on this same form. do they need referring to in the
append
query?
does it matter at all that both pn and ln are comboboxes; do
they
need
to
get
changed into textboxes?
i still think that the "Default" 'method' is the simpler
conceptually
to
understand and the one that 'feels' the 'best'.
:
I'm not saying the default value method will never work...
just
that
it
isn't the best way. Anyway - the SQL method:
Set up a new append query (not based on any table/query)
that
will
add
some
values into the table your data would be adding to. Use any
values
in
that
query. Then switch to the SQL view of that query (via the
View
menu).
It
will look something like...
INSERT INTO tblTarget ( [Patient Number], [Lesion Number] )
SELECT 1 AS PN, 2 AS LN;
You now need to build that same string but instead of the
random
values
1
and 2... include the actual values you want to use. That
will
be
something
like...
dim sql as string
dim pn as long
dim ln as long
pn=.... 'whatever.. set it to the patient number you want
ln=.... 'whatever... set it to the lesion number you want
sql="INSERT INTO tblTarget ( [Patient Number], [Lesion
Number] )
"+
_
"SELECT "+cstr(pn)+" AS PN, "+cstr(ln)+" AS LN;"
Doing it that way you can check that pn and ln are getting
calculated
correctly, and by adding a breakpoint, check that the SQL
statement
works.... by copying and pasting it into a new query window.
You can then run it by...
dim db as database
set db=currentdb
db.execute sql
set db=nothing
The last part will be just to move to that newly added
record.
But
I
wouldn't worry about that until you get the above working.
since i don't pretend to be the final word on vba (what
would
i be
doing
asking all this stuff), what i think you're saying is that
it
won't
work
on
the Default Property of Lesion Number no matter how much i
adhere
to
the
tenets of the other contributors to my posting....
you mentioned that you thought some SQL-izing would be
constructive.
can
you
flesh that out a little. what are you thinking
about....remember
this
is a
newbie on this end
:
It's viable... just a bit flaky and difficult to control
(as
I
think
you've
found). Your expression [Me]![Patient Number] in your
dmax
will
only
work
if the record has already been added and that leads to
timing
issues
which
is where I think your problem is.
If you want a record with a specific PN and lesion
number
then
you're
better
off writing it directly. You get way more control and
debugability.
.....i forgot to propose an example of the code you
had in
mind
(assuming
the
idea of setting expressions on the Default properties
of
the
Patient
and
Lesion Number fields isn't viable).
-ted
:
When you click the 'New Record' button on the
standard
record
navigation
bar
then, if the form is filtered, it doesn't
automatically
carry
that
filtering
value on to the new record.
Where is the form opened from? It should (always)
be
another
form
(or
forms) that specify the patient number. I'd tend to
use
code on
that
(or
those) form(s) that adds a new record via an SQL
statement
(including
the PN
and the dmax+1) and then just go to that record.
message
i have an a2k application and a form having two
controls
which
make up
the
PK.
a) Patient Number
b) Lesion Number
the form is 'filtered' so when user designates a
patient
number to
work on
from another open form, he's taken to this one and
can
view
only
those
PNs
selected.
there are multiple records viewable on this form
(it's
coninuous)
each
one
having an incrementally larger value of "Lestion
Number"
(from
1,2,....,12)
i wrote a few vba pieces of code shown below
Private Sub Form_Load()
LAS_EnableSecurity Me
Me.Patient_Number.Value = Forms![RECIST Disease
Evaluation:
Nontarget
Lesions]![Patient Number]
End Sub
the above to handle the case when the form's
loading
(and
was
written
for
the case when no records were entered prior)
how can i let user simultaneously duplicate a
Patient
Number
and
increment
a
Lesion Number w/o having to do so manually.
Private Sub Patient_Number_AfterUpdate()
Me.Patient_Number.DefaultValue = "'" & [Patient
Number] &
"'"
'
that's
a
single quote within doubles...
Me.Lesion_Number = IIf(IsNull("[Lesion Number]"),
1, 1
+
DMax("[Lesion
Number]", "Lesions: Non-Target", "[Patient Number]
= "
&
Me![Patient
Number]))
If Me![Lesion Number] > 10 Then
RetValue = MsgBox("Delete any records exceeding
the
upper
limit",
vbInformation)
End If
DoCmd.Requery
End Sub
the above intended to duplicate the value of
Patient
Number
and
increment
Lesion Number.
in testing this out when no other records matching
the
PN
were
created, i
opened the form and found Patient Number filled in
(by
the
'load'
vba
presumably).
the 'pencil' is on the first record and the
Patient
Number's
completed.
the
'*' is on the record below. if i manually key in
'01'
into
the
Lesion
Number
control it returns a run-time error '3058' abut
index
or
pk
not
being
able
to contain a null -value and highligts the
DoCmd.Requery
in
the
vba
code
below
Private Sub Lesion_Number_AfterUpdate()
Me.Lesion_Number = IIf(IsNull("[Lesion Number]"),
1, 1
+
DMax("[Lesion
Number]", "Lesions: Non-Target", "[Patient Number]
= "
&
Me![Patient
Number]))
If Me![Lesion Number] > 10 Then
RetValue = MsgBox("Delete any records exceeding
the
upper
limit",
vbInformation)
End If
DoCmd.Requery
End Sub
this is probably one of those 'really simple'
solutions
that
befuddle
the
newbie population.
but to further round things out, if i open a form
with
a
bunch
of
records
already entered and click on 'add record'. i need
to
manually
key
in
the
PN
when i click 'add record' at which instant it
automatically
fills
in
the
LN,
displays a record (w/ completed PN in the '*' row)
and
causes
the
cursor
to
jump to the topmost (first record).
????
