I am quite new to Access and programming, I have a table with 1,101,203 records, 16 fields. How do I create a form to search by, 'Account number', and narrow down searches for
+/- 10% on price and sq ft.
example:I would search the account number and it would give me;
Account_no: 10140455
Color: White
Etc: Etc
Etc: Etc
Value: 149720
Sq_Ft: 13875
I want to get a list of records displaying +/- 10% of the 'Value' and 'Sq_Ft' I got from the search I made of the 'Account_no'.So when I search 'Account_ no': 10140455 and it gives me that account information(etc, etc, etc, Value and Sq ft), I also want to get a list of records that have +/- 10% of the value and sq ft of the 10140455 record.
I am trying to Dcount in a report agianst a query but I need to narrow down to a specific name within the [Catgory] for example [Category] ='Allowable' How do I fit that into the statement?
I'm developing a form to link a clause to one of a selection of internal documents from drop down boxes (of which there are a lot and hence looking for ways to make it more manageable to find).Now the setup of the internal documents is unfortunately beyond my control (separate SharePoint Libraries, inconsistent fields, you name it they've picked the worst way to do it :P).
I figure the best way to do this is to set up some unbound controls - two drop down (to narrow down location/department), two text boxes (doc code and name) with the idea that it will limit the selection to anything 'like' what is typed in.What I would like is either: How to set the RowSource criteria to only apply if the relevant control is not blank (i.e. if all the boxes are blank, all docs will show; if 'Sales' is selected in department and the user types 'Sales Order Entry' only the Sales Documents relating to order entry will appear)How to add criteria to the RowSource of a dropdown box via VBA (I've created the relevant 'If then ... else' statements but not sure how to add rowsource criteria in VBA)
I have a form frmMember, it has a search button that performs a complex search of fields. after performing my search my [BaseDues] field shows #Name? BaseDues is set by a CBO in the field above.
It works correctly except after the search...
Here is the Code for the Search Button:
Private Sub cmdSearchForm_Click() On Error GoTo Err_cmdSearchForm_Click Dim stDocName As String Dim stLinkCriteria As String stDocName = "frmSearch"
1. I created a form with some search-fields which are related to a query. Then I added a Subform in which I put some more Search criteria (So that I can easily hide and unhide those additional searchfields). It sounds strange but is necessary ;-). Now I related those searchfields in the subform to the same query. When I run that query a window pops up that I should put in a value in all those searchfields which are in the subform. But I told Access that it should display all rows, if there is no value in those searchfields. Just as I did it with the Searchcriteria in the Main form. Do I have to do something special, when I have a query which is related to two Forms?
2. I want a searchfield to search in three different columns. Usually the value will just be found in one of those columns. As the Table I search is very long and has many searchfields and multiple of those will relate to more than one column, is there an easy way to do it in VBA? As I did it by using the "or" field when designing a query, but this seems very slow and unstable.
I have a search combo box to search for a field on my form. But it just goes to that particular record. The combo box is for client id and it has more than one record. How can I have it return just the records pertaining to the client id choosen in the combo box.
I am using access 2010. I technically have an unbound form but I am changing the record source by command buttons. Switching between 3 command buttons. My problem is that I have a subform that I was linking to the form to an unbound search field. Now I want to switch the master and child links to a field on the form when I switch the record source which happen to be the same field as the unbound search field; however; it still pulls from the unbound search field rather than the field on the form. Do I need to delete the search field in order to get is to point to the appropriate field?
i have created an asset database for a school. i have made a query that search for a room but say, for example, i want to find the record for "room 32" the user has to put "room 32" in the search box. is there anyway for example if the user types "32" that the record will be found?
I want to built in an search button on the form in order to find records with the desired value in a certain field of the record. the caption of the textbox I want to search in is "NombreSTL" and the code below doesn´t work. whats wrong? thanks in advance...
Private Sub search1_Click() On Error GoTo Err_search1_Click Dim strWhere As String strWhere = "NombreSTL"
I am trying to copile an herbal database. I have a form in which I enter the information on each herb. One field is "medicinal uses" and I list all of the uses of the herb.
Okay, so how do I get it to list all of the herbs that have a medicinal use of "cramps" or "sedative". It won't find them because it is looking for the whole list of medicinal uses, not an individual use. Is there a better way to do this, or does the program not do what I want?
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.
Here is what I need to do: I have 1 table that contains groups responsibilities, there is a field Code-group that the initiator uses to track the groups & their % of impact to the overall project hours. Sadly enough he enters the data as such (EX: PROJECT1 GROUP1-25%, GROUP2-50%, GROUP3-25%.
The problem is that he has varied instances of groups listed in each record & wants me to be able to parse the data out regardless of how many groups are listed. I know how to parse data when there are a specific number of groups what I can't figure out is how to parse data regardless of how many groups is in the field.
Hi all I reread this and it was way to long. What I want to do is use a combo box to list values from a table field, when an option is selected, the rest of the fields update. Not sure what I have to do to achieve this. Any advise would be welcome. Regards Craig
Below is the main data entry form of my application. We disrtibute a product called MC Cloth to Shops to display. After a month we visit again and take stock of products sold, replenish and the shop pays for the products sold. the Database keeps a record of the shop, products displayed, refilled, sold and respective payments.
The dtabase and the form is loaded in Handheld (PDAs) by the sales people who enter data during the visit and then synchronise with a master on return
http://affiliatesexcel.com/MC_main_form.jpg
I need to sum up values in one field for example MC Refill from the first record till the new record and show it in another field, for example MC Refill Total
Another Forum answer to my question about summing up values in one field to be used as default in a second field showed that a search needs to be done based on base field (in this case NAME of customer).
However since I use a COMBO BOX to enter this NAME field values and then select it from a pull down list to create new records, I have this value ONLY in the very first Record. All subsequent records have all other values where as the NAME value remain empty.
This makes the search function impossible based on the NAME value. As a solution I would like to AUTOMATICALLY copy the NAME value to a second field (for example CUST_ID) during creation of each NEW RECORD so that I can then base my search on this field instead of the Name field (with empty values)
Currently I have the code below which works correctly for entering NEW Data and for recalling by Pull Down . (I have tried a mehod which entered the values for all records but this clutter up the Pull down with SAME Name for repeated records making the PULL DOWN unusable.)
I have tried to copy the Name value to Cust_ID value for each new record but the code gives an error.
+++++++++++ Code:
Private Sub Name_Combo_AfterUpdate() ' This procedure tries to find the matching product's record. ' If the matching record is found, the procedure goes to it. ' If the record isn't found, the focus stays on the current record.
Dim Criteria As String ' This is the argument to the FindFirst method. Dim MyRS As Recordset ' Recordset used to search. Dim ComboName As String ' The name of the company to search for. Const IDYES = 6
Set MyRS = Me.RecordsetClone ' Build the criteria. ComboName = Chr$(34) & Screen.ActiveControl & Chr$(34) Criteria = "[Name]=" & ComboName ' Perform the search.
MyRS.FindLast Criteria If MyRS.NoMatch Then
Response = MsgBox("Could not find the Supplier Name: " & ComboName & " Do you wish to register a New Supplier: " & ComboName & " in this Database?", 4 + 48) If Response = IDYES Then MyRS.AddNew ' Create new record. MyRS("Name") = Screen.ActiveControl MyRS.Update ' Save changes. MyRS.Move 0, MyRS.LastModified ' Go to new record Me.Bookmark = MyRS.Bookmark ' Go to new record Else GoTo Endsub End If Else MyRS.AddNew ' Create new record. MyRS("Name") = Screen.ActiveControl MyRS("Cust_ID") = MyRS("Name") MyRS.Update ' Save changes. MyRS.Move 0, MyRS.LastModified ' Go to new record Me.Bookmark = MyRS.Bookmark ' Go to new record
'Me.Bookmark = MyRS.Bookmark
Dim recNo As Long
' for this to work there cannot be any RecordNumber with a value of 0 ' it finds the highest record number for the name in the combo box ' and returns 0 and exits if no record found. recNo = Nz(DMax("[RecordNum]", "Miracle_Cloth_Main", "[Cust_ID]='" & Me.Cust_ID & "'"), 0) Debug.Print "RecordNo: " & recNo & " and Name: '" & Me.Name_Combo & "'" If recNo = 0 Then Exit Sub End If Me.Text90.SetFocus DoCmd.FindRecord "'" & recNo & "'", acAnywhere, , acSearchAll, , acCurrent
End If Endsub: MyRS.Close
End Sub
+++++++++++++++++++++++ The question is is there an easier way to achieve the summing function ?
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.
Hey gang, Is there a way to click on the find record button and have it automaticly search from a specific field? The only way I know how to search a field in a form is to select the field and then click on search. I just wanted it to automaticly select a field. Thanks gang, have a great weekend. PuJo
I'm working on this database in which I have 2 forms:
one form has personal information (PersonalInfo) and the other one is a search form (Search)
In the search form I have a field in which I want to type any part of the Name field in PersonalInfo form and I want to retrieve all the records that have this part of the name.
Here is what I tried:
In the PersonalInfo form, I want to the criteria of the NameField and typed the following:
Hi everybody, How I can set a query to search in all table fields. In my table I have around 48 fields and I want to search for particular value from combo box if this value exists in any of those fields.
I have an extremely large database which is in a continuous form format, and what I would like to happen is, instead of using the find button that would just find a word, I would like to have a pop-up search box that would yield actual texts throughout the DB (something like the search feature in Adobe Acrobat). In addition, I would like to use the "and" or "plus" together to search for additional data if needed. Please, unless there is a sample I could download and use in my db, please be specific in your instructions since I am new to all this.
I have a search form, generally it runs fine...until now. When the record contains a ' in it, I get an error. For example, I can search on last name Smith fine, but unable to search on O'Malley.
I get runtime error 3075 Syntax error (missing operator) in query expression 'lstnam LIKE 'O*M*' AND fstnam LIKE 'g*'
It works with other characters, like ? or . or !, just not the apostrophe '.
Any help would be great.
Here is the code I have for the search button. Private Sub cmdSearch_Click()
Dim LSQL As String Dim LSearchString As String Dim LSearchString1 As String
If Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then MsgBox "Please enter a last name." End If If Len(txtSearchstring1) = 0 Or IsNull(txtSearchstring1) = True Then MsgBox "Please enter a first name or portion of the first name"
