asked on

Using numeric data type columns for Record Search

In one of my recent posts, an expert suggested performing record search without having to use lookup up function
suggestion was also made about creating a single multi-column index
on the four fields (SConnTypeID, SewerTypeID, BlockNo and LotNo).

..And suggested Use and Exists(Select * ...) clause to find a duplicate value.
This, he suggested should use the multi-column index and stop searching
when it finds a match or a value greater than that which was supplied.

In addition, it was suggested to create a numeric hash of these four columns
in a new numeric column which should also have an index.

...And create a hash of the new row and store the hash value when user do your Insert.
and employed error trapping duplicates.

Other suggestion include but "not" limited to the following examples:

Example 1:
Building a recordset on the following:
SELECT TOP 1 1 AS Expr1
FROM tblSewer
WHERE (((Exists (Select * From tblSewer Where sconntypeid = 'NC' and sewertypeid = 'CM' and blockno = '00001' and lotno = '0001'))=True));

If recordset.EOF =True or recordset.BOF =True, then there is no duplicate.

Example 2:
Similar to Example 1, but creating a parameterized query with parameters for the different values. Code will need to define a querydef object, set to the stored query. The querydef variable should have parameters that will need to be set to the values to search.

Building a recordset on this querydef variable and check of records.

Example 3:
Using the DFirst() function instead of the DCount() function. Stop searching when we find a duplicate or know there will be no duplicates, based on the criteria parameter to this function.

Other Notes:
Because text searches are always slower than numeric searches, even with indexes.
therefore,the BlockNo, and LotNo fields have been suggested to be changed back to numeric data types.
such that When this data is displayed, it can be formatted with leading "0" characters.
Extraneous indexes found on tblSewer, tblSewerType and tblSConnType tables.

I will like to try this new technique on experimental basis to see it would speed up
the record search in my db.

All said than done...I am a little confused as to how to go about it and will appreciate samples on this subject for comparison with my current working sample.

I am now attaching a sample db for a start on the subject. Any assistance will be appreciated.
http://www.ee-stuff.com/Expert/Upload/getFile.php?fid=1394

To test simply open the db and do the following:
(1). Select "New Connection" from the first combo
(2). select "Combined in the next combo
(3). Enter "1" in the BlockNo field and
(4). Enter "1" in the LotNo field, a msgbox flag will popup
(5). Choose the "no" option for frmListbox to popup
(6). Double click of the record you want to load in frmSewer
and close frmListbox.

aikimark

you can format numbers displayed in textboxes by changing the textbox format property.

rockiroads

This is in response to your CheckDup function?

Using recordsets is going to be quicker than using aggregate functions, thats for sure
And if any of those 4 indexed fields are not indexed then it will be slow. Any searches u do on non indexed fields is going to be a full table scan. If u have a small data set, u wont notice it that much, but if u have loads then yes, there will be a noticeable difference.

I must admit, I do use aggregate functions but really for smallish things and usually check against indexed fields.

In your DB, u can create a query based on all 4 fields by running this sql (Ive given the index the name of BillIdx)

CREATE INDEX BillIdx ON tblSewer (SConnTypeID,SewerTypeID,BlockNo,LotNo);

Now your query

SELECT TOP 1

ensures we only return one row

SELECT TOP 1 1

returns the number 1 only, this basically is not returning any data from the table but does return a row of a matching row is found

Do note though, in your function CheckDup, which is where I assume u want this, u will not get the number of duplicates

Also, in your sample db, u now use AfterUpdate, which is fine, it means, since your form is bounded to the table, u need to ensure u do the validation and ensure record not saved to db

Another thing, u no longer need cancel because u are not making use of BeforeUpdate - does that make sense?

here is a updated version of the DB
there are two SQL versions, just set iOptMethod to either 1 or 2

1=u get the number of dups
2=as per post sql

'Changed to boolean TRUE if dup, FALSE otherwise because rec counts no longer mean anything
Function CheckDup(ByRef Cancel As Integer) As Boolean

Dim LotVar, RecCount As Long
Dim Msg, Style, Title, Help, Ctxt, Response, MyString, criterion As String
Dim sLotNo As String
Dim sSql As String
Dim rs As DAO.Recordset
Dim iRecCnt As Integer
Dim iOptMethod As Integer
If (Not Me.NewRecord) Or IsNull(Me.cboSConnType) Or IsNull(Me.cboSewerType) Or IsNull(Me.cboBlockNo) Or IsNull(Me.txtLotNo) Then Exit Function

If Len(txtLotNo) <> 4 Then
sLotNo = Right("0000" & txtLotNo, 4)
End If

'****** BILL SET THIS VALUE TO EITHER 1 or 2
iOptMethod = 2

'one way, u can use 1 or get a value from the table, either way a read is done
'Use this to return all matching records so that u have
If iOptMethod = 1 Then
sSql = "SELECT SID " & _
"FROM tblSewer " & _
"WHERE BlockNo = '" & Me.cboBlockNo & "' " & _
"AND LotNo = '" & sLotNo & "' " & _
"AND sConnTypeID = '" & Me.cboSConnType & "' " & _
"AND SewerTypeID = '" & Me.cboSewerType & "'"

Debug.Print sSql
Set rs = CurrentDb.OpenRecordset(sSql)
If rs.EOF = True Then
CheckDup = False
Else
rs.MoveLast
iRecCnt = rs.RecordCount
CheckDup = True
End If

rs.Close
Set rs = Nothing

If CheckDup = False Then Exit Function

Style = vbYesNoCancel + vbInformation + vbDefaultButton2
Title = "Duplicate Record(s)"
Msg = " *** Record Already Exists ... You have 3 Options *** " & vbCrLf & vbCrLf & "Yes - Add Another Record With The Same Block And Lot No" _
& vbCrLf & vbCrLf & "No - Update All Duplicate Records ( " & iRecCnt & " Matching Records Found )" & vbCrLf & vbCrLf & "Cancel - Cancel And Return To The Data Entry"
Beep
Response = MsgBox(Msg, Style, Title)
If Response = vbNo Then
DoCmd.OpenForm "frmListbox", acNormal
ElseIf Response = vbCancel Then
Cancel = True
End If

'another way as per post, but u wont get the count of duplicates
Else
sSql = "SELECT TOP 1 1 " & _
"FROM tblSewer " & _
"WHERE (((Exists (SELECT * FROM tblSewer WHERE BlockNo = '" & Me.cboBlockNo & "' " & _
"AND LotNo = '" & sLotNo & "' " & _
"AND sConnTypeID = '" & Me.cboSConnType & "' " & _
"AND SewerTypeID = '" & Me.cboSewerType & "')) = True))"
Debug.Print sSql
Set rs = CurrentDb.OpenRecordset(sSql)
If rs.EOF = True Then
CheckDup = False
Else
CheckDup = True
End If

rs.Close
Set rs = Nothing

If CheckDup = False Then Exit Function

Style = vbYesNoCancel + vbInformation + vbDefaultButton2
Title = "Duplicate Record(s)"
Msg = " *** Record Already Exists ... You have 3 Options *** " & vbCrLf & vbCrLf & "Yes - Add Another Record With The Same Block And Lot No" _
& vbCrLf & vbCrLf & "No - Update All Duplicate Records " & vbCrLf & vbCrLf & "Cancel - Cancel And Return To The Data Entry"
Beep
Response = MsgBox(Msg, Style, Title)
If Response = vbNo Then
DoCmd.OpenForm "frmListbox", acNormal
ElseIf Response = vbCancel Then
Cancel = True
End If
End If
End Function

Ok, back to caller, because u now use afterupdate, how do u plan on handling duplicates? at the moment, I can hit Cancel as the value can be saved to the DB.
U used Cancel, Im sure for the BeforeUpdate functionality.

So I think u should perhaps go back to using BeforeUpdate, remember the change I made, was to use a string variable right padded with 4 zeros i.e. use sLotNo, that seemed to work right?

Private Sub txtLotNo_BeforeUpdate(Cancel As Integer)

On Error GoTo Err_txtLotNo_BeforeUpdate

Dim RecCount As Long

RecCount = CheckDup(Cancel)

Exit_txtLotNo_BeforeUpdate:
' tidy up here
Exit Sub

Err_txtLotNo_BeforeUpdate:
' handle error here
MsgBox err.Description
Resume Exit_txtLotNo_BeforeUpdate

End Sub

Uploaded sample here for u
https://filedb.experts-exchange.com/incoming/ee-stuff/1423-SampleError-New_ET_v2.zip

Now u can tell me whether Im on the right track or not in what u want

And its a good nite from me, 11.15pm

billcute

ASKER

rockiroads,
This is quite a beautiful technique.

I like the new look of frmListbox - it still needs to be tweaked a little. It was not displaying any duplicate record when launched from txtLotNo.

To be able to simulate the same experience, you will need to delete all data in tblSewer.LotNo wth value of "1" such that what you have left would be "0001" in the field. Then test the sample, you'll notice it was blank.
(1).
I copied 'frmListbox" from your other sample in the other post into this new sample...then your old frmLitbox populated data "but" I liked the way the new frmListbox was fine tuned. If you could tweak it to work, it would be great.

(2).
I noticed that you suggested a point which was not implemented in your latest sample.

(3).
You wrote:
In your DB, u can create a query based on all 4 fields by running this sql (Ive given the index the name of BillIdx)
CREATE INDEX BillIdx ON tblSewer (SConnTypeID,SewerTypeID,BlockNo,LotNo);
' ******

In what way can we implement the above suggestion?

I have indexed the SConnTypeID, SewerTypeID, BlockNo and LotNo in that order...however, I did not convert The BlockNo / LotNo current text type into "numeric type" as suggested by the expert.

How will the conversion of the BlockNo / LotNo impact the speed of searches for records.
If I leave the settings as text type, in what way will your new technique work with without having to compromise on record search speed?

(4).
In your last post, you raised this question in your old amended copy of frmListbox....Is the question applicable to this topic...if "Yes", then how can this be done....will it help in achieving my desired goal in this new post?

You wrote:
'BILL READ THIS
'Because the format of LotNo varies, we can do one of two things
'Either wrap txtLotNo into a temp variable like we did with CheckDups and add leading zeros
'Or we can do a VAL around LotNo and compare against the entered LotNo
sSql = "SELECT tblSewer.SID,tblSewer.BlockNo as Block, " _
& "tblSewer.LotNo as Lot, tblSewer.SConnTypeID as ConnT, tblSewer.SewerTypeID as SType, " _
& "tblSewer.AddressAbbrev as AddAbb,tblSewer.PermitNo as Permit, " _
& "tblSewer.AppEntryDate,tblSewer.SDEDate FROM tblSewer " _
& "WHERE tblSewer.BlockNo= '" & [Forms]![frmSewer]![cboBlockNo] & "' AND " _
& "VAL(NZ(tblSewer.LotNo,'0'))=" & Val([Forms]![frmSewer]![txtLotNo]) & " " _
& " AND tblSewer.SConnTypeID='" & [Forms]![frmSewer]![cboSConnType] & "'" _
& " AND tblSewer.SewerTypeID='" & [Forms]![frmSewer]![cboSewerType] & "' ORDER BY tblSewer.SID DESC"
End If
' *****************

Thank you for your assistance.

Regards
Bill

billcute

ASKER

rockiroads,
I saw the "BillIdx you created in tblSewer but did not see therelated sql as stated above.

or...
is the sql you are talking about...if it is where is the index "BillIdx" ?

sSql = "SELECT TOP 1 1 " & _
"FROM tblSewer " & _
"WHERE (((Exists (SELECT * FROM tblSewer WHERE BlockNo = '" & Me.cboBlockNo & "' " & _
"AND LotNo = '" & sLotNo & "' " & _
"AND sConnTypeID = '" & Me.cboSConnType & "' " & _
"AND SewerTypeID = '" & Me.cboSewerType & "')) = True))"

(2).
I also sorted the four fields in "Descending Order" in accordiance with Ms. Access rules.

Regards
Bill

billcute

ASKER

rockiroads,
Did you see my last two comments on this topic?

Regards
Bill

rockiroads

Howdo Bill

rockiroads

Ignore the other sample now, it will just get confusing. Lets just concentrate what we got here so far

Main thing is, did u understand it? I tried to cmment as much as I can

regarding BillIdx, look in the queries tab, there is a query there, it has nothing to do with this

sSql = "SELECT TOP 1 1 " & _
"FROM tblSewer " & _
"WHERE (((Exists (SELECT * FROM tblSewer WHERE BlockNo = '" & Me.cboBlockNo & "' " & _
"AND LotNo = '" & sLotNo & "' " & _
"AND sConnTypeID = '" & Me.cboSConnType & "' " & _
"AND SewerTypeID = '" & Me.cboSewerType & "')) = True))"

the creation of the index is this sql

CREATE INDEX BillIdx ON tblSewer (SConnTypeID,SewerTypeID,BlockNo,LotNo);

create a index called billidx based on the table tblSewer
and it consists of the 4 fields listed in brackets

billcute

ASKER

Ok,
I have seen the query 'qryCrtSewerIndex" - it created "BillIdx" in tblSewer. I read your posts on the subject but still confused, I must confess. your current frmListbox does not display any duplicate when opened for Block "00001" ; and Lot "0001"

Regards
Bill

rockiroads

Ok, Bill, lets take this one step at a time

I need for u to understand CheckDup first, before we tackle the listbox

Do u understand that?

Now I changed the code to use BeforeUpdate because when u hit Cancel u need to ensure the user stays on that field until corrected or yes/no selected from that popup that appears

You had used ET's code that used AfterUpdate, problem with this is, u have to capture the return event (user selected Cancel) then reset it or do something.

Now frmListBox

in the previous example, I changed it to use VAL around LotNo, that was one way, this ensures both table field and variable are converted to numeric.

But Ive changed it again, Ive used the same approach in setting LotNo as in CheckDup

eg - look for occurences of sLotNo

Private Sub Form_Open(Cancel As Integer)

Dim sSql As String
Dim sLotNo As String

Me.TimerInterval = 300
DoCmd.MoveSize 5100, 0

Me.List1.RowSourceType = "Table/Query"

If IsLoaded("frmSewer") Then

If IsNull([Forms]![frmSewer]![cboSConnType]) Then

Me.Label0.Caption = "Do You Wish To Update Blank AppDate/SDEDate"

sSql = "SELECT tblSewer.AppID, tblSewer.BlockNo as Block," _
& "tblSewer.LotNo as Lot, tblSewer.SConnTypeID as ConnT, tblSewer.SewerTypeID as SType," _
& "tblSewer.AddressAbbrev as AddAbb,tblSewer.PermitNo as Permit, tblSewer.AppEntryDate, tblSewer.SDEDate as SDEDate" _
& " FROM tblSewer WHERE tblSewer.DataEntryDate <= Now() - 30 Or tblSewer.DataEntryDate Is Null or (tblSewer.AppEntryDate Is Null And tblSewer.SDEDate Is Null) " _
& " ORDER BY tblSewer.SID DESC"

Me.Label4.Caption = " Above List contains " & Me.List1.ListCount - 1 & " Records without AppEDate/SDEDate"
Me.List1.RowSource = sSql

Else

If Len([Forms]![frmSewer]![txtLotNo]) <> 4 Then
sLotNo = Right("0000" & [Forms]![frmSewer]![txtLotNo], 4)
Else
sLotNo = [Forms]![frmSewer]![txtLotNo]
End If

sSql = "SELECT tblSewer.SID,tblSewer.BlockNo as Block, " _
& "tblSewer.LotNo as Lot, tblSewer.SConnTypeID as ConnT, tblSewer.SewerTypeID as SType, " _
& "tblSewer.AddressAbbrev as AddAbb,tblSewer.PermitNo as Permit, " _
& "tblSewer.AppEntryDate,tblSewer.SDEDate FROM tblSewer " _
& "WHERE tblSewer.BlockNo= '" & [Forms]![frmSewer]![cboBlockNo] & "' AND " _
& "tblSewer.LotNo='" & sLotNo & "'" _
& " AND tblSewer.SConnTypeID='" & [Forms]![frmSewer]![cboSConnType] & "'" _
& " AND tblSewer.SewerTypeID='" & [Forms]![frmSewer]![cboSewerType] & "' ORDER BY tblSewer.SID DESC"

Me.List1.RowSource = sSql
End If

ElseIf IsLoaded("frmWater") Then

If IsNull([Forms]![frmWater]![cboWConnType]) Then
Me.Label0.Caption = "Do You Wish To Update Blank AppDate/JobDate"

sSql = "SELECT tblWater.AppID, tblWater.BlockNo as Block," _
& "tblWater.LotNo as Lot, tblWater.WConnTypeID as ConnT, tblWater.ServiceTypeID as SType," _
& "tblWater.AddressAbbrev as AddAbb,tblWater.PermitNo as Permit, tblWater.AppDate, tblWater.DateInstalled as JobDate" _
& " FROM tblWater WHERE tblWater.DataEntryDate <= Now() - 30 Or tblWater.DataEntryDate Is Null or (tblWater.AppDate Is Null And tblWater.DateInstalled Is Null) " _
& " ORDER BY tblWater.AppID DESC"

Me.Label4.Caption = " Above List contains " & Me.List1.ListCount - 1 & " Records without AppDate/JobDate"

Me.List1.RowSource = sSql
Else
sSql = "SELECT tblWater.AppID,tblWater.BlockNo as Block," _
& "tblWater.LotNo as Lot, tblWater.WConnTypeID as ConnT, tblWater.ServiceTypeID as SType," _
& "tblWater.AddressAbbrev as AddAbb,tblWater.PermitNo as Permit,tblWater.AppDate," _
& "tblWater.DateInstalled FROM tblWater" _
& " WHERE tblWater.BlockNo= '" & [Forms]![frmWater]![cboBlockNo] & "' AND " _
& " tblWater.LotNo= '" & [Forms]![frmWater]![txtLotNo] & "'" _
& " AND tblWater.WConnTypeID='" & [Forms]![frmWater]![cboWConnType] & "'" _
& " AND tblWater.ServiceTypeID='" & [Forms]![frmWater]![cboServiceType] & "'" & "ORDER BY tblWater.AppID DESC"
End If

ElseIf IsLoaded("frmShutOff") Then

If IsNull([Forms]![frmShutOff]![cboWConnType]) Then

Me.Label0.Caption = "Do You Wish To Update Blank ThreeDayDate/ShutOffDate"

sSql = "SELECT tblShutOff.ShutOffID, tblShutOff.BlockNo as Block," _
& "tblShutOff.LotNo as Lot, tblShutOff.WConnTypeID as ConnT, tblShutOff.ServiceTypeID as SType," _
& "tblShutOff.AddressAbbrev as AddAbb,tblShutOff.WorkOrderNo as WorkNo, tblShutOff.ThreeDayNotice, tblShutOff.ShutOffDate as ShutDate" _
& " FROM tblShutOff WHERE tblShutOff.DataEntryDate <= Now() - 30 Or tblShutOff.DataEntryDate Is Null or (tblShutOff.ThreeDayNotice Is Null And tblShutOff.ShutOffDate Is Null) " _
& " ORDER BY tblShutOff.ShutOffID DESC"

Me.Label4.Caption = " Above List contains " & Me.List1.ListCount - 1 & " Records without AppDate/ShutOffDate"

Me.List1.RowSource = sSql
Else
sSql = "SELECT tblShutOff.ShutOffID,tblShutOff.BlockNo as Block," _
& "tblShutOff.LotNo as Lot, tblShutOff.WConnTypeID as Conn, tblShutOff.ServiceTypeID as SType," _
& "tblShutOff.AddressAbbrev as AddAbb,tblShutOff.WorkOrderNo as WorkNo,tblShutOff.ThreeDayNotice," _
& "tblShutOff.ShutOffDate FROM tblShutOff" _
& " WHERE tblShutOff.BlockNo= '" & [Forms]![frmShutOff]![cboBlockNo] & "' And " _
& " tblShutOff.LotNo = '" & [Forms]![frmShutOff]![txtLotNo] & "'" _
& " AND tblShutOff.WConnTypeID='" & [Forms]![frmShutOff]![cboWConnType] & "'" _
& " AND tblShutOff.ServiceTypeID='" & [Forms]![frmShutOff]![cboServiceType] & "'" & " ORDER BY tblShutOff.ShutOffID DESC"

Me.List1.RowSource = sSql
End If

End If
End Sub

What might be good, is if u are going to use LotNo in various places, is to ceate a generic function.
That we can deal with later

But u first need your question answering first

billcute

ASKER

rockiroad,
I do understand it so far...

Bill

billcute

ASKER

There are three forms where duplicate checks are necessary as shown above...and each form checks for duplicate at txtLotNo....so how would creation of generic function help?

Regards
Bill

rockiroads

Is LotNo stored with leading zeros in all other tables? tblWater, tblShutOff
Do u add 0000 in front of all LotNo's ?

By generic I mean, if u going to keep this on lot numbers in all tables, u can simply do this

Public Function BuildLotNo(ByVal sLotNo As String) As String

If Len(sLotNo) <> 4 Then
BuildLotNo = Right("0000" & sLotNo, 4)
Else
BuildLotNo = sLotNo
End If

End Function

Now in all places where u need to prefix with leading zeros, just call this func
eg
in CheckDup

replace this

If Len(txtLotNo) <> 4 Then
sLotNo = Right("0000" & txtLotNo, 4)
End If

with this

sLotNo = BuildLotNo(txtLotNo)

and in frmListBox

replace this

If Len([Forms]![frmSewer]![txtLotNo]) <> 4 Then
sLotNo = Right("0000" & [Forms]![frmSewer]![txtLotNo], 4)
Else
sLotNo = [Forms]![frmSewer]![txtLotNo]
End If

with this

sLotNo = BuildLotNo([Forms]![frmSewer]![txtLotNo])

Now in all checks in the db (like in frmList against tblWater/tblShutdown), use sLotNo instea d of form field becase sLotNo has been built up to how u want it.
Adding it in one central place is also easier on the maintenance (just in case u decide to change format)

Just a question, why do u store with leading zero's, u can always display leading zero's on output.

billcute

ASKER

Yes, I have made the changes.... as suggested...wht is next?

The leading zeros were not stored in my previous development. It was suggested by colleague to display the leading zeros, so I thought that storing them might be important but I am willing to try any other better approach.

Does it mean we dont have to store it...just display it ...then how about record search...I do have a form for seaching for Block / Lot in the tables...how would this need development affect the record search popup form?

Do we have to convert Block / Lot fields to numeric type again?

Regards
Bill

rockiroads

If u dont plan any storing any non numerics then it doesnt make sense to store as text, but just store as numerics

With regards to searches, its not a problem, u can just put a VAL around it and compare against the DB field
Also numeric fields are usually quicker on index searches

If u need to display with leading zero's, u can do that in your reports and form easily enough

With the DB I have now, I can certainly change to numeric and give u an example of how it would look

rockiroads

Make a copy of your DB and work on this - we are trying the numeric approach.

In the table definition

tblSewer

change data type of BlockNo and LotNo from text to Number
next set the format property to the number of zero's u want (5 and 4 respectively)

If u notice, u may get the error icon appearing, if u click on that u have the option of correcting, this will modify frmSewer. Thats okay, its just modifying the field type in the form

Now if u run this query in query editor

SELECT * from tblSewer where lotno=1

u will get the results with leading zero's because we set the format property in the table

Now u dont need to wrap your code with leading zero's anymore

ASKER CERTIFIED SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rockiroads

Is tblWater and tblShutdown the same format of blockno's?

in that case form_open of frmListBox needs changing
eg

Private Sub Form_Open(Cancel As Integer)

Dim sSql As String

Me.TimerInterval = 300
DoCmd.MoveSize 5100, 0

Me.List1.RowSourceType = "Table/Query"

If IsLoaded("frmSewer") Then

If IsNull([Forms]![frmSewer]![cboSConnType]) Then

Me.Label0.Caption = "Do You Wish To Update Blank AppDate/SDEDate"

sSql = "SELECT tblSewer.AppID, tblSewer.BlockNo as Block," _
& "tblSewer.LotNo as Lot, tblSewer.SConnTypeID as ConnT, tblSewer.SewerTypeID as SType," _
& "tblSewer.AddressAbbrev as AddAbb,tblSewer.PermitNo as Permit, tblSewer.AppEntryDate, tblSewer.SDEDate as SDEDate" _
& " FROM tblSewer WHERE tblSewer.DataEntryDate <= Now() - 30 Or tblSewer.DataEntryDate Is Null or (tblSewer.AppEntryDate Is Null And tblSewer.SDEDate Is Null) " _
& " ORDER BY tblSewer.SID DESC"

Me.Label4.Caption = " Above List contains " & Me.List1.ListCount - 1 & " Records without AppEDate/SDEDate"
Me.List1.RowSource = sSql

Else

sSql = "SELECT tblSewer.SID,tblSewer.BlockNo as Block, " _
& "tblSewer.LotNo as Lot, tblSewer.SConnTypeID as ConnT, tblSewer.SewerTypeID as SType, " _
& "tblSewer.AddressAbbrev as AddAbb,tblSewer.PermitNo as Permit, " _
& "tblSewer.AppEntryDate,tblSewer.SDEDate FROM tblSewer " _
& "WHERE tblSewer.BlockNo=" & [Forms]![frmSewer]![cboBlockNo] _
& " AND tblSewer.LotNo=" & [Forms]![frmSewer]![txtLotNo] _
& " AND tblSewer.SConnTypeID='" & [Forms]![frmSewer]![cboSConnType] & "'" _
& " AND tblSewer.SewerTypeID='" & [Forms]![frmSewer]![cboSewerType] & "' ORDER BY tblSewer.SID DESC"

Me.List1.RowSource = sSql
End If

ElseIf IsLoaded("frmWater") Then

If IsNull([Forms]![frmWater]![cboWConnType]) Then
Me.Label0.Caption = "Do You Wish To Update Blank AppDate/JobDate"

sSql = "SELECT tblWater.AppID, tblWater.BlockNo as Block," _
& "tblWater.LotNo as Lot, tblWater.WConnTypeID as ConnT, tblWater.ServiceTypeID as SType," _
& "tblWater.AddressAbbrev as AddAbb,tblWater.PermitNo as Permit, tblWater.AppDate, tblWater.DateInstalled as JobDate" _
& " FROM tblWater WHERE tblWater.DataEntryDate <= Now() - 30 Or tblWater.DataEntryDate Is Null or (tblWater.AppDate Is Null And tblWater.DateInstalled Is Null) " _
& " ORDER BY tblWater.AppID DESC"

Me.Label4.Caption = " Above List contains " & Me.List1.ListCount - 1 & " Records without AppDate/JobDate"

Me.List1.RowSource = sSql
Else
sSql = "SELECT tblWater.AppID,tblWater.BlockNo as Block," _
& "tblWater.LotNo as Lot, tblWater.WConnTypeID as ConnT, tblWater.ServiceTypeID as SType," _
& "tblWater.AddressAbbrev as AddAbb,tblWater.PermitNo as Permit,tblWater.AppDate," _
& "tblWater.DateInstalled FROM tblWater" _
& "WHERE tblWater.BlockNo=" & [Forms]![frmWater]![cboBlockNo] _
& " AND tblWater.LotNo=" & [Forms]![frmWater]![txtLotNo] _
& " AND tblWater.WConnTypeID='" & [Forms]![frmWater]![cboWConnType] & "'" _
& " AND tblWater.ServiceTypeID='" & [Forms]![frmWater]![cboServiceType] & "'" & "ORDER BY tblWater.AppID DESC"
End If

ElseIf IsLoaded("frmShutOff") Then

If IsNull([Forms]![frmShutOff]![cboWConnType]) Then

Me.Label0.Caption = "Do You Wish To Update Blank ThreeDayDate/ShutOffDate"

sSql = "SELECT tblShutOff.ShutOffID, tblShutOff.BlockNo as Block," _
& "tblShutOff.LotNo as Lot, tblShutOff.WConnTypeID as ConnT, tblShutOff.ServiceTypeID as SType," _
& "tblShutOff.AddressAbbrev as AddAbb,tblShutOff.WorkOrderNo as WorkNo, tblShutOff.ThreeDayNotice, tblShutOff.ShutOffDate as ShutDate" _
& " FROM tblShutOff WHERE tblShutOff.DataEntryDate <= Now() - 30 Or tblShutOff.DataEntryDate Is Null or (tblShutOff.ThreeDayNotice Is Null And tblShutOff.ShutOffDate Is Null) " _
& " ORDER BY tblShutOff.ShutOffID DESC"

Me.Label4.Caption = " Above List contains " & Me.List1.ListCount - 1 & " Records without AppDate/ShutOffDate"

Me.List1.RowSource = sSql
Else
sSql = "SELECT tblShutOff.ShutOffID,tblShutOff.BlockNo as Block," _
& "tblShutOff.LotNo as Lot, tblShutOff.WConnTypeID as Conn, tblShutOff.ServiceTypeID as SType," _
& "tblShutOff.AddressAbbrev as AddAbb,tblShutOff.WorkOrderNo as WorkNo,tblShutOff.ThreeDayNotice," _
& "tblShutOff.ShutOffDate FROM tblShutOff" _
& "WHERE tblShutOff.BlockNo=" & [Forms]![frmShutOff]![cboBlockNo] _
& " AND tblShutOff.LotNo=" & [Forms]![frmShutOff]![txtLotNo] _
& " AND tblShutOff.WConnTypeID='" & [Forms]![frmShutOff]![cboWConnType] & "'" _
& " AND tblShutOff.ServiceTypeID='" & [Forms]![frmShutOff]![cboServiceType] & "'" & " ORDER BY tblShutOff.ShutOffID DESC"

Me.List1.RowSource = sSql
End If

End If
End Sub

rockiroads

list1 click event also needs to change
eg

Private Sub List1_DblClick(Cancel As Integer)

Dim Sconn As String, Sewer As String, Block As Long, Lot As Integer

If IsLoaded("frmSewer") Then

Forms!frmSewer.Undo

Forms!frmSewer.Filter = "SID=" & Me.List1.Column(0) & " AND " & _
"BlockNo = " & Me.List1.Column(1) & " AND " & _
"LotNo = " & Me.List1.Column(2) & " AND " & _
"SConnTypeID = '" & Me.List1.Column(3) & "' AND " & _
"SewerTypeID ='" & Me.List1.Column(4) & "'"

If Me.List1.ListCount = 2 Then DoCmd.Close

Forms!frmSewer.FilterOn = True
Forms!frmSewer.SetFocus

ElseIf IsLoaded("frmWater") Then

Forms!frmWater.Undo

Forms!frmWater.Filter = "AppID=" & Me.List1.Column(0) & " AND " & _
"BlockNo = " & Me.List1.Column(1) & " AND " & _
"LotNo = " & Me.List1.Column(2) & " AND " & _
"WConnTypeID = '" & Me.List1.Column(3) & "' AND " & _
"ServiceTypeID ='" & Me.List1.Column(4) & "'"

If Me.List1.ListCount = 2 Then DoCmd.Close

Forms!frmWater.FilterOn = True
Forms!frmWater.SetFocus

ElseIf IsLoaded("frmShutOff") Then

Forms!frmShutOff.Undo

Forms!frmShutOff.Filter = "ShutOffID=" & Me.List1.Column(0) & " AND " & _
"BlockNo = " & Me.List1.Column(1) & " AND " & _
"LotNo = " & Me.List1.Column(2) & " AND " & _
"WConnTypeID = '" & Me.List1.Column(3) & "' AND " & _
"ServiceTypeID ='" & Me.List1.Column(4) & "'"

If Me.List1.ListCount = 2 Then DoCmd.Close

Forms!frmShutOff.FilterOn = True
Forms!frmShutOff.SetFocus
End If
End Sub

rockiroads

in frmSewer I guess u dont need to do these type of calls now
eg

Private Sub cboBlockNo_Exit(Cancel As Integer)
If Len(Me.cboBlockNo) <> 5 Then
Me.cboBlockNo = Right("00000" & Me.cboBlockNo, 5)
End If
End Sub

Bill in frmSewer, u need to do more work as there are ways round duplicates
If u dont fill in all fields, and click Update, what happens?
A simple check like this should help. You may need to expand more on what u feel are mandatory fields

Private Sub btnUpdate_Click()
On Error GoTo Err_btnUpdate_Click

If (Not Me.NewRecord) Then
Beep
Exit Sub
ElseIf IsNull(Me.cboSConnType) Or IsNull(Me.cboSewerType) Or IsNull(Me.cboBlockNo) Or IsNull(Me.txtLotNo) Then
MsgBox "Please fill in all mandatory fields", vbInformation
Exit Sub
End If

DoCmd.RunCommand acCmdSaveRecord

'BILL - TO SAVE RECORDS U CAN ALSO DO THIS Me.Dirty = False

On Error GoTo 0
Exit_btnUpdate_Click:
Exit Sub
Err_btnUpdate_Click:
MsgBox Err.Description
Resume Exit_btnUpdate_Click
End Sub

rockiroads

Drawback of setting format in fields is u will get it in empty textboxes. Is that okay?

Lots of posts, I realise, so Ive uploaded a sample

https://filedb.experts-exchange.com/incoming/ee-stuff/1435-SampleError-New_v3.zip

rockiroads

Keep the text version and compare now

What looks easier for u to maintain? What is more easy to present?

My personal approach is to use Numerics, but this decision is entirely yours, as u have the text version working - well I hope so :)

Im gonna call it a nite out

Ive been quiet on the EE front last two days. Have been enjoying the pleasure of your company/answering your questions. :)

billcute

ASKER

rockiroads,
Thank you for your tremendous efforts here, please accept my sincere gratitude.

Based on your last sample, I finally decide to do away with the leading "zeros" - I will explain to my colleagues and I am sure they will understand.

.....but I want to keep your refined code...since it's already numeric anyway....it's likely going to improve the search record speed anyway and make the search record run much faster.

Here is what I have done....
(a). Block / Lot will remain numeric types.
(b). I have removed the zeros from "format" properties of both Block, Lot
(c). I have also removed the "zeros" format properties from Block / LotNo form fields in frmSewer.

I think, I will like to stick strictly with the applicaion without the leading zeros...

Testing.
I first deleted all the leading "zeros" from tblSewer.BlockNo (i.e I changed the data type from "00001" to "1". I also the same for LotNo.

I tried testing for duplicate records by selecting "New Connection", "Combined" from the combos, enter 1 for BlockNo and 1 for the txtLotNo I received but instead of getting the msgbox flag, I received a compile error: "data type mismatch" at the txtLotNo.

Is there anything else I need to remove from your above code despite removing all "zeros" from the format properties of both the BlockNo / LotNo ? ......... with a view to stopping the "Data type mismatch error???.

Regards
Bill

billcute

ASKER

rockiroads,
Actually, I have taking care of the "data type mismatch error...

...but is there anything else I need to know..

Regards
Bill

billcute

ASKER

rockiroads,
I really appreciate all your efforts with this thread. You have done a superb job.

Regards
Bill

billcute

ASKER

rockiroads,
Thanks for the extra code for Private Sub btnUpdate_Click()
I do something similar to your suggested code...it was just not sent with my sample.

Regards
Bill

billcute

ASKER

rockiroads,
I have just sent a you thank you e-mail...to show my sincere gratitude for this post. I know you went an extra mile on this. Please let me know if you receive my e-mail.

Regards
Bill

billcute

ASKER

To All Experts,
I have just created a related post at the link below...this post is designed to create a backdoor entrance to a table. Any assistance will be appreciated.

https://www.experts-exchange.com/questions/22065475/Make-datasheet-read-only.html

Regards
Bill

billcute

ASKER

rockiroads / aikimark ,
Would you please take a quick look at this link for me and make an appropriate recommendation?
https://www.experts-exchange.com/questions/22065742/Validating-User's-entry-before-Update-in-datasheet.html

Regards
Bill