Finding records on secondary index - Rookie

  • Thread starter Thread starter Atlas
  • Start date Start date
A

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
 
Atlas said:
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?
Click to expand...

-----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-----
 
dim cn as ado.connection
dim rs as ado.recordset
Click to expand...

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
Click to expand...

I get the following error:

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

Any hint??

Thanks for helping!
 
Atlas said:
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??
Click to expand...

-----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-----
 
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. ...
Click to expand...

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
 
Back
Top