Basic Search Form Inquiry
Dec 12, 2005
Greetings, I'm a bit new to the world of MS Access and have encountered a problem when making a search form for my database. The query is using a Like Condition with the '*' Wildcard for a single field like so:
Like "*" & [Forms]![frmPickUpReport]![txtAgentName].[Text] & "*"
While the query on its own runs correctly from the viewer of course prompting me to enter the data manually the actual data from the form is being ignored. Instead of taking the text value within my form's text box, when I press the report creation button the same data box is prompted to me asking for me to enter the data manually.
anybody has a solution?
I'm using MS Access 2000 and cannot use any other application such as visual studion
View Replies
ADVERTISEMENT
Sep 8, 2005
first i must thank everyone here. this forum has proved invaluable for some of the simple databases i have created. now i have a problem which i cannot find the answer for.
i'm using access 2000
basically i am creating a cemetery database so most of the information i'm dealing with is duplicated. to create a unique record i have six primary keys on the one table. ie people with the same name, age, and birthday can be buried in the same grave.
anyway i need to do a wildcard seach from a form (QBF) based on surname and firstname. i have downloaded the code below and modified it:
Private Sub cmdsearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As querydef
Set dbNm = CurrentDb()
'Constant Select statement for the Query definition
strSQL = "SELECT searchtestdata.Surname; searchtestdata.Firstname; " & " FROM searchtestdata"
strWhere = "WHERE"
strOrder = "ORDER BY searchtestdata.autonumber;"
'Set the WHERE clause for the QueryDef if information has been entered into a field on the form
If Not IsNull(Me.txtsurname) Then '<--If the textbox txtFName contains no data THEN do nothing
strWhere = strWhere & " (searchtestdata.Surname) Like '*" & Me.txtsurname & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If
If Not IsNull(Me.txtfirstname) Then
strWhere = strWhere & " (Searchtestdata.firstname) Like '*" & Me.txtfirstname & "*' AND"
End If
'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)
'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs("quesearchtestdata")
qryDef.SQL = strSQL & " " & strWhere & " " & strOrder
'Open the Query
DoCmd.OpenQuery "quesearchtestdata", acViewNormal
End Sub
the code stops at the line:
qryDef.SQL = strSQL & " " & strWhere & " " & strOrder
and tells me either that i have "run-time error 3142 - characters found at end of SQL statement" - this is the error i am recieving at the moment, or more usually " run-time error 3141 - which tells me the SELECT - WHERE statement is wrong.
i am definetly no expert in VB - i am only just starting using it.
so guys any idea?
thanks in advance
View 9 Replies
View Related
Aug 22, 2005
Hello,
I am requesting guidence as well as some advise. I have a database table that is linked to our business software. The data is very very filthy and needs to be scrubbed.
Here is what it requires:
1. TRIM
2. CONCATENATE
3. Blank cells filled with NA
What I would like to know is, can I group these three commands into one query? I want to execute all my commands into one table. So far, I have done each query and created a table for each one. I know this is antiquated method, hence the posting.
Can someone shed some light on how to do this efficiently?
Thanks,
View 5 Replies
View Related
Nov 1, 2007
Hi all,
I need to create a MONTHLY report and I need help, please!
I created 2 combo box on the main form. The first combo box for the month drop-down list of Jan, Feb, Mar, ..., Dec. The second combo box for the year 2005, 2006, 2007, etc...
On the report underlying data source table, it has the Invoice Date field mm/dd/yyyy (for example: 11/01/2007)
I would like to select to view the report for the invoice of the month of October, 2007. How do I do that if I choose Oct and 2007 on the 2 combo boxes and click the command button View report to pick up the report for only Invoice Date during October 2007.
Please help. Thanks so much!!
View 1 Replies
View Related
Jul 15, 2014
There are 3 drop down button that should filter category of items in a database:
Category 1
Category 2
Category 3
Whatever users choose in category 1, it will filter the items in Category 2. whatever users choose in category 2, it will filter the items in category 3. users can choose 1 to 2 categories or sometimes the 3 categories. the items will show in a subform. the subform are getting the items from a query. the dropdown button gets its value from a Table.
My challenge is that, i want to get the number of items in Category 1 once users chose it. if they choose something in Category 2 the number of items will change also. there will be 3 textbox for Category 1 COUNT, Category 2 COUNT, and Category 3 COUNT.
as of now, what im getting is only the filtering of Category 1 and Category 1 COUNT.
View 7 Replies
View Related
Jan 12, 2006
Hi
Im an absolute noob when it comes to Access so please bear with me
For work I want to make a database to keep track of ordering, so thus far i have made 2 tables: suppliers and products
in my form to add products, i want it to display a drop down with the supplierName in it, but store the supplierID in the products table.
I figure this should be pretty simple, and could easily do it if this were php....
Can anyone help?
View 4 Replies
View Related
May 19, 2006
Hello,
Help appreciated,
trying to create a form so that when I type in a value it will bring back held in a table it will display two fields held in the same row. i.e.
Table 1
no
first name
surname
I want to type the no field and it displays the appropriate first name & surname fields for the row.
View 1 Replies
View Related
Jun 27, 2005
I have forms in a database that I am looking at. How can I tell which table the forms are connected to?
Easiest thing, I know. Help much appreciated.
-Mike
View 1 Replies
View Related
Sep 20, 2005
I am trying to incorporate a field in my customer order form for "Invoice Number". Not every customer order form will have an invoice number.
The Invoice Number field will default as null, or blank. I want to make it so the Invoice Number gets automatically populated ONLY IF another field on the same form is populated with "X".
Also, I would like the Invoice Number to equal the Previous Record that has an invoice number + 1. For example, if the previous 5 client records did not have an invoice number (blank because the client's form did not have a certain field populated with "X"), but the previous 6th record had an invoice number of 1001, then the current invoice number would be 1002.
So far, I just have the Form for the underlying table created. I don't have the invoice field in the table created yet. Not too sure where to start.
Thanks much appreciated in advance.
View 3 Replies
View Related
Mar 16, 2005
This is probably really basic stuff to most of you, but here goes.
(In the past, I have always had tables with zillions of fields, rather than having linked tables, so I have never had to mess with this stuff before...)
I have a db with 2 tables. A Main table with, say, contact names and addresses and a Lesser table with specialised info that I only want for some of those contacts.
The Primary Key in the Main table is fldContNum (autonumber), the primary key in the Lesser tables is fldNum. The two tables are linked 0ne-to-one on those fields.
There is not a record in the Lesser table for every record in the Main table.
I have a form showing records from the main table and a sub-form showing records from the Lesser. They are linked Child fld Num / Master fldContNum.
At the moment, I can go to a Contact from the main table and I can enter info for him in the sub-form, thus creating a record in the lesser table. That works ok.
However, if I move the main form to another contact and then go back, that info that I just entered does not show in the subform. In other words the subform is in "add new record" mode, whilst I want it to go automatically to the existing "Lesser" record with that number.
I can make it do this with VBA and searching recordsets and suchlike easily enough, but I feel that there must be a much more basic way of linking the two form and tables.
Also I want to make sure that if there is already a "Lesser" record with that number, I want to make it so that the sub form cannot even try to make a new record (because then I obviously get primary key violations).
I hope that I have made myself relatively clear here...
Thanks
George
View 2 Replies
View Related
Apr 3, 2005
Hey,
Got a simple question really...
Designing a form and I have boxes where the data is entered.
The form background is grey and i would like the data boxes to be grey
as well, unless the box is "active" meaning its selected.
ive done it for one form but i can't remember how on earth i would do it on another from i need to do.
Many Thanks
View 1 Replies
View Related
Oct 18, 2006
hello.. i have 2 tables
CUSTOMER TABLE
customerid
customername
customerphone
BOOKING TABLE
bookingid
bookingdate
customerid
At the moment i have 2 forms.. one customer form based on the customer table and one booking form based on the booking table.
at the moment, my user enters a customer record using the customer form.. they then save and close the customer form..
they then open the booking form.. and in the customer id field they enter the customers id.. this way, they successfully register that customer to that booking..
what i want to do is allow the user to enter the customer details and the booking details in one form..
i assume i would have to create a third form based on a query..
if i include all the fields from both tables in this query.. and then make a form based on this query, how can i make the following 2 things happen..?
1. when the third form is opened and customer details are entered... a new customer record will be added to the customer table (with a new customerid)..
2. this id will then need to be auto placed into the foreign key customer id field in the booking table.
do you get me?
View 3 Replies
View Related
Mar 10, 2005
I have a form which I use for many different tasks, in order to save database size, reuse code and keep things at their simplest.
Sometimes, I'll click a button to open the form which will only show specific filtered data in "Form" view, using a default "Dialog" border style.
Sometimes, I click another button which opens the same form, filters different data and displays the form in "DataSheet" view, but this time, I want the borderstyle to be "Sizable".
What I need is a line of VB code which will force the form to open-up with "Sizable" borderstyle, regardless of the form's Design view settings.
Something that would look like this (which obviously doesn't work):
Forms!frmFormName.Borderstyle = sizable
Does anyone have suggestions?
View 1 Replies
View Related
May 30, 2005
Dear All.
I have a visual basic function, placed in a module.
I need to call it from a form field, and pass a value to it and the function has to return a value.
I tried it but it doesn't work
the value I get on the field is " #Name? "
any help will be very appreciated.
Thanks!
CS.
View 8 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
Jul 10, 2012
i need to create a system that can search data using keyword.so i want to create search button that i will create it at form. currently i have 1 table and in that table i have 10 fields which are project_name, doc_ref_no, doc_title, volume, book_no, author, doc_status, box_barcode, filling_location, doc_availability.
i have create 2 forms, one form for user to add new record and another one is to search record. the user can search by enter project name, doc title, volume and box barcode. for project name and volume, i use combo box and for doc title and box barcode i'm using text box.
i want to create search function that can detect from user keyword and even user did not fill in all the form, the system still can search the record.
View 5 Replies
View Related
May 7, 2013
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.
View 5 Replies
View Related
Jun 11, 2006
Hey all,
I was wondering if anyone could point me in the direction of creating a basic "Add Record" form? I want everything to be button controlled, so once I've entered data, I click "add" or "save" or something. Similarly, create a remove button.
I'm trying to create a basic app for organizing my music collection, and I'd like to create a simple interface, with a few buttons ("add cd" "remove cd" "search for cd"). I just don't really know how to setup such a standalone app. Any help with any of this would be greatly appreciated. thanks guys!
View 2 Replies
View Related
Jan 31, 2006
HI!
I'm trying to figure out, how to make seach form with one text field, that will accept OR statements. I have one table with fields name, category, and description.
Is there any chance to make search like this:
User will put "part of the string" OR "part of the string2".
Result will be the list of the records that include these parts of the strings in any of the given above fields (name,category,description)
I hope i'm clear enough
Thank You in Advance
for any Advice
View 2 Replies
View Related
Aug 19, 2014
I have a bound form which is from tblEmployee, I'd like to have a dataset below (like a splitform but not a split as they have limits) so when i search in the box it gives me say all the smiths - i select for example david smith and it displays his information in the form objects above so they can be edited?
View 1 Replies
View Related
Sep 25, 2013
I've built a PostgreSQL database for some ecology data and am trying to use MS Access 2010 to make a front-end for it.
I've created a form to display site data; it includes such fields as site ID, site name, county, state, landscape, etc. (There are more, but if I can get the idea down with a couple of these, I should be good to handle the rest). One can click through the form and see 14 pages of site details, which is fine because there are 14 sites.
What I want to do now is make a search form in which one can enter values for site ID, site name, county, etc, and somehow get the pages of the display form that match those values. It's not terribly important to me how that output looks - whether it's a list of matches that allows for clicking on a match and showing just one page of the display form, or whether it's a filtered subset of the display form pages that one still clicks through to view them all.
if there is code to write, where do I type it? Do I start my search form by making a form with the search fields as values, or is there some other way? How do I make those search fields into search boxes rather than display boxes?
View 14 Replies
View Related
Aug 15, 2013
I have a form with two textboxes called: txtAptDep, txtAptArr.
On their right side there are two buttons (btn1, btn2) to open a form called frmAirportSearch. It allows to choose (from a listbox) the airport to be filled in those two textboxes.
Now, if I search for an airport pressing btn1, I want the airport to be returned into txtAptDep, while if I open the form by btn2, the airport choosen must be returned in the second textbox.
View 7 Replies
View Related
Jul 27, 2015
creating a searching form and to show the results inside the form! It's like a Library type searching. u want to search for some type of monument or so and it shows the results, all the info, photos and that!
View 6 Replies
View Related
Feb 21, 2007
Please can you guys give me some basic help, im pulling my hair out over this one!
Attached is a test database which im writing at work, what I would like to happen is that when a new machine ID is selected from the drop down box (combo box) all the relevant fields update with all the information in them.
Please could somebody download my test database have a quick look, see if they can sort it for me then upload it back and explain how they did it.
Thanks in advance
Andrew.
View 6 Replies
View Related
Oct 3, 2005
I will try and be as concise as I can...
I am trying to create a database for our organization that will keep track of our "Gift Fund". I have a table with all of our personnel that includes all of their basic info with a customer ID field as the PK. I then have a table that is for all of the transactions. Deposits, withdrawls, description, date, customer ID to relate the two tables and transaction ID as the PK.
So we have a table with our personnel and a table to record transactions. Transactions occur anytime we put money in or take money out as well as when one of our personnel becomes a member of this fund by paying their "dues".
I have created a form to update our personnel table. It works fine. I also have a form to enter basic transactions. It also works fine. My problem lies where I try to make a form that will add a member to this fund (which in the personnel table is a yes/no checkbox) at the same time entering this as a transaction. Ideally the form would have the user pick personnel from a drop down list, click the yes/no checkbox making them a member and then entering their "dues" as a transaction.
Ultimately I would like to be able present this info in several dif reports showing all the silly statistics that boss' love to see.
Any help would be appreciated and if I have left out any key info, let me knw and I will be more specific.
View 2 Replies
View Related
Mar 1, 2006
Hi there,
Having read many posts here regarding normalization, table designs with a view to avoiding duplication of data, avoiding storing of calculated contols into a field in a table (duplication as well as danger of keeping data updated, etc) - I would like to clarify something...
I've designed 2 databases, one sticking to the rules and one breaking them...
I hear the 'don't store' - calculate on demand principle - and this works well in the case of closing a new record where you 'store' a value by passing a subtotal from a subform to a field in the main form. All that passing of values works but there are time delays. Look at the time delay of summing a range of values in a form footer...bout half a second in general...So in this case, storing values fails...(incidentally is there an 'event' for this time delay a calculated control takes to show it's value in a form? - OnErrorCausedByDelayOfCalculatedControlToDisplayIt 'sValue?? Oops ignore the apostrophe ;-).....).
On the other hand you may want to work through many thousands of records, summarising all your data into reports, charts, etc...It just seems that if you stored each records 'total' (say an invoice total) it would be quicker for to work through thousands of 'ready stored numbers' rather than thousands of 'calculations to get those numbers'...or maybe I'm missing something!?
Sorry for all the fuss, but I'm self taught and I wish I wasn't LOL. I shoulda definitely done college instead of getting hooked on databases in my 30's)
And I'm also sorry if this question has been asked before, even under different wording...
Best regards
dazza61
View 1 Replies
View Related
