Reset Identity Key
Mar 15, 2007Hi there....
How can i reset the identity key?
Thanks
Danny
ADVERTISEMENT
Insert... ; Select @@identity
Feb 19, 2006I work with project access (*.adp)
I want to know if it possible to take the last ID after the sql INSERT QUERY
my code:
strSQL = "INSERT INTO missionFather ....."
DoCmd.RunSQL strSQL
thanks!
Login Identity Issue
Feb 12, 2006I have this form that searches a table to find a unique username for the user. Let me be clear this is not the primary key, but it is as unique since there can not be more than one in table. What i have been able to do is search the table and retrieve a unique id. Example jason brown comes into your company
Fname = jason
lname = brown
what the code will do is create a user id based on his name. the first typical id will be jbrown, but lets say in this insistance jay brown is taken. will what my code does is it returns jabrown. the problem i am having is that i want to find out how to output the name of the user who has the orginial name. example jabrown is returned for jason brown, now a subform will return the current information:
John Brown, jbrown, table1, and the list could go on. This is where i am stuck, i get an error.
Code:Private Sub btnUserID_Click()On Error GoTo Err_btnUserID_Click' this module is designed to check and return a unique user id.' If a user id is not unique enough it will continue to add characters until either there are no more' letters to count or it finds the unique id desired.Dim strUsername As StringDim booMatch As BooleanDim intChar As IntegerCurrentDb.Execute ("delete * from tbl_temp")booMatch = FalseintChar = 1 'defines how many characters to count forstrUsername = Left(Me!txtFname, intChar) & Me!txtlname 'statement counts the first character in the first name and combines it with the last name. Do While booMatch = False 'boomatch is false since it will asume the record is not present booMatch = IsNull(DLookup("[Username]", "tbl_Users", "[Username] = '" & strUsername & "'")) 'Here since boomatch will be assigned the value of the new username entered. 'if a value is equal then a value of true will be returned If booMatch = False Then ' since no match has been found yet boomatch continues meet criteria Dim firstname As String firstname = "SELECT tbl_users.[fname]" & _ "FROM tbl_users" & _ "WHERE tbl_users.[username]='" & strUsername & "';" CurrentDb.Execute "INSERT INTO tbl_temp (UserFirstName, UserLastname, username) VALUES ('" & firstname & "','" & [txtlname] & "','" & strUsername & "');" intChar = intChar + 1 'Boomatch will add one more character to char to move to the next letter strUsername = Left(Me!txtFname, intChar) & Me!txtlname 'this line will assign a value of username to strusername End If LoopMe.txtUsername = strUsername ' user name is assisgned to our VB text boxIf intChar > 1 Then ' if our word counter is greater than one then the following values are visible Me.txtintchar = intChar Me.txtintchar.Visible = True Me.lbintchar.Visible = True Me.Form2.Visible = True Else 'if it is 1 or less are values will be false Me.lbintchar.Visible = False Me.txtintchar.Visible = False Me.Form2.Visible = FalseEnd IfForms![form1]![Form2].Form.RequeryExit_btnUserID_Click: Exit Sub Err_btnUserID_Click: MsgBox Err.Description Resume Exit_btnUserID_Click End Sub
the highlighted part is where the code does not seem to be working.
Calculate Age Using Persons Identity Number
Apr 25, 2012I am using 2010 and have a bound field called Policyholder ID number in a table called Policy Information. I would like to calculate how old a person is by using the identity number which is 13 digits (South African ID) and the format is as follows - 7009215069084. Using the first 6 digits this person was born on 21 Sept 1970 and is 42 years old ( Current year of 2012 minus year of birth of 1970 = 42)
How I can include a calculated field so once I type in the identity number the age of the person reflects in a field called Current Age.
Queries :: Checking Format (Identity Of Field)
May 28, 2013I've got a database with an 'address 1' field. Sometimes if it's just a number, excel formats the field as a date. Is there anything to check the field to identify any that are showing as dates as opposed to just normal fields. For example identify all customers who have some variation of dd-mm-yyyy in the address-1 field.
View 10 Replies View RelatedReset
Aug 17, 2005In my DataBase I want to reset the the records back to the first record. The only way to reset is to exit the form tehn go back in. Is there another way?
Please help this MS Access beginner
Autonumber Reset
Nov 1, 2005Hi,
I have a autonumber function in my form, but i have deleted all the records and want to start again but the autonumber has not reset back to 1, is there a way to do this??
Thanks in advance.
Reset Primary Key
Jan 21, 2006Hi all,
Ive written a program in .net and am currently using an access db as the backend.
In the db I had 200 test records which i deleted to get the db ready for deployment. When i add a new record now the primary key on my "booking" table carries on from the old test number so 201.
Can I resest this so it starts back at #1 as once the db gets updated, the program i wrote then takes the data and updates the accounting system and uses the primary key as the invoice number and so I would like to start at 1.
Thanks in advance
Scott
Reset Tables
May 1, 2006How can I reset my Tables without affecting the Relationships
ViRi
How Do You Reset The Menu Bar?
Aug 10, 2007I was in the startup options and unchecked a couple of toolbar boxe and now I dont have a full menu any more.
Please help
My Autonumber Has Reset To 1 ?
Oct 20, 2005I have an inventory split database. It has been working fine. I noticed when I try to enter a new record, it will not let me as it would create a duplicate record. It has actually started to use the autonumbers in the pk again reusing numbers that already exist starting at 1 and moving up sequentially.
How can I make it continue from where it left off or at least not duplicate any of the existing records. This database has many tables with a bunch of relationships.
Thanks!
Bill Hesson :confused:
Reset Autonumber
Jul 29, 2005just a small question. I have a database that assigns a unique 'audit id' to each entry. the ID is in the form of : DEA-[client prefix]-autonumber. Everything is working fine except the autonumber part. I have gaps in the numeric sequence. Ex: DEA-AAA-1, DEA-AAA-2, DEA-AAA-8, DEA-ABC17, etc.
is there a way that I can reset this autonumber so that it may look like this:
Ex: DEA-AAA-1, DEA-AAA-2, DEA-AAA-3, DEA-ABC4, etc.
Value Reset On January 1
Nov 8, 2005I'm building a form for a clinic.
Insured patients have deductables and copays that usually reset on January 1st.
To be clear, a deductable of $500 is reduced each time the patient pays, but jumps back to $500 at the beginning of the next year.
The table containing patient records has a deductable field, where the form gets the initial value. I'll be making a running calculation of how much remains after each visit, but that calculation has to start over again each year.
Can anyone suggest code to do this or point me at something similar?
How To Reset AutoNumber
May 18, 2006Anyone know how to reset the autonumber for access ? Cox It keep running the number and I just seem can't make it back.
Also if there is some records which mistaken keyin I deleted it keep go for next new number. It would be a waste for that number.
:confused: :confused: :confused:
Reset Primary Key
Jun 3, 2005Is it possible to reset the primary key?
View 3 Replies View RelatedReset All Id Numbers To Zero
Feb 29, 2008hi,
having tested my forms etc and now they all work! i would like to reset all the id numders back to 0 to start from scratch
there are lots of links in the relationships
how do i do that
thank you
steve
Reset Autonumber
Mar 28, 2006Is it possible to set autonumer to start from 1 again after all records in a database have been deleted?
View 7 Replies View RelatedHow To Reset Value Of A Check Box
Sep 23, 2011Access 2007.. I have a check box in a form that I wish to reset using a command button. How do I achieve this or is there a more efficient way to make this happen?
View 5 Replies View RelatedReset Record Number
Mar 7, 2007I have a cumlative DB that exports a file daily based on what was keyed daily..
Therfore, every day I am sending only new records from the DB to a vendor.
I was using autonumber to assign the record number but they now want the record number to restart at 1 for a new days worth of data entry.
So today's file may have record number 1-10 (10 recs) and tomorrow's file would have 11-20 populated om the record number field.
My new record number would have to be 1-10 today and 1-10 tomorrow etc..
Not sure how to do this easily.
thanks!!!
Reset Created Autonumber
Jan 28, 2005I have two fields
1. Month (date field that shows only the current month only, ex: 04)
2. ItemNumber (created autonumber using =Dmax("ID","Table")+1 to retrieve the number from the previous record)
What I would like to do is everytime the current month changes I want the ItemNumber to reset to one.
I tried an If statement in the on current of the form
If Month <> Date() Then
ItemNumber = 1
Else
ItemNumber = Null
However it resets for every new record created, which by the code makes sense. Is there any way to have do this one time until the change of date or is there another way to go?
Reset Number After One Year
Feb 11, 2005I am currently working on a db to handle our FMLA time. I keep a continous form that totals the time used and subtracts that from the starting amount. One year after the time is used their total should grow. I am looking for some code that will not calculate any record that has a date beyond one year from the current date.
View 2 Replies View RelatedReset Not Running Query
Feb 6, 2006Hello all, I posted this earlier but got no response, please help!
I have the following event procedure in my Reset button on my form:
Private Sub cmdClearAll_Click()
' Clear All TextBoxes and Set all Check boxes to false (no tick)
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
ctl.Value = ""
ElseIf ctl.ControlType = acCheckBox Then
ctl.Value = False
End If
Next ctl
Set ctl = Nothing
End Sub
I also have a macro on my execute button which opens another form, Form B, runs the query behind the new form and closes the query.
I noticed that when I open my current form, Form A, click the reset button to clear all the fields and then fill in a few fields or leave the fields blank and then click the Execute button. The query does not run at all and Form B opens up blank with no data populated. Do you know why? But if close the form and reopen it and click the execute button, it works okay? Does anyone know what I am doing wrong?
Please help
Thanks for your help.
Check Save And Reset
Sep 18, 2006I have a form I have created used to inventory items on trailers.
User enters the trailer number, uses labeled checkboxes to indicate items on the trailer. I then added a save button to update the trailer database.
Now what I want it to do is clear all the checkboxes so a new number can be entered an start the process over for a new trailer.
Ive added this command to the save button.
Option Compare Database
Option Explicit
Private Sub save_datta_Click()
On Error GoTo Err_save_datta_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_save_datta_Click:
Exit Sub
Err_save_datta_Click:
MsgBox Err.Description
Resume Exit_save_datta_Click
End Sub
Im trying but I am in over my head.
Thanks
Option Group Reset
Oct 25, 2004I have an unbound form with an option group among several other controls. I also have a "Clear" command button which resets the form and removes any entries in any controls.
How can I reset the value of an option group (with check boxes) so that none of the options are selected? VB does not allow me to assign values to either of the options independently.
Any help is greatly appreciated!
Clear Rowsource Before Reset
Nov 15, 2004I have a reset button that restores my combo boxes and list boxes to their default values.
I want the 'on click' event on the reset button to first clear all the existing values
before it resets them to their defaults. Does anybody know how can I do this?
The reason i need to know is because, if the user presses reset again, I get an error message because
those values are already inserted in their respective boxes. And since I'm using queries from tables as
the rowsources for these combo and listboxes, I get an error because it will not allow duplicate values.
Yes, it's true that I don't want duplicate values so i just want the values cleared first. , so that the
error doesn't have the chance to occur.
Help!! I Need To Reset My Counting By Month...
Jan 7, 2005I have this BIG Problem:
I have a form where i use 9 text fields named “Pallet” bound to 9 different tables (each table belongs to each terminal user, I create these tables due I have to perform differents actions and reports by person) i get the max value of each table by model and by user using the DMax function, the key point is to get the Max Value of this 9 fields just like this:
Field (Pallet)#1:108 Field (Pallet)#5:136 Field (Pallet)#9:159
Field (Pallet)#2:109 Field (Pallet)#6:138
Field (Pallet)#3:115 Field (Pallet)#7:132
Field (Pallet)#4:125 Field (Pallet)#8:138
Max Pallet: 159 I got this value creating the follow function...
Public Function fMax(ParamArray Variables())
Dim i As Integer
For i = 1 To UBound(Variables)
If Variables(i) > Nz(fMax) Then
fMax = Variables(i)
End If
Next i
End Function
Based on this i open a controlled field in my form and wrote this function:
= fmax ([Pallet#1]; [Pallet#2]; [Pallet#3]; ...[Pallet#9] )
The BIG QUESTION is how can i reset to zero the criteria in my Dmax function to get the max value of each table??? Due each month the counting needs to be reset to zero because the issue is to get every month the production quantity of cell phones pallets produced, and if I let this function alone, it will give me the max pallet number regardless the month the key is to get the max value according the month…
I try this function but doesn't work:
=DMáx("[Escaneo_Gabriela]![Tarima]","[ Escaneo_Gabriela]"," [Escaneo_Gabriela]![Modelo] = 'G1500S' And Month([Escaneo_Gabriela]![Fecha])=Month(Date()) And Year([Escaneo_Gabriela]![Fecha])=Year(Date())")
I was thinking in open a field in each table named "Month" and create a function or module to give me the value only of the current month based on this field, but i'm not quite sure about the sintax, PLEASE HELP ME>>>!!!!!!!