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 Replies


ADVERTISEMENT

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

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 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 5 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

Updating Default Field Values

Jun 29, 2005

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?

View 2 Replies View Related

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.

View 1 Replies View Related

Check For Values In Form Field To A Table

Mar 23, 2006

I'm certain this question has probably been asked before, but I can't seem to find it!

I have a form field called fldTitle, and want to ensure users write something that is meaningful by evaluating words within the title to a table of keywords (tblKeywords). I know how to write the IF/ENDIF and the other stuff required, but am struggling to find the right commands to do the comparision. Could someone help me out?

Many thanks.

View 6 Replies View Related

Forms :: Combobox In A Form To List Values Of A Field In A Table - Rowsource

Apr 14, 2014

So I have a combobox in a form that lists the values of a field in a table. The rowsource is this:

Code:
SELECT [Estes Training Modules].[Training Module], [Estes Training Modules].[Period] FROM [Estes Training Modules] ORDER BY [Period];

The problem is that ALL values on that field show up, including repeated values. I only need each different value to show up once. How can I do that?

For example, what I get in the combobox is: Annual, 2-Year, Annual, Annual, 2-Year, 3-Year,...

What I need is: Annual, 2-Year, 3-Year,...

View 10 Replies View Related

Forms :: Requerying Values In A Subform After Updating Combo Box In Main Form

Apr 21, 2015

Within my main form I have a combo box called "workgroup." Also in the main form is a subform called "sfrmSubmissionRecords," and within this subform is a combo box called "covermemo."

Each cover memo is assigned to a specific workgroup, so my intention is for each time a new workgroup is selected from the dropdown, the covermemo combo box in the subform becomes populated only with the covermemos associated with that workgroup. I'm almost finished except for the final step when I try to make the values regenerate...Access says that can't find the referenced form "sfrmSubmissionRecords"

This is the code that I'm using:

Private Sub cmbWorkgroup_AfterUpdate()
Forms![sfrmSubmissionRecords]![cmbCovermemo].Requery
End Sub

View 2 Replies View Related

Queries :: Rename Field Values With Values From Another Table

Mar 2, 2015

I have two tables.descriptions I'd like to relate and use to find/replace in bulk.

[Checking].[Description] (with the source data)
[Rename].[NewDescription] (with the correct data)

I'd like the values in [Checking].[Description] to be replaced with the values in [Rename].[NewDescription], including those that are "Like".

Examples:
[Checking].[Description] = Geico 12345
[Rename].[NewDescription] = Geico

[Checking].[Description] = Geic
[Rename].[NewDescription] = Geico

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

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

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

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

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







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