Hi Everyone,
I need to find a way of finding where a particular record is within a query, meaning if my query returns 20 records of Surnames and Mr Smith happens to be a returned as a row somewhere in the middle of the returned values, then I need to show that Number.value
I thought I might be best to have a go and tweak the below code(I found on this Forum) to suit my needs, but will need a little help with the VBA!
Hopefully someone with expertise will be able to tell me where I am going wrong or if the below is way to complex for doing what I need.
Here goes !!!
I need to find a way of finding where a particular record is within a query, meaning if my query returns 20 records of Surnames and Mr Smith happens to be a returned as a row somewhere in the middle of the returned values, then I need to show that Number.value
I thought I might be best to have a go and tweak the below code(I found on this Forum) to suit my needs, but will need a little help with the VBA!
Hopefully someone with expertise will be able to tell me where I am going wrong or if the below is way to complex for doing what I need.
Here goes !!!
Code:
[FONT=Courier New]Dim Cnt as long[/FONT]
[COLOR=#8d38c9][FONT=Courier New]For [/FONT][/COLOR][COLOR=#8d38c9][FONT=Courier New]Each[/FONT][/COLOR][COLOR=#555555][FONT=Courier New] fld [/FONT][/COLOR][COLOR=#8d38c9][FONT=Courier New]In[/FONT][/COLOR][COLOR=#555555][FONT=Courier New] qryfld.Fields [/FONT][/COLOR][COLOR=green][FONT=Courier New]'loop through all the fields of the Query[/FONT][/COLOR]
[COLOR=#555555][FONT=Courier New]Cnt = Cnt + 1[/FONT][/COLOR]
[COLOR=#555555][FONT=Courier New] If fld.Name = [/FONT][/COLOR][COLOR=maroon][FONT=Courier New]"[/FONT][/COLOR][COLOR=#555555][FONT=Courier New]mr Smith[/FONT][/COLOR][COLOR=maroon][FONT=Courier New]"[/FONT][/COLOR][COLOR=#555555][FONT=Courier New] then[/FONT][/COLOR]
[COLOR=#555555][FONT=Courier New]Msgbox [/FONT][/COLOR][COLOR=maroon][FONT=Courier New]"[/FONT][/COLOR][COLOR=#555555][FONT=Courier New]mr Smith[/FONT][/COLOR][COLOR=maroon][FONT=Courier New] is at row “ & Cnt [/FONT][/COLOR]
[COLOR=#8d38c9][FONT=Courier New]Next[/FONT][/COLOR]
Code:
[COLOR=green][FONT=Courier New]'---------------------------------------------------------------------------------------[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]' Procedure : listQueryFields[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]' Author : CARDA Consultants Inc.[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]' Website : http://www.cardaconsultants.com[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]' Purpose : Return a listing of all the fields (column names) of a give Query[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]' Copyright : The following code may be used as you please, but may not be resold, as[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]' long as the header (Author, Website & Copyright) remains with the code.[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]'[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]' Input Variables:[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]' ~~~~~~~~~~~~~~~~[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]' strQryName - Name of the query to list the fields of.[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]'[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]' Revision History:[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]' Rev Date(yyyy/mm/dd) Description[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]' **************************************************************************************[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]' 1 2007-June-01 Initial Release[/FONT][/COLOR]
[COLOR=green][FONT=Courier New]'---------------------------------------------------------------------------------------[/FONT][/COLOR]
[B][COLOR=#e56717][FONT=Courier New]Function[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] listQueryFields(strQryName [/FONT][/COLOR][B][COLOR=#151b8d][FONT=Courier New]As[/FONT][/COLOR][/B][B][COLOR=#f660ab][FONT=Courier New]String[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New]) [/FONT][/COLOR][B][COLOR=#151b8d][FONT=Courier New]As[/FONT][/COLOR][/B][B][COLOR=#f660ab][FONT=Courier New]String[/FONT][/COLOR][/B]
[B][COLOR=#151b8d][FONT=Courier New]On[/FONT][/COLOR][/B][B][COLOR=#151b8d][FONT=Courier New]Error[/FONT][/COLOR][/B][B][COLOR=#8d38c9][FONT=Courier New]GoTo[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] listQueryFields_Error[/FONT][/COLOR]
[B][COLOR=#151b8d][FONT=Courier New]Dim[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] db [/FONT][/COLOR][B][COLOR=#151b8d][FONT=Courier New]As[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] DAO.Database[/FONT][/COLOR]
[B][COLOR=#151b8d][FONT=Courier New]Dim[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] qryfld [/FONT][/COLOR][B][COLOR=#151b8d][FONT=Courier New]As[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] DAO.QueryDef[/FONT][/COLOR]
[B][COLOR=#151b8d][FONT=Courier New]Dim[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] fld [/FONT][/COLOR][B][COLOR=#151b8d][FONT=Courier New]As[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] Field[/FONT][/COLOR]
[B][COLOR=#151b8d][FONT=Courier New]Set[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] db = CurrentDb()[/FONT][/COLOR]
[B][COLOR=#151b8d][FONT=Courier New]Set[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] qryfld = db.QueryDefs(strQryName)[/FONT][/COLOR]
[B][COLOR=#8d38c9][FONT=Courier New]For[/FONT][/COLOR][/B][B][COLOR=#8d38c9][FONT=Courier New]Each[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] fld [/FONT][/COLOR][B][COLOR=#8d38c9][FONT=Courier New]In[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] qryfld.Fields [/FONT][/COLOR][COLOR=green][FONT=Courier New]'loop through all the fields of the Query[/FONT][/COLOR]
[COLOR=#555555][FONT=Courier New] Debug.[/FONT][/COLOR][B][COLOR=#151b8d][FONT=Courier New]Print[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] fld.Name[/FONT][/COLOR]
[B][COLOR=#8d38c9][FONT=Courier New]Next[/FONT][/COLOR][/B]
[COLOR=#555555][FONT=Courier New]Error_Handler_Exit: [/FONT][/COLOR]
[B][COLOR=#151b8d][FONT=Courier New]Set[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] qryfld = [/FONT][/COLOR][B][COLOR=#00c2ff][FONT=Courier New]Nothing[/FONT][/COLOR][/B]
[B][COLOR=#151b8d][FONT=Courier New]Set[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] db = [/FONT][/COLOR][B][COLOR=#00c2ff][FONT=Courier New]Nothing[/FONT][/COLOR][/B]
[B][COLOR=#e56717][FONT=Courier New]Exit[/FONT][/COLOR][/B][B][COLOR=#e56717][FONT=Courier New]Function[/FONT][/COLOR][/B]
[COLOR=#555555][FONT=Courier New]listQueryFields_Error:[/FONT][/COLOR]
[COLOR=#555555][FONT=Courier New] MsgBox [/FONT][/COLOR][COLOR=maroon][FONT=Courier New]"MS Access has generated the following error"[/FONT][/COLOR][COLOR=#555555][FONT=Courier New] & vbCrLf & vbCrLf & [/FONT][/COLOR][COLOR=maroon][FONT=Courier New]"Error Number: "[/FONT][/COLOR][COLOR=#555555][FONT=Courier New] & _[/FONT][/COLOR]
[COLOR=#555555][FONT=Courier New] Err.Number & vbCrLf & [/FONT][/COLOR][COLOR=maroon][FONT=Courier New]"Error Source: listQueryFields"[/FONT][/COLOR][COLOR=#555555][FONT=Courier New] & vbCrLf & _[/FONT][/COLOR]
[COLOR=maroon][FONT=Courier New]"Error Description: "[/FONT][/COLOR][COLOR=#555555][FONT=Courier New] & Err.Description, vbCritical, [/FONT][/COLOR][COLOR=maroon][FONT=Courier New]"An Error has Occured!"[/FONT][/COLOR]
[B][COLOR=#8d38c9][FONT=Courier New]Resume[/FONT][/COLOR][/B][COLOR=#555555][FONT=Courier New] Error_Handler_Exit[/FONT][/COLOR]
[B][COLOR=#8d38c9][FONT=Courier New]End[/FONT][/COLOR][/B][B][COLOR=#e56717][FONT=Courier New]Function[/FONT][/COLOR][/B]