How To Clear Values In Specific Fields Based On Updating Value Of Another Field
Feb 27, 2015
I am working on form where the user selects either "IN" or "OUT" from a dropdown of field name "CheckOut" in Frm1.
If they select "OUT" they will in turn need to fill in 2 additional fields. When they change the value back from "OUT" to "IN",
I want those other fields to be cleared of data for just this record so next time they change back to "OUT" from "IN" those 2 additional fields are already blank.
I have a Form named 'Opening_Screen' which consists of various objects - Combo Boxes, RadioButtons, Check Boxes which the user can select.There is a subform called 'Report' within the 'Opening_Screen'. The source object of the subform is a query (called 'QueryX').
There is a button called 'Generate Report' on the form which when clicked - an SQL must run on the backend, update the query which in turn should update the Subform 'Report'.For me, the QueryX gets updated, but the subform doesn't. When I manually switch the form to Design View and then back to Form View - the subform gets updated. I tried the same through the vba code instead, but it doesn't update the subform.
Hello everybody. I am new to Access and as such I don’t know how to perform the following. Please help me out.
The project is about a library loan system. I have a database consisting of the following tables: member (MemCode, LName, …), book (BkCode, Title, TotalCopies,…), purchase (PurDate, BkCode, CopiesBought,…) and transaction (MemCode, BkCode, RentalDate,ReturnDate,Returned).
TotalCopies is the total no. of copies there are of the book CopiesBought is the number of copies that have been purchased RentalDate is the date on which book is being loaned ReturnDate is the last date on which the book has to be returned Returned is a Yes/No field to indicate if the book has been returned
In the library a member can take only one book at a time.
Problem 1: The library may have several copies of a particular book, but all will have the same code. For example, a book, let’s say Harry Potter 6 has code 97. Initially only 1 copy of the book is purchased. When the librarian sees the book has great demand, he decides to purchase 3 more copies. All the copies will have the same code, i.e., 97. Only the TotalCopies will become 4.
BkCode is Autonumber in book table and Number in Purchase table. Relationship is 1:many.
I have a Purchase form to record details of books being purchased. I have a command button “Book form” that opens the book form when the user clicks on it. He will use it if the book being purchased is not already available in the library. What I want is:
(i)If he opens the book form to fill in details of the book, then when he closes the form I want the book details to automatically appear in the corresponding fields on the Purchase form. (ii)The CopiesBought will have to be used to update the TotalCopies in the book table. How should I do this?
Problem 2: When the user wants to record a loan, the system will need to check if that member already has a book which he has not yet returned. If it is so, then the system will need to provide a prompt to inform the user of this and consequently blocks the user from completing the transaction. How do I do this?
I have a field called density which needs to be updated to show either 10, 20, 30, 40, 50,60, 70 or 80 depending on a number of variables, for example: If market location is 'hot' and unit type is 'house' and discounted is 'no' then show '10' in the density box.
or perhaps:
If market location is 'cold' and unit type is 'apartment' and discounted is 'no' then show '20' in the density box.
I have tried all sorts of expressions and queries but have really reached the limit of my know how and can't solve it. Is it even possible to do this in Access?
So I currently have a table that tracks medication activity for patients called tblMedRanges. The Fields are the ID, StartMedication, EndMedication, Med ID/Medication Name.
In the End of Medication, some patients are not currently active so they have a set date/time field property. What I have for right now is the default value is set to
Code: =date()
So that when staff adds a new medication it is set to the current date. These patients are supposed to be active, but it doesn't update the default value. I want it to continue to update to the current date every time the database is open.
I have three options that I know of(or think I know of) to update but can't commit to one in particular :
1)I want these individual records in the field to update the current time for today and was considering using some kind of data macro code using before update.
2)The other idea I had was splitting up the field into an EndOfMedication field that would display a value only if their inactive, and an Active field (text, value list active/inactive choices) that would be used in a query to generate the current date as med field. This would take some crafty query work with comparisons later on to generate list of drugs that they were on for each visit.
3) set to text field and convert the field to date field for comparison in queries.
I am creating a small database to house results of certain tests. Is there a function in access that allows me to add fields if a result is out of the specification required. In other words I still need to record the out of spec results but I also need more fields to appear for the next lot of results to be entered, for example.
When cooking a batch of product I need to test the pH at the 30 minute mark if the pH is too low I will continue cooking the product for another 30 minutes and then test the product again. The cook time is not a constant so I never know how many samples are required, therefore I don't know how many fields i would need in advanced.
I have a Table with 57 fields. I would like to display this table in a form as a subform, but only certain fields depending on what selection is made in a combo box.
For instance, if the user selects "Missing Information" in the Combo Box, then the form will show a few standard fields such as ID, Market, Sales Manager, and then some specific ones such as date missing information requested and date missing information received.
If the user selects another option, again the standard fields will remain plus a few different ones.
I have done much searching on this and feel like I am so close but so far. I have looked into controlling the record source of the subform, columhidden =false and a multitude of others. All of which may or in fact probably do work in this situation but I can't seem to put it all together.
Using Access 2010. Fairly new to automation and macros.I have two tables (tblProductReceived and tblBins) and a form (frmProductReceived).A field in tblBins corresponds to a location the bin is at (named BinLocationID) and the tblProductReceived table tracks product that a specific bin has received.
What I need is for the tblProductReceived field PRLocationID ([tblProductReceived].[PRLocationID]) to be automatically populated with where the bin is at ([tblBins].[BinLocationID]) when selecting a specific bin in the form (frmProductReceived).
Every quarter I run a report that pulls loans that meet specific criteria.I export this report into excel (the loans fall into column A)I add a file number and box number in columns B and C.I import the excel spreadsheet to table 2 (they're linked so I don't need to import, it's automatic)
Now that I have the updated information back into the database (table 2), how can I get this information back into table 1? The excel spreadsheet only contains a few loans that need to be updated in table 1. I have tried creating an update query with both tables linked and use the "update to" field. However, when I tried to run the query, it says I have 0 records updated.
My update query is as follows:
Field: Access Bar Code Table: Table 1 Update To: [Table 2].[Access Bar Code]
Field: Access Box Number Table: Table 1 Update To: [Table 2].[Access Box Number]
Basically I'm trying to have the query update specific fields in table 1 based on the information from table 2.
I have 100 records in a table with a field that does not have a default value specified. If I specify a default value is there an easy way to have all the records updated with the new value without writing an update query?
Client records from tblClient contain a field called Client_CID (Primary Key), as the Client ID. There are also fields Client_HIGH_FILE_NO, a numeric value of the last case number assigned to the specific client and Client_PREFIX that contain a unique three letter prefix that identifies the client.
Case records from tblCase contain a field called Case_CFN (Primary Key), as the Case File Number. tblCase also contains a field called Client_CID that contains the Client ID associated with the case (obtained from a combo box lookup from tblClient).
My form is frmCase bound to tblCase.
The Case_CFN is constructed by combining the value of the selected Client_CID’s Client_PREFIX with the value of Client_HIGH_FILE_NO plus 1.
I am constructing the Case_CFN on the before update event of the combo box for selecting the client. The resulting Case_CFN may appear as follows
ABC10001
Where Client_PREFIX = “ABC” and Client_HIGH_FILE_NO = 10000
Now, I need to increment Client_HIGH_FILE_NO in tblClient by 1, meaning I need to set the value of Client_HIGH_FILE_NO for the selected Client_CID to, in this example, to 10001.
Questions:
1) Is anyone familiar with this type of number scheme generally and if so any ideas?
2) Can anyone tell me how to update the value Client_HIGH_FILE_NO for the selected Client_CID?
I have a query that takes a value, Proposalvalue, and depending on the currency, loc curr, it calculates the currency. It gets the currency value from the currencies table and appends to TableB
eg. proposalvalue currency 50000 1
Currencies id Value 1 0.6587
This creates the conversionvalue = 32935 in the TableB.
I have a form that can viewedit the data in TableB.
Using this form, I want to be able to change the proposalvalue and for it to automatically update the conversionvalue.
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.
Is there a way in a query (using SQL) to go to the first record to update a field based on a criteria ? For example if my amount field shows two amounts for $300.00 I want to go to the first record that has $300.00 to update a field in that record.
I've put together a database for my company to track our archived files. When the files are archived, there are three important values pertaining to their location: a list number, a box number, and a barcode number. Each box has a barcode on it, so what I'd like to do is make it so that when any record has both a list and box number entered (since it is dependent on the list number to know which, for example, "Box 1" I'm entering information for), it would automatically insert the appropriate barcode number in the field. It would obviously need a seed for each barcode number to be entered before it would know which one to use, but that could either be based on the first unique entry, or entered another way. If anyone can help me do this, I'd really appreciate it. Also, if my description was insufficient or confusing, just let me know and I'll try to clear it up. Thanks!
Example: If I entered: List # Box # Barcode # 77 4 It might not know what the barcode is the first time. But once I had entered List # Box # Barcode # 77 4 366582 It would know what I was entering. Keeping in mind that I'm not showing the rest of the record, but just this end part, my next entry would look like: List # Box # Barcode # 77 4 366582 77 4 366582 ...the red representing that it would automatically fill that in, because it knows that if I'm talking about Box 4 in List 77, that must be the barcode.
I have a search form that's slightly modified search form modeled after the one available here on this site. It's a main form with a list box. I use the listbox to narrow down the results I want from the search function.
The search details are shown in a sub form that contains sub-sub forms
As seen in the code below, I am using the listbox to set a filter on the subform . Works famously until I search for something that does not exist in the database. I then receive runtime error 3075.
I believe it's related to the way I'm showing the details in the subform (by enabling a filter). If there is no record in the database that match the search criteria, I don't want any error messages or pop-ups and preferably would like the listbox to be blank.
Attached a screenshot of my form. red = main form green = subform blue = sub-subforms tan = obscuring the search results
Code: Private Sub SearchFor_Change() Dim vSearchString As String vSearchString = SearchFor.Tex SrchText.Value = vSearchString Me.SearchResults.Requery
I'm trying to update an imported table from an excel spreadsheet with missing details. The table's records are in order so I just need to fill in a blank field with data based on the previous one as shown.
ID Name Location 1 Bob London 2 Larry 3 Harry 4 Jerry Glasgow 5 Paul 6 John Southampton
I need to fill in the location blanks simply with the last location details, so records 2 and 3 with London and 5 with Glasgow. Is this easily done or would I need to pull all the data into an array and work on it there?
I've tried searching for an answer but haven't had any luck.
I have a question I've been trying to figure out with no luck. I have 2 fields, companyname and zip, these 2 fields may have duplicate values or they may be uniquei.e.
companyname zip billybobs 68135 dilberts 68137 billybobs 68135
what I need to do is delete all of the duplicate records where both companyname and zip match. Is there a way I can do that with a query?
Hello: Is there (simple) way that I can populate a field based on the values of two other fields? For example, in a list of new employees, if a new employee starts on Nov 30 and works in the Administration Department, his human resources contact is Mary. If a new employee starts on Nov 30 and works in Sales, his contact would be Fred. I hope this is clear! Many thanks!
I would like the "DateOfConfirmation" to populate with today's date when "SSurvDiagThisYear" is Confirmed.
The DateRecordCreated is a simple =Date() that populates when the record is entered into the table.
SSurvDiagThis Year is the case outcome - Pending, Probable, or Confirmed. It is possible that the case could be confirmed on the same day it was entered into the table but that is RARE.
I am hoping for the The DateOfConfirmation to capture the date the case is confirmed so that I can gather some duration between the case being opened to confirmed.
My database has a text field "Status" where the text is either, A, W, C. There is a form to update this field; currently it uses a standard Access created text field. The users want a checkbox which will show up as checked when the status is W, if the status is anything else, the checkbox will be blank. If the user clicks the checkbox within the form the status will be changed to W.
If [table.status] = 'W' then checkbox = 0 else checkbox = -1
I have a table setup to enter dates for each person that requires safety quizes for the month those that don't have an "N/A" for the month by thier names.
Each year I have to start over and it is a real pain to manualy delete all the dates for each person for Jan through December. I only want the dates gone not the name or the N/A values.
Is there a sub or query that will search each record and only delete the dates?
I have a lengthy CASE statement in my database that displays specific text in a field based on the value of another. Simple stuff but for some reason it randomly will not work on certain values, and never the same one twice. Is there a commonly known cause for this? I have verified that the spelling and spacing etc. are correct in my code so that shouldn't be causing the problem.
I have a report that I would like to have the rows be a specific color based on the value in 1 field on the row.
I have attempted to use conditional formatting but it will not work (IE, I can get font color to change, but not field boxes to have color).
The field is labeled [text144] based on the value in the field (1,2, or 3) I would like the field to be a different color. i.e. 3 = green 2 = yellow 1 = red
