Re: duplicating control and incrementing another control

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.


"Ted" <Ted@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:66900C3A-1200-4339-825E-E25EEE8A9BAC@xxxxxxxxxxxxxxxx
> 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'.
>
> "Rob Oldfield" wrote:
>
> > 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.
> >
> >
> > "Ted" <Ted@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:EE70A176-C9AE-400B-8142-CA63B53FDE83@xxxxxxxxxxxxxxxx
> > > 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 :-)
> > >
> > >
> > >
> > > "Rob Oldfield" wrote:
> > >
> > > > 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.
> > > >
> > > >
> > > > "Ted" <Ted@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > > news:A0DFBC29-C20C-45BE-9B87-1D9097C9DBD9@xxxxxxxxxxxxxxxx
> > > > > .....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
> > > > >
> > > > > "Rob Oldfield" wrote:
> > > > >
> > > > > > 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.
> > > > > >
> > > > > >
> > > > > > "Ted" <Ted@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > > > > news:AB5A595D-256D-4093-BE47-182AB0E4406C@xxxxxxxxxxxxxxxx
> > > > > > > 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).
> > > > > > >
> > > > > > > ????
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >


.

Relevant Pages

  • Re: Query to Filter by Dates, Using Form
    ... This is the SQL that works: ... Dim strDateCondition As String ... drop the stored query and create a new one with the SQL you have built? ...
    (microsoft.public.access.queries)
  • Re: Calendar Form
    ... If it's the name of a query, I need to see the SQL of that query. ... My Calendar form opens, and I can add information into it, as well as ... The fact that the previous line (Dim db As DAO.Database) doesn't ...
    (microsoft.public.access.forms)
  • Re: Calendar Form
    ... If it's the name of a query, I need to see the SQL of that query. ... To see the SQL of a query, open the query in Design view, then select SQL View from the View menu. ... Is there perhaps a way that I can insert a combo box on my calendar form for "CompanyName", in which I can select a company from the list, and then add/view information on the calendar for that specific company? ... The fact that the previous line (Dim db As DAO.Database) doesn't cause problems implies that the DAO library is properly referenced. ...
    (microsoft.public.access.forms)
  • Re: Ordering a filtered proximity search
    ... >>> search variable, filtering it, and ordering it at the same time. ... >>> appended query approach. ... if p1 is empty then your SQL syntax is messed up. ... Dim SearchTerm1 ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Building SQL statement in VBA. Getting 3131 Runtime Error in FROM clause :@
    ... Number of characters in a cell in the query design grid 1,024 ... Since the SQL parser mentions an error in the FROM clause, ... The only problem I can see is the SQL string is very long. ... Dim qdf As QueryDef ...
    (microsoft.public.access.queries)