Resetting Seed Identities
Mar 5, 2006
I have a quick question. I have a table that has a field with a seed identity with an increment of 1. Right now I have records in the table ranging from 1965001 - 1988704. Every so often the oldest records are archived off to another database. However, I have just been informed that the records from this table can not exceed 2000000 in the seed identity field. Ideally, I would like to just go into the table design view and change the seed identity to 1000001 and leave all of the existing records as they are, knowing that it will be years before it causes an issue. I've tried this in the lab and it didn't work, the seed identity issued was always 1 higher than the highest record in the table.
My plan B is to export all of the records out of the table, change the seed identity, and append the records issuing a new identity. The problem with this is that the seed identity is referenced in other tables, which would have to updated.
The easiest solution for me would be to be able to just change the seed identity and start issuing new seed identities, leaving the existing records alone.
I am no Access expert so please forgive me if my approach is way off.
View Replies
ADVERTISEMENT
May 3, 2006
Hi there gurus...
I have made a form with various text-boxes, radio-buttons and other controls on it. The form is not bound to a table or query: I'm just using the form as a data-gathering device for my VBA code. After the user has entered the data I want to use a button to reset the boxes and controls to the way they were when the form was openened, ie as though the user closed the form down and re-opened it again. I know I can do this by resetting the .value of each control back to its default, but there's about 30 controls to reset! Is there not something like "forms!main.reset" that just resets all the controls a form back to their default values, clears out text boxes, etc etc as though the user has closed & reopened the form?
Ta
John
View 12 Replies
View Related
Oct 11, 2005
here the scenario:
i have a page displaying all the contents of my table with the first field a autonumber as a button on the page.
when i click the button, (Ex. number 15) it brings me to another page where i can delete the clicked entry.
after deleting, i go back to the viewing page, because i deleted number 15, it now shows, 1,2,-----14,16,--- so on so forth.
how can i reset the autonumber so that when i go back to the viewing page the numbers are in order again?
hope u got my point. thanks for any help u can give.
View 3 Replies
View Related
Sep 15, 2005
could someone please tell how i can reset an autonumber field to start from 1.
basically i have created a table and carried out a number of test with useless data. now that i have the table set up the way i want it and i have tested the queries etc i want to delete all the current data in the table and begin filling it with correct data. however i want my primary id to start from 1 which at the moment it doesn't because i have just deleted 50 records.
thanks in advance
View 2 Replies
View Related
Jan 4, 2007
I cleared a table of records and need to restart (at one) the autonumber field. I used the instructions found on on-line help but they will not reset the field back to one. Any one have a solution. I have even deleted the field and created a new one with the same name and it started with the same value it would hav used before I deleted any records.
View 3 Replies
View Related
Oct 17, 2006
Trying to calculate a YTD running sum by Month where the cumulative amount resets the next year as below.
AYearAmonthSumOfFreightRunTot FDate
19951$2,798.59$2,798.59Jan
19952$1,675.06$4,473.65Feb
19953$2,165.37$6,639.02Mar
19954$1,661.66$8,300.68Apr
19955$3,166.25$11,466.93May
19956$3,461.40$14,928.33Jun
19957$1,852.65$16,780.98Jul
19958$2,458.72$20,527.88Aug
19959$3,078.27$25,003.32Sep
199510$3,237.05$29,363.85Oct
199511$3,934.70$34,708.77Nov
199512$2,019.68$38,990.68Dec
19961$3,757.96$3,757.96Jan
19962$5,395.28$9,153.24Feb
19963$4,341.10$13,494.34Mar
19964$5,379.02$18,873.36Apr
Here's my practice SQL (in NorthWinds.mdb
SELECT DatePart("yyyy",[OrderDate]) AS AYear, DatePart("m",[OrderDate]) AS Amonth, Sum(Orders.Freight) AS SumOfFreight, Val(DSum("Freight","Orders","DatePart('m',[OrderDate])<=" & [Amonth] & " And DatePart('yyyy',[OrderDate])<=" & [Ayear] & " ")) AS RunTot, Format([OrderDate],"mmm") AS FDate
FROM Orders
WHERE (((DatePart("yyyy",[OrderDate])) Between 1995 And 1996))
GROUP BY DatePart("yyyy",[OrderDate]), DatePart("m",[OrderDate]), Format([OrderDate],"mmm")
ORDER BY DatePart("yyyy",[OrderDate]), DatePart("m",[OrderDate]), Format([OrderDate],"mmm");
Here's what I get:
AYearAmonthSumOfFreightRunTotFDate
19951$2,798.59$2,798.59Jan
19952$1,675.06$4,473.65Feb
19953$2,165.37$6,639.02Mar
19954$1,661.66$8,300.68Apr
19955$3,166.25$11,466.93May
19956$3,461.40$14,928.33Jun
19957$1,852.65$16,780.98Jul
19958$2,458.72$20,527.88Aug
19959$3,078.27$25,003.32Sep
199510$3,237.05$29,363.85Oct
199511$3,934.70$34,708.77Nov
199512$2,019.68$38,990.68Dec
19961$3,757.96$6,556.55Jan
19962$5,395.28$13,626.89Feb
19963$4,341.10$20,133.36Mar
19964$5,379.02$27,174.04Apr
19965$6,481.29$36,821.58May
19966$597.36 $40,880.34Jun
Anyone have an idea for correcting this problem?
Thanks in advance,
Barb
View 3 Replies
View Related
Feb 7, 2005
I have a Form/subform that is used to allow editing of a user selected record from a table called personnel. When the form/subform is first opened, all text boxes are blank. The user selects the desired record from a combo box that uses a dropdown list to display all records of the table. When a record is selected, the contents of that record are displayed in the subform in which the user can then edit/modify. The user then clicks on a "save button" which then saves the record.
I want to use a msgbox that will notify the user that he has just updated a record and ask if he/she would like to update another record. If the answer is NO, then close form/subform. If answer is YES, I would like to clear the combo box and the subform text boxes. then the user can them start the process over by selecting the desired record from the combo box.
When I placed the msgbox coding into the "save" button's on click property, the NO portion worked fine. However, when YES is selected, the combo box would clear but the subform texboxes still show the previous record data (does not clear).
Can anyone provide some assistance?
Here is the code for the main form:
Sub SetFilter()
Dim LSQL As String
LSQL = "select * from personnel"
LSQL = LSQL & " where last = '" & cboSelected & "'"
Form_Editpersonnel_sub.RecordSource = LSQL
End Sub
Private Sub cboSelected_AfterUpdate()
'Call subroutine to set filter based on selected last name
SetFilter
End Sub
Private Sub Form_Open(Cancel As Integer)
'Call subroutine to set filter based on selected last name
SetFilter
End Sub
Here is the code for the subform "save" button:
Private Sub SAVE_Click()
On Error GoTo Err_SAVE_Click
DoCmd.RunCommand acCmdSaveRecord
DoCmd.close
If MsgBox("You have updated information on a Detachment Member. Do you wish to update another member?", vbExclamation + vbYesNo + vbDefaultButton2, "WARNING") = vbNo Then
DoCmd.OpenForm "PERSONNEL MANAGEMENT"
Else
DoCmd.close
DoCmd.OpenForm "Edit personnel"
End If
Exit_SAVE_Click:
Exit Sub
Err_SAVE_Click:
MsgBox Err.Description
Resume Exit_SAVE_Click
End Sub
View 2 Replies
View Related
Dec 1, 2006
I have a number of tables (I know someone is going to say you don't need a primary field for these tables but I want to use one) that are basically lookup tables for combo boxes. Generally I use an autonumber to identify the ID of each record. There are occasions when all the records need to be changed (i.e. delete all old records) - does anyone know how I can programmatically (or otherwise) reset the autonumber from the last used back to "1" if all records from the table are deleted.
Beeky
View 4 Replies
View Related
Jul 3, 2007
I'm redeveloping a DB for a new project, so have removed all previous records from relevant tables (to start a-fresh)
One snub, the ID fields in the tables are auto increasing from where they left off, rather than from 1
If anyone knows how to reset the auto counters, it would be very much appreciated
Thanks in advance
View 4 Replies
View Related
Feb 9, 2005
I have five Option Groups on my form. I was running into a problem where after selecting options for one record and then moving onto the next record, the previous options would stay selected for the new record.
I "thought" I had found a solution by setting the Option Group values to Null in the OnCurrent event of the form, but I found a problem in that when going back to a previous record, it is nulling out the previously selected options. Doh!
Any ideas on how to get around this problem? I want to be able to go from one record to the next without carried over options, but I also want to be able to go back to already entered records without having them nulled out.
View 3 Replies
View Related
Mar 2, 2005
I have a couple of fields on my form (Resolution Type, Date Closed) that I want to display only if the Status field has Closed as its selection for each record. I initially set the Visible parameter of both the type and closed fields to False and I reset them to visible by checking for open/closed with an afterupdate event. This works great for the first record but...subsequent entries display those 2, regardless.
I know just enough regarding events to struggle my way through on some things but more advanced field, record level and form level events are beyond my experience level.
Can anybody offer any advice on how to get those two fields to toggle visible/invisible when scrolling through the records?
With that said, does anyone have a link to a post/website referencing this subject?
Thanks in advance,
Mike
View 1 Replies
View Related
Apr 1, 2006
I have a form that I use to collect selection criteria for reports. The controls on the form are combo boxes and date fields. After I run the report, I would like all of the fields on the form to be cleared of the selected values that were used to run the previous report. I have tried the 'repaint' method but it does not work.
Any suggestions?
Thanks,
John
View 2 Replies
View Related
Sep 11, 2013
I am fairly new with Access and VBA and am having troubles with the following. I filter a second combo box "cboTagNumber" with the first combo box "Combo133". The problem is when I clear the first combo box, the second combo box remains filtered. Is there an easy way to clear this?
This is the code:
Private Sub Combo133_AfterUpdate()
Dim strSource As String
strSource = "SELECT ID,[Tag Number] " & _
"FROM [E&I Table] " & _
"WHERE System = '" & Me.Combo133 & "' ORDER BY [Tag Number]"
Me.cboTagNumber.RowSource = strSource
Me.cboTagNumber = vbNullString
End Sub
View 10 Replies
View Related
Apr 4, 2013
On a form I have 2 list boxes and a subform in a cascading arrangement. When the form is opened the first list box is populated with data from a query. When an item is selected in List Box 1 then entries appear in List Box 2 from another query. When an entry in List Box 2 is selected then entries appear in the Subform from a third query. It's all working fine except for this:- if a new selection is made in List Box 1 then I want all entries in the Subform to be cleared until something is selected in List Box 2, but I can't see how to do it.
I've tried setting the Subform's recordsource to null, to " " and to "" but none of these work properly. They either give an error or leave a single entry in the subform with #Name? in every text box.
It does work if I use a List box instead of a subform but that's not what I want here.
View 2 Replies
View Related
Mar 13, 2008
I have a primary key field called "Issue Number", with the properties set to auto-number.
After testing, I need to set it back to zero before going live.
I deleted all the records and did a compact and repair, but the first number that comes up is the one after the last one used during testing.
Help????
Thanks
View 14 Replies
View Related
Jan 19, 2013
I have a form for student attendance that is bound to a query and stores a temporary value for ClassesAttended in a StudentEnrollment table as faculty enters the attendance. They then run an append query to write the temporary records to a StudentAttendance table. Because the ClassesAttended field is bound, when the form is opened, it recalls the last number entered for that student in that class as entered by the faculty the last time attendance was updated. I tried leaving the field unbound, but the first value enter into the first record of the form is updated automatically to all subsequent records.
Is there a way to change the properities, use code, etc. to assigne a null or 0 to the ClassesAttended field when the form opens, without the first updated record to propagate through the reaming records?
View 3 Replies
View Related
Aug 20, 2004
I have created a form in access that contains a multiple select list box and a command button "New Record," that creates a new record. I select values in the list box for the current record. When I click on the "New Record" button, the values that I selected in the previous record are still selected. How can I reset the list box so that no values are selected when I create a new record?
View 8 Replies
View Related
Oct 8, 2013
Occasionally a user of mine needs to reset a sequence number in a table.
A few months ago I made him a simple app that has two text entry boxes and a button. The first text entry box uses a dlookup:
Code:
=DLookUp("sSessionNumID","qryGetAPIsessionLastValue")
...the other textbox ties to an update query, which updates the sequence number with the new value supplied by the user. Simple.
Then about a week ago it stopped running the dlookup part - nothing shows in the text box that is to show the current sequence num.
Oddly, if I enter a value in the update text box and press the button to update the sequence number (which still works) -- the previously dormant dlookup textbox now shows the new value - so it works ...it just doesn't want to work on this one workstation unless the update query is run first.
What can this be? The update button merely calls a DoCmd.RunSQL with both an insert (storing old value locally in ms access table) and an update (updating the sql server table's seq number using an ODBC DSN and sql server driver).
It's almost like the dlookup falls asleep now and won't wake up until the update is run...
View 2 Replies
View Related
Sep 10, 2014
I have the following Event Procedures in a form:
Private Sub PrimaryDisability_Change()
Select Case Me.PrimaryDisability
Case "Other (Specify)"
PrimaryOther.Visible = True
Case Else
[Code] ....
My problem is that when I go to a new record, the fields that I want hidden in the new record unless they meet the criteria are still visible. I have looked all over online and in books to determine the code I use or whatever I need to do to make the field invisible in the new record and only to appear in each record if it meets the criteria.
View 13 Replies
View Related
Sep 21, 2013
I have created quite a substantial and effective database for a small gliding club with all the major data tables being linked. I need to be able to re-index tables periodically so that running totals, which are needed to calculate statement balances, works correctly - all well and good. The only problem is that the process of re-indexing requires the data in the main table to be stored temporarily and the original data deleted. When the temporary data is appended to the main table the auto-numbering just keeps clocking up.how to reset the auto-numbering in a local table.
View 3 Replies
View Related
Dec 3, 2012
In month-end posting - I basically delete all the records from my transaction file. And in doing so - it seems to be resetting the auto number index - which is causing me major problems.
The resetting of the index after deleting all the records is normal.
I am working in VB 2010 - using a 2003 MS Access database.
View 4 Replies
View Related
Nov 5, 2007
Hi,
I have a make table query that deletes the old table every time the new one is made. However every time the new table is made it resets the field property for the “Date” field to “Text” rather than “Date/Time”
Does anyone know how I can avoid this problem please?
I’ve tried an update query but Access won’t allow me to perform the sum function that I require the actual query to perform. Any suggestions would be very much appreciated.
Thank you
Dean
View 4 Replies
View Related
