I am creating a form to add records to my database. I have created a primary key that is not autonumber. When adding a new record, I want the form to put a primary key default value of the next number in sequence. How do I do this?
View RepliesHi, I have 7 tables in my database and 1 form corresponding to all the fields in these tables (linked by a query by recordsource). My problem is that the form will only show a record if ALL 7 tables have manually had the primary key entered (not good when i have information that needs to be added at different times). How can I make it such that if I create a new record on the form that all primary key fields will be updated, and this record will be present every time i open my the form??
Cheers
Tania :o
I have a database with 200.000 records, how I Add Primary Key Field.
I'm getting error "File sharing count exceeded..", tried to increase "MaxLocksPerFile" registry , but without success.
I have a simple database that I inherited, that contains basic customer contact info. We want to start adding more functionality to the database, so I've done alot of research to learn about how to begin normalizing my data.The existing table does not contain a useful primary key, since we may have multiple individuals from the same company, companies with very similar names, etc.
I would like to add an AutoNumbered field to use as a CustomerID/primary key, but I can't seem to find a way to do so. Here is what I've tried so far:
I have tried to add a field to the table, and make it an AutoNumber data type. When I do so, I can error message saying that I cannot make a field AutoNumber if any other field in the table already has data entered in it.I have tried to create a new table containing only an AutoNumber PrimaryID field, and then import data from the existing table, but that just creates a 3rd table.
I need to build the following query:
I have 2 tables:
tblBusiness
tblInspection
tblShops have an ID, an adress, bussines name, owner name, etc
every Shop can be inspected many times. A inspection can be futile if the store is closed. If the store is open, the inspection is made, and as result with can have “with irregularities” “without irregularities”. Two or more inspections can be made the same day (if the n – 1 time the store was close).
So, here is the basic select:
SELECT tblBusiness.idBusiness, tblBusiness.BussinessName, tblInspection.InspectionDate, tblnspeccion.InspeccionTime, tblInspection.Open, tblInspection.Irregularity
FROM tblBusiness INNER JOIN tblInspection ON tblBusiness.idBusiness = tblInspection.idBusiness;
What I need is to get the LAST inspection where the bussines was open and with irregularies. One inspection per Business, but of all business. Ah, and if there ir a newer inspection but the business was close, it doesn´t count.
I tried “Group by MAX” with InspectionDate, but it just didn´t work. For example, it showed me two inspection of the same business, not “the last” of that bussiness (it was easy to filter the Open field and the Irregularity field).
I suspect that what I need to solve this is to use two or more SQL stament, two in one., something like that. But I´m in a hurry and I just can seem to get it right.
I hope you can help me with this. Thanks for reading anyway.
P.S.: the fact that there can be more than one inspection per day can be a bitch =(
but there is a "InspectionTime" that can save that". Mmm the problem is that one inspection can be made 1 july around 20:00, but the seccond on 2 july 01:00. But we store the two inspections in 1 july... so... that mens problem.
But that issue is not so important as the previous problem up there (well, in order to solve the seccond I need to solve the firts...)
PS2: I don´t know about the title...
I have an access form which includes my primary key field "ID". Normally when I use this form to enter a new record this field displays "(New)" until I enter data in any other field, then the ID field updates to reflect the new value. This worked fine when the table I was adding the record to was a table linked from another access database.
I recently moved my data to SQL Server 2008, without making any other changes to the application, and this behavior has changed. Now when I enter data into a new record the ID field changes to "NULL". And I am not able to reference this value in my program (Me.ID) like I used to be able to. When I save the record the ID field gets assigned and everything works as normal.
So I am able to make my application work again by forcing the record to save before trying to reference Me.ID, but why the behavior changed when my table moved from Access to SQL Server.
Hi
How can I make the primary key (participant #) in my table equal the record number on the Form? In other words, when I open the form, I need two things to always happen:
1. the primary key must equal the record number (which is displayed on the form)&
2. when I click "Add New Record" button, I want a new customer # automatically generated and in the correct order.
Is this possible? I've been struggling...
I got this function which is supposed to find a record based on the primary key, and then give three fields a value if they should be empty.
Public Function DoesXrefExist(faPersonID As Long, faRoleCode As String, faDorpID As Long, faDorpCode As String)
If (DLookup("PersonID", "PEOPLE", "PersonID =" & faPersonID) > 0) Then
End Function
Thats as far as i have got...for the next part.I get the feeling that i need to use the recordset object that exists somewhere.How do i access that recordset object? I have not been using any DAO or ADO, just straight VBA. I know i can create recordsets and stuff using DAO/ADO, but there must be a recordset that has been created already by the Dlookup command...
I have an existing database with multiple tables. Two tables have the same primary key Cab#.
1. When user clicks on the form on the menu form, I want to request the Cab# value from the user.
2. Then I want to get the record from the first table CabData based on the entered Cab#.
3. Then I want to create a new record for the second table Rides with five fields from CabData table inserted into the Rides record.
4. Then I want to display the new Rides record to the user for updating.
5. Then I want to save the new record to the RIDES table.
What is the ACCESS termonolgy to do each step? Are there any special details I should watch for?
I want to change misspelled records, but the misspells are in the primary key of my table. By changing the values of primary key fields, can I break table relationships? I'm not changing field names, just field values of a primary key field.
View 5 Replies View RelatedI have a database in Excel that contains farmers who can be identified by a Unique identifier: MZ-01-0001. The registration details for these farmers are not always up to date. Now when I try to append the data for the farmer with MZ-01-0001; with the primary key set on the Unique identifier, Access does not want to do it since there is already a record in the system. When I remove the primary key it doubles the record, which is also not what I want. So I am wondering what I need to do to append the record while keeping the primary key intact.
View 4 Replies View RelatedIs it possible to open a form to add a child record related to the highlighted record in the subform?
View 2 Replies View RelatedI want my primary key to be
First 3 letters of surname + first 3 letters of forename + DD + YY (Date of birth)
I can use the after update event to update the primary key field but it won't get saved for some reason so have I missed something?
I want to recall a record by it's primary key in a form with a command button
I mean write a primary key in a textbox and press the button then it shows the record.
How to find the max value of a field in a query... How does a query display the max record based on the primary key... want to isolate the last record created, the one with the max primary key.
View 2 Replies View RelatedI've designed a DB in access which has a BackEnd and 2 FrontEnds (one person insert all the records and the others just keep inserting infos till the process is finished.The DB has 12 tables and we used it for about 6 months without having any trouble but recently (2 weeks ago) i've add 3 new tables and then related them to one table that already exist.
The DB was running smoothly for a week after the changes but last monday (09/15) the "Record is deleted" appeared. I've compacted and repaired an the following errors descriptions appeared:
ErrorCode: -1017
ErrorDescription: Record is deleted.
ErrorTable: tblFatura
ErrorCode: -1053
ErrorDescription: Index or primary key cannot contain a Null value.
ErrorTable: tblFatura
ErrorCode: -1630
ErrorDescription: You cannot add or change a record because a related record is required in table 'TblExpense'.
ErrorTable: tblFatura
I've restored the file via IT using the Backup2 days before the error occurred but after 30 minutes the same error appeared! I dont know if it is related to the new tables that i have add or no?
I have a form that has a FIND button. I want the user to enter the 3 fields that make up the primary key, then find the associated record. How can I do this in Access - not VBA?
View 1 Replies View RelatedI am building a database to capture monthly statistics on a number of items. I want to ensure that users don't enter statistics for the same item for the same reporting period. I found the following instruction, but can't make it work:
It suggests that I create multiple primary keys in the table
When I do it, it comes back with an error: Index or primary key cannot contain a null value.
I have an Access 2010-form which inserts a record in a MS SQL 2008-database by using an ADODB-connection. I need to retrieve the primary key of the newly added record.
With code at the bottom I create a SQL-string which is stored in variable strSQL. If I execute the SQL-string directly in a MS SQL 2008 Query Window, the record is inserted and the MS SQL 2008-result pane shows a column "ID" with the primary key of the newly added record.
If I execute same SQL-string in MS Access 2010 the record is inserted. However, the code fails on Debug.Print rs![ID] with error "Item cannot be found in the collection corresponding to the requested name or ordinal". Same error appears if I use Debug.Print rs.Fields(0). I've enable the reference "Microsoft ActiveX Data Objects 2.8 Library". It looks as the recordset rs is closed as soon the command "Set rs = cn.Execute(strSQL)" finished.
Why I can't retrieve the Primary Key with VBA-code below?
Code:
Private Sub cmdSave_Click()
dim cn As ADODB.Connection
dim rs As ADODB.Recordset
set cn=New ADODB.Connection
[Code] .....
My problem is that I am trying to update a field (called 'Sold' which is a yes/no checkbox column) for a specific record whenever an event is triggered. I have two forms (derived from two tables), one is called frmInventory and the other is called frmSales. In frmSales, I made a combo box called 'cboItemID' that allows the user to select from a list of items from my inventory table. Each selection from the list has 4 properties, the first of which is the 'Item ID' from the inventory table. Lastly, I have a field in both frmSales and frmInventory called 'Sold' as mentioned above. What I want to do is that whenever I check/uncheck the box in the 'Sold' field in frmSales, I want the 'Sold' field in frmInventory to check/uncheck as well, but only in the record with an 'Item ID' that matches the 'Item ID' from the combo box selection. In other words, I want to match the 'Sold' field in frmInventory with the 'Sold' field in frmSales, but for only the record that has the same 'Item ID' primary key as the one I picked from my selection in the combo box from frmSales.
how to reference another table and check whether or not it's 'Item ID' primary key is identical to the one I specified from the combo box, and then take action to update the 'Sold' field if the IDs match.
I have a continuous form that shows all the available lease numbers for a client. Each lease number has a monthly charge associated with it and has multiple cars associated with it. For example a client may have lease number 1, 2.5, and 3. Lease 1 may have 3 cars, lease 2 10 cars and lease 3 1 car.
I created a continuous form that shows each individual lease number for a client and the monthly charge. The form is based on a totals query where I use a count to get the total number of cars for the lease number. That all works fine and when i open the form it shows me all the lease numbers for the active client and the monthly charge like the table below.
Lease number
Monthly Charge
Total cars in lease
total value of lease
value left on lease
1
100
3
100x3=300
2.5
200
10
200x3=600
3
250
1
250x3=750
I am trying to do =Sum([CRevFromCustomer]*[CountOfLeaseNumber]) but it gives me the same value for all lease numbers. It gives me the total value of all the lease numbers for that client instead of showing me the total value each individual lease number. table below shows what I mean where 1650 is the total from the 3 different lease numbers and revenue added together.
Lease number
Monthly Charge
Total cars in lease
total value of lease
value left on lease
1
100
3
1650
2.5
200
10
1650
3
250
1
1650
After a lot of reading and consolidating VBA codes for audit trail.How My Audit Trail Works..A module was made for a function named as "Changes" .Then inserted into before update event of a form where I will do the editing of the records.Then I made a table named as Audit.Inside this table I made all the fields I needed such as:
*AuditRecordId[autonumbered]
*FormName[The name of the form for editing]
*Index[The record ID of the record being edited]
*ControlName[The Field being edited]
*DateChanged[Date change was done]
*TimeChanged[Time change was done]
*PriorInfo[for the old value of data being changed]
*NewInfo[For the new value of data changed]
*CurrentUser[The user base on log in form that was set to Global into another module]
*Reason[The reason for changing for future references]
And Here is the Function Code:
Code:
Option Compare Database
Option Explicit
Function Changes()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim strCtl As String
Dim strReason As String
[code]....
This audit trail function is valid only for one(1) form, due to the limitation of
Code:
Screen.ActiveForm.Controls("SUP ID").Value
where "SUP ID" is the primary key of the record being updated/Change, so if there are Five(5) tables that needs audit trail, there will be also Five(5) forms, as well as Five(5) Function Changes namely; Changes(), Changes1(),Changes2(),etc... because all the table do have their own sets of primary Key.
Is there a shortcut, in such a way that the "rs!Index" will automatically return a value, equivalent to the Primary Key/Record Id of the record being updated/change, given that there are different updating forms for each table to be updated?
Hi,
I've a quick question. In Access, I have a form that allows user to add new record into a table. Is there anyway of finding whether the new record has successfully been inserted or added?
So i think it may be wise to have a message to notify us of whether it's inserted successfully or not.
Hope this explain clear
Thank you very much in advance for your help
Can someone please tell me why I might be adding a record everytime I open my database. I have to forms that work from querys if I open the main form all is well , if I open the other oone first and then the main form I find a record has been added. Most annoying! :(
View 1 Replies View RelatedThis may seem basic but I need some help.
I have a table of vacinations and need to add a new record every time an animal is vacinated. I have created a form with all the fields in the table in it. But when I open it it takes me to the first record. I have had to add a button to the form to add a new record. This button then takes me to a blank form where i can input data. This is very messy and not very user friendly. Any suggestions on how i can tidy this up would be appreciated greatly.
I have looked on here for hours and cannot find anything, perhaps someone can help.
I have just converted from 97 to 2003 and now I cannot add new records to my forms. I have checked that the allowAddtions property is set to Yes but the New record icon and menu option is grayed out. I have looked through all the form settings and I can't see what it might be.