Modules & VBA :: Sorting TextBox Values In Numerical Order
Sep 20, 2014
I've got a rapidly expanding database that I designed to do my quotes for work and now I'm trying to expand it to add up the invoice amounts when the jobs come to fruition. I've got 99% of it working well but as I'm not the only one entering data I'm trying to make it as foolproof as possible and here is my problem.
I have a multi-line textbox that receives 'vehicles' in an abbreviated format and I need to get them in order based on a sort column number in the appropriate table. This i can do but the code I have found removes the duplicates which I don't want as I can can two identical vehicles on the same job.
I have a report in which a textbox generates numerical values and letter values. I want to...On report load - if textbox = numbers then hide otherwise show if it contains letter values.
I've been building a small app for some friends. At present, they enter "records" line by line into a word processor, then use (archaic, IMHO) macros to produce the desired printouts. They wanted the new data entry UI to mimic that process.
Ok, so I've adopted the datasheet form as the principal entry vehicle. Based on the initial info they gave me, there were two convenient fields to sort on, so I used them in the OrderBy clause of the query which serves as the form's recordsource. I also developed the code to maintain the sort order after they inserted or deleted a record.
Now it turns out they don't require an entry into those fields, and in fact there's no logical field at all on which to order by.
So the question is: how to approach this? From my reading, I can't use the primary key or an autonumbered field, since the former won't necessarily reflect the desired incrementation and the latter only triggers for a new record, not an insertion (is this correct?).
My thought is to add a simple numerical field (which will be hidden from the user) with sequential values. I can do the OrderBy on it, and add code to adjust those values whenever a record is deleted or inserted. (That will require looping through the recordset from the point where the deletion or insertion occurred.)
For instance, is there some way to add a calculated field to the recordsource for this purpose?
I have a problem with a cascading form, which writes back to another table. The scenario is:
There are three tables. Users, Departments & SubDepartments.
Each table has an Autonumber set as the primary key .
The form is to write back to the user table, and within the user table there is a Department & SubDepartment field. These are linked to the relevant tables and all that works.
For the form I have two combo boxes for Department and SubDepartment. Department simply pulls from the Departments table and displays/writes back to User table correctly.
The SubDepartment is the one I am having problems with. I have got it to cascade correctly but here is the exact issue:
On the existing records the subdepartment is being displayed as a number (the primary key autonumber). The drop down list displays the text of what the subdepartment is (which is what I require) but then when that is selected it throws up the error that the value entered isn't correct for that field. I guess it's because it wants to write back the numerical record ID rather than the text from the other field.
The row source query is SELECT [qrySubDepartment].[SubDepartmentName] FROM qrySubDepartment ORDER BY [SubDepartmentName];
The table fields for the subdepartment table are SubDepartmentID, SubDepartmentName, DepartmentID and SDID.
SDID can be ignored for the purpose of this question and DepartmentID links back to the main department. Just to confirm visually the cascading form does work in terms of when you pick a department it only displays the subdepartments associated to it.
I have a list of 22 soccer players with their weights sorted from heaviest to lightest in numerical order. I want to add further players and have the list reflect their standing each time it changes i.e. Col 1 Player ID, Col2 FirstName, Col 2 SecondName, Col 3 Mass:90 Kgs Col 4 Position In group: 1
A new player registers and is 95Kg.....he becomes #1 and the rest go down etc. Can this be done?
Lets say I have table with one of the column header as notes. The notes is of type 'text'
In the notes column information like the following is typed out
MAX O.D 3.456" Min ID 1.2" and OAL 3.4"
or
MAX O/D 5.456" Min I.D 1.2" and Min Length 4.4 inches
I want to pull out the first numerical value (3.456 - first example, 5.456 -second example) and the last numerical value (3.4 -first example and 4.4 in second example). How would I be able to do this.
So the end result should be 2 more columns with max od as one and min length as the other. Regards George
I have an ASP form that saves numerical data to an access database via an insert statement in the asp page.
The main problem i have is that i have some code that adds all the stored values together and provides a total on the screen for the user to print off.
I would like to save this 'total' value to a field in the table but cannot seem to get this to work.
So i thought of running a query in access that would add the sum total of a number of fields and store it to the total field.
What is the best solution to this problem? How can I automatically assign a numerical value to a field based on the information currently contained as test in another field? For example, if I have a field that ranks a film as “Poor, Fair, Good, Great, Superior”, how can I have a set value appear for statistical purposes that assigns a number to each of these values (Something like: If rating of current record = “Poor”, then ranking = “1”; If rating of current record = “Fair”, then ranking = “2”; If rating of current record = “good”, then ranking = “3”, etc. I also want to be able to have the values recalculate if someone changes their original opinion.
How do I suppress the numerical values from being displayed on the "yes/no" check boxes when a user clicks directly in the field, instead of clicking the arrowhead as they should?
I have a form in which the user has the privilege to download some data from the table.the user will paste some of the numbers to the textbox from the excel in order to download the specific data.i need to write a query to parse the textbox using the in condition
a.) how to also stop a leading zero (e.g. don't allow 04, but allow 4) b.) Don't allow any combination of numbers to be outside 1 to 36
Yes, I can do this on a validate event after the fact. Just wondering if there is anything that could put this all into the KeyDown event to prevent errors in the first place.
This code works great to prevent any key entry except 0..9, Tab, and backspace
Code: Private Sub txtSectionNumber_KeyDown(KeyCode As Integer, Shift As Integer) Select Case KeyCode ' only accept a number 0 to 9 Backspace or Tab Case vbKey0, vbKey1, vbKey2, vbKey3, vbKey4, vbKey5, vbKey6, vbKey7, vbKey8, vbKey9, vbKeyBack, vbKeyTab ' do nothing and accept the value Case Else KeyCode = 0 End Select End Sub
In the US Western States, a survey township is simply a geographic reference used to define property location for deeds and grants as surveyed and platted by the General Land Office (GLO). A survey township is nominally six by six miles square. They are assigned numbers of 1 to 36.
I have created a report from the information submitted on a form. When I pull up the report, I would like it to sort differently than it is. I am not sure what it is using to sort from but I want it to sort by a designated # I have assigned it.
Is there a way to make it so the access's record # will always match our companies record #?
Today I entered information in a form that should have gone in as record 96 but when I went back to look at the record in the report, it was record # 72. ??????? Now what do I do?
I am creating a database for cases. I want to set autonumber into sequence, Let say if there are data numbering 1,2,3,4,5. If I delete no.2, The data will rearrange in oreder from 1,2,3,4. While If I add a new value it would be the no.5.
Instead of data value autonumber 1,2,3,4,5 as I deleted no.2 and add new records. The data has become 1,3,4,5,6.
I am working on an app, that has a field named "Name". I have everything set up with a module, which, for example, the name "John", it assigns 1. when it sees "John" again it assigns 2. Then "Jane" It assigns 1 again. I want the module to see every instance of "John" to assign the same number, 1, then all instances of "Jane" 2, etc. Here is the code in the module I am using, but it is assigning the values wrong. My final plan is to use the numbers for conditional formatting, so all johns one color, all janes, a different color. I can't use the conditional formatting wizard because these names pop up at random, and the names populate at random.
Option Compare Database
Global GBL_Category As String Global GBL_Icount As Long Public Function Increment(ivalue As String) As Long If Nz(GBL_Category, "zzzzzzzz") = ivalue Then GBL_Icount = GBL_Icount + 1 ' MsgBox icount Else GBL_Category = ivalue GBL_Icount = 1 End If Increment = GBL_Icount End Function
I have a query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE. Is there another way to achieve the same result? If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:
UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN) SET TableA.HCC = TableB.HCC WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL ORDER BY TableB.HCC, TableA.CDN;
My table have orderid along with other columns, i want to create a form where i am having a textbox for order id input by user. Firstly when user sees the form its only with the textbox and table fields, when i put the order id in the textbox the listing should be made.
There is a text box in a form that user can enter the value. How can I check if the value entered by user is already existed in the record of the linked table or not? If the value (or record) do not exist, that new value/record will be added to the table. If the value do exist, just show the msgbox to indicate.
I have a database with a code field which contains alfanumerical values like: IB1, IB2, IB3...IB180, and so on. When I sort the usual way A-->Z, I obtain: IB1, IB10, IB100, IB101...IB109, IB11, IB110, IB111
How can I sort nicely to obtain IB1, IB2, IB3...
To complicate things, there are alfanumerical values with a different number of letters before the figures: like F1, F2... and SER1, SER2 and so on.
:confused: :confused: :confused: Please bear with me. I'm new to using access and plodding along little by little and I had little luck finding relavent past posts. I have a table with the column headings and a couple of records listed below. Each of those records has a subdatasheet pertaining to data for the commands. For every command, my dept does a survey or inspection every so many years. How often we do these surveys depends on the HAZ CAT. If the command has a HAZ CAT of I, they have a survey done every year. (HAZ CAT= II, every 2 years; HAZ CAT = III, every 4 years). On a form, I want to be able to generate the next serveral survey years (let's say for the next 10 years) based on the command's most recent survey year and its HAZ CAT. So for each command, there should be a listing of all its survey years. Then I want to be able to sort by year so I'll know what commands will need to be surveyed each year. I'll eventually have a form which will be my control panel. On the control panel will be command buttons labeled with years for the next 10 years. The button should open up a form with the records for the respective command information.
[CommandID] [Command] [HAZ CAT] [Last Recent Survey] 1 AIMD I 2004 16 DODDS III 2002
I have a sub for which is set to sort by ascending order for a given field which happens to be a text field with vales set something like abc1.1,abc1.2 ect. The problem is, is that when you get to abc1.10, and more these are then sorted as so abc1.1 abc1.10 abc1.11 abc1.2 abc1.3 ect
How can I stop this and sort it: abc1.1 abc1.2 abc1.3 ....... abc1.10 abc1.11
Im trying to sort a form on a date. it only has dates put in once a confirmed date is known so when i sort all the blanks come to the top is there a way of sorting excluding null values
I currently have records that end with a letter and 2 numbers. For example, A1, A2, ... , A10, A11. When I try to sort my table/query by these values, A10 & A11 come before A2. It seems that it is sorting by the first digit shown. Is there any way to fix this quickly within table/query properties so that this can be displayed in proper numeric order?
I'm trying to make a report that a untrained user can use to review the relevant data from the database I'm creating. All of the work I've done so far has been directly in the tables . Here's a quick outline of my general data organization:
Basically, I want the user to select a department to view using a combo box or option group (which I'm not sure of how to do in a report). Then, I want to view the employees within that department sorted first by whether or not the Needs_Work check box is selected, and then by how many of their competencies are unchecked.
hi i am trying to order a select statement by two columns, so it first orders by type, then by value. the above code would work in php/mysql (i think!) but not with asp/access. any ideas?
I have a Form which presents the results from a query. Upon clicking the header of each column of data the records are sorted A-Z and Z-A using the code below. This works fine when all records have data in the relevant field but not when several records have a value of null. It appears that when null values are present the "If Me.OrderBy = "property" & " DESC"" always evaluates as not true, even though all the null values are on top and all the filled in values are in appropriate order.
A second issue is one of my fields is not sorting properly at all. I have two date fields, one sorts the other does not. The one that sorts is a user entered date (8/22/2012) which defaults to "9/9/9999" if the user does not enter a different value. The other date field which does not sort properly at all is a calculated date field which chooses the earlier of two possible dates (both associated with the same record) using
This code is repeated for each of the six fields with the relevant field name in place of 'property'.
Private Sub Property_Label_DblClick(Cancel As Integer) Me.OrderByOn = True If Me.OrderBy Like "Property" Then Me.OrderBy = "property" & " DESC" Else Me.OrderBy = "property" End If End Sub
