I'm trying to write an app that will allow the user to search for records based on database fields, then select a subset of those records to be manipulated by other functions. The VBA book I'm reading led me to believe that a RecordSet would be the best way to store this subset of records, but searching around on the web has pointed me toward using a DAO.QueryDef. I can't seem to get either method to work!
Here's a quick overview of the app: everything is placed on one form. I have two tab controls that make a sort of upper and lower set of pages. One of the lower pages has the search functions. The user enters his search criteria into one or more textboxes (correlating to database fields, i.e. Last Name, SSN, etc), then hits the Search button. This should query the database, then populate a listbox with the search results. The user can then select one or more records from the listbox, hit another button, and the selected records are copied to another listbox on a page on the upper half of the form, where they can be further manipulated.
Can someone advise me on the best objects to use to accomplish this? A short code sample would be awesome.
By the way, does VBA have some sort of online API reference (like Java)? I know that MS Access has the object browser, but it doesn't give descriptions of the objects, nor does it list methods that can be invoked on them. Could someone also point me towards some good programmer's resources?
I have a form that runs a parameter query to search for university name and then displays 2 fields, university name and course name.
I am having difficulty with a search button that i have on the form called search_command; it is supposed to run the exact query as when you enter the form, it does this but displays the result in a dataheet, i want it to repopulate my 2 text label fields as mentioned above.
In addition i want then to be able to go to a specific record, select it and then press a button to append it to another table. i ahve not started this part yet
I have a parent table called patient info. and a child table called fills. The database in the future will become very large, and will be difficult to find a particular patient in the parent table to update fills in the child table. How can I search through the parent table with ease so that I can update fills? Thanks for those who help!
hi.. i am currently creating an employee database.. and there is a form on the database that needs to search on records.. i am planning to create a form with a single textbox (for the keyword entry) and a search command button that when clicked, the search results will be shown in a seaparate continuous form.
now here is my query:
SELECT tblEmployee.EmployeeIDPK, tblEmployee.LastName, tblEmployee.FirstName, tblEmployee.MiddleName, tblCompany.CompanyName, tblDealer.DealerName, tblOutlet.OutletName, tblPosition.PositionName FROM (tblOutlet INNER JOIN (tblDealer INNER JOIN (tblCompany INNER JOIN tblPosition ON tblCompany.CompanyIDPK = tblPosition.CompanyIDFK) ON tblDealer. DealerIDPK = tblPosition.DealerIDFK) ON tblOutlet.OutletIDPK = tblPosition. OutletIDFK) INNER JOIN tblEmployee ON tblPosition.PositionIDPK = tblEmployee. PositionIDFK WHERE (((tblPosition.PositionName)=[Forms]![frmSearchEmployee]![txtSearchFor]) ) OR (((tblOutlet.OutletName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR (((tblDealer.DealerName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR (( (tblCompany.CompanyName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR (( (tblEmployee.MiddleName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR (( (tblEmployee.FirstName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR (( (tblEmployee.LastName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR (( (tblEmployee.EmployeeIDPK)=[Forms]![frmSearchEmployee]![txtSearchFor]));
but when i click search, the results are empty. what could be wrong with the query? is it the query? what should be the record source for the search form and the result form?
if you want to see the actual ms access file, here it is: http://www.gigafiles.co.uk/files/636/human%20resource%20info%20system_2006-10-11.zip
the name of the form is frmSearchEmployee and frmSearchResultEmployee and the name of the query is qrySearchResult.. thanks a lot and God bless..
I would like to put a search tool on a form that does pretty much the same as the find dialogue box, with out it being a dialogue box...
I need to be able to search specific fields on the form and all the fields. It needs to "Find Next" so to speak, as in not just finding the first match.
I wanted to see if it is possible to pull a query by just entering say the first three letters of a project name and the query displaying everything from the table that begins with those three letters. Any help with this is greatly appreciated. I am just completely stuck!
I have a form I am using to search for records based on any number of criteria. The one I am having a problem with is a model number search. I would like to search based on a from value and a to value.......for instance........let's say you had the following items:
bird birth bill birdbath bite bitter
If I want to search for values from bir to birt, I would get bird, birdbath and birth. How can I code my query value to look for values between two text boxes in a form?
With some help from this forum (esp. Pat Hartman), i've been able to code up a script that performs a check on duplicate values.
My database has a Room Bookings form which consists of the room name, periods and booking date (BDate) fields. I need to prevent a duplicate on the same date, period and room.
The only problem with my code is that it only does a check on one of the fields e.g. BDate although i select a different period or room it still comes up with an error message saying that booking already exists? This means it is only checking the duplicate value of 1 field and not a combination of fields.
Here is my code:
Private Sub Form_BeforeUpdate(Cancel As Integer) Dim strSearch As String Dim varKey As Variant
strSearch = "BDate = #" & Me.BDate & "# And Period1 = """ & Me.Period1 & """And Period2 = """ & Me.Period2 & """And Period3 = """ & Me.Period3 & """And Period4 = """ & Me.Period4 & """And Period5 = """ & Me.Period5 & """And Period6 = """ & Me.Period6 & """And Lunch = """ & Me.Lunch & """And After_School = """ & Me.After_School & """" varKey = DLookup("Booking_ID", "Furtherbookings", strSearch) If Not IsNull(varKey) Then If MsgBox("Booking already exists Booking ID: " & varKey & ". Do you wish to continue to create a new record?", vbYesNoCancel) = vbYes Then Else Cancel = True Me.Undo End If End If
End Sub
Can any1 identify where i am going wrong?
I'd really appreciate any help/guidance to resolve this problem!
I've been looking and abusing the search function on this forum for this particular obstacle but no luck.
The client im building a database for wants a search form and everyone who used the old system are used by using *'s in their search criteria. I know how I can use *'s in queries but its fixed (I think). I want to let the end user use * when they want to pin down a particular product. So they just type in a part of a word and use a * at the end, middle or before.
Or should I just abandon the idea of using them in input form controls? I know this works because I have seen it before, too bad I had no access to the source of it.
I hope someone comes up with some ideas, pointers or howto's
Hi all, I've got one question again. Let's say. I've created several tables to store data for several categories.Note: there is no relationship between each table.In each table, there is the date field in which the record is created. Then I want to make another daily record form that is to find records from all categories which is created at the current date. Is this possible to search records from various tables and combine them to show in one form or report? Can anyone help me in this case? thanks in advance.
I have a database which has numbers for different statistics and i would like to be able to search, for example, the past 10 weeks and find out how many time a certain number has been recorded.
i am a beginner.. how i can write a code in a afunction that search a record in a table according to some conditions example: i hv table employee that contains Title field which can have one of 3 values:Admin, Rep or Driver i need to search the employee that has his type = admin i can have one or more records that have this type... so how to find first one, last one and all?
I have a database that I am creating for my work. I have a form that I am trying to get it to search the information from a table to pull the record on the form. I would like to search infomation such as employee id and wanting it to pull that information from the table.
I have a query that gives an out put of 'worst offenders'. these offenders are in order of on field ascending (number of entries made per week) and another field descending (total sales). It is by the combination of the sorting of these fields that we have an ordered list of offenders.
For my report i want to only see the top 10 records of this sorted list.
so for this selection i simply want to select the [B]first 10 records [B]of the ordered list. I cant make a selection by any one particular field(as explained above)
i have looked into the 'select top' functions and 'dfirst', but I dont think they'll work for me.
hey guys, im having one last problem with a report im making. just want to thank boblarson, Rich and Dennisk for all the help they have given me upto now, i have added to your reputation guys, thanks!
My problem is as follows:
I have a form for my products and a subform for the suppliers, each product can have many suppliers.
I need to have a report of products to send out to customers, so i created a check box to "tick" if i want to include it in the report. So far so good, however, the report shows each product several times for each supplier :(
can anyone tell me how to select only the supplier with the lowest supplier price for each product
I am building a database to evaluate football team performance. I have a table with results (tblResults) where a team will have records in either the Home Team or Away Team fields depending on the fixture.
I want to include a "Team Form" calculation that selects the last 6 games for each team (both home & away) and allocates points to calculate a recent form stat for comparison with the opposition.
I have tried using the TOP VALUES option in a query, but I can't seem to get it to extract the top 6 in descending date order for every team.
I created a personal database for keeping track of my meeting schedules. My table has Date field, Time field and Subject field. I have made several entries. On a given day, I have several meetings at different times.
I created a combo box in my form to choose the Date and see that day’s meetings. The meetings list of the date chosen by combo box should appear in my form. But here I think I made a mistake. Each record has date, time and subject. When I click the combo box, the drop down list shows the same date many times (as I have entered the same date but different time and subject for each record). How do I make the combo box display the date only once and not same date several times? Do I have to change the table set up? Please guide me.
I have a database that allows the user to order clothing items. Each size of each item has a different stock number.
I have [StockNo] - [StaffNo] - [Qty] i need to generate [OrderNo].
The problem i have is that [OrderNo] is incremented after every five five items (ie[StockNo]). This gives me my first problem of selecting just the first five records of a table in order to allocate an order number.
The second problem i have is that in order to allocate an order number, i have to summarise the [Qty] by [StockNo]. This gives me the opportunity to select the order number but i lose the individual [StaffNo] information. This information, together with the [OrderNo] is needed for a master order table.
I have created a crosstab query which summarised the [StockNo]but how do i retrieve the [StaffNo] information from the column headings.
I am new to Access and would be grateful for any help or comments as to whether i am going about solving this problem in the best way.
ok so I want a form that has a combo box with [item_id, item_name] on each row that i select. when i select one it displays the relevant quantity and allows me to edit the quantity.
I am using an unbound box on the top of a form where I would like to select a clients name and have the form populate with their info. My problem is that the box will pull up the record by last name, but only pull up the first record with that last name in alphabetical order.
Example, If I have the following names in my database
Ben Smith John Smith Tim Smith
If I select "john smith" from the drop down "ben smith" will show up, if I select "tim smith" "ben smith" will still show up. If I select "ben smith" the correct record would show up.
I tried the Crystal Reports forum, but no responses. Can anyone see what I'm doing wrong with the below statement in Crystal Reports 8.5. I'm trying to select reecords that exceed 7 days based on a Crystal Reports parameter. The parameter has the format of 2/27/2006 (Discrete value). The 'LastRouteDate' has a format of 'datetime'. Thanks ! ! !
I have a problem I should be able to solve, but I can't think my way around it.
I need to automate a series of tasks to perform the following:
1. The user clicks a button on a menu form and arrive at a select records form. 2. The user makes choices regarding the contents of certain fields (blank, not blank, equals a text string, etc.) and presses OK. 3. On the OK click, the fields are applied to a query, the results of which are exported in an excel spreadsheet.
I've been doing step 2 and 3 by hand in that I alter the selections in the design view of the query and save it. I then run a macro that deletes the data in the current table, runs the append query with the new criteria, and creates an excel spreadsheet of the new data.
However, I now have to make this functionality available to a non-tech person, thus the need to create a GUI menu that can launch a form on which the user can select options and with a single click, launch the macro.
I've tried adapting select forms that I've used with forms and reports using the doCmd.openquery, but they will only work for select or crosstabs, not appends. I considered having the form create the query and then qrite the append query off that, but I can't seem to get that to work.
Can anyone give me some direction on where I can take this?
I was wondering if someone could help me. I have a simple table with data that gets updated daily. I need to split the data into 5 equal parts for work distribution. Because the amount of records always changes I can not go by numbering so I need to go by percentage, to split it into 5 ( 20% ) parts. Please help.
I have looked but have been unable to find the answer in past posts and this must be a common problem. I wish to include all records up to and including a date passed in the prompt "Criteria: <=[enter end date]". I changes the date format in the back end to short and I am in the US. However I get only dates less then the the prompted date. Thanks for any help Jim
