Hi,
I’m looking for a little help with creating a form & query combination for searching my database. For starters – I am using Access 2003.
I have a large amount of information stored in a table on my database (the table has around 20 fields, with around 200 rows of data which is added to on a daily basis). I have several different search forms depending on what type of search the user might want to do. The fields vary from records such as ‘Date Received’, ‘Unique ID’, ‘Account Name’ etc.
I have hit a bit of a roadblock on creating what I have named the ‘AllSearch’ and ‘SingleSearchForm’ forms. As the names would indicate, I want the ‘AllSearch’ form to be where the user can search the entire table and generate a query showing all of the entries that match their chosen criteria. The ‘SingleSearchForm’ is where the user can search for a specific record based on their chosen criteria and the results are displayed on the form itself.
The problem that I have with the ‘SingleSearchForm’ is that I have created a Combo box populated with all of the field names (called Combo65 at present) and a Text box where the user can input what they wish to search for (called Text67 at present). I would like the user to be able to choose their chosen field from the Combo box and then type in what they would like to search that field for in the Text box and then press the ‘Search’ button in order for the form to find the first instance of their chosen Text input against the chosen field from the Combo box, and return that on the form itself. For example – The user chooses ‘Account Name’ from the Combo box and then types in ‘Ryan Peck’ into the Text box and presses Search, with the form then finding the first instance of ‘Ryan Peck’ in the ‘Account Name’ field and returning all of the record details associated with this name. I have the form built with the appropriate boxes, but I am stumped on the VBA coding for the ‘Search’ button as to how to get it to find what I want it to.
This leads me in to the issue that I then have with the ‘AllSearch’ form. The form works on the same principal – the user selects which field they wish to search from a Combo box and then types in what they wish to search in a Text box and executes the search by pressing a button. This is obviously the same issue as I have above in that I’m unsure as to how to write this, but is amplified by the fact that I wish the results of this search to be output into a query. Basically – if the user wants to search by ‘Date Received’ and inputs the date ‘03/09/2012’, I want the query to then execute and return all records which have the date as 03/09/2012 in the Date Received column.
Hopefully you’ll be able to offer some help / guidance on this point as this issue has been stumping me for some time and I have been unable to find any information via numerous searches around the internet (for starters I’m not even certain how to word the problem I am facing to search for it accurately, but suffice to say I have found nothing).
If you need any further information / examples / background, please let me know and I’ll happily oblige - many thanks in advance for any help you are able to offer!
I’m looking for a little help with creating a form & query combination for searching my database. For starters – I am using Access 2003.
I have a large amount of information stored in a table on my database (the table has around 20 fields, with around 200 rows of data which is added to on a daily basis). I have several different search forms depending on what type of search the user might want to do. The fields vary from records such as ‘Date Received’, ‘Unique ID’, ‘Account Name’ etc.
I have hit a bit of a roadblock on creating what I have named the ‘AllSearch’ and ‘SingleSearchForm’ forms. As the names would indicate, I want the ‘AllSearch’ form to be where the user can search the entire table and generate a query showing all of the entries that match their chosen criteria. The ‘SingleSearchForm’ is where the user can search for a specific record based on their chosen criteria and the results are displayed on the form itself.
The problem that I have with the ‘SingleSearchForm’ is that I have created a Combo box populated with all of the field names (called Combo65 at present) and a Text box where the user can input what they wish to search for (called Text67 at present). I would like the user to be able to choose their chosen field from the Combo box and then type in what they would like to search that field for in the Text box and then press the ‘Search’ button in order for the form to find the first instance of their chosen Text input against the chosen field from the Combo box, and return that on the form itself. For example – The user chooses ‘Account Name’ from the Combo box and then types in ‘Ryan Peck’ into the Text box and presses Search, with the form then finding the first instance of ‘Ryan Peck’ in the ‘Account Name’ field and returning all of the record details associated with this name. I have the form built with the appropriate boxes, but I am stumped on the VBA coding for the ‘Search’ button as to how to get it to find what I want it to.
This leads me in to the issue that I then have with the ‘AllSearch’ form. The form works on the same principal – the user selects which field they wish to search from a Combo box and then types in what they wish to search in a Text box and executes the search by pressing a button. This is obviously the same issue as I have above in that I’m unsure as to how to write this, but is amplified by the fact that I wish the results of this search to be output into a query. Basically – if the user wants to search by ‘Date Received’ and inputs the date ‘03/09/2012’, I want the query to then execute and return all records which have the date as 03/09/2012 in the Date Received column.
Hopefully you’ll be able to offer some help / guidance on this point as this issue has been stumping me for some time and I have been unable to find any information via numerous searches around the internet (for starters I’m not even certain how to word the problem I am facing to search for it accurately, but suffice to say I have found nothing).
If you need any further information / examples / background, please let me know and I’ll happily oblige - many thanks in advance for any help you are able to offer!