I now want to update one field (what I type into it is irrelevant) but if this field is updated, it means quite a few others become Not Applicable and I would like that to be populated once I updated this one field. After Update is what I am looking for.I know it is simple (but will mean entering each field name) but it would save me truckloads of time.
View RepliesHi Folks,
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.
Thank you,
Joe
I'm mid build on a commitments tracking (pseudo Purchase Order system) project, but seem to have run into an issue with a loop I'm building to allow users to edit commitments. The idea is that an edit form is launched, values amended and the submit button is clicked. A VB subroutine then validates the entries to ensure that the mandatory entries are included. It then writes a copy of the original values to the Archive table, before attempting to update the existing commitment with the new values..
This is working perfectly, apart from the fact that 3 fields that are "updated" are being updated to a blank value..
Code:
Private Sub CommitSubmit_Click()
Dim SQLStr, LastID, DOwner, DHeading As String
Dim ErrState, Dtype, DProperty, DTCA, DITD, DSD, DED, DSP, DRetention, DRA, DRPD, DSupplier, DDOW, DStatus, DUser, DShD As String
Dim Authcheck, Complete, ErrMsg As Boolean
Dim QDF1, QDF2 As QueryDef
[code]...
I am new to access i have a problem which is i have made a form which contains a subform and a read only subreport, what i want is the ability to select a record in read only subreport as in the picture attached and make a button that when i press on it, it should copy the values of the itemsID field, Packing field, ContainerNo field and origin field from the selected record and then paste them in the subform below.
Also i want to add more then one item, so the when i press on another record it should paste the values below the first record.
Parent form/table, with a subform (separate table, 1:M link on "ID") The parent form data stays fairly constant (occasional change/update), with multiple entries in the subform for each parent record which are added to more frequently The master table has a field "last interaction" which I need to update whenever a new entry is made in the subform/subtable It updates fine when I change an entry in the parent table/form, but not when I add a new subentry for that parent
View 12 Replies View RelatedI'm running into an issue with my forms. I have a form that contains a subform, that contains a list of projects. This list is read-only. To update a record, you can double click on it, which opens another form filtered on this record, and which contains the fields in the first form as well as some others.
I have a command button on that last form to "Save and close", which fires up a macro that saves the record and closes the window.
Now, the issue that I'm having is that, when the window closes and I'm once again in front of the first form with the list of projects, the values of the record I changed are not updated and when I click on another record, Access tells me there's a conflict in the values and asks me if I want to keep the changes, discard them or copy to the clipboard.
It seems to consider the old values from the first form as a change, and thus asks me which to keep between these, and the actual (proper) changes I made in the dedicated form.
For the proper changes to be applied, I need to select "Drop changes".
I tried to requery the first form, also undo the changes to it after closing the window, but none of that worked.
I'm actually basing this on one of the templates, which does exactly what I want and which obviously works...
how can I get dates to show in last updated and last viewed fields..I did get last updated working but for some reason when I tried to do last viewed, last updated vanished :/
I did try writing code (but im a novice lol, I think that's why last updated vanished).I have got 2 unbound fields called HiddenLastViewed and HiddenLastUpdated thought I might need them.
We use Access to pull data from data warehouse and this year they upgraded the new system on the back end and moved old data to the new one. Problem is that in some tables having big fields such as Asset Long Description, Work Long Description and when moved to the new database system, somehow there are certain some XML tags such as <br> </br, etc.. added into the description. So when we pull the data onto the table in Access, is there a way to remove those tags out of the descriptions fields? a macro or module?
View 1 Replies View RelatedI have 7 combos, which for the most part set the value in another field - however, there are two values in the combo that do not have a value attached - one is AH, the other is SK. I have two textboxes (one for AH, one for SK) that I wish to have a count (preferably running) of how many times each of these values appears in the seven combo boxes.
View 5 Replies View RelatedI have a simple Select Query based on one table.
In SQL View, the query is:
Code:
SELECT SYSADM_CUSTOMER_ORDER.ID, SYSADM_CUSTOMER_ORDER.STATUS
FROM SYSADM_CUSTOMER_ORDER
WHERE (((SYSADM_CUSTOMER_ORDER.ID) Like 'Q%') AND ((SYSADM_CUSTOMER_ORDER.STATUS)="H"));
So basically getting all records in the CUSTOMER_ORDER table that have ID beginning with Q and the STATUS is H (on hold).
I want to simply update these to change the STATUS to C (closed).
I converted the SELECT Query to an UPDATE Query and added a "C" in the Update To Field.
The SQL View is now:
Code:
UPDATE SYSADM_CUSTOMER_ORDER SET SYSADM_CUSTOMER_ORDER.STATUS = "C"
WHERE (((SYSADM_CUSTOMER_ORDER.ID) Like 'Q%') AND ((SYSADM_CUSTOMER_ORDER.STATUS)="H"));
But for some reason Access is telling me that it will update 0 records. There are over 2500 records to update.
Hello,
I have a database that has 5 linked excel files. From there I have a form that runs many different queries. I have a macro that updates the queries by running them and closing them. Occasionally I update the excel files and was wondering if there is a way to show which records were added to the linked excel files. In other words, I need to display the new records that were updated. Does anyone have any solutions? Thank you for your help in advance.
I'm working on a form with almost a hundred various fields (it's what my employer needs). He wants to be able to create new records with much of the same information as the old ones by clicking on a button that will copy the information to a new record, then clear certain specific fields (or some process that will safely create the same result). I don't need specifics on how to write the same line of code for each and every field I wish to clear.
Say I have a form with 5 fields: part_number, part_owner, procedure, file_name, and date.
How could I copy the record to keep the fields part_owner and procedure the same, but clear part_number, file_name, and date?
All of the fields are from the same table (no, it's not normalized...).
I have created a database with other to log my clubs sports score by player etc. what I want to do is to create and email form that I can email out so other club captains can send me their scores and info to add to my database.
I do not want to use excel, I just want to know if this is possible using out look to send the email.
There are multiple tables that will need updating. Via the email form. Basicaly the main database entry form i need to create a query or vba code to allow all the fields in that form to be updated via email.
I have a continuous subform recording the information of various wireless products for an employee embedded in an employee main form. Please see screen shot enclosed.
There is a check box "BYOD/Personal" on the subform. I want to hide a number fields when this check box on independent record is checked. However, if I use the following codes, the changes apply to all records under that employee.
Private Sub BYOD/Personal_AfterUpdate()
If BYOD/Personal.Value = True then
Me.Provider.Enabled = False
Me.XXX.Enabled = False....etc.
Else
Me.Provider.Enabled = True
Me.XXX.Enabled = True....etc.
End If
End Sub
How can I just disable those fields on the subform of a specific record when the BYOD/Personal field for that record is checked?
I am in the of designing an access 2010 database for data entry. Is it possible to create a button on a form in which a prompt asks a user for which records to export. Then depending on the entry export specific fields (First Name, Last Name, DOB) to a specific excel 2010 workbook (setup) and worksheet (template)?
For example, if the user entry is 1, only record 1: First Name, Last Name, DOB is exported to the setup.xlsx (more specifically the template worksheet of the setup.xlsx). However if 2,3,4 are entered then records,2,3,4: First Name, Last Name, DOB are exported to the setup.xlsx (more specifically the template worksheet of the setup.xlsx).
I have done this in Excel before, but not Access. I do not know VBA. I figure this will have to be done in a query or a macro. I don't even know if all of this is possible in Access. I need to be able to split an Address field into:
Street Number
Street Name
Street Type
Street Direction
And the purpose of this is so that I can pull out the Street Type (Drive, Road, Lane, etc) and update the abbreviations (DR, RD, LN) to the Street Type spelled completely out.
I did this in Excel by creating a Named Range "Types" on a sheet that has the abbreviations in Column A and the spelled out versions in Column B so that I could convert the abbreviations to complete street types. I broke down the entire address into each part on another sheet. Then I did a VLOOKUP to look up the Street Type in Column B in the Types range. Works great! And the only way I knew at the time to do that.
But, by doing this, I have to get the data I need from a download into a spreadsheet, break the address fields down on a second sheet, do all kinds of field update conversions (to get the field names from the download to match the field names in my Access table). This takes extensive Excel programming. I just thought there might be a way to do it by simply importing the data from the download straight into Access. That is easily done, but the Street Types aren't consistent.
There may even be a better way to do this than splitting....something like if a field CONTAINS DR update it to Drive. This would be a long process to set up because there are so many different street types to consider.
This is basically for the purpose of finding duplicates. If there is one entry called 123 Main St and another called 123 Main Street, they are not going to show up as duplicates, rather as two separate records.
I have a main form and a subform. The main form displays the information as combo box = column.2 (in other words it shows the actual word not recorded y/n checkbox = obviously shows which is checked
However the subform shows
combobox = 1 or 2 etc the recordid
y/n check box 1 for yes 2 for no
How do I get the subform to display the words instead of numbers? (can live with the Y/N)
have basically the same issue with a split form (combo only) shows words and datasheet view shows corresponding record id number
tried using =[coffeetype].[column.2] on both the subform and split form datasheet but doesn't work.
Is it possible to convert a number (whether in the "Standard" or "Currency" format) into words ?
View 5 Replies View Relatedi have a combobox which i want to use on a touch screen using touch buttons in ms access 2010
i set the combobox ime sentence mode to phrase predict, allow autcorrect to yes, row source type to value list and typed some words that i want in the row source eg. "kitchen"
i've set the on click event of my buttons a,b,c,d..... as following Me.Combo56 = Me.Combo56 & "A" for button a and so on.
the problem is that when i press the first letter of the word lets say "k" for the "kitchen" on my onscreen keypad the combo can not predict the word.
I am having some difficulty putting togather a small database (attached).
Basically I want to be able to search for mutiple words and get the results
There are 3 main columns in the main table
1) Shop
2) Part Number
3) Part Type
Problem 1:I have created a lookup table (with check boxes) for ComboBoxes in a form but I do not know how to connect it so that when I click on the comobo box it gives me the all different (Shop numbers/part number/Part types) to select from.
Problem 2:Once I have made my selection I want to search and run query to show me the results. I know that once multiple items are selected in a combo box they are separated by commas/space, so the search must take that in account.
Other nice to have
1) If Shop 1 is selected then only the parts which were sold from shop 1 to appear in the Part number Box & visavera & same goes for Part type.
2) Ability to do a wildcard search so if I Enter "A" in part number I can see all the resealts
I'm working on a form that when opened has a pop up filter that asks you to enter "1, 2, 3, 4 or 5". These numbers have to be equal to an actual word within the form. For example, on the form we have a box called "business unit". One of the options in "business unit" is "Human Resources".
I want to set 1 = to Human Resources so that way in the filter box all a person has to type is 1, and all of HR's records will pop up. The reason for this is we are trying to eliminate typing as much as possible.
how to Add/Update record including existence of record based on single unique field.Now I trying to learn how to check existence of record on multiple field before adding (at least two) and how to update the record with multiple field record
Below is my testing table fields
Year Month Working Days
2013 Jan 20
2013 Feb 17
2013 Mar 22
Now two situations adding a new record and updating the existing record Which means the unique fields are (Year + Month)
I would like to update the Previous Education tab based on values on the Courses tab.The aim of this is to keep a clients previous education upto date in order to append it to a CV etc.
View 4 Replies View RelatedIn this table I have two fields Seats Available and Seats Remaining
When I register a student to a Course in the Courses table I would like the corresponding Seats Remaining field for that course to decrease automatically.
I can, of course, run a report or a query to see how many are enrolled in the course and then manually update the Seats Remaining field (which is what I currently do), but I would like this process to be automated each time I register a new student.
GOAL - I would like to have one text box where I enter a sentence
txtKeywords: Access Is For Smart People Smarter Than I
I would like to create a button when clicked moves txtKeywords into another textbox [txtKeywordscombined] field and removes the spaces between the words and adds hyphens
txtKeywordscombined: Access-Is-For-Smart-People-Smarter-Than-I
Would I use the 'Replace' command in even procedures to do this? Another way? Example?
I'm building a customer form using a list box of summary customer info on the left side of the form (CustId and Names) and customer details on the right hand side. I want the details shown on the right to be those for the customer selected in the list box on the left. I'm having problems getting the details to update when a different record in the list box is selected or clicked.
I originally set the data source for the detail records to the base customer table but couldn't work out how to update them when the list box was clicked!
I then tried creating a query with the select clause conditional on the list box (CustId = Me.qryContactDetails) and set the data control source for the detail record to the query (=[qryContactDetails]![FirstName]). The query works as expected but the field in the form just shows '#Name?'.
I tried adding a field requery on the List31_Click() event using Me.[FieldName].ReQuery but that didn't seem to change anything