Re: multiple criteria for finding record

Tech-Archive recommends: Fix windows errors by optimizing your registry

Thanks, Graham. I am somewhat new at development, so could I put your
response in context? The code would be run from an unbound form in which the
user had entered site, date, and time. The issue is whether a new record
should be written in MyTable, with an additional autonumbered ID, or whether
further data entry in a related junction table should simply use the existing
ID. I assume that the If-Then portion of the code would be the area in which
a bound data entry form for MyTable could be opened and a new record written
(if necessary) and in which a bound data entry form for MyJunctionTable could
be opened and the appropriate ID value could be set in that field. Is this
the right approach?

Regards,
LAF

"Graham R Seach" wrote:

> Yes, you can, but I wouldn't advise it. Domain functions such as DLookup are
> notoriosuly slow, and having 3 of them back-to-back can cause latency
> problems in large tables.
>
> I'd be more inclined to use a single recordset:
> Dim db As Database
> Dim rs As DAO.Recordset
> Dim sSQL As String
>
> Set db = CurrentDb
> sSQL = "SELECT ID FROM MyTable " & _
> "WHERE site = " & Me.txtSite & _
> " AND [date] = " & Me!txtDate & _
> " AND [time] = " & Me!txtTime
>
> Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
> If rs.AbsolutePosition > -1 Then
> 'site, date and time already exist in the table
> End If
>
> rs.Close
> Set rs = Nothing
> Set db = Nothing
>
> Regards,
> Graham R Seach
> Microsoft Access MVP
> Sydney, Australia
> ---------------------------
>
> "LAF" <LAF@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:52F90ED8-A298-4CFD-B45E-93915B242F9D@xxxxxxxxxxxxxxxx
> > Is it possible to use the DLookup function 3 times connected by AND in the
> > criteria for running a macro for data entry? The issue, I think, is that
> > a
> > master table with an autonumbered primary key, may be part of overall data
> > entry. Say that EventID is an autonumbered field that is related to
> > unique
> > combinations of site, date, and time. If one were to enter a new record
> > for
> > the master tables and junction table, would it not be important to
> > determine
> > if the site, date, and time were the same as a prevous record. If so, the
> > EventID of that previous record should be the EventID of the current
> > record
> > being entered. It seems that a macro with the specified condition would
> > do
> > the trick. Thus, is it possible that DLookup could be used for site,
> > date,
> > and time in association with macro actions? The user could enter site,
> > date,
> > and time on an unbound form with a control to run the macro (or code).
>
>
>
.

Relevant Pages

  • Re: multiple criteria for finding record
    ... Regards, ... Graham R Seach ... > further data entry in a related junction table should simply use the ... It seems that a macro with the specified condition ...
    (microsoft.public.access.forms)
  • Re: Database Design
    ... program an unbound form to behave like a continuous form bound to the ... what is needed in the way of data entry and editing facilities. ... So if a continuous form is the best design for data entry, ... calculated by multiplying the appropriate labor rate by FTEs. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Database Design
    ... "John Nurick" wrote: ... program an unbound form to behave like a continuous form bound to the ... what is needed in the way of data entry and editing facilities. ... So if a continuous form is the best design for data entry, ...
    (microsoft.public.access.tablesdbdesign)
  • Thinking about an unbound data entry form...
    ... data entry, and I don't know if it would be a good idea or not. ... I use a tab control, with book information on the first ... subform on a third tab, and a series subform on the fourth tab. ... unbound form would have to permit the entry of more than one author name. ...
    (microsoft.public.access.forms)
  • MS Access 97 form
    ... I have an unbound form which I use to display a message. ... When I open it in a macro (Open ... the form's border appears at least 60 ...
    (microsoft.public.access.forms)