Modules & VBA :: Search Code Deactivates Navigation Buttons When Search Results Are Longer Than A Page
Jun 24, 2015
I have a form that has a subform on it. The main form shows a category of furniture and has custom navigation buttons and a search text box for asset numbers and command button that runs the search. The subform shows the asset numbers associated with that furniture category, sometimes there is only one asset number, in other cases there could be 60. There is a scroll bar to scroll through the asset numbers when there are too many to see in the initial window.
The buttons all work as they should except when I search for an asset number that is part of a category that has too many asset numbers to show in the main window. When this happens the "previous" and "next" navigation buttons do not take you to the previous or next record. All of the other buttons on the form work though - you can go to the first, or the last record, and you can search for a new asset.This is the code for the search:
Code:
Private Sub cmdAssetSearch_Click()
Dim rs As Object
If IsNull(Me.TextAsset) Or Me.TextAsset = "" Then
MsgBox "Please type in an asset number to search for.", vbOKOnly
Me.TextAsset.SetFocus
[code]....
I've also attached a picture of what I mean when I say there are more asset numbers than what the window shows.
View Replies
ADVERTISEMENT
Apr 29, 2014
I have built a custom search form in a MS Access 2010 database so that users can find specific records to edit. After entering the search criteria and hitting a Search button, another form opens up that shows the search results. This second form includes a command button for generating a report of the search results.
Right now, the custom search form and the search results form are both working properly, but the search results report is showing every record in the database instead of just the search results. This is true whether I access the report via the command button in the form or the navigation pane. I'm not sure if I need to correct my VBA code or the report's properties.
View 4 Replies
View Related
Sep 21, 2012
I'm having Table with some universities name and i want web link address for all universities. Take university from table1 in column1 and search on google page and return first link of the search page and save into column2...
View 1 Replies
View Related
Oct 4, 2013
Using vba code I want to populate the search box found in the navigation bar. I don't want to filter the form as it's important the user can scroll up and down to see other records in the continuous form. At the moment the user has to manually enter search to find the record.
View 5 Replies
View Related
Aug 5, 2014
I have a search form that uses a query to show results of a search, but everytime I press search everything comes up even though I have entered search parameters, even though my search requeries every time and the search used to work before I added new records today. Also when I press the query alone on the navigation pane it asks me for the parameters and then it actually works but it won't when I use my form.
View 2 Replies
View Related
Dec 19, 2005
I have a students database with fields like
Id
Name
Address
City
State
I have a mainform with just two fields (Name and State)
and then a subform, I want to be able to enter students name or state on a field in my mainform and click on FindStudent, and it displays the record on my subform.
I also want to have a AddNew record button on my main form, and when it's clicked, i will be able to enter data into the underlying table using my subform.
How do i achieve this?
Any input will be greatly appreciated. Thanks :) :)
View 2 Replies
View Related
Aug 15, 2013
How to write some code that will search a known drive and folder structure for a tif file and on finding it, open that tif file.
The known drive/folder structure is as follows:
M:CustomerSatisfactionStdDGImages
or it could have the full path as follows:
prdfs01QUESTIONSCustomerSatisfactionStdDGImag es
and then there are the following folders which contain any number of tif images:
01_Q
02_Q
03_Q
04_Q
05_Q
06_Q
07_Q
10_Q
11_Q
12_Q
13_Q
14_Q
17_Q
18_Q
19_Q
20_Q
21_Q
23_Q
24_Q
28_Q
29_Q
30_Q
31_Q
32_Q
33_Q
34_Q
35_Q
36_Q
37_Q
38_Q
39_Q
AR_Q
HE_Q
SKY_SKY3B
I would like to have a button in a form that the end user clicks and they then enter the name of the tif file they are looking for and on pressing enter the file is searched for and if found it is automatically opened up for them to see, if it is not found then a message "File Not Found" is displayed.
I Believe that I will need something like this:
Code:
Dim FS As FileSystemObject
Dim filenum As Integer
Dim tmp As String
Dim Folder As Folder
Dim subFolder As Folder
Dim File As File
[Code] .....
It's when I get to this point that I've got stuck, I don't know how to structure the code required to do the search and on finding the tif file open it.
An example tif file I might search for is: 0H214_2CJ0001905.tif.
View 11 Replies
View Related
May 28, 2015
I need some VBA coding to do the following tasks
Table 1: two columns - Child Tag and Parent Tag. Parent Tags can also be in Child Tag column. In other words, a parent can have multiple levels of children.
Table 2: one column - Backup Tag.
I'd like to have a form with a combo box, pick a Parent Tag, the search all its child tags and compare each Child Tag found with records in Table 2 to see if there is a match. Then populate all results in a tree view control.
A visual example :
Parent Tag
...Child Tag 1 - Back up tag found
......Child Tag 11
......Child Tag 12 - Back up tag found
...Child Tag 2
......Child Tag 21
...Child Tag 3
......Child Tag 31
.........Child Tag 311 - Back up tag found
View 7 Replies
View Related
Jul 31, 2013
I'm trying to tie a from into the results from and AD Query. I'm not that experienced with doing this kind of thing so I may be going about this the wrong way. Anyway here's what I've got so far:
Code:
Private Sub Form_Open(Cancel As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
With cn
.ConnectionString = "Provider=ADSDSOObject;Trusted_Connection=yes;"
[Code] .....
When stepping through the code, it all goes well until it hits the line that actually connects it to the form "Set Me.Recordset = rs".
When this line is executed, Access crashes and attempts to restart.
PS: Access 2010, Win7 64bit
View 5 Replies
View Related
Feb 25, 2015
Staff are monitored to make sure they are keeping up to date with our customers. A customer can have multiple projects going through the factory at any one time. Each customer has a record per project and a 'general' record. Ideally we would like our staff to be able to move the 'general' record when they update a project record as opposed to either having to find and then update the general record after, or forgetting and calling the customer again 2 days later!
Including a msgbox for the EnqNum seems to show the general record correctly, however being new to access I am unsure if I have the update part correct.
Code:
If Me.chkMoveGen.Value = "-1" Then
Dim EnqNum As Integer
EnqNum = DLookup("[e_id]", "tblEnquiries", "[c_id]=" & Me.txtc_id & " and [e_status] = " & "13")
DoCmd.RunSQL "UPDATE tblEnquiries " & _
" SET e_date_due=#" & Format(Me.txte_date_due, "MM/DD/YYYY") & "#" & _
" WHERE e_id= EnqNum"
View 3 Replies
View Related
May 10, 2004
Hi everybody ,
First time i'm gonna ask something here....
My question is :
I have a form on which people can fill in the hours they have worked on that day
Now i want to have e little form on which i can give in the name of the person and date
When i click the button i want access to show the form (where people can fill in their hours)
But it has to show the correct record.
For example Mr x on 10/03/2004 when ik click the button he shows the form with that record id ,
I know that i hav eto search for Namen and date , and when he finds it , he has to show the record with that id ..
But i don't know how i have to code thiis , anyone can help me????,
Thanxxxx
View 2 Replies
View Related
Oct 18, 2005
Hello,
I have a form named Welcome which I want to use as search menu for my database.
I have added 4 option buttons and a unique textbox into which users will write their query criteria.
My database has 4 queries and each option button should refer to one of them once selected
Obviously I will need a command button that will run the query.
Any help or code to do this? Thanks.
View 1 Replies
View Related
Mar 1, 2015
I have my setup all complete and everything works great. Now, I have a subNavigation form within the Navigation form. Thing is, I am not sure how to go about the code for the query to pick up the Beginning and End Date to generate my report. Here is my Form setup:
frmNavigationForm (Main form) - Tabs on top.
NavigationSubform (first Navigation Form, where I have a tab called Admin)
frmAdminNav (where I have embedded under the Admin tab) - Tabs on left side.
NavigationSubform (Second Navigation form where I have a tab called "Search") I don't recall what the name. How do I find it, stupid question, I know...
frmSearch (where I am trying to pull a detailed report from date search criteria)
Here is one of the codes I have been trying to use in my Query and nothing seems to work.
Code:
Between [Forms]![frmNavigationForm]![NavigationSubform].[Form]![frmAdminNav].[Form]![frmSearch]![BeginDate] And [Forms]![frmNavigationForm]![NavigationSubform].[Form]![frmAdminNav].[Form]![frmSearch]![EndDate]
View 2 Replies
View Related
Jul 11, 2014
To briefly explain my database; it is a bespoke referral management system within a hospital. Each record on the database contains patient demographic information, as well as information on their referral (i.e. date of referral, date of assessment, date of commencing treatment, discharge date etc.) Therefore, the same patient will appear multiple times in the database, with each separate record corresponding to a unique referral pathway.
The database forms are split to show patient information at the top, with referral information shown in a subform. I am trying to add navigation buttons to the subform that will allow the user to scroll through the referrals corresponding to the patient currently displayed on the main form.
Each patient has a uniquely identifiable number associated with them, and so it seems straightforward enough in my mind to have a button that will search for the record in the database where the patient's number matches the patient number of the current record, and where the referral date is minimum (for "First Referral"), maximum but less than current (for "Previous Referral"), minimum but greater than current (for "Next Referral"), and maximum (for "Last Referral").
View 5 Replies
View Related
Sep 12, 2005
dear experts,
i am a student and i have a project to complete in 2days. i have managed to crawl through access and come up with forms and a functional query set. i need to create a search function for names and addresses. i manged to do a fisrt name search. i need to make an option for users to choose either first or last name and then search for the other details.
here is my situation, i need help connecting a radio button to a search form. allow me to illustrate my form.
************************************************** *******
first name : _________ (combo box) last name : _________ (combo box)
[0] (option button) [0] (option button)
SEARCH ( command button)
************************************************** ********
as of now, the first name combo is connected to the search button via a macro which launches the query with a value from the combo box. how do i make the user choose either option "first/last" and then make the search button search appropriately. please help me. i know this is elementary. i've spent too much time looking for a solution on my own. my time is running out to complete the project. and i've yet to create switch boards and all those other things. please! thanx all.
View 2 Replies
View Related
Mar 20, 2008
I have a form with drop down boxes that list 3 related fields and I have a search button that will requery based on the the input from these boxes. These boxes are all prepopulated with the data and I want to be able to select something from Box1 and then based off Box1 change whats populated in Box2 and Box3. Any idea's???
I already have a query setup like this to requery a query I make:
( I have a button that initiates the requery based off what input is given)
Box1:
IIf(IsNull([Forms]![FormName]![Combo1]),[Field1],[Forms]![FormName]![Combo1])
Box2:
IIf(IsNull([Forms]![FormName]![Combo2]),[Field2],[Forms]![FormName]![Combo2])
Box3:
IIf(IsNull([Forms]![FormName]![Combo3]),[Field3],[Forms]![FormName]![Combo3])
Problem with this is that is does not requery correctly and it only filters on one of the criteria ( Field1) and spits out all records for the other two?
So I figured since I already populate the drop downs with the records why not just change the contents of the drop downs?
If anyone can give me some insight it would be much appreciated?
Thanks in advance.
View 3 Replies
View Related
Jul 30, 2013
How can I put search button on the navigation form to search all form in my database? I have eight form I would like to be able to search to be able to pull all information for one student worker.
View 14 Replies
View Related
Nov 21, 2005
I have the database and the Data page, but how do I add a search button to the data page to search the database by a certain field? Any help would be appreciated. I would be happy to send you the files if you give me a contact.
Thanks
View 2 Replies
View Related
Sep 6, 2007
Hi i have a small problem that i hope you maybe able to answer.
I have creted an access data page, which works ok. However i really would like the option to search on a certain field to lookup a record. Is this possible?
I understand that there are filter options, but i need to define just one field, for one record.
The nature of this database is to store info on batches we produce, therefor it's important i can search via a batch number.
Is this possible?
View 1 Replies
View Related
May 16, 2014
have set up a navigation form which contains 5 unbound subforms that are made visible or not visible thru the on click event of command buttons on the main form, the subforms have a command button with an embedded macro that searches for a record based on what has been keyed to an unbound text box on the subform, this works fine when the sub forms are opened independently of the navigation form but when the search is done from within the main form, the error message is, "....cannot find the referenced from frmAQIFAList" subform, I tried referencing the main form name infront of the subform name but this also doesn't work, perhaps this would be better with code ? or is it because the subforms are unbound ?
View 4 Replies
View Related
Jan 11, 2005
I’m having problems trying to construct a database for my company. They need the database to enter information about certain insurance policies that our company has. We have a table setup and ready to go. I need to allow multiple users to add records to the database so I want to setup a Data Access Page to accomplish this. However, there’s a problem I’m still stuck on.
In the Data Access Page, I need the ability to search and display a record designated by the primary key field (policy number). I found code to do this, however when I click the button, input a known policy number at the prompt, and click ok, it displays Error: 3265 the item cannot be found in the collection corresponding to the requested name or ordinal. Here’s the code from http://office.microsoft.com/en-us/assistance/HA010345491033.aspx
<!-- I took this out as i did not know what to do with it, it could be causing the problem
<SCRIPT LANGUAGE=vbscript FOR=MSODSC EVENT=Current(oEventInfo)>
-->
<SCRIPT language=vbscript event=onclick for=Command0>
<!--
' Clone the recordset.
Dim rs
Set rs = MSODSC.DataPages(0).Recordset.Clone
On error resume next
' This line assumes that the value you are filtering on is an integer.
' If the search value is a string, use slightly different syntax.
' For example, "CustomerID = '" & CStr(InputBox("Please enter customer to find", "Find")) & "'"
' rs.find "ProductID=" & cLng(inputbox("Enter a ProductID","Find"))
rs.find "CustomerID = '" & CStr(InputBox("Please enter customer to find", "Find")) & "'"
' Custom error handling.
If (err.number <> 0) Then
Msgbox "Error: " & err.number & " " & err.description,,"Invalid Search"
Exit Sub
End If
' Check search results for success.
If (rs.bof) or (rs.eof) Then
Msgbox "No Product found",,"Search Done"
Exit Sub
End If
MSODSC.DataPages(0).Recordset.Bookmark = rs.Bookmark
-->
View 4 Replies
View Related
May 27, 2005
please can someone help me with a vb code to view search results in a subform such that
double clicking search result on subform displays another form with details of the result.
Heeeeeeelp.
Thanks
View 2 Replies
View Related
Jun 28, 2005
I created a search form (using code from here) that searches serveral fields and displays the results in a listbox. I also created a clear button to clear the fields, including the listbox. I can search all day and it works fine, and the CLEAR button clears out the fields fine but once I have clicked the CLEAR button, I cannot search anymore. I don't get any results anymore (until I close and reopen). It must be something simple that I am overlooking so any hints would be appreciated.
Here is my code (sorry it's long!):
Private Sub cmdClearForm_Click()
On Error GoTo Err_cmdClearForm_Click
Dim I As Integer
' Cycle through the form's controls, testing for text,
' and clear each field.
For I = 0 To Me.Count - 1
If TypeOf Me(I) Is TextBox Then
Me(I) = ""
ElseIf TypeOf Me(I) Is ListBox Then
Me(I).RowSource = " "
End If
Next
Me.txtMacAddr1.SetFocus
Exit_cmdClearForm_Click:
Exit Sub
Err_cmdClearForm_Click:
MsgBox Err.Description
Resume Exit_cmdClearForm_Click
End Sub
Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click
Dim strSQL As String, strOrder As String, strWhere As String, strOrderChoice As String
Dim db As DAO.Database
'Dim qryDef As QueryDef
Set db = CurrentDb()
strSQL = "SELECT tblAsset.MacAddr1, tblAsset.SerialNum, tblIPAddresses.IPAddress,tblIPAddresses.HostName, tblLocation.JackNumber, tblLocation.CircuitID, tblLocation.Department,tblLocation.SpecificLoc, tblLocation.User, tblLocation.Building, tblLocation.RoomNumber " & _
"FROM tblAsset, tblIPAddresses, tblLocation " & _
"WHERE tblAsset.AssetNum=tblIPAddresses.AssetNum and tblAsset.AssetNum=tblLocation.AssetNum"
strWhere = " and "
strOrder = "order by"
strOrderChoice = "tblLocation.Department"
If Not IsNull(Me.txtMacAddr1) Then
strWhere = strWhere & "(tblAsset.MacAddr1) like '*" & Me.txtMacAddr1 & "*' and "
strOrderChoice = "tblAsset.MacAddr1"
End If
If Not IsNull(Me.txtMacAddr1) Then
strWhere = strWhere & "(tblAsset.MacAddr2) like '*" & Me.txtMacAddr1 & "*' and "
strOrderChoice = "tblAssest.MacAddr2"
End If
If Not IsNull(Me.txtSerialNum) Then
strWhere = strWhere & "(tblAsset.SerialNum) like '*" & Me.txtSerialNum & "*' and "
strOrderChoice = "tblAsset.SerialNum"
End If
If Not IsNull(Me.txtIPAddress) Then
strWhere = strWhere & "(tblIPAddresses.IPAddress) like '*" & Me.txtIPAddress & "*' and "
strOrderChoice = "tblIPAddresses.IPAddress"
End If
If Not IsNull(Me.txtHostName) Then
strWhere = strWhere & "(tblIPAddresses.HostName) like '*" & Me.txtHostName & "*' and "
strOrderChoice = "tblIPAddresses.HostName"
End If
If Not IsNull(Me.txtJackNumber) Then
strWhere = strWhere & "(tblLocation.JackNumber) like '*" & Me.txtJackNumber & "*' and "
strOrderChoice = "tblLocation.JackNumber"
End If
If Not IsNull(Me.txtCircuitID) Then
strWhere = strWhere & "(tblLocation.CircuitID) like '*" & Me.txtCircuitID & "*' and "
strOrderChoice = "tblLocaton.CircuitID"
End If
If Not IsNull(Me.txtBuilding) Then
strWhere = strWhere & "(tblLocation.Building) like '*" & Me.txtBuilding & "*' and "
strOrderChoice = "tblLocation.Building"
End If
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)
Me.lstResults.RowSource = strSQL & " " & strWhere & " " & strOrder & " " & strOrderChoice
db.Close
Exit_cmdSearch_Click:
Exit Sub
Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click
End Sub
View 4 Replies
View Related
Jan 2, 2006
Good Day All,
I have a wild card search query (Like "*" & [Forms]![search]![Text0] & "*") that works very well. I want to be able to have the phrase that user types in highlighted when it returns the search results on a form. I found the following thread that asks the same question using Conditional formating, but the suggested answer doesn't work. Any ideas?
http://www.access-programmers.co.uk/forums/showthread.php?t=73845&highlight=highlight+search
View 3 Replies
View Related
May 26, 2006
Whats the best way to filter search results?
I'll do my best to explain my problem:
I made a form that lets users search for records in a database and displays them in another form. In that form I've made 3 buttons to let users narrow down the results to show only certain items within the search results.
Now, 2 of the buttons show specific items and the third one toggles the form to display all the records. So lets say I'm filtering records by whether or not they contain the words "car" and "truck." If a record has "car" but not truck and I click the button to sort by "truck" then the search result goes blank, which is ok. But if I click "car" or "show all" to display everything again then the search result stays blank.
This doesn't happen with records that contain both "car" and "truck."
For every button I have something like this: DoCmd.OpenForm "AdminSearchResults", , "SOW", "VendName = '" & VendName & "'", , , "'VendName'"
I know my explanation may suck, but if anyone can help me, that would be great, lol
View 3 Replies
View Related
Sep 12, 2004
SELECT [tblCallTracking].[PKCallID], [tblCallTracking]![PhoneNumber] & "," & [tblCallTracking]![LastName] & "," & [tblCallTracking]![FirstName] AS Expr2 FROM tblCallTracking;
I have this code in an unbound combo box. It does bring the info into the combo box properly, but when i select the record I want to use, it doesnt populate the form with the correct record.
Any help or ideas would be appreciated.
View 1 Replies
View Related