Updating Table Field Using Query Or Form?

Dec 23, 2014

I have a table holding clients data, I need it to work out the age of someone when an application is made, which I would like to be stored on said table. I have two fields [DOB] and [signed date], which I have used created a query with and an (unbooud?) field called age at application with the expression =DateDiff("yyyy",[DOB],[Date signed])

This works fine when I run the query, but I am unsure of the new next step of how to commit it to the table and even if that's possible.Ideally I would like this to run behind a form maybe using some click event after the [signed date] field has been entered.

View Replies


ADVERTISEMENT

Modules & VBA :: Updating A Field In A Form Based On Another Table Field

Sep 13, 2013

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).

View 1 Replies View Related

Updating A Field In Another Table With Query

Sep 5, 2012

I have a field in table A (used as my main form) that I would like to update with a unique yet understandable name. I have created a query that takes the first few characters of two other fields and the event date and concatenates them to a unique name.

Challenge is the three fields that create the unique name are in table B.

So I tried the following in query design just to see if I could update a field in the same table

Code:
Expr1: IIf(([EVENT_TBL].[EVENT]="Jacksonville" And [EVENT_TBL].[EVENT_DATE]="4/17/2011" And [EVENT_TBL].[EVENT_TYPE]="PRE-DEPLOYMENT"),([EVENT_TBL].[EVENT_TYPDTE_ID]="PREJACK170411"))

The results were #Error# in the cells.

View 14 Replies View Related

Updating A Field In A Table From A Query?

Jan 8, 2014

I have a number hat has been calculated in a query which I want to be kept in a field in a table.

View 4 Replies View Related

Updating Table Field Values From A Form

Dec 5, 2004

Greetings! This is my first post.

I have two tables: 1) tblClient, 2) tblCase.

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?

View 1 Replies View Related

Updating Table Field From A Form W/List Boxes

Oct 31, 2005

This is my first post, but I've been lurking for sometime. I'm grateful for all the great advice given here; despite my efforts, I can't find anything directly related to what I'm doing, though.

I have a form that is populated from a query. The query has some calculated fields and some direct selection fields from a couple of tables. One of the direcly selected fields is one that I'm trying to populate from the items in a list box.

On this form, there are two list boxes, List1 and List2. The user makes selections in List1 and clicks a command button, which runs code so that the second list box is populated with the items from List1. This was shown here:

MS Article (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office03022000.asp)

I actually just used this code and made changes accordingly so that this feature is working perfectly. However, the items in List2 need to be updated into a table's field, and this is where I'm having the problem.

I've got List2 bound to the proper field in the query, and I can manually run that query and make changes in that field fine. What I can't figure out is why I can't get the ItemsData property of the List2 control in there. For ease of code, I've added a line that copies the List2 rowsource variable to another variable so that the values can be used elsewhere. I can't seem to get the field to receive the variable in VBA, and I can't figure out how to get the values back into the query so that the query's source table is updated.

Any clues? Or is this unclear? I'm happy to give any further information. I've been working on this for more than a week, trying different things, and I'm at wits' end.

View 3 Replies View Related

Forms :: Updating Table With Calculated Field On A Form?

Apr 13, 2013

A textbox on a form concatenates 2 strings. I want to insert that resulting string into a table .how can i do that ?

View 1 Replies View Related

Updating A Table Field In A Form Using Public Function?

Nov 13, 2014

I have created a Public Function that would get a new Production Instructions number based off the [PI Number] of Tbl_Production_Instruction table.

I have a form that people will put in all information but the PI Number, then when ready they will click a button to update the PI Number. I place an unbound txtbox that will be hidden, with the control source to =NewPINum(), but when I tested the see if the unbound txtbox was populated with the new PI Number it was blank.can't figure out what I am doing wrong.

Code:
Public Function NewPINum() As String
Dim vNum As String
Dim strYYMM As String
Dim getnextPI As String
strYYMM = Format(Date, "yy") & "-" & Format(Date, "mm") & "-"
If strYYMM = Left(DMax("[PI Number]", "Tbl_Production_Instruction"), 6) Then
vNum = Right(DMax("[PI Number]", "Tbl_Production_Instruction"), 3) vNum = vNum + 1 getnextPI = Format(Date, "yy") & "-" & Format(Date, "mm") & "-" & Format(vNum, "000")Else
vNum = "001" getnextPI = Format(Date, "yy") & "-" & Format(Date, "mm") & "-" & Format(vNum, "000")
End If
End Function

View 4 Replies View Related

General :: Update Main Form Field Value After Updating Field Value In SubForm?

Feb 12, 2015

I have a main form and a subform.

Both forms have the field called JobID in common.

Both forms have a field called JobStatus.

Any easiest solution so that After I Update the field called JobStatus in the subform, it changes the field called JobStatus in the main form to the value which was selected from the subform?.

View 3 Replies View Related

Updating A Field In A Table

Jan 28, 2007

Dear all,

I have a simple database with few tables for which I created forms for the user to enter data.

I craeted in one ofthe forms a ombobox that reads from a table (list of suppliers fro example). I want the user to be able to select a supplier name and then this selected name will be fed to another table (order form for example). I did all requested work as I could understand biut the end results is that it writes only the corresoponding ID number (e.g. 3) of the supplier and the supplier name itself (text).

Am I doing something wrong or missing a code?

appreciate helping a newbie

regards

View 3 Replies View Related

Updating Field In Table

Jun 19, 2005

I have some data in a field with text type. I want to update some data in the field basing on a criteria by adding text to existing field by using update queries.For example suppose I have "rs" in the field . I want to add "D" to the field then it becomes "rsD". pl. tell me how to do this? or which functions should be used in the queries.

View 4 Replies View Related

Updating A Field Base On The Value Of Other Field In The Same Table

Jun 27, 2005

I have an access table call "Department" and two fields calls "DSec" and "DCode"
like this

DSec --------DCode
50 --------70.1587
60 ---------80.6987
80 ---------60.8521
70 ---------50.1512I like to make a query so that
DSec -------DCode
50 --------50.1512
60 --------60.8521
80 -------80.6987
70 -------70.1587Please Help
Thanks

View 14 Replies View Related

Updating Field In Table From Webpage Field

Sep 28, 2012

I have a Contact table and imported data into it from another Access Contact table. All but one field inserted correctly - the "Notes" field from the table I was importing FROM was inserted into the WebPage field of my current table. So now I am trying to update my Notes field in the Contact table from the WebPage field in the Contact table - or move the data over rather. I created an update query but when I try to run it it is asking for the Notes and WebPage parameters.

View 3 Replies View Related

Updating A Composite Key Field In A Table.

Oct 9, 2006

I will try to explain my problem.
I have a composite key in my table....(i.e. 2 primary keys), when I do an "update" querry through a form and try to update just one of the primary keys, it adds an entirely new record to the table with the updated primary key field.

What I want to do is to be able to update just one of the pirmary keys in the same record (i.e. without adding a new record)

Is this possible?? How?

View 2 Replies View Related

Combo Box Not Updating Table Field Properly

Aug 16, 2005

I have created a combo box with the two fields CompanyID and CompanyName. I used a query for my combo box and it looks at my company info table for the info. I want it to update the two same fields on my contact Table but it updates the company name field with the CompanyID and and the company ID field dosent update at all. I am not sure were I went wrong please help.

Rickilynn

View 3 Replies View Related

Queries :: Updating Other Field Values In Table

Apr 14, 2014

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.

View 2 Replies View Related

Tables :: Adding Field To Table And Updating Data?

Dec 1, 2013

I have a table in my DB called, devices. This contains all the information about various devices we have deployed in the field.

These devices are also contained in 2 other separate MySQL DB's.

What I need to do is add 2 additional field to my access table for the DeviceRecno and DeviceID of the same device from the MySQL DB's.

Adding the field is easy, but I cant think of a way to enter the recno and ID from the other DB's without typing them in manually for each one.

The common between them all is the serial number of the device, and I can get a list of serial numbers, recno's and ID in an excel sheet.

like a vlookup in excel to easily populate all the existing records with the recno and id's from the other db's?

When I created the access system there was no intention to link it to the other DB's for any reason, but that has now changed due to a lot of reasons.

View 4 Replies View Related

Queries :: Update Query (table To Table) Not Updating All Records

Nov 26, 2013

I'm using an UPDATE query to update records in one table (tblMain) from another table (tblTemp)

Here is my SQL :

Quote:

UPDATE [tblMain]
INNER JOIN [tblTemp] ON [tblMain].[MainField1] = [tblTemp].[TempField1]
SET [tblMain].[MainField2] = [tblTemp].[TempField2];

I only want to update the records in tblMain which have a corresponding record in tblTemp (linked by MainField1 / TempField1)

If any record doesn't appear in tblTemp, I want tblMain to retain the existing value for that record.

However, it appears that in such situations, the record in tblMain has it's MainField2 value set to null / ZLS.

I've tried using LEFT JOIN and RIGHT JOIN and also tried WHERE clauses but the result is the same every time.

View 3 Replies View Related

Forms :: Updating A Table Field Using Unbound Text Boxes

Mar 26, 2013

I have a form that contains a combo box (cboEmployeeName) that pulls data from a query and populates three text boxes (Work Area, Last Name, First Name), This part works fine. Because the text boxes are being populated by the Combo box, they are not bound to the record source tblTrainingSchedule). I need the info that is in the text boxes to populate the respective fields in the record source.

I tested by adding "=tblTrainingSchedule!WorkArea=[cboEmployeeName].Column(3)" (column 3 is the work area) to the "after update" control but it does not populate the data.

View 4 Replies View Related

Updating Unbound Field On Form

Nov 4, 2005

I have a form with a bound combo box. There is an unbound text box for display purposes only that is supposed to reflect the third column of the combo box for the current record.

I set it's default value at [cmbPartyType].[Column](2) .

It works for the first record. but when I scroll through the records, the box keeps the value that was appropriate for the first record. how can I get it to look at that combo box on every form? Even if I am not update records, just looking at them?

View 3 Replies View Related

Updating A Field On A Form-why This Works

May 29, 2007

hi,

I just want to understand why this works. I have some fields on a form that I'd like to let the user change. If I put something like "rs.update me.first, trim(me.first)" in the form's event procedure , "on update" why doesnt it like it?
I moved the same code to the field's on dirty event and it is ok. I don't understand why it doesn't let me update one field on the form's event. (Oh, my records can be selected by a drop-down or by navigating with the record selector.)

thanks a lot!

View 4 Replies View Related

Currency Field In Form - Updating Wrong

Aug 12, 2013

I have a question about entering in currency on a field in one of my forms in my DB.

Essentially the problem I'm having is this: Upon entering a dollar amount, for example say I want to add $350,621.

It shows up like this: $35,062,1

That happens because it is filling in left to right when I tab down to the field, but if it fills in right to left I'm sure it'll fill properly.

View 4 Replies View Related

Updating A Table Using A Form

May 14, 2005

How would I go about updating a table from a form? My table consists of employee contact information, ie.. Cell phone, pager number, home phone.
I have created a forum with unbound combo box called employee. As I choose the employee name all other fields are populating just as i want. After I update the form with the correct system contact information i want to be able to save the data and have it update the table. It appears the only data not updating the table is the anything in the combo boxes that are unbounded. Any suggestions?

View 3 Replies View Related

Updating Table Via Form

Jul 4, 2005

If you open the attached sample db you will see that I have a cascading combo box (It is working fine).

The data for the tables that are the source for the combo box are imported from an excel sheet.(This works fine)

Now my problem the data from excel is variable after the data is imported into access I want to add to it items from another table.

After the data is imported into both tblModel and TblParts I want to append to tblParts each item from tblFixed for every Model.

If you open tblPartsEXAMPLE you will see what I want to do (I added this manually).

I hope that my expanation is clear and someone can offer some advice.

Thank you in advance.

View 2 Replies View Related

Updating More Than 1 Table With One Form

Dec 30, 2004

alright I have a form and I need a couple of text boxes on there to add information to a different table than the other ones. I found this code somewhere on this forum can you tell me if I can use it for what I need, A more detailed explination of my project might be nessecary.

Code: sub AddRecord_Onclick docmd.setwarning false if not isnull(txtName1) then docmd.runsql "INSERT INTO table (areaname) VALUES('" & txtName1 & "');" txtName1.Value = Empty endif end sub

what is the proper syntex for the Insert INTO part, Table is obviously the table I want it inserted into, what is (area), is this the field I want it inserted into?

View 4 Replies View Related

Updating A Table Using A Form

Feb 7, 2007

I want to create a form that will update a table. I have two columns, an item number and a paid column. On the form I want to plug in the item number and then I want to hit ok and it will find that item number and put a value (for example "X") in the paid column.
I have tried running an update query when i hit the ok button but I can never get to work

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved