update two fields based on criteria from one


Hello All. I have written (rather sloppy) code to update two other fields on my form based on a selection from the first field. Example is below:

If Engineer_Name = "Mike" Then
Email_Address = "mike@nowhere.com"
Primary_Responsibility = "Hardware"

The thing is, I have tables already created with specific email addresses (the same applies to his responsibility) and I want to automatically insert his email address in one field and his responsibility in another field, based on the selection of his name. I'm still at a VERY BASIC understanding of coding at this point, and this code actually works, but I'd like to be able to write code that would refer to the actual table values and insert them into the fields. Could anyone help me figure out how to do this? I've seen a similar subject in this discussion, but to be honest, I haven't covered the me!xxxx chapter and I don't know what that is =]

Thanks in advance!


Sponsored Links:



Greetings,

Searched on Update Query, but could not find the answer, so here I am . I am using Access 97.

Question 1: I'm working on a fincance and accounting (F&A) database at work. In this instance, I'm interested in PARTIALLY changing the values of data in one field based on criteria from another field.

I'm working on automatically correcting erroneous data. All fields are text fields. In this case, two fields are involved; EOE (Element of Expense) and APC (Account Processing Code). Both fields have a length of 4.

I need an update query that will CHANGE the last two characters of the APC field to the text characters OA only if the EOE = 4140.

EOE APC
4140 ZADA
4140 ZEDA
4140 ZFEN
4140 ZHDA
4140 ZHDA
4140 ZUDA
4140 ZUDA
4140 ZUDA
4140 ZUDA
4140 ZVDA
4140 ZYDA
4140 ZZDA

In this example, the last two characters of the APC must be OA.


Question 2: Working with the same table, but different fields.

These F&A records have one line spread across several fields, and it's called the Total Card.
Once the F&A records are cleaned up, they are uploaded into another database for further processing.
The fields are called MANHRS (length 7) and BK4 (length 4). Both are text fields.

In the below example, I need to delete the last 4 alphanumeric characters in the MANHRS field, leaving only
the first 3 alphanumeric characters in place. In the BK4 field, I need to totally delete anything in that particular record. It appears that the BK4 field is totally null except for this Total Card line...hence my filter criteria in a query would be BK4 Is Not Null.

EXAMPLE:
Current Data:
MANHRS BK4
79C5570 K1

Desired Result:
MANHRS BK4
79C

I hope I've explained myself. I've tried using this in an Update query. Glad I backed up the table .
UPDATE t_DIX_Bak SET t_DIX_Bak.APC = IIf("EOE"="4140","??OA","APC")
WHERE (((t_DIX_Bak.EOE)="4140"));

Regards,

Bob in Indy




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.

Is that possible?




HI all,

I need to update a field based on the number of records within a date period. For example if the total number of record is more than 75, the filed Cost should be updated with a value (e.g. 75 Dollar) from the 76th Record onwards. Hope the requirement is explained well.

Thanks




I've been searching forums for hours and trying different variations of functions to no avail...

I'm trying to sort a query on two fields based on a parameter selection from another query. These two fields change based on this parameter. I tried manipulating the Order By SQL statement using the Choose function (based on the "index" number returned by qryRange.Division). It looked something like this:

Order By Choose([qryRange.Division], [Element.Program, Element.Brand], [Element.Program, Element.Brand], [Element.Program, Element.Brand], [Element.Department, Element.Program], [Element.Program, Element.Brand], [Element.Program, Element.Brand], [Element.Department, Element.Program], [Element.Program, Element.Brand], [Element.Program, Element.Brand]);

where indexes 4 and 7 return a sort using Department/Program and the remaining indexes return a sort using Program/Brand.

When attempting to run the query I received an error message stating that the query in which I added the choose statement is invalid due to a character (or something to that effect). I futzed around with the parantheses and brackets and received other errors having to do with commas, etc... Just couldn't get it to work.

I then attempted to nest two Iif functions after the Order By statement. Translated into English it said, if qryRange.Division = 4 or 7 then [Element.Department, Element.Program], otherwise [Element.Program, Element.Brand]. This also did not work.

Please help. Let me know if you need to see any data/SQL.




Hi All,

I'm kinda stuck on something. I'm trying to make fields either visible or invisible based on criteria chosen in a form.

I have a table with five fields, call "Sign Selection"
Field 1 is called "Window banners"
Field 2 is called "Theme pennants"
Field 3 is called "Exterior banner"
Field 4 is called "Entrance card"
Field 5 is called "Pole sign"
All fields are a 'yes/no' type

I have a report that will generate a list of retail stores from a query called "Store attributes" which shows how many of each sign element each store should receive.

However, when distributing the signs, sometime I'm only shipping two out of the five types. I know that I can go into the design of the report and uncheck certain fields so they don't show up, but I need to make this more user friendly. I'd like to control this with check boxes on a form, so my user will open the form, check off the appropriat types of signs. Then run the report that will show only those sign types.

Any ideas?
Let me know if my question needs more clarity.

Tim




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.


	Code:
	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 am seeking some assistance (obviously ) with updating a field based on the results of a combo box. Currently, I am getting the value of my combo box using the following source:

SELECT [qryUnit/SiteISSOs].ISSOName FROM [qryUnit/SiteISSOs] WHERE ((([qryUnit/SiteISSOs].[Unit/Site])=forms!frmIncidents.cmbUnit)) GROUP BY [qryUnit/SiteISSOs].ISSOName;

What I want to do is now is automatically fill in two additional fields (ISSOPhone and ISSOFax) based on the same query but my mind just can't seem to wrap itself around the issue .

Any help would be greatly appreciated.




Hi all

Apologies for my first post being a question, but I wonder if someone would be willing to help me. I am very new to access and need to create a query that returns results, based on criteria selected by the user. The database is for a restaurant and I am looking to get information about who sold what based on categories.

There are 5 tables - 1 that contains the information that needs to be queried and the other 4 contain the criteria needed to return the results.

Criteria Tables:

kassi - User table: Contains fields kanr (key field) and Name
artgrp2 - Main group table: Contains fields artgrp2 (key field) and maingroupname
artgrp - Sub-Group table: Contains fields artgrp (key field), artgrp2 and subgroupname
artikel - Menu Items: Contains artnr (key field), itemname and artgrp

Information Table:

kasspro - Purchase History table: Contains ID (key field), kanr, artgrp, itemname, numbersold

I need to return information from the kasspro table, but I need it to prompt for the following criteria:

Username (Table: kassi, Field: name)
Main Group (Table: artgrp2, Field: name)
Sub Group (Table: artgrp, Field: name) Also needs the possibility for ALL

And then return all records/fields from the kasspro table that meet the criteria.

I haven't the faintest idea about where to start unfortunately, so I would be very much appreciative of any assistance.

Thanks




Hi all,

I have a form with two main fields.

These fields are used to store dates for the purposes of identifying the time frame of all the records that are in a box.

However, in a small few circumstances I need to be able to store PIN numbers to identify records that are in a box.

So, basically I need a form that will only show two fields based on what is clicked in a combobox.

In other words, instead of having four fields - 2 for date and two for numbers (both are mutually exclusive and aren't required on the form at the same time) or creating another form for the rare instances where I need to store numbers instead of dates - I would like the fields to change from date fields to number fields when a certain item in the combobox is clicked.

So let's say I have dozens of categories like payroll, correspondence, claims, invoices, etc ----when I click invoices I want the default date fields to change to number fields instead.

Is that clear as muddy water?

Thanks in advance my mentors!




Hello,

I have a query called SData2. One of the fields is a part number which originally comes from a form called frmSData which has a part number list box that links to a table called PNumbers.

I'm trying to create a new query so that, depending on the part number, a field called PartPrice appears with the appropriate price for that part number which is also located in the PNumbers table.

So how do I get a query to automatically update the PartPrice field based on the PartPrice Field from the PNumbers table that matches the part number that is found in the SData2 query?

If you're not as confused as I am by now, please help.




Hi all,

I am new to both Access and VBA, so here goes...

I have an MS Graph Chart in a report that I would like to update based on input from a form. The form has several checkboxes. The user clicks on them to select fields to be used in a SELECT statement for a Make Table query.

I would like to base the data shown in the Graph on the results of this Make Table query after the report loads.

For example, the "HN" Checkbox....if it is checked, I would for the chart to graph one set of data, and if unchecked, graph another

The code for the report loading event is as follows:


	Code:
	Public Sub Report_Load()

If Forms("ControlForm").Controls("HN").Value = -1 Then
Me.Graph4.RowSourceType = "TABLE/QUERY"
Me.Graph4.RowSource = "TRANSFORM Sum([HN]) AS [SumOfHN] SELECT (Format([Date],'DDDDD')) FROM " _
& "[Persons] GROUP BY (Int([Date])),(Format([Date],'DDDDD')) PIVOT [PersonID];"
Me.Graph4.Requery
Else
Me.Graph4.RowSourceType = "TABLE/QUERY"
Me.Graph4.RowSource = "TRANSFORM Sum([VMI]) AS [SumOfVMI] SELECT (Format([Date],'DDDDD')) FROM " _
& "[Persons] GROUP BY (Int([Date])),(Format([Date],'DDDDD')) PIVOT [PersonID];"
Me.Graph4.Requery
End If

End Sub

I inserted the chart and the built the Chart ("Chart4") on the Report using the Design Wizard for one instance of the above IF statement.

However, if I run it so as the satisfy the other condition, the chart does not change, still only displaying it as I initially built it. I must be doing something wrong here, I assume I should not built the chart using the Design Wizard, but of course, I could be completely on the wrong track.

Thanks!




I am trying to update a field to say "Yes" or "No" based on whether it is more than thirty days out from the value in another field. I am having trouble doing this for some reason...totally stuck!

Please help :-)




hi guys

Question: I use a combo box to display names and addresses of companies (list is 5 columns wide). The combo box updates a field on the after update event. I want this combo to update more than one field after the update event ie name and address. How do you get a combo box two update two fields from multiple columns in the dropdown list?????????????????

Hope you can help

Cheers
Paul




Okay... So, I'm pretty comfortable with using the After Update and On Change events for a combo box to populate unbound fields on a form.

The problem I'm now having is that when one of the unbound fields (unbEmployeeNo) on the form (that gets updated via the After Update or On Change events) has a value, I'm not quite sure how to get another unbound field (unbPosition1) further down on the form (and not part of the combo box SQL statement record source) to populate. I have a query that I'd like to use to bring data in to this field (with criteria based on the form name and EmployeeNo) so that where the [Forms]![frmNewHireLegalNameVerification]![unbEmployeeNo] criteria for the (unbPosition1) query is satisfied the unbPosition1 field on the form populates.

I don't know if any of this makes any sense...and am more than willing to clarify for anyone out there with ideas as to how I might go about this. Thanks much...




I work for a small Adult Literacy program. Next week, I would like to create an application (hopefuly with Access) that can match Tutors to Students based on similar criteria.

There will be two similar tables, one for students and one for tutors. They will have identical fields, such as Name, Phone, Day(s), Time(s), Location(s) and Availability.

I would like for the software to be able to pair Student (records) with Tutor (records) that have similar criteria, namely days, times and locations.

Ideally, once a student and tutor are paired together, their records will not show up in future results. However, I would like to retain the record in the event that the pair should ever break and the tutor becomes available again.

Does this sound like something that Access is capable of doing? Can I get some direction on how to get started?


Thanks,
Jon




I do not know VBA syntax or how to properly set up programming using VBA code in MS Access.

I would like to automate changes to two records in the database depending on the current date. The situation is that I have two records for one person (each with a different primary auto number field). Each of the two records has a different address for mailing purposes. There is a winter residence and a summer residence. When I prepare a mailing list, I archive the inactive address, depending on the current date, and the active address only is included on the mailing list.

So I would like the VBA code to do the following:

;;REM summer address
If membershipID=12345 ((if date > 04/30/2012 and date < 12/01/2012) set Archived=false, else set Archived=true)

;;REM winter address
If membershipID=67890 ((if date > 04/30/2012 and date < 12/01/2012) set Archived=true, else set Archived=false)


Can the date be used with MMDD only without YYYY so that the code would not have to be edited each year?

Also, how do I enter the VBA code into the database.
--------------------------------------------------------------
NOTE: I already have some unrelated code in this database. The code was written for me with instructions for its insertion, a few years ago. At this point, I don't remember the steps taken to enter the code into the database. But in this case, the code is linked to a form and only activates if I add or edit a record.

It is shown as a Microsoft Office Access Class Objects/Form frmMembership.
It inserts the current date into the AddDate field when a new record is created. The EditDate field has the current date inserted when there is an edit to an existing record.
this is the unrelated code that already exists in this database:

Option Compare Database

Option Explicit

Private Sub AddDate_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub EditDate_AfterUpdate()

End Sub

Private Sub EditDate_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.Dirty Then
Me![EditDate] = Date
End If

End Sub

------------------------------------------------------------------------------------------------


However, in both cases, I manually have to post something in a record for the VBA codes to activate.
What I want now, if for the two records to automatically update the Archived field whenever the database is opened -- that is, I don't want to have to remember to do anything to the two records based on the date. I want VBA code to have the database do this automatically whenever the database is opened.

Thanks.

Allan




I have the following tables & fields within the tables…
Burnings = BurningID(PrimaryID), Part No, Description, Cost
Raw Steel = RawSteelID(PrimaryID), Description, Cost
Labor = LaborID(PrimaryID), Description, Cost Per Minute

…Now I need to create additional parts for our inventory that have one or more Burnings from the "Burnings" Table, some Raw Steel from the "Raw Steel" Table, and Labor from the "Labor" Table all put together for form a new part on a new Table. Obviously I want to draw from my original tables so that I only have to update the price one place. I need my new (combination) parts to be on a table with their costs also so that I can use them for my inventory, etc.

However, when I set up a Relationship and create lookup fields between the above tables and my New Table (COMBINATIONS), I have to use my PrimaryID fields for the Lookup field since my “Cost” fields may not always be unique. My problem is – I need to calculate a new cost for my “Combination” part based on the cost of my original parts – by adding together the cost of the burning(s), raw steel, and labor minutes(s). When I create a calculated field based on my Lookup field – it multiples my Qty by the PrimaryID instead of by the cost. I think I am probably missing something – but I am not sure how to go about it.

I can come up with the new costs by creating Subforms on my Entry Form for the Combination table. But if I do it that way, I don’t know how to link my calculated field from my form back to a field on my Combinations table.




Hi all,

I am endeavouring to filter a form based on the position of an individual.
I would like the users to be able to select their position and then any record where there is a match in the "Primary Responsibility" field or the "Secondary Responsibility " field will be displayed.

I have an underlying query that is populated by a combo box on an unbound form.

This has worked when generating reports for individuals but I cannot get the required result when using the form.

Thanking anyone in advance who can assist me with this.

Regards,

Rod




Hi,

I am building a database that calculates freight costs for shipments.

I have one table that has order data with fields such as [Order Number], [Origin], [Destination], [Service Level], [Weight].

The service level is one of 4 values: PRE 9, PRE 12, NEXT DAY, NEXT DAY +1.

The origin and destination use country codes such as BE, AT, CZ, PL for Belgium, Austria, Czech, and Poland respectively.

I also have rate sheets from carriers such as DHL and UPS and they provide their rates in the following format (numbers are made-up as real rates are confidential): [Origin], [Destination], [Service Level], [0 to 0.5 kg], [0.5 to 1.0kg], [1.0 to 1.5kg], [1.5 to 2.0kg], etc all the way up to 30kg. The rate is then in the appropriate column and the row gives the origin-destination-service level combination.

(sample freight rate table uploaded as zipped excel sheet in attachment)

I need to return the rate from the appropriate row based on the [Origin], [Destination] and [Service Level] fields (easy enough), which is easy enough.

The hard part is selecting the correct field to return. I tried usign a Choose expression but Access rejected it saying it was too complex (maybe because too many choices?).

So I'm stuck. And working towards a deadline... :-)

Can anyone help please?

Many thanks,
Martin.