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 RepliesUsing 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).
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
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
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 RelatedI 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?
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.
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 RelatedGreetings! 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?
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
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 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.
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.
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 RelatedI 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.
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
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.
I have a form that has combo boxes and text fields (as well as sub forms). There is also a button linked to some code that says'
Private Sub cmdQuote_Click()
'Creates quote date and prints quote
Me.QuoteDate = Now()
Me.cbAgentID.Requery
DoCmd.OpenReport "Quote", acViewPreview, , "BookingID = " & Me.BookingID
End Sub
When the button is pressed the QuoteDate field (it is bound) should be be populated, but unfortunately it is not. I have played with refresh and requery but cannot derive a solution.
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?.
I have 2 tables, "Counselors" and "Appeals". There are several names in the Counselor table but it is not a fixed number of names (rows) usually around 7. There is also some variable number of records in the Appeals table, could be 0, could be 50, depending on the day.
I need to, in a round robin fashion, assign the counselor names one by one to the records in the Appeals table. How to loop through the 2 tables and update the name field in the Appeals table with the name in the Counselor table.
I can't seem to get my date field to update to today when I add information to another field.
My table has a lookup field, date field, text field, text field, text field.
I have set the date field to default to today for each new record.
I work with this table as a subform.
I would like to have the date field update to the date I go back to the record and update the first text field.
I have tried using after update, and an if - then statement, but nothing seems to work.
Here's a query that the bottom listview in the attached form i.e. a listview representing a table of calls(many) to fims (1 top listview)
Code:
SELECT calls.id, calls.firm_id, calls.called, calls.said, calls.spoke_to, calls.next
FROM calls
WHERE (((calls.firm_id)=[firms].[id]))
ORDER BY calls.called DESC , calls.next DESC;
When I run the thing...I get a dialog asking me for firm id.
I want to change this so when I move up and down the firms LV (top)... the bottom LV updates taking firm id from the top LV with focus.
Access 2003.
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.
I have a combo box (cboManifestNumber) that is based on the following table:
tblManifestData
ManifestDataIDPK (autonumber PK)
ManifestNumber
RemovedDate
ManifestComments
TsdfIDFK (FK frm tblTSDF)
This table is related to:
tblTSDF
TsdfIDPK (autonumber PK)
I need to be able to update tblManifestData with a new manifest number and manifest comments, along with assigning it a TSDF. how to be able to enter a new manifest number and the associated data without having it create two lines in tblManifestData. I thought that I could enter a new manifest number, then requery the table and form so it shows the complete list of manifest numbers (including the recently entered one) while staying on the newest entry.
I have an Access 2007 application that has a Parts Table that contains a list of automotive parts.I have attached a screen shot (parts.jpg) showing the structure.I have another table called Web_Parts that has exactly the same structure as Parts. The Web_Parts table gets its data from a CSV import that I do that is data extracted from an MySQL database used by an eCommerce website.
You will notice that there is a field called "Web_Product_Id" (number). This is the unique ID for each of the products that I have exported from the Web shop system. I need to regularly (probably every couple of weeks), export out of the web shop system and import into the Access environment.
Due to the fact that the data in the web shop system may change (pricing, description, add new items, delete new items etc), I need to find a way that I can simply update any existing records in the Parts table with any new information contained in the Web_Parts table......
I have two tables that are formatted identically....
Table1 = MasterTable
Table2 = TempTable
I am taking a copy of one of the entry from the master table and Copying it to the temp table.I then open a form on the TempTable that enables the user to modify the content without affecting the information in the MasterTable.On Completion and Save I wish to take this modified content and update the MasterTable Using the Field "ID"
the tables for example look like this
ID NAME AGE DATE1 DATE2
I am guessing I need to use a
Code:
DoCmd.RunSQL UPDATE "MasterTable" WHERE "TempTable"
Type of command but not to sure on the correct syntax and as everything is the same the use of wildcards for all fields