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