What is the best way to implement a search function into a switchboard? I need this search function to allow a user to check a database for specific names, so that they do not enter duplicates. Thanks in advance for all advice
View RepliesHello, I’m using a search in my switchboard
and everything works great but, when I type in a number lets say 1.00 then I get no records found (But there are records) then if I type in 1.19 then it brings up a record that have this number. What I’m saying is it wont find numbers that are like 1.00 2.00 3.00 but it will find numbers if the 00 was like 17 or 12 ect. (example 4.17) What am I doing wrong in my code for the search for it to be doing this? PurchaseCost and Price are the ones doing this. Thanks!
Private Sub cmdSearch_Click()
If IsNull(Me.txtSearch) Then
'show all records
DoCmd.OpenForm "frmRBackyardMain"
Else
On Error Resume Next
'filter for keyword
DoCmd.OpenForm "frmRBackyardMain", , , "[Season] Like '*" & Me.txtSearch & "*' " _
& " OR [Vendor] Like '*" & Me.txtSearch & "*' " _
& " OR [Description] Like '*" & Me.txtSearch & "*' " _
& " OR [Z554] Like '*" & Me.txtSearch & "*' " _
& " OR [OrderNumber] Like '*" & Me.txtSearch & "*' " _
& " OR [PurchaseCost] Like '*" & Me.txtSearch & "*' " _
& " OR [Price] Like '*" & Me.txtSearch & "*' "
End If
End Sub
I was wondering if it was possible to do this, currently if I need to add a new record, I have to add it all in the tables section, this works fine but would be easier if it could be done through the switchoard somehow.
View 4 Replies View RelatedHi! Everyone on this forum has been very helpful so far, and I could really use some expertise now. I'm trying to update an existing DB designed by someone else (no longer here). I need to add a button to the switchboard for 2006, but I don't understand the code that has been written for the form I am trying to update. I've posted the code below. If anyone can help me decipher it enough to add my button, I would really appreciate it. My new button should be the 9th one. Thanks in advance:
************************************************** *
Option Compare Database
Option Explicit
Private Sub Form_Open(Cancel As Integer)
' Minimize the database window and initialize the form.
' Move to the switchboard page that is marked as the default.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.FilterOn = True
End Sub
Private Sub Form_Current()
' Update the caption and fill in the list of options.
Me.Caption = Nz(Me![ItemText], "")
FillOptions
End Sub
Private Sub FillOptions()
' Fill in the options for this switchboard page.
' The number of buttons on the form.
Const conNumButtons = 9
Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String
Dim intOption As Integer
' Set the focus to the first button on the form,
' and then hide all of the buttons on the form
' but the first. You can't hide the field with the focus.
Me![Option1].SetFocus
For intOption = 2 To conNumButtons
Me("Option" & intOption).Visible = False
Me("OptionLabel" & intOption).Visible = False
Next intOption
' Open the table of Switchboard Items, and find
' the first item for this Switchboard Page.
Set dbs = CurrentDb()
strSQL = "SELECT * FROM [Switchboard Items]"
strSQL = strSQL & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & Me![SwitchboardID]
strSQL = strSQL & " ORDER BY [ItemNumber];"
Set rst = dbs.OpenRecordset(strSQL)
' If there are no options for this Switchboard Page,
' display a message. Otherwise, fill the page with the items.
If (rst.EOF) Then
Me![OptionLabel1].Caption = "There are no items for this switchboard page"
Else
While (Not (rst.EOF))
Me("Option" & rst![ItemNumber]).Visible = True
Me("OptionLabel" & rst![ItemNumber]).Visible = True
Me("OptionLabel" & rst![ItemNumber]).Caption = rst![ItemText]
rst.MoveNext
Wend
End If
' Close the recordset and the database.
rst.Close
dbs.Close
End Sub
Private Function HandleButtonClick(intBtn As Integer)
' This function is called when a button is clicked.
' intBtn indicates which button was clicked.
' Constants for the commands that can be executed.
Const conCmdGotoSwitchboard = 1
Const conCmdOpenFormAdd = 2
Const conCmdOpenFormBrowse = 3
Const conCmdOpenReport = 4
Const conCmdCustomizeSwitchboard = 5
Const conCmdExitApplication = 6
Const conCmdRunMacro = 7
Const conCmdRunCode = 8
' An error that is special cased.
Const conErrDoCmdCancelled = 2501
Dim dbs As Database
Dim rst As Recordset
On Error GoTo HandleButtonClick_Err
' Find the item in the Switchboard Items table
' that corresponds to the button that was clicked.
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Switchboard Items", dbOpenDynaset)
rst.FindFirst "[SwitchboardID]=" & Me![SwitchboardID] & " AND [ItemNumber]=" & intBtn
' If no item matches, report the error and exit the function.
If (rst.NoMatch) Then
MsgBox "There was an error reading the Switchboard Items table."
rst.Close
dbs.Close
Exit Function
End If
Select Case rst![Command]
' Go to another switchboard.
Case conCmdGotoSwitchboard
Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rst![Argument]
' Open a form in Add mode.
Case conCmdOpenFormAdd
DoCmd.OpenForm rst![Argument], , , , acAdd
' Open a form.
Case conCmdOpenFormBrowse
DoCmd.OpenForm rst![Argument]
' Open a report.
Case conCmdOpenReport
DoCmd.OpenReport rst![Argument], acPreview
' Customize the Switchboard.
Case conCmdCustomizeSwitchboard
' Handle the case where the Switchboard Manager
' is not installed (e.g. Minimal Install).
On Error Resume Next
Application.Run "WZMAIN80.sbm_Entry"
If (Err <> 0) Then MsgBox "Command not available."
On Error GoTo 0
' Update the form.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.Caption = Nz(Me![ItemText], "")
FillOptions
' Exit the application.
Case conCmdExitApplication
CloseCurrentDatabase
' Run a macro.
Case conCmdRunMacro
DoCmd.RunMacro rst![Argument]
' Run code.
Case conCmdRunCode
Application.Run rst![Argument]
' Any other command is unrecognized.
Case Else
MsgBox "Unknown option."
End Select
' Close the recordset and the database.
rst.Close
dbs.Close
HandleButtonClick_Exit:
Exit Function
HandleButtonClick_Err:
' If the action was cancelled by the user for
' some reason, don't display an error message.
' Instead, resume on the next line.
If (Err = conErrDoCmdCancelled) Then
Resume Next
Else
MsgBox "There was an error executing the command.", vbCritical
Resume HandleButtonClick_Exit
End If
End Function
************************************************** ******
I created a switchboard and when I click on it to bring up my form, the search functions on the form won't work. I created the search through the combo box wizard.
It gives me this error:
Run-Time Error '3021':
No current Record.
Any suggestions?
Thanks
Any help would be really appreciated...
I have a database in the database I have products customers orders etc.
In Products table I have productid price etc. I would like to create another table and this table should consist of all product parts for example.
ProductID x uses
metalnumber23
stone45
plasticnumber43
so all metalnumber23, stone45, plasticnumber43 will be in Parts (new table that i will create) when i am entering a productid to Product table for that productid i should be able to choose which parts from parts table that it uses.
so in the order table if i customer ordered productID x I should be able to see all the parts that ProductID needs in the parts Parts table. There will be maybe 2000 parts but ProductID x will use only 3 of them. And if I create a query lets say order from z date to y dates I should be able to see parts needed for those products ordered.
hope you guys understand and give me a head start i would really appreciete
Hello,
I'm trying to sum values in a table and the result I'm getting is incorrect.
I have a simple table called Test with one row called Number which is Double. I have 4 values in Number field. They are:
-60000
26.6
2661
62849
When I run a query to sum those fields:
SELECT Sum(Number) As Sum FROM Test;
I get the following result:
5536.60000000001
Interestingly if I change the last number in my table from 62849 to 62848 I get this result:
5535.6
The problem seems to be with summing positive and negative numbers together. In my case, if the sum of positive numbers goes over 65535, the resulting sum is incorrect (adding 0.00000000001 to the result).
Does anybody know why this is happening and if there is a way around this? The actual tables that I work with have a large numbers that go well over 65K so this is a problem for me. I couldn't find anything on google about this.
Whenever I search a table using Ctrl+F it always says access returned zero results, even though I am searching for stuff I know is there.
How do I make a search field that will return in a table form all the results.
(Software inventory database, so I need to be able to search by serial number, as well as peoples names that are using those serial numbers)
any way to set the searchfuntion to only search for part of the word as standard?
or is it any other way to make a search function where u can write like 133 and get all the numbers starting on it.
Hi,
I would like to add a search function to a form. There will be a textbox on the form to let user to type in there searching criteria,
the grid (on the same form) will display the result records (search from the database) on the grid base on the searcing criteria. How to acheive this? Any example that I can refer to?
Thanks!
hi guys... i am currently working on a search form for my database.. here's
how my form looks like:
http://i114.photobucket.com/albums/n...clientform.jpg
when the user will type in for example "ABC Company" in the "Search for" field and will choose "Company Name" in the "Search by" combo box, the database will look for all the "ABC Company" entries under the field of "Company Name" in a table. There will be a lot of category to search from such as Position Name, Company Name, Dealer name, Outlet Name, Employee Name and Employee ID..
the results should be shown in a continuous form...
i've already tried this code:
Code:SELECT " & cboSearchBy.Text & " FROM TABLENAME WHERE " & cboSearchBy.Text & " LIKE '*" & txtSearchFor.Text & "*'
but it didn't work...
does anyone has a sample SQL query or VBA code for this function? i've been searching for it, but unfortunately i wasn't able to find what i was looking for. thank you very much. all your replies would mean so much.
if you want, you can also check the actual ms access file:
http://www.gigafiles.co.uk/files/636...2006-09-27.zip
the name of the form is frmSearchEmployee...
thanks a lot... =)
Hello everyone,
I was trying to make a query in access that will search for approximately what the user types into a textfield. I can only make something that searches for EXACTLY what the user has typed into the text field, but I want a more versatile search query that includes wildcards etc. so that anything related to the search will also be found. I was trying something like this but I just cannot get it to work:
SELECT FirstName, LastName, City, Major
FROM Students
WHERE FirstName LIKE '" & FirstNameToolStripTextBox.Text & "* '
I am using Visual Basic 2005 . NET and Access database, but the query is being made through access.
Thanks in advance for any suggestions you have
Hi Guys, sorry to bother you but I'm really stumped. I've looked through some similar sounding old posts on here but they don't seem to answer my problem...
I've got a report, which is grouped, in the footer of each group a SUM label should add the contents of the pricce column, however strangely it only adds the first record of each group as a running total. I've tried both the "Over all" and "Over group" attributes of the running sum property, but they make no difference.
Any help would really save my day. Cheers!
Hi there,
I would like to run a query which will allow me to search a specific column in a table. Any help would be greatly appreciated!!
Thanks
Hello!
I am sure this is a common question but anyway:
I have a form based on a table which I use to fill the table with data.
I would like in the header of the form to have a text box where if I enter a keyword it will display the corresponding entry.
Illustration:
Table:
field 1= first name
field 2= second name
Form:
Textbox 1= first name
Textbox 2= second name
Search textbox I type: "john" I want it to display/filter to show only the entries where first name=john
I hope my question is clear, I know most DBs do this but I can't seem to find a way of implementing it...
Thanx for your help and suggestions
Hi guys and girls,
I'm fairly new to Access and I'd appreciate some help. I've built a form with buttons to open new forms for that specific record (based on queries as all the data is held in a single table). This works fine until I try to add new records. At the moment the only way I can make this work is to add the new record, shut the original form before re-opening it and proceeding on to the other sections (via the buttons I mentioned).
If anyone could tell me how to get Access to save the record before it opens the other forms I'd really appreciate it.
Cheers,
Franny.
Hi everyone,
Thanks for taking the time to read this.
I will firstly give you the low down on the business that I am doing the database for. It is a Car Restoration Business, where we deal with vintage/classic/muscle cars and restore them to the former glory.
I do not know Microsoft Access very well, I have attached a file with what I think is a databae I could be wrong. The main function of this database is to search for suppliers for a specific car part for a specific car.
So if I want to find a doorhandle for a 1970 Ford Mustang, I need the suppliers pertaining to those criterias to come up.
How do I get this to happen?
Thankyou for your help
Jess
:confused:
Item: Access app front end for an SQL database (production tracking/job logging utility)
Problem: The app has a main search page that allows job entries to be located and opened by varies criteria (job#, job name, etc). I can currently run multiple searches on different criteria successfully but if I open a job window(which has been returned as result of my search) and then close that job window to do a new search my search function will no longer return any results(other then “Calculating….”) unless I close out the access app and reload.
Thanks in advance for any ideas that can point me in the right direction to hunt down my bug.
Quick question.....I have a menu that links to 4 main forms displaying data from 4 different tables. I wanted to add the facility to quick search on the menu(i.e enter a record (the primary key) into a text box and click search. I would then want it to open the exising form at the relevant record or produce an error message stating no record present. I would have 4 quick searches on the main menu (one for each form)).
If anyone could help me on this i would greatly appreciate it, or if you could point me in the direction of an example it would also be great (although i have been looking and havent seen one i can get my head round)
Cheers,
Marky
Anybody here know how to create a search field on a form to pull up a certain record?
I am practicing with Microsoft Access by creating a database that tracks my college degree progress as well as my wife's college degree progress.
Here is the Database Structure:
tblStudentID
---numStudentID (AutoNumber)
---strFName
---strLName
tblQuarter
---strQuarter
tblDegreeAudit
---numStudentID (Lookup field from tblStudentID)
******(Displays the First and Last Name according to Student ID number)
---strCourseCode
---strCourseNumber
---strCourseTitle
---strDegreeProgram (Yes/No field)
---strQuarter (Lookup field from tblQuarter)
---strYear
---strCredits
---strComplete (Yes/No field)
frmDegreeAudit
---tblDegreeAudit
Now, on the form (frmDegreeAudit), I want to put in a search function that allows me to drop down a list with the Student's name and all of their courses next to the name according to the Student's ID. Below is how I want to list to look when you drop the list down:
First Name-----Last Name-----Course Code----Course Number
If I can't do that, then this is what I want to do then. I want a drop down list that has the first and last name. Then right next to that drop down list, I want another drop down list that lists all of the courses associated with the Student ID. It would look something like this:
List #1=========================List #2
First Name-----Last Name->->->->->->Course Code----Course Number
Anybody understand what it is I'm looking for? I hope so because once I figure this out, I can figure out the other database I've been trying to create for the longest time.
The following search operation have to search some operation in the database, but there is a bug and I don't know why..
View 4 Replies View RelatedI've been trying to create a search function in my database and a " TmprryQryFrLnkFldsCrtn0123456789 " Query has appeared that I cant alter or change.
View 6 Replies View RelatedIn my DB that we use and a workflow tool, some of our work has and due date and time.If we get the complete_package our work time starts and we have X amount of time to complete our work. This is something i worked on but set it aside, now i am coming back to try and fine tune this so it returns a more accurate value.
So if we receive and [Date_Complete_Package_Received] at 09/13/2014 09:00:00 AM and based on the work being done we have 5 hours to complete the work, then the [Date_and_Time_Complete_Package_DUE] would be 09/13/2014 02:00:00 AM. That part is simple and i have coding that does that just fine. [SLA_Time] is where it gets the amount of time allotted, we have 5,10,12,14,and 16 hours depending on what is being done.
Code:
Me.Date_and_Time_Complete_Package_DUE = DateAdd("h", [SLA_Time], [Date_Complete_Package_Received])
This is done on AfterUpdate of a field on one of my forms and it works the way it is but what i need is to be able to run this through my Workhours Function so i am not getting values that our in off hours.The following doesn't work i know i can't use the Workhours function with the DateAdd but this is just to show what i am trying to do.
Code:
Me.Date_and_Time_Complete_Package_DUE = WorkHours(DateAdd("h", [SLA_Time], [Date_Complete_Package_Received]))
I have a Workhours module and it is used for a lot of things and it works perfectly.
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
-->
I have a form with First name, Last name, Employee number, Information. The information box is a drop down menu with four options (contact, emergency, company, personal) I would like to be able to enter in search criteria for an employee using one of last name first name or employee number then choose an option from drop down box click a button to search and have it pull up just the info I needed.
I think I have two options but i am not sure which would be more efficient.
1. creating a query and using the criteria from design mode to have it filter the information. The only problem with this one is I do not know how to set it up with the drop down box from the form. Or would I need multiple querys for each option or just one with all of it?
2. writing code for the search button on the form that will bring up the appropriate query (I think I would then have to create a query for each drop down option)...
I'm currently trying to build in access a replica of an atrocious search function in excel.
I have a list of data quite simply in 5 columns and i want to filter through this data about (10000 rows).
My form has 5 data points.
The first is Product Name this is a string (i've looked up a lot of codes to search strings and even partial strings but no one seems to have done what i need).
- Basically i need it to search for any / multiple parts of the string entered.
- for example if someone enters apple trees june i need it to look for cells containing those three words in any order, even conjoined for example "appletreejune" would still return or "apples on a tree in june".
- This is attached to a single col called Product Name.
Based on this search i need it to look for data in a col called mark type (which is selected by a drop down)
Then by Market Context (also a drop down)
Then by a start and an end date, however, only one of the values (start or end) needs to be between the start and the end dates listed in the start and end date columns in the table.