Finding records on secondary index - Rookie

[Atlas]

Access 2003 + MS SQL Server 2000

I'm using a form to manage customers and related contracts; with one to many
relation;
all managed with a form containing a subform:

Main form: contains customers
Main subform: contains multiple contracts per customer

Each contract has a begin and end date;

By design each customer can have multiple contracts, so when checking user
input I want to be sure that date mismatches do not occur, like entering a
date across previous ended contracts:
Examples:

Correct:

CustID ContractID StartDate EndDate
1 1 01-01-2002 31-12-2002
1 2 01-01-2003 01-06-2003
1 3 02-06-2003 31-12-2003


Wrong:

CustID ContractID StartDate EndDate
1 1 01-01-2002 31-12-2002
1 2 30-12-2002 01-06-2003
1 3 01-06-2003 31-12-2003

I thought I could easily add some VBA code to query a secondary index based
on CustID+EndDate and check if allready existing, using ADO seek.

I'm on the right way or there's an easier method to do so?
If so any code sample?

Thanks

 

[MGFoster]

Access 2003 + MS SQL Server 2000

I'm using a form to manage customers and related contracts; with one to many
relation;
all managed with a form containing a subform:

Main form: contains customers
Main subform: contains multiple contracts per customer

Each contract has a begin and end date;

By design each customer can have multiple contracts, so when checking user
input I want to be sure that date mismatches do not occur, like entering a
date across previous ended contracts:
Examples:

Correct:

CustID ContractID StartDate EndDate
1 1 01-01-2002 31-12-2002
1 2 01-01-2003 01-06-2003
1 3 02-06-2003 31-12-2003


Wrong:

CustID ContractID StartDate EndDate
1 1 01-01-2002 31-12-2002
1 2 30-12-2002 01-06-2003
1 3 01-06-2003 31-12-2003

I thought I could easily add some VBA code to query a secondary index based
on CustID+EndDate and check if allready existing, using ADO seek.

I'm on the right way or there's an easier method to do so?
If so any code sample?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'll have to do a range check. Here is a VBA method that may work -
put it in the form's class module (untested):

Function isOverlapped() as boolean

dim cn as ado.connection
dim rs as ado.recordset
dim strConnect as string
dim strSQL as string
dim lngRecords as long

' replace parameters in angle-brackets (<>) w/ your values
strConnect = "Provider=SQLOLEDB;Server=<serverName>;" & _
"Database=<databaseName>;Trusted_connection=yes"

cn.open strConnect

' This is the query that will find out how many records
' the new date range overlaps.
' This assumes the Me!BeginDate & Me!EndDate are on the
' current form & valid dates.
strSQL = "SELECT Count(*) " & _
"FROM Contracts " & _
"WHERE CustID <> " & Me!CustID & _
" AND ContractID <> " & Me!ContractID & _
" AND (" & CDate(Me!BeginDate) & " BETWEEN " & _
" BeginDate And EndDate " & _
" OR " & CDate(Me!EndDate) & " BETWEEN " & _
" BeginDate And EndDate) "

set rs = cn.Execute(strSQL, lngRecords, adCmdText)

' If the lngRecords > 0 then there are other records
' that overlap the current BeginDate & EndDate values.

isOverlapped = lngRecords > 0

' Don't forget to clean up objects
rs.Close: set rs = nothing
cn.close: set cn = nothing

End Function

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQGCMioechKqOuFEgEQIh2wCfYXjW/znDEP/ltvh/oArA8OhvfGUAniet
eUuwTzDASP+Gmal1r/8mAG+3
=Qv6G
-----END PGP SIGNATURE-----

 

[Atlas]

dim cn as ado.connection

dim rs as ado.recordset

Apart from changing ado. to adodb. otherwise would get compile error
(currently using Microsoft ADO 2.7 in VB editor reference......)

When executing the statement

cn.open strConnect

I get the following error:

Run-time error '91': Object variable or Wih block variable not set

Any hint??

Thanks for helping!

 

[MGFoster]

Apart from changing ado. to adodb. otherwise would get compile error
(currently using Microsoft ADO 2.7 in VB editor reference......)

When executing the statement




I get the following error:

Run-time error '91': Object variable or Wih block variable not set

Any hint??

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Sorry, my error. I forgot to put in the New and the ADODB application
identifier.

dim cn as new adodb.connection
dim rs as adodb.recordset
dim strConnect as string

strConnect = "<connection string info>"

cn.open strConnect

set rs = cn.execute strSQL, ... etc. ...


-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQGjeeIechKqOuFEgEQJWtACdFYXJeU96rQhJIOV3BtJGO5Celo4AnRMN
nFXMa/NXEwkQFggjEwmwl1G1
=llZm
-----END PGP SIGNATURE-----

 

[Atlas]

Sorry, my error. I forgot to put in the New and the ADODB application

identifier.

dim cn as new adodb.connection
dim rs as adodb.recordset
dim strConnect as string

strConnect = "<connection string info>"

cn.open strConnect

set rs = cn.execute strSQL, ... etc. ...

Ok, e few changes and it works!

I've used CONVERT(DATETIME, '" & Me!Inizio & " 00:00:00', 105))" instead of
CDate, due to incorrect results.

Thanks a lot!!!!

Bye