I have a split form with 10-15 fields. Each of these fields is a combo box. The trouble I am having is sorting the data in the datasheet of the split for. I want to sort ascending by "ProductTypeName", then ascending by "Parent SKU" and then ascending by "ProductSizeID".
How can I do this when each of my combo boxes have an ID and then a name? Example below:
I have a table [Employees] that has the field [FirstName] and [LastName]. On my form [Jobs], I have a combobox [ComboWho]. The combo box has all of the possible first names. When a first name is picked, it makes the last name fill in a textbox.
In the real version, it is a [LastName] is a lot of information, so it is kept in a memo field. I tried to use cascading combo boxes, but it cuts off my text.
I keep getting it to almost work... I've tried about 5 different methods... DLookUp, subform, etc.
Right now, I'm using this code:
Private Sub ComboWho_Change() Dim location As String Me.ComboWho.SetFocus location = DLookup("LastName", "Employee", "FirstName = '" & Me.ComboWho.SelText & "'") Me.MyTextBox.SetFocus Me.MyTextBox.Text = location End Sub
It has properly filled in the textbox. I thought it was working perfectly, but ran into one snag... We have been using a split form... and it won't let me filter that column (Column ComboWho). I think because it is saving everything as numbers instead of text.
ComboWhoDatabase.accdbI've attached a copy of the simplified database.
I've got a module which loads the data into a table, but it can't handle the records that are ~ delimited, so spits them out as a single field.
I know there is some VBA code that can be used to split comma delimited records, and I've seen bits of it floating around online and tried to alter it to work for the ~, but I get the feeling that what I've seen is only a part of the required code, how to put it all together to make something that actually works.
So, what I need to do is; - Split some records in a table out into multiple fields where there is a ~ present - Place these newly split fields into a table (I don't mind if it ends up in a new table or not)
There must be an easy answer to this! I have searched the forums but have not found what I need to make this work the way I want it to. I have a three part reference field. The records in the form are supposed to be sorted into ascending order Field1 first, then Field2 and finally Field3. The forms RecordSource is a query that brings through the relevant fields from my table. I have the query sorted according to these three fields so that every time I open the database it sorts the way I want it to. What I want to do is have a button that will sort the records into the correct order when the buttion is clicked. I am sure this can be done, but cannot get it to work on more than one field at a time! Any help/ideas would be greatly appreciatted.
Is it possible to sort a continuous form by two fields?
ie. If two records have the same value in one field, look at the second field to see which one is displayed first.
I was thinking that it might help to combine the two text boxes into one (a not visible text box) and sort it by that, but I don't know how to sort by an unbound, not visible text box either...
I am using the following by Allen Browne to sort one field. But now I need to sort on two fields.
Code: Function SortForm(frm As Form, ByVal sOrderBy As String) As Boolean On Error GoTo Err_SortForm 'Provided by Allen Browne 'Purpose: Set a form's OrderBy to the string. Reverse if already set. 'Return: True if success. 'Usage: Command button above a column in a continuous form: ' Call SortForm(Me, "MyField")
I have a tabbed form. The main form is titles ContractDtlsFRM. There are 3 other subforms in separate tabs. The first field in the ContractDtlsFRM is Contract No. I would like the form to sort in ascending order by this number.
I tried entering the following code in the Forms Order By event but it didn't work
Private Sub Form_Open(Cancel As Integer) Me.OrderByOn = True Me.OrderBy = [Contract No] End Sub
How to correct the issue below. I created a split form and I have 2 combo boxes that allow multiple selections. The one combo box for LOB (line of business) works perfect and does not create duplicate records in the datasheet view of the split form. The 2nd combo box with multiple selections creates duplicate records in the datasheet depending on how many selections are made.
I have checked this in the underlying table and there are no duplicate records, it is only in the split form datasheet. I have checked settings and configuration between the 2 combo boxes that are reacting differently and they appear to be identical...
I am attempting to split out a descriptive field so that each words which may exist in that particular field will be split out into seperate fields ... for example, if the violation field is populated with a four worded description of 'Assualt on Police Officer' I would like to somehow strip out the words so that 'Assualt' and 'on' and 'Police' and 'Officer' are all in seperate fields. I am not sure if this can be done using a simple query in access ... if anyone can offer any suggestions, I would greatly appreciate it.
The database I am working on, I split a while ago to give it some security. Now i'm updating a related form, and i'm finding that if I delete and add fields in the BE, the FE fields (being the fields that I need to insert into the form so the data entered propogates to the DB) are not updated.
I would like to split the contents of one field (in a table) into two or more. How can I do this? e.g. 1 Coca-Cola Australia becomes 1 (in one field) and Coca-Cola Australia (in another field)
How do I know when I should split a db into multiple tables? Right now- everything is in one table. Is there a rule of thumb or method to determine when I should split it up?
For example- MY DB is running queries on properties for sale. Would I split all the Active, Sold, Expired, Off Market listings into separate tables, since I will be frequently queriing based off of the status of the property? ie- I'll be running avg prices of homes that are in each of these status. If that's the case, how do I know what relationship to give it/how to set it up (use the MLS # as the primary key in each, or do I need a foreign key or something else?)
Is there a general rule of thumb, or guide to use when designing a db?
A quick question... is it possible to sort a table by a memo field? I have a table and am able to sort by other, non-memo fields, however when I place my cursor in the memo fielld I would like to sort, the sorting option becomes 'greyed-out'. Is there a way around this?
The file is huge with many sections (eg Names, Address) in it. What I need to do is load it into Access for some analysis.Is there a way I can split the file one loading to the system into multiple tables?
I have an access form that is used for cash-ups. The cash-ups are not done on a regular basis and the owner requested that the database look at the last cash-up date, current cash-up date and calculate the difference in days and then calculate the average take per day by dividing the total take for the period by the number of days from the last cash-up date to the current cash-up date. This was simple and is done however, what he now wants is to export this data to another table and have it split the total amount per record by the average number of days and reflect the average amount per day over as many lines.
Eg: R5,000.00 / 20 days = R250.00 per day.
This data needs to be displayed in TWENTY lines each with a value of R250.00.
I need help with an access 2000 multiple queries problem.
I have the following:
Table 1 = info below / table 2 = computer types / table 3 = offices 1 through 10
field 1 = name (20 employees) field 2 = computer (5 different type of computers) field 3 = office (10 different offices) field 4 = issued field 5 = not issued field 6 = stolen
On a form I have the 2 combo boxes (computer and office) to select and check boxes to select issued or not issued or stolen.
I need a report printed based on the multiple criteria query only showing the results that I ask for. For e.g. in office 5, computer IBM, issued. Print Report: lists names of all in office 5, with IBM’s, issued only! etc.
I have a field in an Access 2003 table that has several image names in it separated by "; " (semi colon and space). The thing is, I need to split them up into their own fields..My table name is "ONE BIG TABLE" (will be exporting data from one table for CSV).My starting field name is "ALT_IMG".An example of the contents of a record within "ALT_IMG" is
Code: /AAG70260G05_2_1.JPG; AAG70260G05_3_1.JPG; AAG70260G05_5_1.JPG; AAG70260G05_6_1.JPG; AAG70260G05_7_1.JPG; AAG70260G05_8_1.JPG; AAG70260G05_4_1.JPG What I need is to split these up into their own fields. I can create new fields to populate, I just need to get them in the fields and to remove them from the original ALT_IMG field after moved. There can be as many as 0 or 1 to 20 images in the ALT_IMG field.
Ultimately I was thinking about making new fields named "ALT_IMG_2", "ALT_IMG_3" (up tp 20) and then making an update query of sort to anything more than 1 image to the next field. Meaning if there are two images, then the first stays where it is and the second is moved to ALT_IMG_2. If there are three then the first stays where it is, the second goes to ALT_IMG_2 and the third goes to ALT_IMG_3.Now I know that there are benefits of having multiple tables but I need this to end up in the same table.
I was working on a database several months ago and I came across this forum while troubleshooting. I had to abandon the incomplete project for some time now, but I am back and ready to get this thing up and running!
My database used to be comprised of lookup tables and other things for each of the fields in my main table, but I have recently scratched that idea for simply using list boxes.
My main problem, I believe, is with my form. I am trying to have two combo boxes, a beginning date and end date, and a keyword text box, all used as criteria in a query.
Seems pretty simple, and I actually got the keyword text box to work, but the combo boxes and between dates critiria return all records.
Can someone double-check my work? I'm sure it's just a simple error, but I will attach a stripped-down database for anyone to look through.
Thanks ahead of time for anyones help, it is greatly appreciated!
I am new to MS Access. I am creating a small database for motor vehicle clearance. I have a SEARCH form where i want to put the fields for searching query table:
VEHICLE MAKE:
REF No.:
BEGIN DATE: END DATE:
I need it in such a way that even when only one of the above fields is supplied with data it should still return correct data from the query table.
For dates (BEGIN DATE, END DATE) i tried: Between [forms]![search]![begin date] and [forms]![search]![end date]
This worked well when placed alone of the SEARCH form. But when i added more fields (VEHICLE MAKE, REF No.) the query returned nothing but a blank query table.
By means of a multiple value checklist the Field: "Problem" can be selected (marked).The multiple value field information is stored in the table beginsituation in the form Beginsituation. This stored (selected fields only) information I want to be fetched from the table beginsituation and shown in the multiple value checklist in another form called Progress. The user can than change the selection via the multiple value checklist if the user wants this and this information will be stored in the table: "Progress". However I could not get the in the previous form (Beginsituation) selected values in the multiple value checklist.
I had a list of 1800 full addresses including Postcode on excel, I added 2 columns for Co-ordinates then one column that I use to input data when a call needs removing. I then imported to access table and started to use queries to sort our sales into territories of hopefully no more than 26 calls each, I used the spare column to input a R for removed then only list null entries in queries then I keep adding postcodes in the postcode criteria column until I get around 26 per Query.
My Ultimate goal is to Build multiple Tables of no more than 26 calls that I can geocode onto maps, to make it easier and more cost effective for our sales team.
My Problems are. How do I Prevent Duplicates when entering addresses if new address are entered, I Currently have access creating its own KeyI need to be able to track & prevent duplicate postcodes being assigned to more than one query/Territory table.I need either Access to either Produce a Table Report (As in excel) or might be easier if I just import my data back to excel table.
I kind of think I need new tables for Maps with relationships created between Map table and Territory table. but not sure if this is the best way to go or not.
