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?
Hi
I have a table Orders with fields InvoiceNo And Invoice Series.
I want if of course anyone can help me to Validate the next Invoice Number Not To Be Dublicate and to automatically Increment. Meaning that if Last Invoice number was 45 and Invoice Series is A the next to be A 46. If Last Invoice without Series is 40 the next to be 41, and so on. I can manage to increment the values in one field Invoice number but I can not incoporate the Invoice Series.
Any help would appreciated.
Note : I'm not good in VB so please be kind to me.
Kind regards
John
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).
I need some help with developing a certain aspect of a db. I need to track the equipment list of clinics. The equipment list doesn't really change (it has 5 possible values though each clinic may only have one to all 5 of them). The db is also to track who the manufacturer is.
I was originally going to do a three piece composite key consistenting of the clinic id, the equipment id and the manufacturer id with quantity as the non pk field. Or the other possiblility that I can see is just using an autonumber as the pk with clinic id, equipment id, and manufacturer id as fk's with qty as the non-key field. So what do you think? If you can put in explanation why your suggestion would work, that would be appreciated as I'd like to increase my knowledge base on this stuff.
If I haven't made my problem clear, I'm quite willing to provide more info.
Thanks,
scratch
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 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 RelatedI 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 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 two tables linked to each other in one to many relationship. Instead of auto number, the date and shift (Text) is being used as the primary keys (Composite Primary Key). Here is the tables structures,
Payouts Table:
Date: Primary Key
Shift (Day or Night) : Primary Key
Bills Table:
Date: Primary Key
Shift (Day or Night): Primary Key
Autonumber: Primary Key
The tables Payouts and Bills has one to many relationship. One payout row can have many bills. The problem is that I want to start the Autonumber in bills table everyday from 1. As date and shift are different for every day so even if i start bills from 1 everyday, it wont make same primary key. I can do it manually but I want to make it automatically.
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.
hello
how can i create compostie keys in access???
also how would validation rule for a text box which would allow a user to enter a date which between a certain range in relation to today's date. eg the date entered cannot be more than one week greater than the current date. i knew who i could do it using VBA but i want to know how to do to using the regular access features.
thanks you :)
Hi, I am in a great hurry and I need someone to help me as soon as possible. Basically, I have a table called Column, which contains 4 fields:
Column (autonumber, primary key)
Column Description (text)
Rack (number)
Rack Description (text)
There are around 30 columns, and each column has 3 racks. There are different racks for different columns, so the rack description will be different. That is why Rack and Rack Description must be in the same table as column.
The problem is that I cannot make the table have one column with three different racks. I thought perhaps the solution would be to make a composite key, where with Column and Rack, we would be able to identify everything else in the table. But the problem is that I do not know how to create this composite key. Please help me with this. I really need to get this done as soon as possible.
Thanx in advance
16593
I have attached the above relationship diagram as I am having a few problems with a table that uses composite keys to link to 2 other tables. The table is called subsmag and contains the fields subid and magid to link to the subscriptions (subid) and magazine (magid) tables. Basically 1 subscription can have many magazines and I am using the subsmag table to link the 2 tables. When I try to enter data in a form it doesn't like the composite key. Can anyone help, I have inherited the database design and I'm thinking it needs a redesign.
thanks