Re: multiple criteria for finding record
- From: "Graham R Seach" <gseach@xxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 20 Apr 2005 21:13:15 +1000
No, the other way around...
If rs.AbsolutePosition > -1 Then
'Use the existing record
Else
'Create a new record
End If
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
"LAF" <LAF@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:55A91FE2-EDB1-4CBE-AC13-A88EC3E63B0B@xxxxxxxxxxxxxxxx
> 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).
>>
>>
>>
.
- References:
- multiple criteria for finding record
- From: LAF
- Re: multiple criteria for finding record
- From: Graham R Seach
- Re: multiple criteria for finding record
- From: LAF
- multiple criteria for finding record
- Prev by Date: Re: Filter problem
- Next by Date: Re: Moving from access to sql (forms)
- Previous by thread: Re: multiple criteria for finding record
- Next by thread: Re: multiple criteria for finding record
- Index(es):
Relevant Pages
|
