If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Open form display specific record
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 |
#3
|
|||
|
|||
Open form display specific record
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 |
#4
|
|||
|
|||
Open form display specific record
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 |
#5
|
|||
|
|||
Open form display specific record
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 |
#6
|
|||
|
|||
Open form display specific record
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 |
#7
|
|||
|
|||
Open form display specific record
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 |
#8
|
|||
|
|||
Open form display specific record
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 |
#9
|
|||
|
|||
Open form display specific record
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 |
#10
|
|||
|
|||
Open form display specific record
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 |
Thread Tools | |
Display Modes | |
|
|