Selecting A Record On A Value Being Between Two Fields In That Record
Nov 14, 2005
Hi Guys,
Im trying to do something that i dont think is possible, but perhaps i dont know the correct command.
I have a table, which contains costs. the costs are deceided by a circulation. if the circulation is below 10,000, then the cost is A. if its more than 10,000 but less than 20,000, the cost is B.
I have a query that brings back the titles, and thier circulations. what i want to do is use this data to lookup the correct cost. is this poossible?
I have an interesting report query I am working on. Is it possible to have a query that shows different fields for individual records depending on the content of the record? Let me see if I can explain. Let's say that I have a Status field that can be "Complete", "Active", or "Planned". And lets say also in that table I have Field1, Field2, and Field3. For each record, I don't want to include all three fields (Field1, Field2, Field3), but only one of these fields depending on what is in the Status field.
So, it would be something like:
Select Status, (if Status = "Complete", select Field1), (if Status = "Planned", select Field2), (if Status = "Active", select field3) FROM table;
So, the query would result in 2 fields for each record: the status field, and one of the three other fields depending on what is in the status field for that record.
I created a search form that searches for Customer Number, Part Number, and Revision Number. The results are displayed in a List Box.
I want the user to be able to make a selection on the List Box and click an "Open Selected" button that launches that record in another form. How is this possible?
I have a table with customer details, probs over 3000 customer, and i need a method of selecting the customer in a form by their surname. Currently i have a combo box, but as you can imagine, this is not practical with so many records. anyone think of any other way to do it?
I have a table with over 90,000 records, how do I create a query to select only every 4th record? If this cannot be done with a query, what are my options?
I've got a problem with a query that had been working without a problem up untill we moved the database from a Windows 2000 server to a Windows 2003 server. The query is as follows.
SELECT LAST(Tools.Mould_Number) AS LastOfMould_Number FROM Tools HAVING ((Tools!Mould_Number<"M3800"));
The query is supposed to display the last record entered having a Mould_Number less than M3800. However the query is showing the last record entered into the database before the move to the new server. i.e. M2667. This is not the last record entered though. M2668, M2669 and M2670 have been entered into the database after the move.
Any Ideas? I'm starting to pull really hard on my hair... It may start to come out soon...
I have a couple querys that rely on this statement:Like "*" & [ Project Number ] & "*"Now that makes a box pop up for the person to fill in specific information.
Now, is there a way to make a form that a user can choose a job from a drop down box, which then when click "next" it will execute a query with what was selected from the drop down box?
I have a form (in datasheet mode) that lists many records. I need some code that will select all fields in the current record. (That is, the current cursor location.) Thanks
OK, my database is also complete, thanks to the help of this forum!!!
My next question is:
I have a switchboard that has a button for my data entry form. The lady that will use this form will, of course, enter all her records using the form into the database. Because of what she does, she will have to go back to records on a daily basis to make corrections to individual records. I would like a way to put a command button on my switchboard that will say something like "Correct Records". She will hit this button and it will ask her for the medical record number she wants to correct and then will take her to that record to correct.
Is there a way to do this or is there a better way? Currently I have a button that will ask for a day range and then will bring up a query of those records for her to correct but I would really like to be able to somehow bring up the individual record, by medical records number, for her to correct instead of going through a long list of records that were input on a single day. Thanks so much for all your help getting this thing up and running!!!!
The fields we want to focus on is "AccNo" and "TestCode"
Now i want to filter based on this condition:
If "AccNo" AND "TestCode" BOTH have duplicate data in a record. Show that record only once. Remember. . . "AccNo and TestCode, both these fields have to have duplicate data in a record.
Example: MethodCode..... AccNo .....PatientName.....MR..... TestCode ..... etc, etc CAPT.....M566679.....John Blue.....123456.....CBCA CAPT.....M566679.....John Blue.....123456.....CBCA the example above should only display once because of the repeated AccNo and TestCode
Example: MethodCode..... AccNo .....PatientName.....MR..... TestCode ..... etc, etc CAPT.....M566679.....John Blue.....123456..... CBCA CAPT.....M566679.....John Blue.....123456..... LIPID the example above is just fine because the TestCode is not matching
How do i create a query to accomplish this? someone please help, i've had troube with this for the passed couple weeks . .. .
Hi All, What is the easist way to select a column's value in a subform. After the record is selected I would like to write a value ("0") into this column by way of a button click..
I am experience brain flatulence and need some remedial assistance.
I am creating a data entry form. I have envisioned a tabbed format because I will have this idea utilized for Placement, Mentoring and Seminars data entry as well as recruitment. But I am open to other methods. Here is a description of what I have...
tblTeachers (this is the ONE)
Demographic data
tblRecruit (this is the MANY)
recLocation recMethod recYear recSchool
Form =================
List of teachers available to allow a user to see who has already been entered to prevent duplication Use this same list for navigationrecord navigation
The problem I have is this:
I have created a tabbed control that has the data entry fields I have created a subform to display ALL teachers from tblTeachers I do not know/remember how to link these so that the list may be used to to display the select record in the tabbed control
I have a form to modify existing property records. Each record has a PIN number and a unique ID which is a combination of year, state county and PIN. The same property can have a record for more than one year. On this form I have a combo box for selecting the record to be modified. The combo box includes PIN and Prop ID. The combo displays each record by PIN then ID as separate records. For example:
PIN Prop ID 06104409012 2007-IL-Lake-06104409012 06104409012 2010-IL-Lake-06104409012
But when I select a record it will always select the first record with identical PINs. In the case above if I click the 2010 ID the record fills in with the 2007 ID.
I wish having only one record display in a continuous form, after an "On Click" event. The database in which this question lies is attached.
On opening, the user first interacts with Table 1 (Continuous Form), then by clicking on ID on the details for that particular record appear.
What I would like to do is: When the user clicks on ID, only that record whose ID has been clicked on displays first on the continuous form, and then the PERSONAL form opens. To clarify further, my wish is that the sequence of events is:
User Opens Table 1 Form
User Clicks on ID (for example, let's take 2)
Only Record with ID 2 remains on the Continuous form.
Then details for record 2 display on the Personal Form that opens next.
The problem I currently have is with step number 3 above.
I've got a subform that im writing code for, once a user enters a value in one of the fields i want to make sure they cant change that field (and two other fields in that same record, though the rest of the fields are fine to edit at will)
I've got a function already written to validate the data in the record and decide whether or not the fields are enabled and disabled, however im having trouble finding an event in which to activate the function
Is there an event that triggers whenever a user selects a different record?
Any chart or list with all the handlers and how they trigger (in relation to each other) and when?
My Database is really simple, it contains 1 table (called Querys) and a form called Dashboard.
In my main form (Dashboard) I have inserted a sub form which shows all the records in my table (called Querys) when I use my main form and add a record this sub table can then be refreshed and it shows all my records.
This part is fine,What I would like to do is be able to select a record in my sub form and it show the record data in my main form. This would really speed up the navigation process as the database gets bigger.
So I have a multiselect listbox. The list that it shows depends on what is selected in a separate combo box.I want to be able to select multiple items from the listbox, and then be able to leave the record, and when I come back to it, to still have those items highlighted. Right now in my form, as I go through records, if I highlighted items 1,2 and 3 on a record, those first three lines will stay highlighted as I move through different records, rather than changing to what had been selected for each record.
I've already stored the actual selected values in a subtable. I just want to have it highlighted again so that you can see what was previously selected.
Trying to update a field in a record in my form after selecting from a comboox.
This an orders form with a record per row for order items. I select the product from a dropdown list which is populated by a dynamic query from the afterupdate event on another combo. When I do the selection access reports the following error: Runtime error 3331 to make changes to this field, first save the record
Debug takes me to the line : Me!product_id = DLookup("product_id", "products_table", myvar) from : [code] Private Sub comboProd_description_AfterUpdate() Dim strFilter As Integer
I'm lost as to what to do (no such thing as beginners luck!!). I'm not even sure if the error is from the combo box or from the field that it is trying to update (product_id)
I would like to select a record on an already open form called "form_candidates" using a search facility but can't get it to work.
In "form_candidates" the data settings are set to "yes" (filters/edits/deletions/additions) and data entry is set to "no". On opening, the form is set to go to a new record.
In this form I have a button that opens a record search form so both forms are now open. I enter the first few letters of the surname into a text box, press a button and the listbox is populated.
In the doubleclick event of the list box I have the following code:
Dim CandID As Integer CandID = Me.List4.Column(0) DoCmd.OpenForm "form_candidates", acNormal, , "forms![form_candidates]![Cand_ID]=" & CandID
So I double click on the record, the candidate form is being filtered but the record isn't being shown.
I am relearning Access and having a little difficulty. I am creating a Access database that links tables from an I series system which I have done trouble free. In the database I have pulled up for example a patient demographic file (hospital). I have form frmSearch that prompts the user to enter the medical record number OR they can search by name. I have a query that takes that information then loads a 2nd form to display the query results in a list box. I would like to be able to click on one of the results from the 2nd form to then load another form that will display all of the patient information that was selected.How do you set this up so that with the results shown, I can click on one of them and it will load that patient into another form pulling the rest of his or her information from the table file?
I have a query pulling data from two other queries (qry_Reports and qry_Surveys). Clients may have more than one ReportID, but only one ClientID. I need to query for only the most current ReportID (which is the larger value) for each client to find the surveys for the most recent report. How can I query for only the most recent report for each client based on the highest value of the ReportID per ClientID?
I am working on a database used in recording device characteristics/test information. The main table of information has dozens of columns for test/part detailed information. When inputing the data for each specific test, many of the info. details are repeated when testing say 20 devices of the same part all at once. Rather than retype every piece of detailed information in every field, everytime, is there an easier way? Does anyone know of a way to make specific fields copy/paste the previous record's information in the fields automatically when a new record is created? Please, if anyone could help or has ANY ideas, let me know...
I have two subforms on a main form. Both use similar queries and nearly the same set of records and PK. The first subform is for data entry and the 2nd subform is a continuous form that lists the entries in order that are made from the 1st Subform. (for entering in vacation days and appointments)
The continuous form cannot be edited, it is to be a list for viewing the information only.
I have an edit button next to each record on the continuous form. When the button is clicked, I want it to take the 1st subform to that specific record as well (same PK), so the information can be edited there.
I cannot figure out how to get the 1st subform to go to the record on the continuous form when the button next to that record is clicked on the continuous form.
I tried the DoCmdSearch for record and just keep getting object is not open errors.
