Open form display specific record

Hi I am trying to set up my db so that you open frmDataFind on this form I
have 3 fields
txtCustomerID
txtRegNumber
txtName

What I what to do is enter the search criteria in any of these fields say
22000 in the txtCustomerID field
I then want to hit the search button and it opens form Incident Data Entry
showing only record 22000

If I enter a Reg Number in txtRegNumber I want it to display all of the reg
numbers that match the one I entered
and the same for names in form Incident Data Entry.

I also want it to do a search for matches if I only have part of the reg
number or name, and lastly to open the form ready to enter a new record.

The form Incident Data Entry already has the above set up on it but it opens
showing all the records which can be very slow.

How do I adapt the following code to work in my new frmDataFind or is there
a better way of doing this?

Private Sub txtFindDriverName_AfterUpdate()

'Since we want to search on partial values, we have to use a query for the
'record source of this form. We find the records with the query and
tell the
'form it can only see those records.
'Now since we want to search on either of three fields, we have to
control what
'happens to the criteria in the query, so if we are here, we are trying
to find by DriverName
'so we need to set RegNum to null:
On Error GoTo txtFindDriverName_AfterUpdate_Error

txtFindCustomer = "" 'so when the query runs it returns all RegNum
txtFindRegNumber = "" 'but still filters on Name,
we do just the
'opposite in the code for the RegNum
Dim db As Database
Dim rs As Recordset
Dim QD As QueryDef

Set db = CurrentDb()
Set QD = db.QueryDefs("qryIncidentDataFind")
QD.Parameters(0) = "" 'Here we pass Null to RegNum in the query
QD.Parameters(1) = [Forms]![Incident Data Entry]![txtFindDriverName]
QD.Parameters(2) = ""
Set rs = QD.OpenRecordset()
Me.Form.AllowEdits = False

If rs.RecordCount 1 Then
msgbox "No Record Found"
Cancel = True

Else
Me.Requery
Me.Refresh
End If
txtFindDriverName.Value = ""
[Field143].SetFocus
txtFindDriverName.Visible = False
txtFindMsg.Visible = False

On Error GoTo 0
Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & " (" & Err.Description & ") in procedure
txtFindDriverName_AfterUpdate of VBA Document Form_Incident Data Entry"

End Sub

What I am trying to achieve is that we only bring the records we need over
the network rather than all of them ever time we open form Incident Data
Entry.

Thanks Bob

Hi Bob,

what is the SQL for qryIncidentDataFind?

also, if it ends up your do things this way, you need to release your
objexct variables

instead of
'~~~~~~~~~~
Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & " (" & Err.Description & ") in
procedure
txtFindDriverName_AfterUpdate of VBA Document Form_Incident Data Entry"
'~~~~~~~~~~

you would use

'~~~~~~~~~~
Proc_Exit:
if not rs is nothing then
rs.close
set rs = nothing
end if
set QD = nothing
set db = nothing

Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & _
" (" & Err.Description _
& ") in procedure txtFindDriverName_AfterUpdate" _
& " of VBA Document Form_Incident Data Entry" _
,, "Error"

'~~~~~~~~~~

I like using generic names for the error handler label

instead of
txtFindDriverName_AfterUpdate_Error

use something like
Proc_Error

with recent versions of Access, labels only need to be unique within a
procedure, not unique within a module...

also, use line continuation (space, underscore at end of line) to use
multiple lines for long statements


~~~~

I am assuming you have a command button to open the next form?

~~~

if you want to clear a control, do this:
txtFindDriverName.Value = null
instead of this:
txtFindDriverName.Value = ""


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Bob wrote:
Hi I am trying to set up my db so that you open frmDataFind on this form I
have 3 fields
txtCustomerID
txtRegNumber
txtName

What I what to do is enter the search criteria in any of these fields say
22000 in the txtCustomerID field
I then want to hit the search button and it opens form Incident Data Entry
showing only record 22000

If I enter a Reg Number in txtRegNumber I want it to display all of the reg
numbers that match the one I entered
and the same for names in form Incident Data Entry.

I also want it to do a search for matches if I only have part of the reg
number or name, and lastly to open the form ready to enter a new record.

The form Incident Data Entry already has the above set up on it but it opens
showing all the records which can be very slow.

How do I adapt the following code to work in my new frmDataFind or is there
a better way of doing this?

Private Sub txtFindDriverName_AfterUpdate()

'Since we want to search on partial values, we have to use a query for the
'record source of this form. We find the records with the query and
tell the
'form it can only see those records.
'Now since we want to search on either of three fields, we have to
control what
'happens to the criteria in the query, so if we are here, we are trying
to find by DriverName
'so we need to set RegNum to null:
On Error GoTo txtFindDriverName_AfterUpdate_Error

txtFindCustomer = "" 'so when the query runs it returns all RegNum
txtFindRegNumber = "" 'but still filters on Name,
we do just the
'opposite in the code for the RegNum
Dim db As Database
Dim rs As Recordset
Dim QD As QueryDef

Set db = CurrentDb()
Set QD = db.QueryDefs("qryIncidentDataFind")
QD.Parameters(0) = "" 'Here we pass Null to RegNum in the query
QD.Parameters(1) = [Forms]![Incident Data Entry]![txtFindDriverName]
QD.Parameters(2) = ""
Set rs = QD.OpenRecordset()
Me.Form.AllowEdits = False

If rs.RecordCount 1 Then
msgbox "No Record Found"
Cancel = True

Else
Me.Requery
Me.Refresh
End If
txtFindDriverName.Value = ""
[Field143].SetFocus
txtFindDriverName.Visible = False
txtFindMsg.Visible = False

On Error GoTo 0
Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & " (" & Err.Description & ") in procedure
txtFindDriverName_AfterUpdate of VBA Document Form_Incident Data Entry"

End Sub

What I am trying to achieve is that we only bring the records we need over
the network rather than all of them ever time we open form Incident Data
Entry.

Thanks Bob

Crystal thanks for the quick reply the SQL is as follows


SELECT DISTINCTROW [Telephone Checklist].*, [Telephone
Checklist].CustomerID, [Telephone Checklist].[Driver's Last Name],
[Telephone Checklist].[Registration Number], Employer.txtInFo,
Employer.txtInFoPlus, Employer.Comments, [Telephone Checklist2].*
FROM ([Telephone Checklist] LEFT JOIN Employer ON [Telephone
Checklist].Employer = Employer.Employer) INNER JOIN [Telephone Checklist2]
ON [Telephone Checklist].CustomerID = [Telephone Checklist2].CustomerID
WHERE ((([Telephone Checklist].CustomerID) Like IIf([Forms]![Incident Data
Entry]![txtFindCustomer]="","*","*" & [Forms]![Incident Data
Entry]![txtFindCustomer] & "*")) AND (([Telephone Checklist].[Driver's Last
Name]) Like IIf([Forms]![Incident Data Entry]![txtFindDriverName]="","*","*"
& [Forms]![Incident Data Entry]![txtFindDriverName] & "*")) AND (([Telephone
Checklist].[Registration Number]) Like IIf([Forms]![Incident Data
Entry]![txtFindRegNumber]="","*","*" & [Forms]![Incident Data
Entry]![txtFindRegNumber] & "*")))
ORDER BY [Telephone Checklist].CustomerID DESC;

The button I was trying to use was the Search Button which I thought would
run the code and open the Incident Data Entry form on the correct record.

Regards Bob



"strive4peace" wrote in message
...
Hi Bob,

what is the SQL for qryIncidentDataFind?

also, if it ends up your do things this way, you need to release your
objexct variables

instead of
'~~~~~~~~~~
Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & " (" & Err.Description & ") in
procedure
txtFindDriverName_AfterUpdate of VBA Document Form_Incident Data Entry"
'~~~~~~~~~~

you would use

'~~~~~~~~~~
Proc_Exit:
if not rs is nothing then
rs.close
set rs = nothing
end if
set QD = nothing
set db = nothing

Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & _
" (" & Err.Description _
& ") in procedure txtFindDriverName_AfterUpdate" _
& " of VBA Document Form_Incident Data Entry" _
,, "Error"

'~~~~~~~~~~

I like using generic names for the error handler label

instead of
txtFindDriverName_AfterUpdate_Error

use something like
Proc_Error

with recent versions of Access, labels only need to be unique within a
procedure, not unique within a module...

also, use line continuation (space, underscore at end of line) to use
multiple lines for long statements


~~~~

I am assuming you have a command button to open the next form?

~~~

if you want to clear a control, do this:
txtFindDriverName.Value = null
instead of this:
txtFindDriverName.Value = ""


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Bob wrote:
Hi I am trying to set up my db so that you open frmDataFind on this form
I have 3 fields
txtCustomerID
txtRegNumber
txtName

What I what to do is enter the search criteria in any of these fields say
22000 in the txtCustomerID field
I then want to hit the search button and it opens form Incident Data
Entry showing only record 22000

If I enter a Reg Number in txtRegNumber I want it to display all of the
reg numbers that match the one I entered
and the same for names in form Incident Data Entry.

I also want it to do a search for matches if I only have part of the reg
number or name, and lastly to open the form ready to enter a new record.

The form Incident Data Entry already has the above set up on it but it
opens showing all the records which can be very slow.

How do I adapt the following code to work in my new frmDataFind or is
there a better way of doing this?

Private Sub txtFindDriverName_AfterUpdate()

'Since we want to search on partial values, we have to use a query for
the
'record source of this form. We find the records with the query and
tell the
'form it can only see those records.
'Now since we want to search on either of three fields, we have to
control what
'happens to the criteria in the query, so if we are here, we are
trying to find by DriverName
'so we need to set RegNum to null:
On Error GoTo txtFindDriverName_AfterUpdate_Error

txtFindCustomer = "" 'so when the query runs it returns all
RegNum
txtFindRegNumber = "" 'but still filters on
Name, we do just the
'opposite in the code for the RegNum
Dim db As Database
Dim rs As Recordset
Dim QD As QueryDef

Set db = CurrentDb()
Set QD = db.QueryDefs("qryIncidentDataFind")
QD.Parameters(0) = "" 'Here we pass Null to RegNum in the query
QD.Parameters(1) = [Forms]![Incident Data Entry]![txtFindDriverName]
QD.Parameters(2) = ""
Set rs = QD.OpenRecordset()
Me.Form.AllowEdits = False

If rs.RecordCount 1 Then
msgbox "No Record Found"
Cancel = True

Else
Me.Requery
Me.Refresh
End If
txtFindDriverName.Value = ""
[Field143].SetFocus
txtFindDriverName.Visible = False
txtFindMsg.Visible = False

On Error GoTo 0
Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & " (" & Err.Description & ") in
procedure txtFindDriverName_AfterUpdate of VBA Document Form_Incident
Data Entry"

End Sub

What I am trying to achieve is that we only bring the records we need
over the network rather than all of them ever time we open form Incident
Data Entry.

Thanks Bob

Hi bob,

I formatted your SQL to be easier to read

SELECT DISTINCTROW [Telephone Checklist].*
, [Telephone Checklist].CustomerID
, [Telephone Checklist].[Driver's Last Name]
, [Telephone Checklist].[Registration Number]
, Employer.txtInFo
, Employer.txtInFoPlus
, Employer.Comments
, [Telephone Checklist2].*
FROM ([Telephone Checklist]
LEFT JOIN Employer
ON [Telephone Checklist].Employer = Employer.Employer)
INNER JOIN [Telephone Checklist2]
ON [Telephone Checklist].CustomerID
= [Telephone Checklist2].CustomerID
WHERE ((([Telephone Checklist].CustomerID) Like
IIf([Forms]![Incident Data Entry]![txtFindCustomer]="","*","*"
& [Forms]![Incident Data Entry]![txtFindCustomer] & "*"))
AND (([Telephone Checklist].[Driver's Last Name]) Like
IIf([Forms]![Incident Data Entry]![txtFindDriverName] _
="","*","*"
& [Forms]![Incident Data Entry]![txtFindDriverName] & "*"))
AND (([Telephone Checklist].[Registration Number]) Like
IIf([Forms]![Incident Data Entry]![txtFindRegNumber]
="","*","*"
& [Forms]![Incident Data Entry]![txtFindRegNumber] & "*")))
ORDER BY [Telephone Checklist].CustomerID DESC;


IMO, you should skip all the complex criteria and build the SQL in code.
then you can skip criteria that is not filled

why open the recordset in code and count records? If it is speed you
are after, no need to get the records twice... on the form OPEN event,
if it has no records, give a message to that effect and cancel the open
event

anyway, if you like that idea, read Access Basics (in my siggy) and play
close attention to the SQL section (Select statements) and building SQL
in code (think that is covered) ... that will give you a good foundation
to understand what comes next smile

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Bob wrote:
Crystal thanks for the quick reply the SQL is as follows


SELECT DISTINCTROW [Telephone Checklist].*, [Telephone
Checklist].CustomerID, [Telephone Checklist].[Driver's Last Name],
[Telephone Checklist].[Registration Number], Employer.txtInFo,
Employer.txtInFoPlus, Employer.Comments, [Telephone Checklist2].*
FROM ([Telephone Checklist] LEFT JOIN Employer ON [Telephone
Checklist].Employer = Employer.Employer) INNER JOIN [Telephone Checklist2]
ON [Telephone Checklist].CustomerID = [Telephone Checklist2].CustomerID
WHERE ((([Telephone Checklist].CustomerID) Like IIf([Forms]![Incident Data
Entry]![txtFindCustomer]="","*","*" & [Forms]![Incident Data
Entry]![txtFindCustomer] & "*")) AND (([Telephone Checklist].[Driver's Last
Name]) Like IIf([Forms]![Incident Data Entry]![txtFindDriverName]="","*","*"
& [Forms]![Incident Data Entry]![txtFindDriverName] & "*")) AND (([Telephone
Checklist].[Registration Number]) Like IIf([Forms]![Incident Data
Entry]![txtFindRegNumber]="","*","*" & [Forms]![Incident Data
Entry]![txtFindRegNumber] & "*")))
ORDER BY [Telephone Checklist].CustomerID DESC;

The button I was trying to use was the Search Button which I thought would
run the code and open the Incident Data Entry form on the correct record.

Regards Bob



"strive4peace" wrote in message
...
Hi Bob,

what is the SQL for qryIncidentDataFind?

also, if it ends up your do things this way, you need to release your
objexct variables

instead of
'~~~~~~~~~~
Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & " (" & Err.Description & ") in
procedure
txtFindDriverName_AfterUpdate of VBA Document Form_Incident Data Entry"
'~~~~~~~~~~

you would use

'~~~~~~~~~~
Proc_Exit:
if not rs is nothing then
rs.close
set rs = nothing
end if
set QD = nothing
set db = nothing

Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & _
" (" & Err.Description _
& ") in procedure txtFindDriverName_AfterUpdate" _
& " of VBA Document Form_Incident Data Entry" _
,, "Error"

'~~~~~~~~~~

I like using generic names for the error handler label

instead of
txtFindDriverName_AfterUpdate_Error

use something like
Proc_Error

with recent versions of Access, labels only need to be unique within a
procedure, not unique within a module...

also, use line continuation (space, underscore at end of line) to use
multiple lines for long statements


~~~~

I am assuming you have a command button to open the next form?

~~~

if you want to clear a control, do this:
txtFindDriverName.Value = null
instead of this:
txtFindDriverName.Value = ""


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Bob wrote:
Hi I am trying to set up my db so that you open frmDataFind on this form
I have 3 fields
txtCustomerID
txtRegNumber
txtName

What I what to do is enter the search criteria in any of these fields say
22000 in the txtCustomerID field
I then want to hit the search button and it opens form Incident Data
Entry showing only record 22000

If I enter a Reg Number in txtRegNumber I want it to display all of the
reg numbers that match the one I entered
and the same for names in form Incident Data Entry.

I also want it to do a search for matches if I only have part of the reg
number or name, and lastly to open the form ready to enter a new record.

The form Incident Data Entry already has the above set up on it but it
opens showing all the records which can be very slow.

How do I adapt the following code to work in my new frmDataFind or is
there a better way of doing this?

Private Sub txtFindDriverName_AfterUpdate()

'Since we want to search on partial values, we have to use a query for
the
'record source of this form. We find the records with the query and
tell the
'form it can only see those records.
'Now since we want to search on either of three fields, we have to
control what
'happens to the criteria in the query, so if we are here, we are
trying to find by DriverName
'so we need to set RegNum to null:
On Error GoTo txtFindDriverName_AfterUpdate_Error

txtFindCustomer = "" 'so when the query runs it returns all
RegNum
txtFindRegNumber = "" 'but still filters on
Name, we do just the
'opposite in the code for the RegNum
Dim db As Database
Dim rs As Recordset
Dim QD As QueryDef

Set db = CurrentDb()
Set QD = db.QueryDefs("qryIncidentDataFind")
QD.Parameters(0) = "" 'Here we pass Null to RegNum in the query
QD.Parameters(1) = [Forms]![Incident Data Entry]![txtFindDriverName]
QD.Parameters(2) = ""
Set rs = QD.OpenRecordset()
Me.Form.AllowEdits = False

If rs.RecordCount 1 Then
msgbox "No Record Found"
Cancel = True

Else
Me.Requery
Me.Refresh
End If
txtFindDriverName.Value = ""
[Field143].SetFocus
txtFindDriverName.Visible = False
txtFindMsg.Visible = False

On Error GoTo 0
Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & " (" & Err.Description & ") in
procedure txtFindDriverName_AfterUpdate of VBA Document Form_Incident
Data Entry"

End Sub

What I am trying to achieve is that we only bring the records we need
over the network rather than all of them ever time we open form Incident
Data Entry.

Thanks Bob

Crystal the idea is to have an efficient client/server application by
mininimising the amount of data sent to or fetched from the server, at the
moment tens of thousands of records are brought over very time the form
Incident Data Entry is opened. So not confessing to be any sort of expert
isn't the most efficient way of doing it, is for the operator to bring over
the records they want to work with only?

The Incident Data Entry form is in use most of the time and is opened and
closed many times during the day so surely it would be better to open the
form with only the data you need rather than all of the data?

Regards Bob


"strive4peace" wrote in message
...
Hi bob,

I formatted your SQL to be easier to read

SELECT DISTINCTROW [Telephone Checklist].*
, [Telephone Checklist].CustomerID
, [Telephone Checklist].[Driver's Last Name]
, [Telephone Checklist].[Registration Number]
, Employer.txtInFo
, Employer.txtInFoPlus
, Employer.Comments
, [Telephone Checklist2].*
FROM ([Telephone Checklist]
LEFT JOIN Employer
ON [Telephone Checklist].Employer = Employer.Employer)
INNER JOIN [Telephone Checklist2]
ON [Telephone Checklist].CustomerID
= [Telephone Checklist2].CustomerID
WHERE ((([Telephone Checklist].CustomerID) Like
IIf([Forms]![Incident Data Entry]![txtFindCustomer]="","*","*"
& [Forms]![Incident Data Entry]![txtFindCustomer] & "*"))
AND (([Telephone Checklist].[Driver's Last Name]) Like
IIf([Forms]![Incident Data Entry]![txtFindDriverName] _
="","*","*"
& [Forms]![Incident Data Entry]![txtFindDriverName] & "*"))
AND (([Telephone Checklist].[Registration Number]) Like
IIf([Forms]![Incident Data Entry]![txtFindRegNumber]
="","*","*"
& [Forms]![Incident Data Entry]![txtFindRegNumber] & "*")))
ORDER BY [Telephone Checklist].CustomerID DESC;


IMO, you should skip all the complex criteria and build the SQL in code.
then you can skip criteria that is not filled

why open the recordset in code and count records? If it is speed you are
after, no need to get the records twice... on the form OPEN event, if it
has no records, give a message to that effect and cancel the open event

anyway, if you like that idea, read Access Basics (in my siggy) and play
close attention to the SQL section (Select statements) and building SQL in
code (think that is covered) ... that will give you a good foundation to
understand what comes next smile

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Bob wrote:
Crystal thanks for the quick reply the SQL is as follows


SELECT DISTINCTROW [Telephone Checklist].*, [Telephone
Checklist].CustomerID, [Telephone Checklist].[Driver's Last Name],
[Telephone Checklist].[Registration Number], Employer.txtInFo,
Employer.txtInFoPlus, Employer.Comments, [Telephone Checklist2].*
FROM ([Telephone Checklist] LEFT JOIN Employer ON [Telephone
Checklist].Employer = Employer.Employer) INNER JOIN [Telephone
Checklist2] ON [Telephone Checklist].CustomerID = [Telephone
Checklist2].CustomerID
WHERE ((([Telephone Checklist].CustomerID) Like IIf([Forms]![Incident
Data Entry]![txtFindCustomer]="","*","*" & [Forms]![Incident Data
Entry]![txtFindCustomer] & "*")) AND (([Telephone Checklist].[Driver's
Last Name]) Like IIf([Forms]![Incident Data
Entry]![txtFindDriverName]="","*","*" & [Forms]![Incident Data
Entry]![txtFindDriverName] & "*")) AND (([Telephone
Checklist].[Registration Number]) Like IIf([Forms]![Incident Data
Entry]![txtFindRegNumber]="","*","*" & [Forms]![Incident Data
Entry]![txtFindRegNumber] & "*")))
ORDER BY [Telephone Checklist].CustomerID DESC;

The button I was trying to use was the Search Button which I thought
would run the code and open the Incident Data Entry form on the correct
record.

Regards Bob



"strive4peace" wrote in message
...
Hi Bob,

what is the SQL for qryIncidentDataFind?

also, if it ends up your do things this way, you need to release your
objexct variables

instead of
'~~~~~~~~~~
Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & " (" & Err.Description & ") in
procedure
txtFindDriverName_AfterUpdate of VBA Document Form_Incident Data Entry"
'~~~~~~~~~~

you would use

'~~~~~~~~~~
Proc_Exit:
if not rs is nothing then
rs.close
set rs = nothing
end if
set QD = nothing
set db = nothing

Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & _
" (" & Err.Description _
& ") in procedure txtFindDriverName_AfterUpdate" _
& " of VBA Document Form_Incident Data Entry" _
,, "Error"

'~~~~~~~~~~

I like using generic names for the error handler label

instead of
txtFindDriverName_AfterUpdate_Error

use something like
Proc_Error

with recent versions of Access, labels only need to be unique within a
procedure, not unique within a module...

also, use line continuation (space, underscore at end of line) to use
multiple lines for long statements


~~~~

I am assuming you have a command button to open the next form?

~~~

if you want to clear a control, do this:
txtFindDriverName.Value = null
instead of this:
txtFindDriverName.Value = ""


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Bob wrote:
Hi I am trying to set up my db so that you open frmDataFind on this
form I have 3 fields
txtCustomerID
txtRegNumber
txtName

What I what to do is enter the search criteria in any of these fields
say 22000 in the txtCustomerID field
I then want to hit the search button and it opens form Incident Data
Entry showing only record 22000

If I enter a Reg Number in txtRegNumber I want it to display all of the
reg numbers that match the one I entered
and the same for names in form Incident Data Entry.

I also want it to do a search for matches if I only have part of the
reg number or name, and lastly to open the form ready to enter a new
record.

The form Incident Data Entry already has the above set up on it but it
opens showing all the records which can be very slow.

How do I adapt the following code to work in my new frmDataFind or is
there a better way of doing this?

Private Sub txtFindDriverName_AfterUpdate()

'Since we want to search on partial values, we have to use a query for
the
'record source of this form. We find the records with the query
and tell the
'form it can only see those records.
'Now since we want to search on either of three fields, we have to
control what
'happens to the criteria in the query, so if we are here, we are
trying to find by DriverName
'so we need to set RegNum to null:
On Error GoTo txtFindDriverName_AfterUpdate_Error

txtFindCustomer = "" 'so when the query runs it returns all
RegNum
txtFindRegNumber = "" 'but still filters on
Name, we do just the
'opposite in the code for the RegNum
Dim db As Database
Dim rs As Recordset
Dim QD As QueryDef

Set db = CurrentDb()
Set QD = db.QueryDefs("qryIncidentDataFind")
QD.Parameters(0) = "" 'Here we pass Null to RegNum in the query
QD.Parameters(1) = [Forms]![Incident Data
Entry]![txtFindDriverName]
QD.Parameters(2) = ""
Set rs = QD.OpenRecordset()
Me.Form.AllowEdits = False

If rs.RecordCount 1 Then
msgbox "No Record Found"
Cancel = True

Else
Me.Requery
Me.Refresh
End If
txtFindDriverName.Value = ""
[Field143].SetFocus
txtFindDriverName.Visible = False
txtFindMsg.Visible = False

On Error GoTo 0
Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & " (" & Err.Description & ") in
procedure txtFindDriverName_AfterUpdate of VBA Document Form_Incident
Data Entry"

End Sub

What I am trying to achieve is that we only bring the records we need
over the network rather than all of them ever time we open form
Incident Data Entry.

Thanks Bob

Hi Bob,

how about replacing the SQL for the query that the form is based on
before you open the form ... something like this:

'~~~~~~~~~~~~~~~~~~
dim strSQL as string
dim mWhere as variant
mWhere = null

if Not isNull(me.txtFindCustomer) then
mWhere = " [Telephone Checklist].CustomerID =" _
& me.txtFindCustomer
end if

if Not isNull(me.txtFindDriverName) then
mWhere = (mwhere + " AND ") _
& " [Telephone Checklist].[Driver's Last Name] =" _
& "'" & me.txtFindDriverName & "'"
end if

if Not isNull(me.txtFindRegNumber) then
mWhere = (mwhere + " AND ") _
& " [Telephone Checklist].[Registration Number] =" _
& "'" & me.txtFindRegNumber & "'"
end if

strSQL = "SELECT DISTINCTROW [Telephone Checklist].* " _
& ", [Telephone Checklist].CustomerID " _
& ", [Telephone Checklist].[Driver's Last Name] " _
& ", [Telephone Checklist].[Registration Number] " _
& ", Employer.txtInFo " _
& ", Employer.txtInFoPlus " _
& ", Employer.Comments " _
& ", [Telephone Checklist2].* " _
& " FROM ([Telephone Checklist] " _
& " LEFT JOIN Employer " _
& " ON [Telephone Checklist].Employer = Employer.Employer) " _
& " INNER JOIN [Telephone Checklist2] " _
& " ON [Telephone Checklist].CustomerID " _
& " = [Telephone Checklist2].CustomerID" _
& (" WHERE " + mWhere) & ";"


MakeQuery strSQL, "qryIncidentDataFind"
'~~~~~~~~~~~~~~~~~~

Assumptions:
you are in the code behind the form [Incident Data Entry]
CustomerID is numeric
[Driver's Last Name] is text
[Registration Number] is text

when you clear controls for criteria, you will do this:
me.controlname = Null

~~

this code goes into a general (standard) module:

'~~~~~~~~~~~~~~~~~~~~~ MakeQuery
Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)

'modified 3-30-08
'crystal
'strive4peace2008 at yahoo dot com

On Error GoTo Proc_Err

debug.print pSql

'if query already exists, update the SQL
'if not, create the query

If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
'if query is open, close it
on error resume next
DoCmd.Close acQuery, qName, acSaveNo
On Error GoTo Proc_Err
CurrentDb.QueryDefs(qName).sql = pSql
End If

Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"

'Press F8 to step through code and find problem
'comment next line out when program is debugged
Stop: Resume

Resume Proc_exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~

then, on the Open event of the form:

'~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "Form has no records for specified criteria" _
, , "No records"
Cancel = True
End If
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~



Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Bob wrote:
Crystal the idea is to have an efficient client/server application by
mininimising the amount of data sent to or fetched from the server, at the
moment tens of thousands of records are brought over very time the form
Incident Data Entry is opened. So not confessing to be any sort of expert
isn't the most efficient way of doing it, is for the operator to bring over
the records they want to work with only?

The Incident Data Entry form is in use most of the time and is opened and
closed many times during the day so surely it would be better to open the
form with only the data you need rather than all of the data?

Regards Bob


"strive4peace" wrote in message
...
Hi bob,

I formatted your SQL to be easier to read

SELECT DISTINCTROW [Telephone Checklist].*
, [Telephone Checklist].CustomerID
, [Telephone Checklist].[Driver's Last Name]
, [Telephone Checklist].[Registration Number]
, Employer.txtInFo
, Employer.txtInFoPlus
, Employer.Comments
, [Telephone Checklist2].*
FROM ([Telephone Checklist]
LEFT JOIN Employer
ON [Telephone Checklist].Employer = Employer.Employer)
INNER JOIN [Telephone Checklist2]
ON [Telephone Checklist].CustomerID
= [Telephone Checklist2].CustomerID
WHERE ((([Telephone Checklist].CustomerID) Like
IIf([Forms]![Incident Data Entry]![txtFindCustomer]="","*","*"
& [Forms]![Incident Data Entry]![txtFindCustomer] & "*"))
AND (([Telephone Checklist].[Driver's Last Name]) Like
IIf([Forms]![Incident Data Entry]![txtFindDriverName] _
="","*","*"
& [Forms]![Incident Data Entry]![txtFindDriverName] & "*"))
AND (([Telephone Checklist].[Registration Number]) Like
IIf([Forms]![Incident Data Entry]![txtFindRegNumber]
="","*","*"
& [Forms]![Incident Data Entry]![txtFindRegNumber] & "*")))
ORDER BY [Telephone Checklist].CustomerID DESC;


IMO, you should skip all the complex criteria and build the SQL in code.
then you can skip criteria that is not filled

why open the recordset in code and count records? If it is speed you are
after, no need to get the records twice... on the form OPEN event, if it
has no records, give a message to that effect and cancel the open event

anyway, if you like that idea, read Access Basics (in my siggy) and play
close attention to the SQL section (Select statements) and building SQL in
code (think that is covered) ... that will give you a good foundation to
understand what comes next smile

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Bob wrote:
Crystal thanks for the quick reply the SQL is as follows


SELECT DISTINCTROW [Telephone Checklist].*, [Telephone
Checklist].CustomerID, [Telephone Checklist].[Driver's Last Name],
[Telephone Checklist].[Registration Number], Employer.txtInFo,
Employer.txtInFoPlus, Employer.Comments, [Telephone Checklist2].*
FROM ([Telephone Checklist] LEFT JOIN Employer ON [Telephone
Checklist].Employer = Employer.Employer) INNER JOIN [Telephone
Checklist2] ON [Telephone Checklist].CustomerID = [Telephone
Checklist2].CustomerID
WHERE ((([Telephone Checklist].CustomerID) Like IIf([Forms]![Incident
Data Entry]![txtFindCustomer]="","*","*" & [Forms]![Incident Data
Entry]![txtFindCustomer] & "*")) AND (([Telephone Checklist].[Driver's
Last Name]) Like IIf([Forms]![Incident Data
Entry]![txtFindDriverName]="","*","*" & [Forms]![Incident Data
Entry]![txtFindDriverName] & "*")) AND (([Telephone
Checklist].[Registration Number]) Like IIf([Forms]![Incident Data
Entry]![txtFindRegNumber]="","*","*" & [Forms]![Incident Data
Entry]![txtFindRegNumber] & "*")))
ORDER BY [Telephone Checklist].CustomerID DESC;

The button I was trying to use was the Search Button which I thought
would run the code and open the Incident Data Entry form on the correct
record.

Regards Bob



"strive4peace" wrote in message
...
Hi Bob,

what is the SQL for qryIncidentDataFind?

also, if it ends up your do things this way, you need to release your
objexct variables

instead of
'~~~~~~~~~~
Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & " (" & Err.Description & ") in
procedure
txtFindDriverName_AfterUpdate of VBA Document Form_Incident Data Entry"
'~~~~~~~~~~

you would use

'~~~~~~~~~~
Proc_Exit:
if not rs is nothing then
rs.close
set rs = nothing
end if
set QD = nothing
set db = nothing

Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & _
" (" & Err.Description _
& ") in procedure txtFindDriverName_AfterUpdate" _
& " of VBA Document Form_Incident Data Entry" _
,, "Error"

'~~~~~~~~~~

I like using generic names for the error handler label

instead of
txtFindDriverName_AfterUpdate_Error

use something like
Proc_Error

with recent versions of Access, labels only need to be unique within a
procedure, not unique within a module...

also, use line continuation (space, underscore at end of line) to use
multiple lines for long statements


~~~~

I am assuming you have a command button to open the next form?

~~~

if you want to clear a control, do this:
txtFindDriverName.Value = null
instead of this:
txtFindDriverName.Value = ""


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Bob wrote:
Hi I am trying to set up my db so that you open frmDataFind on this
form I have 3 fields
txtCustomerID
txtRegNumber
txtName

What I what to do is enter the search criteria in any of these fields
say 22000 in the txtCustomerID field
I then want to hit the search button and it opens form Incident Data
Entry showing only record 22000

If I enter a Reg Number in txtRegNumber I want it to display all of the
reg numbers that match the one I entered
and the same for names in form Incident Data Entry.

I also want it to do a search for matches if I only have part of the
reg number or name, and lastly to open the form ready to enter a new
record.

The form Incident Data Entry already has the above set up on it but it
opens showing all the records which can be very slow.

How do I adapt the following code to work in my new frmDataFind or is
there a better way of doing this?

Private Sub txtFindDriverName_AfterUpdate()

'Since we want to search on partial values, we have to use a query for
the
'record source of this form. We find the records with the query
and tell the
'form it can only see those records.
'Now since we want to search on either of three fields, we have to
control what
'happens to the criteria in the query, so if we are here, we are
trying to find by DriverName
'so we need to set RegNum to null:
On Error GoTo txtFindDriverName_AfterUpdate_Error

txtFindCustomer = "" 'so when the query runs it returns all
RegNum
txtFindRegNumber = "" 'but still filters on
Name, we do just the
'opposite in the code for the RegNum
Dim db As Database
Dim rs As Recordset
Dim QD As QueryDef

Set db = CurrentDb()
Set QD = db.QueryDefs("qryIncidentDataFind")
QD.Parameters(0) = "" 'Here we pass Null to RegNum in the query
QD.Parameters(1) = [Forms]![Incident Data
Entry]![txtFindDriverName]
QD.Parameters(2) = ""
Set rs = QD.OpenRecordset()
Me.Form.AllowEdits = False

If rs.RecordCount 1 Then
msgbox "No Record Found"
Cancel = True

Else
Me.Requery
Me.Refresh
End If
txtFindDriverName.Value = ""
[Field143].SetFocus
txtFindDriverName.Visible = False
txtFindMsg.Visible = False

On Error GoTo 0
Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & " (" & Err.Description & ") in
procedure txtFindDriverName_AfterUpdate of VBA Document Form_Incident
Data Entry"

End Sub

What I am trying to achieve is that we only bring the records we need
over the network rather than all of them ever time we open form
Incident Data Entry.

Thanks Bob

Crystal I need a bit more help here, working up from the bottom of the page
I have pasted the code into the forms Open Event no problem so far.
I then pasted the code into a new module and called it MakeQuery

I am not sure what you mean here
Assumptions:
you are in the code behind the form [Incident Data Entry]
CustomerID is numeric This is an Auto Number Field so yes it
is numeric
[Driver's Last Name] is text Yes this is a text field
[Registration Number] is text Yes this is a text field

when you clear controls for criteria, you will do this:
me.controlname = Null This bit I do not under stand sorry

And where exactly does this code go? sorry to sound a bit vague here but I
pasted it in to the query that the form is based on Incident Data Find and
it came up with a big list of errors so that must have been the wrong place.

Please don't forget I am fairly new at all this so I probably need more help
than most.

how about replacing the SQL for the query that the form is based on before
you open the form ... something like this:

'~~~~~~~~~~~~~~~~~~
dim strSQL as string
dim mWhere as variant
mWhere = null

if Not isNull(me.txtFindCustomer) then
mWhere = " [Telephone Checklist].CustomerID =" _
& me.txtFindCustomer
end if

if Not isNull(me.txtFindDriverName) then
mWhere = (mwhere + " AND ") _
& " [Telephone Checklist].[Driver's Last Name] =" _
& "'" & me.txtFindDriverName & "'"
end if

if Not isNull(me.txtFindRegNumber) then
mWhere = (mwhere + " AND ") _
& " [Telephone Checklist].[Registration Number] =" _
& "'" & me.txtFindRegNumber & "'"
end if

strSQL = "SELECT DISTINCTROW [Telephone Checklist].* " _
& ", [Telephone Checklist].CustomerID " _
& ", [Telephone Checklist].[Driver's Last Name] " _
& ", [Telephone Checklist].[Registration Number] " _
& ", Employer.txtInFo " _
& ", Employer.txtInFoPlus " _
& ", Employer.Comments " _
& ", [Telephone Checklist2].* " _
& " FROM ([Telephone Checklist] " _
& " LEFT JOIN Employer " _
& " ON [Telephone Checklist].Employer = Employer.Employer) " _
& " INNER JOIN [Telephone Checklist2] " _
& " ON [Telephone Checklist].CustomerID " _
& " = [Telephone Checklist2].CustomerID" _
& (" WHERE " + mWhere) & ";"


MakeQuery strSQL, "qryIncidentDataFind"

Thanks very much for your time it is appreciated

Bob




"strive4peace" wrote in message
...
Hi Bob,

how about replacing the SQL for the query that the form is based on before
you open the form ... something like this:

'~~~~~~~~~~~~~~~~~~
dim strSQL as string
dim mWhere as variant
mWhere = null

if Not isNull(me.txtFindCustomer) then
mWhere = " [Telephone Checklist].CustomerID =" _
& me.txtFindCustomer
end if

if Not isNull(me.txtFindDriverName) then
mWhere = (mwhere + " AND ") _
& " [Telephone Checklist].[Driver's Last Name] =" _
& "'" & me.txtFindDriverName & "'"
end if

if Not isNull(me.txtFindRegNumber) then
mWhere = (mwhere + " AND ") _
& " [Telephone Checklist].[Registration Number] =" _
& "'" & me.txtFindRegNumber & "'"
end if

strSQL = "SELECT DISTINCTROW [Telephone Checklist].* " _
& ", [Telephone Checklist].CustomerID " _
& ", [Telephone Checklist].[Driver's Last Name] " _
& ", [Telephone Checklist].[Registration Number] " _
& ", Employer.txtInFo " _
& ", Employer.txtInFoPlus " _
& ", Employer.Comments " _
& ", [Telephone Checklist2].* " _
& " FROM ([Telephone Checklist] " _
& " LEFT JOIN Employer " _
& " ON [Telephone Checklist].Employer = Employer.Employer) " _
& " INNER JOIN [Telephone Checklist2] " _
& " ON [Telephone Checklist].CustomerID " _
& " = [Telephone Checklist2].CustomerID" _
& (" WHERE " + mWhere) & ";"


MakeQuery strSQL, "qryIncidentDataFind"
'~~~~~~~~~~~~~~~~~~

Assumptions:
you are in the code behind the form [Incident Data Entry]
CustomerID is numeric
[Driver's Last Name] is text
[Registration Number] is text

when you clear controls for criteria, you will do this:
me.controlname = Null

~~

this code goes into a general (standard) module:

'~~~~~~~~~~~~~~~~~~~~~ MakeQuery
Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)

'modified 3-30-08
'crystal
'strive4peace2008 at yahoo dot com

On Error GoTo Proc_Err

debug.print pSql

'if query already exists, update the SQL
'if not, create the query

If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
'if query is open, close it
on error resume next
DoCmd.Close acQuery, qName, acSaveNo
On Error GoTo Proc_Err
CurrentDb.QueryDefs(qName).sql = pSql
End If

Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"

'Press F8 to step through code and find problem
'comment next line out when program is debugged
Stop: Resume

Resume Proc_exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~

then, on the Open event of the form:

'~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "Form has no records for specified criteria" _
, , "No records"
Cancel = True
End If
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~



Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Bob wrote:
Crystal the idea is to have an efficient client/server application by
mininimising the amount of data sent to or fetched from the server, at
the moment tens of thousands of records are brought over very time the
form Incident Data Entry is opened. So not confessing to be any sort of
expert isn't the most efficient way of doing it, is for the operator to
bring over the records they want to work with only?

The Incident Data Entry form is in use most of the time and is opened and
closed many times during the day so surely it would be better to open the
form with only the data you need rather than all of the data?

Regards Bob


"strive4peace" wrote in message
...
Hi bob,

I formatted your SQL to be easier to read

SELECT DISTINCTROW [Telephone Checklist].*
, [Telephone Checklist].CustomerID
, [Telephone Checklist].[Driver's Last Name]
, [Telephone Checklist].[Registration Number]
, Employer.txtInFo
, Employer.txtInFoPlus
, Employer.Comments
, [Telephone Checklist2].*
FROM ([Telephone Checklist]
LEFT JOIN Employer
ON [Telephone Checklist].Employer = Employer.Employer)
INNER JOIN [Telephone Checklist2]
ON [Telephone Checklist].CustomerID
= [Telephone Checklist2].CustomerID
WHERE ((([Telephone Checklist].CustomerID) Like
IIf([Forms]![Incident Data Entry]![txtFindCustomer]="","*","*"
& [Forms]![Incident Data Entry]![txtFindCustomer] & "*"))
AND (([Telephone Checklist].[Driver's Last Name]) Like
IIf([Forms]![Incident Data Entry]![txtFindDriverName] _
="","*","*"
& [Forms]![Incident Data Entry]![txtFindDriverName] & "*"))
AND (([Telephone Checklist].[Registration Number]) Like
IIf([Forms]![Incident Data Entry]![txtFindRegNumber]
="","*","*"
& [Forms]![Incident Data Entry]![txtFindRegNumber] & "*")))
ORDER BY [Telephone Checklist].CustomerID DESC;


IMO, you should skip all the complex criteria and build the SQL in code.
then you can skip criteria that is not filled

why open the recordset in code and count records? If it is speed you
are after, no need to get the records twice... on the form OPEN event,
if it has no records, give a message to that effect and cancel the open
event

anyway, if you like that idea, read Access Basics (in my siggy) and play
close attention to the SQL section (Select statements) and building SQL
in code (think that is covered) ... that will give you a good foundation
to understand what comes next smile

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Bob wrote:
Crystal thanks for the quick reply the SQL is as follows


SELECT DISTINCTROW [Telephone Checklist].*, [Telephone
Checklist].CustomerID, [Telephone Checklist].[Driver's Last Name],
[Telephone Checklist].[Registration Number], Employer.txtInFo,
Employer.txtInFoPlus, Employer.Comments, [Telephone Checklist2].*
FROM ([Telephone Checklist] LEFT JOIN Employer ON [Telephone
Checklist].Employer = Employer.Employer) INNER JOIN [Telephone
Checklist2] ON [Telephone Checklist].CustomerID = [Telephone
Checklist2].CustomerID
WHERE ((([Telephone Checklist].CustomerID) Like IIf([Forms]![Incident
Data Entry]![txtFindCustomer]="","*","*" & [Forms]![Incident Data
Entry]![txtFindCustomer] & "*")) AND (([Telephone Checklist].[Driver's
Last Name]) Like IIf([Forms]![Incident Data
Entry]![txtFindDriverName]="","*","*" & [Forms]![Incident Data
Entry]![txtFindDriverName] & "*")) AND (([Telephone
Checklist].[Registration Number]) Like IIf([Forms]![Incident Data
Entry]![txtFindRegNumber]="","*","*" & [Forms]![Incident Data
Entry]![txtFindRegNumber] & "*")))
ORDER BY [Telephone Checklist].CustomerID DESC;

The button I was trying to use was the Search Button which I thought
would run the code and open the Incident Data Entry form on the correct
record.

Regards Bob



"strive4peace" wrote in message
...
Hi Bob,

what is the SQL for qryIncidentDataFind?

also, if it ends up your do things this way, you need to release your
objexct variables

instead of
'~~~~~~~~~~
Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & " (" & Err.Description & ") in
procedure
txtFindDriverName_AfterUpdate of VBA Document Form_Incident Data
Entry"
'~~~~~~~~~~

you would use

'~~~~~~~~~~
Proc_Exit:
if not rs is nothing then
rs.close
set rs = nothing
end if
set QD = nothing
set db = nothing

Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & _
" (" & Err.Description _
& ") in procedure txtFindDriverName_AfterUpdate" _
& " of VBA Document Form_Incident Data Entry" _
,, "Error"

'~~~~~~~~~~

I like using generic names for the error handler label

instead of
txtFindDriverName_AfterUpdate_Error

use something like
Proc_Error

with recent versions of Access, labels only need to be unique within a
procedure, not unique within a module...

also, use line continuation (space, underscore at end of line) to use
multiple lines for long statements


~~~~

I am assuming you have a command button to open the next form?

~~~

if you want to clear a control, do this:
txtFindDriverName.Value = null
instead of this:
txtFindDriverName.Value = ""


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Bob wrote:
Hi I am trying to set up my db so that you open frmDataFind on this
form I have 3 fields
txtCustomerID
txtRegNumber
txtName

What I what to do is enter the search criteria in any of these fields
say 22000 in the txtCustomerID field
I then want to hit the search button and it opens form Incident Data
Entry showing only record 22000

If I enter a Reg Number in txtRegNumber I want it to display all of
the reg numbers that match the one I entered
and the same for names in form Incident Data Entry.

I also want it to do a search for matches if I only have part of the
reg number or name, and lastly to open the form ready to enter a new
record.

The form Incident Data Entry already has the above set up on it but
it opens showing all the records which can be very slow.

How do I adapt the following code to work in my new frmDataFind or is
there a better way of doing this?

Private Sub txtFindDriverName_AfterUpdate()

'Since we want to search on partial values, we have to use a query
for the
'record source of this form. We find the records with the query
and tell the
'form it can only see those records.
'Now since we want to search on either of three fields, we have
to control what
'happens to the criteria in the query, so if we are here, we are
trying to find by DriverName
'so we need to set RegNum to null:
On Error GoTo txtFindDriverName_AfterUpdate_Error

txtFindCustomer = "" 'so when the query runs it returns all
RegNum
txtFindRegNumber = "" 'but still filters on
Name, we do just the
'opposite in the code for the RegNum
Dim db As Database
Dim rs As Recordset
Dim QD As QueryDef

Set db = CurrentDb()
Set QD = db.QueryDefs("qryIncidentDataFind")
QD.Parameters(0) = "" 'Here we pass Null to RegNum in the query
QD.Parameters(1) = [Forms]![Incident Data
Entry]![txtFindDriverName]
QD.Parameters(2) = ""
Set rs = QD.OpenRecordset()
Me.Form.AllowEdits = False

If rs.RecordCount 1 Then
msgbox "No Record Found"
Cancel = True

Else
Me.Requery
Me.Refresh
End If
txtFindDriverName.Value = ""
[Field143].SetFocus
txtFindDriverName.Visible = False
txtFindMsg.Visible = False

On Error GoTo 0
Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & " (" & Err.Description & ") in
procedure txtFindDriverName_AfterUpdate of VBA Document Form_Incident
Data Entry"

End Sub

What I am trying to achieve is that we only bring the records we need
over the network rather than all of them ever time we open form
Incident Data Entry.

Thanks Bob

Hi Bob,

only the MakeQuery code goes into a general module ... and I would
recommend that you name it something other than the procedure it
contains ... maybe bas_MakeQuery or mod_MakeQuery

the first snippet of code goes behind the form where you prepare the
query for the form you will pop up

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~~

read the Access Basics doc in my siggy smile


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Bob wrote:
Crystal I need a bit more help here, working up from the bottom of the page
I have pasted the code into the forms Open Event no problem so far.
I then pasted the code into a new module and called it MakeQuery

I am not sure what you mean here
Assumptions:
you are in the code behind the form [Incident Data Entry]
CustomerID is numeric This is an Auto Number Field so yes it
is numeric
[Driver's Last Name] is text Yes this is a text field
[Registration Number] is text Yes this is a text field

when you clear controls for criteria, you will do this:
me.controlname = Null This bit I do not under stand sorry

And where exactly does this code go? sorry to sound a bit vague here but I
pasted it in to the query that the form is based on Incident Data Find and
it came up with a big list of errors so that must have been the wrong place.

Please don't forget I am fairly new at all this so I probably need more help
than most.

how about replacing the SQL for the query that the form is based on before
you open the form ... something like this:

'~~~~~~~~~~~~~~~~~~
dim strSQL as string
dim mWhere as variant
mWhere = null

if Not isNull(me.txtFindCustomer) then
mWhere = " [Telephone Checklist].CustomerID =" _
& me.txtFindCustomer
end if

if Not isNull(me.txtFindDriverName) then
mWhere = (mwhere + " AND ") _
& " [Telephone Checklist].[Driver's Last Name] =" _
& "'" & me.txtFindDriverName & "'"
end if

if Not isNull(me.txtFindRegNumber) then
mWhere = (mwhere + " AND ") _
& " [Telephone Checklist].[Registration Number] =" _
& "'" & me.txtFindRegNumber & "'"
end if

strSQL = "SELECT DISTINCTROW [Telephone Checklist].* " _
& ", [Telephone Checklist].CustomerID " _
& ", [Telephone Checklist].[Driver's Last Name] " _
& ", [Telephone Checklist].[Registration Number] " _
& ", Employer.txtInFo " _
& ", Employer.txtInFoPlus " _
& ", Employer.Comments " _
& ", [Telephone Checklist2].* " _
& " FROM ([Telephone Checklist] " _
& " LEFT JOIN Employer " _
& " ON [Telephone Checklist].Employer = Employer.Employer) " _
& " INNER JOIN [Telephone Checklist2] " _
& " ON [Telephone Checklist].CustomerID " _
& " = [Telephone Checklist2].CustomerID" _
& (" WHERE " + mWhere) & ";"


MakeQuery strSQL, "qryIncidentDataFind"

Thanks very much for your time it is appreciated

Bob




"strive4peace" wrote in message
...
Hi Bob,

how about replacing the SQL for the query that the form is based on before
you open the form ... something like this:

'~~~~~~~~~~~~~~~~~~
dim strSQL as string
dim mWhere as variant
mWhere = null

if Not isNull(me.txtFindCustomer) then
mWhere = " [Telephone Checklist].CustomerID =" _
& me.txtFindCustomer
end if

if Not isNull(me.txtFindDriverName) then
mWhere = (mwhere + " AND ") _
& " [Telephone Checklist].[Driver's Last Name] =" _
& "'" & me.txtFindDriverName & "'"
end if

if Not isNull(me.txtFindRegNumber) then
mWhere = (mwhere + " AND ") _
& " [Telephone Checklist].[Registration Number] =" _
& "'" & me.txtFindRegNumber & "'"
end if

strSQL = "SELECT DISTINCTROW [Telephone Checklist].* " _
& ", [Telephone Checklist].CustomerID " _
& ", [Telephone Checklist].[Driver's Last Name] " _
& ", [Telephone Checklist].[Registration Number] " _
& ", Employer.txtInFo " _
& ", Employer.txtInFoPlus " _
& ", Employer.Comments " _
& ", [Telephone Checklist2].* " _
& " FROM ([Telephone Checklist] " _
& " LEFT JOIN Employer " _
& " ON [Telephone Checklist].Employer = Employer.Employer) " _
& " INNER JOIN [Telephone Checklist2] " _
& " ON [Telephone Checklist].CustomerID " _
& " = [Telephone Checklist2].CustomerID" _
& (" WHERE " + mWhere) & ";"


MakeQuery strSQL, "qryIncidentDataFind"
'~~~~~~~~~~~~~~~~~~

Assumptions:
you are in the code behind the form [Incident Data Entry]
CustomerID is numeric
[Driver's Last Name] is text
[Registration Number] is text

when you clear controls for criteria, you will do this:
me.controlname = Null

~~

this code goes into a general (standard) module:

'~~~~~~~~~~~~~~~~~~~~~ MakeQuery
Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)

'modified 3-30-08
'crystal
'strive4peace2008 at yahoo dot com

On Error GoTo Proc_Err

debug.print pSql

'if query already exists, update the SQL
'if not, create the query

If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
'if query is open, close it
on error resume next
DoCmd.Close acQuery, qName, acSaveNo
On Error GoTo Proc_Err
CurrentDb.QueryDefs(qName).sql = pSql
End If

Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"

'Press F8 to step through code and find problem
'comment next line out when program is debugged
Stop: Resume

Resume Proc_exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~

then, on the Open event of the form:

'~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "Form has no records for specified criteria" _
, , "No records"
Cancel = True
End If
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~



Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Bob wrote:
Crystal the idea is to have an efficient client/server application by
mininimising the amount of data sent to or fetched from the server, at
the moment tens of thousands of records are brought over very time the
form Incident Data Entry is opened. So not confessing to be any sort of
expert isn't the most efficient way of doing it, is for the operator to
bring over the records they want to work with only?

The Incident Data Entry form is in use most of the time and is opened and
closed many times during the day so surely it would be better to open the
form with only the data you need rather than all of the data?

Regards Bob


"strive4peace" wrote in message
...
Hi bob,

I formatted your SQL to be easier to read

SELECT DISTINCTROW [Telephone Checklist].*
, [Telephone Checklist].CustomerID
, [Telephone Checklist].[Driver's Last Name]
, [Telephone Checklist].[Registration Number]
, Employer.txtInFo
, Employer.txtInFoPlus
, Employer.Comments
, [Telephone Checklist2].*
FROM ([Telephone Checklist]
LEFT JOIN Employer
ON [Telephone Checklist].Employer = Employer.Employer)
INNER JOIN [Telephone Checklist2]
ON [Telephone Checklist].CustomerID
= [Telephone Checklist2].CustomerID
WHERE ((([Telephone Checklist].CustomerID) Like
IIf([Forms]![Incident Data Entry]![txtFindCustomer]="","*","*"
& [Forms]![Incident Data Entry]![txtFindCustomer] & "*"))
AND (([Telephone Checklist].[Driver's Last Name]) Like
IIf([Forms]![Incident Data Entry]![txtFindDriverName] _
="","*","*"
& [Forms]![Incident Data Entry]![txtFindDriverName] & "*"))
AND (([Telephone Checklist].[Registration Number]) Like
IIf([Forms]![Incident Data Entry]![txtFindRegNumber]
="","*","*"
& [Forms]![Incident Data Entry]![txtFindRegNumber] & "*")))
ORDER BY [Telephone Checklist].CustomerID DESC;


IMO, you should skip all the complex criteria and build the SQL in code.
then you can skip criteria that is not filled

why open the recordset in code and count records? If it is speed you
are after, no need to get the records twice... on the form OPEN event,
if it has no records, give a message to that effect and cancel the open
event

anyway, if you like that idea, read Access Basics (in my siggy) and play
close attention to the SQL section (Select statements) and building SQL
in code (think that is covered) ... that will give you a good foundation
to understand what comes next smile

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Bob wrote:
Crystal thanks for the quick reply the SQL is as follows


SELECT DISTINCTROW [Telephone Checklist].*, [Telephone
Checklist].CustomerID, [Telephone Checklist].[Driver's Last Name],
[Telephone Checklist].[Registration Number], Employer.txtInFo,
Employer.txtInFoPlus, Employer.Comments, [Telephone Checklist2].*
FROM ([Telephone Checklist] LEFT JOIN Employer ON [Telephone
Checklist].Employer = Employer.Employer) INNER JOIN [Telephone
Checklist2] ON [Telephone Checklist].CustomerID = [Telephone
Checklist2].CustomerID
WHERE ((([Telephone Checklist].CustomerID) Like IIf([Forms]![Incident
Data Entry]![txtFindCustomer]="","*","*" & [Forms]![Incident Data
Entry]![txtFindCustomer] & "*")) AND (([Telephone Checklist].[Driver's
Last Name]) Like IIf([Forms]![Incident Data
Entry]![txtFindDriverName]="","*","*" & [Forms]![Incident Data
Entry]![txtFindDriverName] & "*")) AND (([Telephone
Checklist].[Registration Number]) Like IIf([Forms]![Incident Data
Entry]![txtFindRegNumber]="","*","*" & [Forms]![Incident Data
Entry]![txtFindRegNumber] & "*")))
ORDER BY [Telephone Checklist].CustomerID DESC;

The button I was trying to use was the Search Button which I thought
would run the code and open the Incident Data Entry form on the correct
record.

Regards Bob



"strive4peace" wrote in message
...
Hi Bob,

what is the SQL for qryIncidentDataFind?

also, if it ends up your do things this way, you need to release your
objexct variables

instead of
'~~~~~~~~~~
Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & " (" & Err.Description & ") in
procedure
txtFindDriverName_AfterUpdate of VBA Document Form_Incident Data
Entry"
'~~~~~~~~~~

you would use

'~~~~~~~~~~
Proc_Exit:
if not rs is nothing then
rs.close
set rs = nothing
end if
set QD = nothing
set db = nothing

Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & _
" (" & Err.Description _
& ") in procedure txtFindDriverName_AfterUpdate" _
& " of VBA Document Form_Incident Data Entry" _
,, "Error"

'~~~~~~~~~~

I like using generic names for the error handler label

instead of
txtFindDriverName_AfterUpdate_Error

use something like
Proc_Error

with recent versions of Access, labels only need to be unique within a
procedure, not unique within a module...

also, use line continuation (space, underscore at end of line) to use
multiple lines for long statements


~~~~

I am assuming you have a command button to open the next form?

~~~

if you want to clear a control, do this:
txtFindDriverName.Value = null
instead of this:
txtFindDriverName.Value = ""


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Bob wrote:
Hi I am trying to set up my db so that you open frmDataFind on this
form I have 3 fields
txtCustomerID
txtRegNumber
txtName

What I what to do is enter the search criteria in any of these fields
say 22000 in the txtCustomerID field
I then want to hit the search button and it opens form Incident Data
Entry showing only record 22000

If I enter a Reg Number in txtRegNumber I want it to display all of
the reg numbers that match the one I entered
and the same for names in form Incident Data Entry.

I also want it to do a search for matches if I only have part of the
reg number or name, and lastly to open the form ready to enter a new
record.

The form Incident Data Entry already has the above set up on it but
it opens showing all the records which can be very slow.

How do I adapt the following code to work in my new frmDataFind or is
there a better way of doing this?

Private Sub txtFindDriverName_AfterUpdate()

'Since we want to search on partial values, we have to use a query
for the
'record source of this form. We find the records with the query
and tell the
'form it can only see those records.
'Now since we want to search on either of three fields, we have
to control what
'happens to the criteria in the query, so if we are here, we are
trying to find by DriverName
'so we need to set RegNum to null:
On Error GoTo txtFindDriverName_AfterUpdate_Error

txtFindCustomer = "" 'so when the query runs it returns all
RegNum
txtFindRegNumber = "" 'but still filters on
Name, we do just the
'opposite in the code for the RegNum
Dim db As Database
Dim rs As Recordset
Dim QD As QueryDef

Set db = CurrentDb()
Set QD = db.QueryDefs("qryIncidentDataFind")
QD.Parameters(0) = "" 'Here we pass Null to RegNum in the query
QD.Parameters(1) = [Forms]![Incident Data
Entry]![txtFindDriverName]
QD.Parameters(2) = ""
Set rs = QD.OpenRecordset()
Me.Form.AllowEdits = False

If rs.RecordCount 1 Then
msgbox "No Record Found"
Cancel = True

Else
Me.Requery
Me.Refresh
End If
txtFindDriverName.Value = ""
[Field143].SetFocus
txtFindDriverName.Visible = False
txtFindMsg.Visible = False

On Error GoTo 0
Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & " (" & Err.Description & ") in
procedure txtFindDriverName_AfterUpdate of VBA Document Form_Incident
Data Entry"

End Sub

What I am trying to achieve is that we only bring the records we need
over the network rather than all of them ever time we open form
Incident Data Entry.

Thanks Bob

Crystal This is how its laid out frmDataFind is a form with 3 fields
txtFindCustomer
txtFindDriverName
txtFindRegNumber
and one command button called Search

On the open event I have pasted the code starting with
dim strSQL as string
dim mWhere as variant
mWhere = null

Is this correct?

I have renamed the module

What code should go behind the search button on the frmDataFind that opens
up the Incident Data Entry form with the criteria from any of the 3 txtFind
fields?

Or am I barking up the wrong tree completely?

Bob


"strive4peace" wrote in message
...
Hi Bob,

only the MakeQuery code goes into a general module ... and I would
recommend that you name it something other than the procedure it contains
... maybe bas_MakeQuery or mod_MakeQuery

the first snippet of code goes behind the form where you prepare the query
for the form you will pop up

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile before
executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~~

read the Access Basics doc in my siggy smile


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Bob wrote:
Crystal I need a bit more help here, working up from the bottom of the
page I have pasted the code into the forms Open Event no problem so far.
I then pasted the code into a new module and called it MakeQuery

I am not sure what you mean here
Assumptions:
you are in the code behind the form [Incident Data Entry]
CustomerID is numeric This is an Auto Number Field so yes
it is numeric
[Driver's Last Name] is text Yes this is a text field
[Registration Number] is text Yes this is a text field

when you clear controls for criteria, you will do this:
me.controlname = Null This bit I do not under stand sorry

And where exactly does this code go? sorry to sound a bit vague here but
I pasted it in to the query that the form is based on Incident Data Find
and
it came up with a big list of errors so that must have been the wrong
place.

Please don't forget I am fairly new at all this so I probably need more
help than most.

how about replacing the SQL for the query that the form is based on
before you open the form ... something like this:

'~~~~~~~~~~~~~~~~~~
dim strSQL as string
dim mWhere as variant
mWhere = null

if Not isNull(me.txtFindCustomer) then
mWhere = " [Telephone Checklist].CustomerID =" _
& me.txtFindCustomer
end if

if Not isNull(me.txtFindDriverName) then
mWhere = (mwhere + " AND ") _
& " [Telephone Checklist].[Driver's Last Name] =" _
& "'" & me.txtFindDriverName & "'"
end if

if Not isNull(me.txtFindRegNumber) then
mWhere = (mwhere + " AND ") _
& " [Telephone Checklist].[Registration Number] =" _
& "'" & me.txtFindRegNumber & "'"
end if

strSQL = "SELECT DISTINCTROW [Telephone Checklist].* " _
& ", [Telephone Checklist].CustomerID " _
& ", [Telephone Checklist].[Driver's Last Name] " _
& ", [Telephone Checklist].[Registration Number] " _
& ", Employer.txtInFo " _
& ", Employer.txtInFoPlus " _
& ", Employer.Comments " _
& ", [Telephone Checklist2].* " _
& " FROM ([Telephone Checklist] " _
& " LEFT JOIN Employer " _
& " ON [Telephone Checklist].Employer = Employer.Employer) " _
& " INNER JOIN [Telephone Checklist2] " _
& " ON [Telephone Checklist].CustomerID " _
& " = [Telephone Checklist2].CustomerID" _
& (" WHERE " + mWhere) & ";"


MakeQuery strSQL, "qryIncidentDataFind"

Thanks very much for your time it is appreciated

Bob




"strive4peace" wrote in message
...
Hi Bob,

how about replacing the SQL for the query that the form is based on
before you open the form ... something like this:

'~~~~~~~~~~~~~~~~~~
dim strSQL as string
dim mWhere as variant
mWhere = null

if Not isNull(me.txtFindCustomer) then
mWhere = " [Telephone Checklist].CustomerID =" _
& me.txtFindCustomer
end if

if Not isNull(me.txtFindDriverName) then
mWhere = (mwhere + " AND ") _
& " [Telephone Checklist].[Driver's Last Name] =" _
& "'" & me.txtFindDriverName & "'"
end if

if Not isNull(me.txtFindRegNumber) then
mWhere = (mwhere + " AND ") _
& " [Telephone Checklist].[Registration Number] =" _
& "'" & me.txtFindRegNumber & "'"
end if

strSQL = "SELECT DISTINCTROW [Telephone Checklist].* " _
& ", [Telephone Checklist].CustomerID " _
& ", [Telephone Checklist].[Driver's Last Name] " _
& ", [Telephone Checklist].[Registration Number] " _
& ", Employer.txtInFo " _
& ", Employer.txtInFoPlus " _
& ", Employer.Comments " _
& ", [Telephone Checklist2].* " _
& " FROM ([Telephone Checklist] " _
& " LEFT JOIN Employer " _
& " ON [Telephone Checklist].Employer = Employer.Employer) " _
& " INNER JOIN [Telephone Checklist2] " _
& " ON [Telephone Checklist].CustomerID " _
& " = [Telephone Checklist2].CustomerID" _
& (" WHERE " + mWhere) & ";"


MakeQuery strSQL, "qryIncidentDataFind"
'~~~~~~~~~~~~~~~~~~

Assumptions:
you are in the code behind the form [Incident Data Entry]
CustomerID is numeric
[Driver's Last Name] is text
[Registration Number] is text

when you clear controls for criteria, you will do this:
me.controlname = Null

~~

this code goes into a general (standard) module:

'~~~~~~~~~~~~~~~~~~~~~ MakeQuery
Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)

'modified 3-30-08
'crystal
'strive4peace2008 at yahoo dot com

On Error GoTo Proc_Err

debug.print pSql

'if query already exists, update the SQL
'if not, create the query

If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
'if query is open, close it
on error resume next
DoCmd.Close acQuery, qName, acSaveNo
On Error GoTo Proc_Err
CurrentDb.QueryDefs(qName).sql = pSql
End If

Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"

'Press F8 to step through code and find problem
'comment next line out when program is debugged
Stop: Resume

Resume Proc_exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~

then, on the Open event of the form:

'~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "Form has no records for specified criteria" _
, , "No records"
Cancel = True
End If
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~



Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Bob wrote:
Crystal the idea is to have an efficient client/server application by
mininimising the amount of data sent to or fetched from the server, at
the moment tens of thousands of records are brought over very time the
form Incident Data Entry is opened. So not confessing to be any sort
of expert isn't the most efficient way of doing it, is for the operator
to bring over the records they want to work with only?

The Incident Data Entry form is in use most of the time and is opened
and closed many times during the day so surely it would be better to
open the form with only the data you need rather than all of the data?

Regards Bob


"strive4peace" wrote in message
...
Hi bob,

I formatted your SQL to be easier to read

SELECT DISTINCTROW [Telephone Checklist].*
, [Telephone Checklist].CustomerID
, [Telephone Checklist].[Driver's Last Name]
, [Telephone Checklist].[Registration Number]
, Employer.txtInFo
, Employer.txtInFoPlus
, Employer.Comments
, [Telephone Checklist2].*
FROM ([Telephone Checklist]
LEFT JOIN Employer
ON [Telephone Checklist].Employer = Employer.Employer)
INNER JOIN [Telephone Checklist2]
ON [Telephone Checklist].CustomerID
= [Telephone Checklist2].CustomerID
WHERE ((([Telephone Checklist].CustomerID) Like
IIf([Forms]![Incident Data
Entry]![txtFindCustomer]="","*","*"
& [Forms]![Incident Data Entry]![txtFindCustomer] & "*"))
AND (([Telephone Checklist].[Driver's Last Name]) Like
IIf([Forms]![Incident Data Entry]![txtFindDriverName] _
="","*","*"
& [Forms]![Incident Data Entry]![txtFindDriverName] &
"*"))
AND (([Telephone Checklist].[Registration Number]) Like
IIf([Forms]![Incident Data Entry]![txtFindRegNumber]
="","*","*"
& [Forms]![Incident Data Entry]![txtFindRegNumber] &
"*")))
ORDER BY [Telephone Checklist].CustomerID DESC;


IMO, you should skip all the complex criteria and build the SQL in
code. then you can skip criteria that is not filled

why open the recordset in code and count records? If it is speed you
are after, no need to get the records twice... on the form OPEN event,
if it has no records, give a message to that effect and cancel the
open event

anyway, if you like that idea, read Access Basics (in my siggy) and
play close attention to the SQL section (Select statements) and
building SQL in code (think that is covered) ... that will give you a
good foundation to understand what comes next smile

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Bob wrote:
Crystal thanks for the quick reply the SQL is as follows


SELECT DISTINCTROW [Telephone Checklist].*, [Telephone
Checklist].CustomerID, [Telephone Checklist].[Driver's Last Name],
[Telephone Checklist].[Registration Number], Employer.txtInFo,
Employer.txtInFoPlus, Employer.Comments, [Telephone Checklist2].*
FROM ([Telephone Checklist] LEFT JOIN Employer ON [Telephone
Checklist].Employer = Employer.Employer) INNER JOIN [Telephone
Checklist2] ON [Telephone Checklist].CustomerID = [Telephone
Checklist2].CustomerID
WHERE ((([Telephone Checklist].CustomerID) Like IIf([Forms]![Incident
Data Entry]![txtFindCustomer]="","*","*" & [Forms]![Incident Data
Entry]![txtFindCustomer] & "*")) AND (([Telephone
Checklist].[Driver's Last Name]) Like IIf([Forms]![Incident Data
Entry]![txtFindDriverName]="","*","*" & [Forms]![Incident Data
Entry]![txtFindDriverName] & "*")) AND (([Telephone
Checklist].[Registration Number]) Like IIf([Forms]![Incident Data
Entry]![txtFindRegNumber]="","*","*" & [Forms]![Incident Data
Entry]![txtFindRegNumber] & "*")))
ORDER BY [Telephone Checklist].CustomerID DESC;

The button I was trying to use was the Search Button which I thought
would run the code and open the Incident Data Entry form on the
correct record.

Regards Bob



"strive4peace" wrote in message
...
Hi Bob,

what is the SQL for qryIncidentDataFind?

also, if it ends up your do things this way, you need to release
your objexct variables

instead of
'~~~~~~~~~~
Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & " (" & Err.Description & ") in
procedure
txtFindDriverName_AfterUpdate of VBA Document Form_Incident Data
Entry"
'~~~~~~~~~~

you would use

'~~~~~~~~~~
Proc_Exit:
if not rs is nothing then
rs.close
set rs = nothing
end if
set QD = nothing
set db = nothing

Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & _
" (" & Err.Description _
& ") in procedure txtFindDriverName_AfterUpdate" _
& " of VBA Document Form_Incident Data Entry" _
,, "Error"

'~~~~~~~~~~

I like using generic names for the error handler label

instead of
txtFindDriverName_AfterUpdate_Error

use something like
Proc_Error

with recent versions of Access, labels only need to be unique within
a procedure, not unique within a module...

also, use line continuation (space, underscore at end of line) to
use multiple lines for long statements


~~~~

I am assuming you have a command button to open the next form?

~~~

if you want to clear a control, do this:
txtFindDriverName.Value = null
instead of this:
txtFindDriverName.Value = ""


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Bob wrote:
Hi I am trying to set up my db so that you open frmDataFind on this
form I have 3 fields
txtCustomerID
txtRegNumber
txtName

What I what to do is enter the search criteria in any of these
fields say 22000 in the txtCustomerID field
I then want to hit the search button and it opens form Incident
Data Entry showing only record 22000

If I enter a Reg Number in txtRegNumber I want it to display all of
the reg numbers that match the one I entered
and the same for names in form Incident Data Entry.

I also want it to do a search for matches if I only have part of
the reg number or name, and lastly to open the form ready to enter
a new record.

The form Incident Data Entry already has the above set up on it but
it opens showing all the records which can be very slow.

How do I adapt the following code to work in my new frmDataFind or
is there a better way of doing this?

Private Sub txtFindDriverName_AfterUpdate()

'Since we want to search on partial values, we have to use a query
for the
'record source of this form. We find the records with the
query and tell the
'form it can only see those records.
'Now since we want to search on either of three fields, we have
to control what
'happens to the criteria in the query, so if we are here, we
are trying to find by DriverName
'so we need to set RegNum to null:
On Error GoTo txtFindDriverName_AfterUpdate_Error

txtFindCustomer = "" 'so when the query runs it returns
all RegNum
txtFindRegNumber = "" 'but still filters
on Name, we do just the
'opposite in the code for the RegNum
Dim db As Database
Dim rs As Recordset
Dim QD As QueryDef

Set db = CurrentDb()
Set QD = db.QueryDefs("qryIncidentDataFind")
QD.Parameters(0) = "" 'Here we pass Null to RegNum in the query
QD.Parameters(1) = [Forms]![Incident Data
Entry]![txtFindDriverName]
QD.Parameters(2) = ""
Set rs = QD.OpenRecordset()
Me.Form.AllowEdits = False

If rs.RecordCount 1 Then
msgbox "No Record Found"
Cancel = True

Else
Me.Requery
Me.Refresh
End If
txtFindDriverName.Value = ""
[Field143].SetFocus
txtFindDriverName.Visible = False
txtFindMsg.Visible = False

On Error GoTo 0
Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & " (" & Err.Description & ") in
procedure txtFindDriverName_AfterUpdate of VBA Document
Form_Incident Data Entry"

End Sub

What I am trying to achieve is that we only bring the records we
need over the network rather than all of them ever time we open
form Incident Data Entry.

Thanks Bob

Hi Bob,

"What code should go behind the search button on the frmDataFind..."

aside from the controls to collect criteria, do you have a command
button to open the Incident Data Entry form?

If so, the code starting with:
dim strSQL as string
dim mWhere as variant
mWhere = null
goes in the [Event Procedure] code for the click event of the command
button -- then you will need code to actually open the form... and I
assume you will want to do it as a dialog window so it must be closed
before you can go back to the search form

docmd.openform "formname",,,,,acDialog

~~~

the MakeQuery code is a general procedure that can be used anywhere in
your database, so it goes into a general (standard) module

You need to do some studying so that the code makes sense to you -- for
a start, read the Access Basics document. It does you no good to paste
code that we give you without understanding it -- when you compile the
code, there may be errors and you need to understand enough to fix them.
Also, if there are other problems, you need to understand what code is
running so that you can fix it -- and you need to be able to change it
if your requirements change.

after you get Access Basics under your belt...on learning more about
VBA, email me and I will send you the first three chapters of a book I
started writing on programming with VBA -- put "VBA" in the subject so I
know what it is in reference to.

don't be scared off by code smile ... it just takes a bit of time to
understand, and VBA is easy once you get past mental barriers and get
into it ... so plan to spend a day studying -- print the reference
documents, get a highlighter, make a nice pot of tea, get comfy in your
favorite chair, relax ... and enjoy!


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Bob wrote:
Crystal This is how its laid out frmDataFind is a form with 3 fields
txtFindCustomer
txtFindDriverName
txtFindRegNumber
and one command button called Search

On the open event I have pasted the code starting with
dim strSQL as string
dim mWhere as variant
mWhere = null

Is this correct?

I have renamed the module

What code should go behind the search button on the frmDataFind that opens
up the Incident Data Entry form with the criteria from any of the 3 txtFind
fields?

Or am I barking up the wrong tree completely?

Bob


"strive4peace" wrote in message
...
Hi Bob,

only the MakeQuery code goes into a general module ... and I would
recommend that you name it something other than the procedure it contains
... maybe bas_MakeQuery or mod_MakeQuery

the first snippet of code goes behind the form where you prepare the query
for the form you will pop up

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile before
executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~~

read the Access Basics doc in my siggy smile


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Bob wrote:
Crystal I need a bit more help here, working up from the bottom of the
page I have pasted the code into the forms Open Event no problem so far.
I then pasted the code into a new module and called it MakeQuery

I am not sure what you mean here
Assumptions:
you are in the code behind the form [Incident Data Entry]
CustomerID is numeric This is an Auto Number Field so yes
it is numeric
[Driver's Last Name] is text Yes this is a text field
[Registration Number] is text Yes this is a text field

when you clear controls for criteria, you will do this:
me.controlname = Null This bit I do not under stand sorry
And where exactly does this code go? sorry to sound a bit vague here but
I pasted it in to the query that the form is based on Incident Data Find
and
it came up with a big list of errors so that must have been the wrong
place.

Please don't forget I am fairly new at all this so I probably need more
help than most.

how about replacing the SQL for the query that the form is based on
before you open the form ... something like this:

'~~~~~~~~~~~~~~~~~~
dim strSQL as string
dim mWhere as variant
mWhere = null

if Not isNull(me.txtFindCustomer) then
mWhere = " [Telephone Checklist].CustomerID =" _
& me.txtFindCustomer
end if

if Not isNull(me.txtFindDriverName) then
mWhere = (mwhere + " AND ") _
& " [Telephone Checklist].[Driver's Last Name] =" _
& "'" & me.txtFindDriverName & "'"
end if

if Not isNull(me.txtFindRegNumber) then
mWhere = (mwhere + " AND ") _
& " [Telephone Checklist].[Registration Number] =" _
& "'" & me.txtFindRegNumber & "'"
end if

strSQL = "SELECT DISTINCTROW [Telephone Checklist].* " _
& ", [Telephone Checklist].CustomerID " _
& ", [Telephone Checklist].[Driver's Last Name] " _
& ", [Telephone Checklist].[Registration Number] " _
& ", Employer.txtInFo " _
& ", Employer.txtInFoPlus " _
& ", Employer.Comments " _
& ", [Telephone Checklist2].* " _
& " FROM ([Telephone Checklist] " _
& " LEFT JOIN Employer " _
& " ON [Telephone Checklist].Employer = Employer.Employer) " _
& " INNER JOIN [Telephone Checklist2] " _
& " ON [Telephone Checklist].CustomerID " _
& " = [Telephone Checklist2].CustomerID" _
& (" WHERE " + mWhere) & ";"


MakeQuery strSQL, "qryIncidentDataFind"
Thanks very much for your time it is appreciated

Bob




"strive4peace" wrote in message
...
Hi Bob,

how about replacing the SQL for the query that the form is based on
before you open the form ... something like this:

'~~~~~~~~~~~~~~~~~~
dim strSQL as string
dim mWhere as variant
mWhere = null

if Not isNull(me.txtFindCustomer) then
mWhere = " [Telephone Checklist].CustomerID =" _
& me.txtFindCustomer
end if

if Not isNull(me.txtFindDriverName) then
mWhere = (mwhere + " AND ") _
& " [Telephone Checklist].[Driver's Last Name] =" _
& "'" & me.txtFindDriverName & "'"
end if

if Not isNull(me.txtFindRegNumber) then
mWhere = (mwhere + " AND ") _
& " [Telephone Checklist].[Registration Number] =" _
& "'" & me.txtFindRegNumber & "'"
end if

strSQL = "SELECT DISTINCTROW [Telephone Checklist].* " _
& ", [Telephone Checklist].CustomerID " _
& ", [Telephone Checklist].[Driver's Last Name] " _
& ", [Telephone Checklist].[Registration Number] " _
& ", Employer.txtInFo " _
& ", Employer.txtInFoPlus " _
& ", Employer.Comments " _
& ", [Telephone Checklist2].* " _
& " FROM ([Telephone Checklist] " _
& " LEFT JOIN Employer " _
& " ON [Telephone Checklist].Employer = Employer.Employer) " _
& " INNER JOIN [Telephone Checklist2] " _
& " ON [Telephone Checklist].CustomerID " _
& " = [Telephone Checklist2].CustomerID" _
& (" WHERE " + mWhere) & ";"


MakeQuery strSQL, "qryIncidentDataFind"
'~~~~~~~~~~~~~~~~~~

Assumptions:
you are in the code behind the form [Incident Data Entry]
CustomerID is numeric
[Driver's Last Name] is text
[Registration Number] is text

when you clear controls for criteria, you will do this:
me.controlname = Null

~~

this code goes into a general (standard) module:

'~~~~~~~~~~~~~~~~~~~~~ MakeQuery
Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)

'modified 3-30-08
'crystal
'strive4peace2008 at yahoo dot com

On Error GoTo Proc_Err

debug.print pSql

'if query already exists, update the SQL
'if not, create the query

If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
'if query is open, close it
on error resume next
DoCmd.Close acQuery, qName, acSaveNo
On Error GoTo Proc_Err
CurrentDb.QueryDefs(qName).sql = pSql
End If

Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"

'Press F8 to step through code and find problem
'comment next line out when program is debugged
Stop: Resume

Resume Proc_exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~

then, on the Open event of the form:

'~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "Form has no records for specified criteria" _
, , "No records"
Cancel = True
End If
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~



Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Bob wrote:
Crystal the idea is to have an efficient client/server application by
mininimising the amount of data sent to or fetched from the server, at
the moment tens of thousands of records are brought over very time the
form Incident Data Entry is opened. So not confessing to be any sort
of expert isn't the most efficient way of doing it, is for the operator
to bring over the records they want to work with only?

The Incident Data Entry form is in use most of the time and is opened
and closed many times during the day so surely it would be better to
open the form with only the data you need rather than all of the data?

Regards Bob


"strive4peace" wrote in message
...
Hi bob,

I formatted your SQL to be easier to read

SELECT DISTINCTROW [Telephone Checklist].*
, [Telephone Checklist].CustomerID
, [Telephone Checklist].[Driver's Last Name]
, [Telephone Checklist].[Registration Number]
, Employer.txtInFo
, Employer.txtInFoPlus
, Employer.Comments
, [Telephone Checklist2].*
FROM ([Telephone Checklist]
LEFT JOIN Employer
ON [Telephone Checklist].Employer = Employer.Employer)
INNER JOIN [Telephone Checklist2]
ON [Telephone Checklist].CustomerID
= [Telephone Checklist2].CustomerID
WHERE ((([Telephone Checklist].CustomerID) Like
IIf([Forms]![Incident Data
Entry]![txtFindCustomer]="","*","*"
& [Forms]![Incident Data Entry]![txtFindCustomer] & "*"))
AND (([Telephone Checklist].[Driver's Last Name]) Like
IIf([Forms]![Incident Data Entry]![txtFindDriverName] _
="","*","*"
& [Forms]![Incident Data Entry]![txtFindDriverName] &
"*"))
AND (([Telephone Checklist].[Registration Number]) Like
IIf([Forms]![Incident Data Entry]![txtFindRegNumber]
="","*","*"
& [Forms]![Incident Data Entry]![txtFindRegNumber] &
"*")))
ORDER BY [Telephone Checklist].CustomerID DESC;


IMO, you should skip all the complex criteria and build the SQL in
code. then you can skip criteria that is not filled

why open the recordset in code and count records? If it is speed you
are after, no need to get the records twice... on the form OPEN event,
if it has no records, give a message to that effect and cancel the
open event

anyway, if you like that idea, read Access Basics (in my siggy) and
play close attention to the SQL section (Select statements) and
building SQL in code (think that is covered) ... that will give you a
good foundation to understand what comes next smile

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Bob wrote:
Crystal thanks for the quick reply the SQL is as follows


SELECT DISTINCTROW [Telephone Checklist].*, [Telephone
Checklist].CustomerID, [Telephone Checklist].[Driver's Last Name],
[Telephone Checklist].[Registration Number], Employer.txtInFo,
Employer.txtInFoPlus, Employer.Comments, [Telephone Checklist2].*
FROM ([Telephone Checklist] LEFT JOIN Employer ON [Telephone
Checklist].Employer = Employer.Employer) INNER JOIN [Telephone
Checklist2] ON [Telephone Checklist].CustomerID = [Telephone
Checklist2].CustomerID
WHERE ((([Telephone Checklist].CustomerID) Like IIf([Forms]![Incident
Data Entry]![txtFindCustomer]="","*","*" & [Forms]![Incident Data
Entry]![txtFindCustomer] & "*")) AND (([Telephone
Checklist].[Driver's Last Name]) Like IIf([Forms]![Incident Data
Entry]![txtFindDriverName]="","*","*" & [Forms]![Incident Data
Entry]![txtFindDriverName] & "*")) AND (([Telephone
Checklist].[Registration Number]) Like IIf([Forms]![Incident Data
Entry]![txtFindRegNumber]="","*","*" & [Forms]![Incident Data
Entry]![txtFindRegNumber] & "*")))
ORDER BY [Telephone Checklist].CustomerID DESC;

The button I was trying to use was the Search Button which I thought
would run the code and open the Incident Data Entry form on the
correct record.

Regards Bob



"strive4peace" wrote in message
...
Hi Bob,

what is the SQL for qryIncidentDataFind?

also, if it ends up your do things this way, you need to release
your objexct variables

instead of
'~~~~~~~~~~
Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & " (" & Err.Description & ") in
procedure
txtFindDriverName_AfterUpdate of VBA Document Form_Incident Data
Entry"
'~~~~~~~~~~

you would use

'~~~~~~~~~~
Proc_Exit:
if not rs is nothing then
rs.close
set rs = nothing
end if
set QD = nothing
set db = nothing

Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & _
" (" & Err.Description _
& ") in procedure txtFindDriverName_AfterUpdate" _
& " of VBA Document Form_Incident Data Entry" _
,, "Error"

'~~~~~~~~~~

I like using generic names for the error handler label

instead of
txtFindDriverName_AfterUpdate_Error

use something like
Proc_Error

with recent versions of Access, labels only need to be unique within
a procedure, not unique within a module...

also, use line continuation (space, underscore at end of line) to
use multiple lines for long statements


~~~~

I am assuming you have a command button to open the next form?

~~~

if you want to clear a control, do this:
txtFindDriverName.Value = null
instead of this:
txtFindDriverName.Value = ""


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
(: have an awesome day
*



Bob wrote:
Hi I am trying to set up my db so that you open frmDataFind on this
form I have 3 fields
txtCustomerID
txtRegNumber
txtName

What I what to do is enter the search criteria in any of these
fields say 22000 in the txtCustomerID field
I then want to hit the search button and it opens form Incident
Data Entry showing only record 22000

If I enter a Reg Number in txtRegNumber I want it to display all of
the reg numbers that match the one I entered
and the same for names in form Incident Data Entry.

I also want it to do a search for matches if I only have part of
the reg number or name, and lastly to open the form ready to enter
a new record.

The form Incident Data Entry already has the above set up on it but
it opens showing all the records which can be very slow.

How do I adapt the following code to work in my new frmDataFind or
is there a better way of doing this?

Private Sub txtFindDriverName_AfterUpdate()

'Since we want to search on partial values, we have to use a query
for the
'record source of this form. We find the records with the
query and tell the
'form it can only see those records.
'Now since we want to search on either of three fields, we have
to control what
'happens to the criteria in the query, so if we are here, we
are trying to find by DriverName
'so we need to set RegNum to null:
On Error GoTo txtFindDriverName_AfterUpdate_Error

txtFindCustomer = "" 'so when the query runs it returns
all RegNum
txtFindRegNumber = "" 'but still filters
on Name, we do just the
'opposite in the code for the RegNum
Dim db As Database
Dim rs As Recordset
Dim QD As QueryDef

Set db = CurrentDb()
Set QD = db.QueryDefs("qryIncidentDataFind")
QD.Parameters(0) = "" 'Here we pass Null to RegNum in the query
QD.Parameters(1) = [Forms]![Incident Data
Entry]![txtFindDriverName]
QD.Parameters(2) = ""
Set rs = QD.OpenRecordset()
Me.Form.AllowEdits = False

If rs.RecordCount 1 Then
msgbox "No Record Found"
Cancel = True

Else
Me.Requery
Me.Refresh
End If
txtFindDriverName.Value = ""
[Field143].SetFocus
txtFindDriverName.Visible = False
txtFindMsg.Visible = False

On Error GoTo 0
Exit Sub

txtFindDriverName_AfterUpdate_Error:

msgbox "Error " & Err.Number & " (" & Err.Description & ") in
procedure txtFindDriverName_AfterUpdate of VBA Document
Form_Incident Data Entry"

End Sub

What I am trying to achieve is that we only bring the records we
need over the network rather than all of them ever time we open
form Incident Data Entry.

Thanks Bob