I've a button that position the table on a new record:
DoCmd.GoToRecord , , acNewRec
And I create a button to cancel that action:
Me.Undo
But the code field (autoincrement) continues autoincrementing! Explaning:
1. I click on "Add" button
2. The table's positioned on autoincrement code #1
3. I click on "Cancel" button
4. The register is cancelled, it isn't includded on table
5. I click on "Add" button
6. The table's positioned on autoincrement code #2 and the #1 isn't exists...
Is there a way in MS Access to keep an autoincrment field counting successivly after deleting and inserting? I want the field having the right counting.
I have a database that a client of mine has started to create. He wants to be able to import data from an excel spreadsheet into a table in Access. I created a macro that works fine but now I am having an issue with importing additional data.
His excel sheets consist of Agency ID numbers, first name, last name, SSN, etc. He also has a column in the table called order number and internal ID. Which I duplicated in the spreadsheet. He wants the internal ID to autonumber. When I first use the macro to import the spreadsheet into Access it numbers the rows accordingly (after I fill the excel sheet using the series option). However, when I try to import an additional spreadsheet (which is a test sheet)I have to fill in the Internal ID number manually and consecutively according to the last record in access.
The sample spreadsheet he sent me has 2079 records which imported fine with the macro and then I made a second spreadsheet with two rows with information in them and if I don't put in the numbers 2080 and 2081 it imports them at the top of the table without any number in the internal ID. I hope this is making sense.
Can anybody help me make it so that it automatically puts a number (consecutively)in the internal ID field when I use the macro to import another spreadsheet.
Thanks in advance
Tracy
P.S. When I go to the order details table in design view it says that field is autonumber. If I do not assign a value to the Internal ID field in the spreadsheet it comes up with an import error saying that the fields had a null value but if I number the internal ID field manually in the spreadsheet it imports just fine. I don't want to manually input numbers into the internal ID field in the spreadsheet, I want access to do that for me but it won't let me.
I have a database that a client of mine has started to create. He wants to be able to import data from an excel spreadsheet into a table in Access. I created a macro that works fine but now I am having an issue with importing additional data.
His excel sheets consist of Agency ID numbers, first name, last name, SSN, etc. He also has a column in the table called order number and internal ID. Which I duplicated in the spreadsheet. He wants the internal ID to autonumber. When I first use the macro to import the spreadsheet into Access it numbers the rows accordingly (after I fill the excel sheet using the series option). However, when I try to import an additional spreadsheet (which is a test sheet)I have to fill in the Internal ID number manually and consecutively according to the last record in access.
The sample spreadsheet he sent me has 2079 records which imported fine with the macro and then I made a second spreadsheet with two rows with information in them and if I don't put in the numbers 2080 and 2081 it imports them at the top of the table without any number in the internal ID. I hope this is making sense.
Can anybody help me make it so that it automatically puts a number (consecutively)in the internal ID field when I use the macro to import another spreadsheet.
Thanks in advance
Tracy
P.S. When I go to the order details table in design view it says that field is autonumber. If I do not assign a value to the Internal ID field in the spreadsheet it comes up with an import error saying that the fields had a null value but if I number the internal ID field manually in the spreadsheet it imports just fine. I don't want to manually input numbers into the internal ID field in the spreadsheet, I want access to do that for me but it won't let me.
I have a Memo box where the user puts comments and then I have a check box that if check automatically puts a specific phrase on the first line of the Memo box (before any other text currently in the box). What I am trying to do is have it so that if the "check box" is UN-checked it will remove the line that it just added to the Memo box. I have been able to get it to undo everything in the Memo box, but what I really need is for it to just undo the last line of text that was enterd when the user checked the box.
Example:
Memo Box: This is the line that the checkbox just added when the checkbox is set to "True" This is the test data the user has already put in the box... ==================
User now UN-checks the checkbox (I want the Memo box to read.. see below) Thereby deleting the line "This is the line that the checkbox just added when the checkbox is set to "True""
Memo Box: This is the test data the user has already put in the box...
Hi, I have found the answer to most of my problem, thanks to previous posts. I am using the Undo command on a Date field. However it seems that my code is maybe not staying on the event to action the undo.
After it runs the Focus goes to the next field Me.LeaveDays and the value is automatically entered, unless I comment back in the Goto's in which case the value will be zero.
I want to be able to Undo the EndDate (and preferable the StartDate, and Setfocus to StartDate) EndDate will do if not possible.
Don't want to Undo the whole form, although that could be a last resort.
My code is in the before_update event, image posted (ignore faded fields, not visible/needed in final solution) Private Sub EndDate_BeforeUpdate(Cancel As Integer) On Error GoTo Err_EndDate_BeforeUpdate
Dim intLeaveLeftAL As Integer Dim intLeaveLeftSL As Integer Dim intLeaveDays As Integer Dim dtNullDate As Date Dim varNullField As Variant
' Use the WorkingDays function to calc the number of Leave Days ' set LeaveDays and requery the form intLeaveDays = WorkingDays(Me.StartDate, Me.EndDate)
If Me.LCode = "LC41" Then ' Sick Leave
Select Case intLeaveDays Case Is > intLeaveLeftSL MsgBox "Employee does not have enough Leave" & vbCrLf & "to take as Sick Leave.", vbCritical + vbOKOnly + vbDefaultButton1, "Entry in Error" varErrorCondition = True Me.EndDate.Undo GoTo Exit_EndDate_BeforeUpdate Case Else: End Select
ElseIf Me.LCode = "LC11" Or Me.LCode = "LC13c" Then ' Annual or C/Fwd Leave
Select Case intLeaveDays Case Is > intLeaveLeftAL MsgBox "Employee does not have enough Leave" & vbCrLf & "to take as Annual Leave.", vbCritical + vbOKOnly + vbDefaultButton1, "Entry in Error" varErrorCondition = True Me.EndDate.Undo GoTo Exit_EndDate_BeforeUpdate Case Else: End Select End If
When I have a record that I copy and paste into a new record with one column having a unique constraint , I display message "Cannot insert duplicate code" then if not all values in the record are inserted , I get a #deleted in all columns
Refreshing , I find that the id is incremented by 2 instead of one.
That is if the previous record has ID 20 , the #deleted record has ID 22 .
How to avoid the #deleted which happens if not all columns are filled.
Hai guys Iam looking out for a sample DB for employees leave management. # With leave application form # Leave approval form # Leave status # All with username and password
I have 2 tables... one with 'answers' (on questions) and one with these 'questions'. Now I want to show the questions with their answers on a rapport (based on a query) .. but only these whereby the answer-field is NOT NULL..
So I have already: ansques1: IIf([answers.1] Is Null;Null;[questions.1]+" "+[questions.1])
But now if the answer-field is NULL, then the ansques-field is also NULL... I want if the answer-field is NULL, to have no new field.. like ansques1.. just want to leave it out of the query... can someone help me with this?
I have an unbound text box that displays when certain criteria exits, which I placed in the Form's properties, "On Current" ...
If [Decor6Date] <= Date - 30 And [NonrecommendQuestion] = 0 And IsNull([DateDecReceived]) Then
[LateDecoration2] = "Late Decoration"
Else
[LateDecoration2] = ""
End If
I then made a timer event to show/flash the message
Me!LateDecoration2.Visible = Not (LateDecoration2.Visible)
Everything works fine ... the problem is the message will only flash if I exit the record then return to the record. How can I fix that problem? I tried Refresh ... no luck.
I have a database with shifts in for staff. They have a bunch of times in and times out over a four week period. I have gotten an average weekly amount of hours for each staff member based on this but I need another equasion to work out their leave entitlement. It breaks down like this...
Average weekly hours x 5.6 x number of days working in this period (ie start date and end of financial year day count) divided by number of days in the financial year (ie 1/4/2015 - 31/3/2016 day count)
I'm just wondering of a way to do the day count based on me keying in the start date of the staff member (default 1/4/2015) and that access can work out the days in that financial year left and the actual days in that financial year.
It sounds simple enough but I want to get it to automate based on my start date.
I am trying to create a database to record employee sick leave and to indicate when an employee has surpass they allotted sick leave yearly.
I have the following tables and attributes
Employee Table ID#, FName, MInitial, LName
Status Table Category Allocation
Transaction Table EntryDate StartDate
EndDate TotalDays Certify? DaysBalance
Category indicates if a person is Temporary, Appointed or Substitute and Allocation indicates how many sick days is allocated per category yearly. I place both in the same table so when the status is selected the allocation is autofill
I am thinking of creating a form with the Employee Table info at top and a subform with the status and the transaction information.
I would like when a new year begins the sick days not used is discarded and a new allocation of days per employee is generated also when an employees status is change how to deal with that. When an employee used all of there sick days is there a way to have all those names display when you open the database.
Trying to modify this calendar database to track employee leave. I need the calendar form (frmCalendar) to show all employee leave on the calendar using the "Show All" command button on click event. This works if I specify a uID (UserID from employee table) in the code, but only for that specific employee. I need all employees at the same time up on the calendar so I can see if there is more than one employee off on any given day.
Events or leave is populated on the form through "Private Sub DisplayEvents" and the mdlCalendar module.
I inadvertently deleted my previous thread. Sargeant reply to my previous thread recommending using the 'Union' statement. I did and below are the results. Below is what I used. Please advise. Thanks again ! ! !
SELECT Table1.Column1, Table1.Column2, "" FROM Table1 UNION SELECT Table2.Column1, "", Table2.Column3 FROM Table2;
I have a form with an image embedded in it. I want to be able to click on the image and leave a "dot" or "mark" where i have clicked, allowing me to click in multiple places on the image and leave multiple "dots"
I am assuming i need to crate a new shape each time i click, how is this done?
Is there a way to code something that will say do Undo All changes. I have a button called Exit without changes…and I would like to be able to undo everything that a user has just done on the form. Thanks.
Is there a way that I can make a button which will undo all the changes being made on a form (on multiple records). I have a continious form with a checkbox after each record and want to restore the 'old values' if necessary.
Thanks.
I know it's possible by record, but the tricky part is to do this on multiple records.
We have an mdb back end on our server with read / write permissions for several approved users. One of them (don't know who yet) has converted the mdb file from 1997 to 2000. Is there a way to undo this? :o (a few have both versions on their machines which is why this was possible to begin to with)
We have had this set up for almost four years now, with no issues might I add but always knew in the back of my mind something like this would happen.
I have a form with a subform, on the mainform there is an undo button but when someone makes changes on the subform the undo button wont run and the error message "The command or action Undo isn't available now". How can i get the mainform to detect that changes have been made on the subform and then if the button is clicked undo the changes.
I would like to be able to check to see if undo is available.
ie. i have a button thats called 'Cancel' When clicked it undo's changed and then closes the form, but if there are aren't any changes to undo it flags up an error saying not available.
I would still like it to close the form even if there are no changes to undo.
So check to see if the undo is available then undo and close if not then just close the form.
Hello All, I am finding this one difficult so felt I should post my issue. I have status field on my form. It is a bound field. A combobox - inactive, active. I wanted the field to do the following when changed If change then msgbox = Please enter reason in activity notes a vbYesCancel If they select it calls the activity notes form - this portion works. If they select cancel I tried making the field do form.field.undo (Me.statusid.undo) - something similar Cancel = True to reset the field to the original state. This fails and says my object is not valid I would be happy to take any suggestions . As always thanks in advance.
I use a listbox and a command button on a form as criteria to open another form.
The click event of my cmdbutton has the following code that runs after selecting a record in the listbox:
If IsNull(Me![NAME]) Then MsgBox "Sorry! No record selected" Exit Sub End If
DoCmd.OpenForm ("myform")
I have now created a menu bar and would now like to remove the command button and place it somewhere that can run when I select the menu to open a specific form.
Basically I need that when I select a menu from my menu bar, the code should verify is a record in the listbox has been selected. If yes, then it will open that form, if not it should run the undo command and not open the form but bring me back to the form on which is placed my listbox (frmEforms).
I have a form with an undo button which works fine...sometimes.
The issue is if the user hasn't clicked out of the text box they have updated before clicking the undo changes button only the changes they have made to that text box are un-done.
My question is when the user clicks the undo chnages button how do I code it so the changes to the whole form are undone even if they haven't clicked out of the last text box they updated.
I have a form that has multiple text boxes. Was trying to add an Undo button that behaves exactly the same as the (Ctrl+Z) or the undo button used in microsoft access, word, excel ..etc. as it only undos one action in a click. When added the undo button i used just a normal button and added a code to it. if i type in different text boxes and press on the undo button it undos everything modified since the form was opened. Is there a way to change that ?
