Q? Find Last Edited Record Using DMax And FindRecord
Oct 27, 2004
Hi,
I have a database / form with a hidden timestamp field [LastEdit]
that is filled automatically with the current time Now() when updated.
The purpose is that each time the form is opened I want it to "Goto" the last edited record.
I *think* I get around the find edit quotes because the smaller snippets below do work.
findit = DMax("[LastEdit]", "esn")
MsgBox DMax("[LastEdit]", "esn")
But the whole thing fails when I put it into one command as either one of these
DoCmd.FindRecord DMax("[LastEdit]", "esn"), , , acSearchAll
or DoCmd.FindRecord findit, , , acSearchAll
perhaps because of the quotes around the variable.
Any ideas?
Thank you,
View Replies
ADVERTISEMENT
Jan 21, 2015
I have a split db in use by about 20 people. Ocasionally a user opens a record that is already opened by some other user and if changes are made then he gets error 7878; "the data has changed error"
Is it possible to find out which records are actually being used/open/edited in a split db? All tables are related to the main key (CallID on table Contacts).
If it was possible to find out, I could have a label show up in the db record search form telling the user not to open that record.
View 6 Replies
View Related
May 6, 2005
I have a problem that should have an easy solution. But I can't find it.
I need to have a user add a new record. (Created a form with the fields on it, No problem , so far.)
The problem is when the user is adding a new record if they hit the PAGE DOWN Key or the mouse scroll, they then go to a new NEW record. (And if they are not paying attention they now have two new records)
How do I prevent this.
Thanks
Mike Lester
View 2 Replies
View Related
Aug 5, 2015
I have a form where a user can change the scheduled start date for a job. On a sub form on the same screen is a list of notes relating to that job.
Any notes added, automatically have todays date and are locked when the user clicks off.
When the scheduled start date is changed I need a note to be made. Either forced, then entered by the user or automatically.
I was thinking of making the scheduled start appear in a small form and the button to make it come up could add a new note on the click event (possibly in a message).
Or even better (probably harder) any time the value is changed in the form a new note is added.
Other options could be a pop up form to add one note on a change.
View 4 Replies
View Related
Sep 29, 2005
Hi All,
I have a continious form where each record can be edited. After editing a record the form does a me.save and a me.requery (is important). After this it jumps back to the first record (seems logical), but how can I write a code that he goes back to the 'previous edited record'.
Thanks.
View 4 Replies
View Related
Jan 8, 2005
Having a problem when saving a record that has been edited and contains a duplicate field. Here is what I'm doing.
I have an existing record that is being viewed by the user. I have an edit button on the form that is displaying the record. When the user clicks the edit button I do the following
'User clicked on edit customer record
Private Sub CustEditRec_Click()
'Store current customer key in string so we can cross
'check if user has changed the key during edit
EditCustKey = Me.txtCustomerKey
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
'Set customer record test string so we can determine
'what the user is doing
CustomerRecStat = "edit"
' Go unlock the customer data fields for editing
UnlockCustomerFields
TxtCompanyName.SetFocus
'Go Unlock the customer editing buttons
UnLockCustomerAddButtons
'Lock the add, delete, edit buttons
CustAddRec.Enabled = False
CustEditRec.Enabled = False
CustDelRec.Enabled = False
'Go Disable the customer navagation buttons
DisableCustomerNavigation
txtCustomerKey.Visible = True
txtCustomerKey.Locked = False
txtCustomerKey.Enabled = True
'Set focus on the customer key
txtCustomerKey.SetFocus
'disable & hide the customer key combobox
cmboCustomerKey.Locked = True
cmboCustomerKey.Enabled = False
cmboCustomerKey.Visible = False
Exit_CustEditRec_Click:
Exit Sub
Err_CustEditRec_Click:
MsgBox Err.Description
Resume Exit_CustEditRec_Click
End Sub
The field which duplicate entries are not allowed in the table is txtCustomerKey. Now remember we are just editing a record NOT ADDING A NEW ONE.
When the user finished making the changes to the record we use the same procedure to save the changes as we when the user is adding a new record...here it is.
'User clicked save customer record
Private Sub CustSaveRec_Click()
On Error GoTo Err_CustSaveRec_Click
SaveCustomerRecord:
'Update the table data fields with the data contained on the form
CustomerKey = Me.txtCustomerKey
CustomerCompany = Me.TxtCompanyName
CustomerFirst = Me.txtCustomerFirst
CustomerLast = Me.txtCustomerLast
CustomerAddress = Me.txtCustomerAddress
CustomerCity = Me.txtCustomerCity
CustomerProvince = Me.txtCustomerProvince
CustomerPostal = Me.txtCustomerPostal
CustomerCountry = Me.txtCustomerCountry
CustomerPhone = Me.txtCustomerPhone
CustomerFax = Me.txtCustomerFax
CustomerEmail = Me.txtCustomerEmail
CustomerWeb = Me.txtCustomerWeb
CustomerNotes = Me.txtCustomerNotes
'Save the record
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
'*** IF WE GOT THIS FAR WITH OUT ERRORS WE SAVED THE RECORD
'*** GO AHEAD & DISABLE THE FORMS VARIOUS FIELDS
'*** & BUTTONS ONCE AGAIN AS WE ARE JUST BACK TO VIEWING
'*** THE CUSTOMERS DATABASE
'Enable and unlock the customer key field
txtCustomerKey.Visible = True
txtCustomerKey.Locked = False
txtCustomerKey.Enabled = True
'Hide & disable the customer keycombo box
cmboCustomerKey.Locked = True
cmboCustomerKey.Enabled = False
cmboCustomerKey.Visible = False
'Set focus on the customer key field
txtCustomerKey.SetFocus
'Lock the customer fields
LockCustomerFields
'Enable the navigation buttons
EnableCustomerNavigation
'Lock the customer adding buttons
LockCustomerAddButtons
'Clear the record testing status
CustomerRecStat = ""
txtCustomerKey_AfterUpdate
Exit_CustSaveRec_Click:
Exit Sub
Err_CustSaveRec_Click:
'If the error generated was by a duplicate value.
'This can only be caused by the customer key as this
'is the only field which does not allow duplicate values.
'so warn the user of this duplicate value error and set
'the focus on the customer key field
If Err.Number = 3022 Then
'if user is editing a record
If CustomerRecStat = "edit" Then
'And the entered customer key has not changed
If Me.txtCustomerKey = EditCustKey Then
'Return to saving the record as the key is
'not really a duplicate
GoTo SaveCustomerRecord
End If
End If
Select Case MsgBox("This Customer ID was already located in the database. Click OK to enter a new Customer ID or Cancel to stop adding this record?", vbExclamation + vbOKCancel + vbDefaultButton1, "Duplicate Customer ID")
Case vbOK
Me.txtCustomerKey.SetFocus
Resume Exit_CustSaveRec_Click
Case vbCancel
'Go simulate undo record click
CustUndoRec_Click
Resume Exit_CustSaveRec_Click
End Select
End If
MsgBox Err.Description
Resume Exit_CustSaveRec_Click
End Sub
The problem is when the user is editing a record. The database assumes the txtCustomerKey is a duplicate in the table. However we are not adding a new record so the duplicate error is false. Its just that the txtCustomerKey is the same as the record being edited. Its not DUPLICATED its the SAME.....
Any help anyone? Sorry for the long post but I'm a strong believer in the more information the better when trying to solve a problem....
Thx
Kao
View 1 Replies
View Related
Feb 20, 2015
I'm working on a project that has project start / end dates which can change. A report is created based on this data. The customer wants me to save all changes as a new record and disregard the older record when the report is created. I was thinking that I would add a (yes/no) column that marks the new record as the most recent (yes) and marks older records as (no). Then I would modify the query for the report to only include records with 'yes' set to true. I was also thinking that I should create a second table that has a foreign key for the original record from the main table. The main table would have the most recently edited record while the second table would have the previous records. Which method should I use?
View 3 Replies
View Related
Aug 8, 2013
after I insert a new record using INSERT INTO and then use DMax() (in a private sub) to look up the new record. About half the time DMax() pulls the new record based on the primary key field (AutoNumber) just fine. However, half the time it pulls the max record prior to the new record being inserted. I.e. record 1001 was added and DMax() pulls record 1000. I'm assuming that my issue has something to do with the timing of when the record is writen/saved in the table. Is there a simple method of refreshing the table that I can use prior to using DMax()?
View 2 Replies
View Related
Feb 24, 2005
Hello all!
Help m' help m'.....I'd like to create a form that allows the end user to fill in a field to search a table. Can't use the standard drop down box option. Needs to be open so it will search a few letters or a complete word. It needs to search the whole table.
:confused: noob :rolleyes:
View 1 Replies
View Related
Nov 3, 2005
I'm kind of banging my head against this one. I've searched the forums and outside, and think I have the idea, but Access say it can't find the forms.
I added an "Notepad" for end user to make notes of each clients if they wanted to. It is a pop up form, has its own table, with ClientID FK to the primary table's PK of same name.
The code I tried is
DoCmd.OpenForm "Notepad", , , Forms![Notepad]![ClientID] = ClientID Or acNewRec
It wouldn't accept an If... Else statement which makes it problematic, because I only want one note per client, so need to make sure Access check whether there is already a record that corresponds to the client, pull that one up. If there isn't any, then create a new record.
The other code I used allowed me to run and can get the ClientID, but does not create a new record when there should be a new one for a new client. It was
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm "Notepad"
Let Forms![notepad]![ClientID] = ClientID
End Sub
If I add an Do.cmd.Gotorecord, , acNewRec, it returns a error message that there would be a duplicate record, which is expected.
So how do I get Access to simply
1) Check if there is a note already written for so and so client then pull it up
2) if there isn't any for that client, create a new record
View 3 Replies
View Related
Jan 17, 2007
Hi, I am currently doing an A2 level Coursework on Information Systems and part of this embodies a database and I am have a few issues with a certain aspect of the database that I am currently building.
The three forms that concern this answer are: "Frm_Main", "Frm_Customer" and "Frm_Projects".
"Frm_Main" is the main database form with a subform ("Sub_Main") displaying the content and buttons to the right for navigation etc.
Basically, within the "Frm_Customer" form that is viewed within the subform on the "Frm_Main" form there is another subform, this time showing projects specifically for that Customer.
I want to be able to select a record from the table, click a button and the "Frm_Projects" will open within the main subform on "Frm_Main" and use FindRecord to navigate to the record that was selected.
At the moment, I have it working properly, however instead of the "Frm_Projects" openning within the subform it opens as a new form.
This is the code that I am currently using for the button on the "Frm_Customer":
Code:Private Sub openrecord_Click()On Error GoTo Err_openrecord_Click If Forms![Frm_Main]![Sub_Main]![project_subform].Form.RecordsetClone.RecordCount = 0 Then MsgBox "There are no proposed projects entered for this Client." Else DoCmd.OpenForm "project_form" DoCmd.FindRecord Forms![Frm_Main]![Sub_Main]![project_subform].Form![Project ID] End IfExit_openrecord_Click: Exit SubErr_openrecord_Click: MsgBox Err.Description Resume Exit_openrecord_ClickEnd Sub
As you can see, I have also implemented an error message to generate if there are no records entered within the table subform.
If it's any use, here is the code I am using for the buttons on the "Frm_Main" to change the source of the subform ("Sub_Main")
Code:Sub_Main.SourceObject = "Frm_Customers"
Thankyou, ever so much for your time and it will be muchly appreciated.
Scott
View 1 Replies
View Related
Apr 24, 2007
When trying to use FindRecord I get a "Runtime error 2406 - the command or action 'FindRecord" isn't available now". The script up to that point is as follows:
Sub Test()
Dim Connection As New ADODB.Connection
Dim Catalog As New ADOX.Catalog
Dim rstRain As New ADODB.Recordset
Dim ppn_0900 As Field
Set Connection = CurrentProject.Connection
Call rstRain.Open("0800Rain", Connection, adOpenForwardOnly)
DoCmd.FindRecord "10", , False, acDown, , acAll
Can anyone point me in the right direction?
View 2 Replies
View Related
Jun 20, 2005
Hello,
I would like to use the FindRecord Method, but something does not work. I would like to find a record which contains the data I entered in an unbound textbox in the form. The action should be started by a command button. As 'Find what'-object I used '=[text61].[text].
Thanks for your help.
View 2 Replies
View Related
Dec 21, 2013
I have a split form with many of a combobox (date, text,numbers and both).
How can displayed only the records in the datasheet based on a combo box selection?
How to build this mechanism and write a sample code?
View 14 Replies
View Related
Feb 6, 2014
I have a database which has a main form and subform built in linked by parent/child customerid, what i would like to do is search all the subform records from the whole DB and return its parent record on the main form?
Can this be done? because if i use find it will only search the filtered form i have onload of the form?
My onload event is based on fosusername()
View 3 Replies
View Related
Jul 24, 2006
What is the easiest way to update a record with the last user that updated it and the time and date?
View 1 Replies
View Related
Feb 2, 2005
I am at a loss as to why this error has suddenly appeared.
I have a form (frmPurchaseOrders) plus a sub form (frmPurchaseOrderDetails).
The sub has a combo ProdcutID row source:
SELECT DISTINCT [ProductID], [ProductName], [Discontinued] FROM tblProducts WHERE SupplierID=Forms!frmPurchaseOrders!SupplierID ORDER BY [ProductName];
Column Count = 3
Bound Column = 1
If I select a Product from the combo I get error:
'Control can't be edited, its bound to autonumber field ProductID'???
I can't recall changing anything??
Why am I getting this error? I have existing orders with order details (thus proving it worked once!!).
Anybody got any ideas?
:confused:
View 2 Replies
View Related
Feb 24, 2005
I have an Access form linked to two SQL tables, I had to make a change to the field length/datatype of a field named notes. The form now tells me that the "text is too long to be edited" whenever I try to add data to that Notes field in my form. I went from char to nvarchar... how do I fix this?
View 3 Replies
View Related
Nov 26, 2012
I combined two textboxes column into a calcualted one. It is the combination of "First Name" and "Last Name" = "Full Name".I listed a calculated textbox ("Full Name") on my form. Are there anyways to edit it on the form through the "FullNameTextBox"?
View 1 Replies
View Related
May 26, 2005
The question is in the title...
How do I allow people to enter data into a field once, but then lock it or disable it from being editted later.
Tried this:
Private Sub SubmittedD_Enter()
If SubmittedD = Null Then
SubmittedD.Locked = False
Else
SubmittedD.Locked = True
End If
End Sub
but the If statement isn't working... it just does the Else statement regardless of wether SubmittedD is null or not.
Thank you.
View 3 Replies
View Related
Jan 26, 2013
I have a database, originally made in earlier version of Access, recently converted into Access 2010. Due to the high number of tables relationship page is quite crowded. My problem is that I can't move the page to the most left-upper corner of the relationwhip page where I still have relations but they simply can't be seen and edited; I can't move the page any further with the silpers.
View 5 Replies
View Related
Jul 18, 2014
Is there a possibility that a control CAN be edited that is bound to an expression?
All of my Form properties are set to editable, no locks, allow deletion, allow addition, etc.
I just want a text box in a Form to be edited by users if they want to update it but they CAN'T since it is bound to an expression.
View 14 Replies
View Related
Aug 17, 2005
Hello to all,
Little new in Access i would like to know how to use the key to find directly to a record in a table.
ie : i have a table where the primary key (unique) is a date, using VBA i would like to know the instruction to find directly the record 01/06/2004 for exemple.
Thanks in advance.
VINCENT
View 2 Replies
View Related
Jun 14, 2005
I forgot to mention that my form is based upon a table, i have created other tables and forms based on this one therefore I don't want to change a lot of stuff. I tried the forum but nothing helped.
I have a form with about 10 fields, i added a find button to search for a record, using three fields, when this button is click i want it to display all the info pertaining to the search criteria, (this will be displayed on the same form populating in the form which contains other feilds) or a error saying record not found.
Thank You
View 2 Replies
View Related
Mar 30, 2006
Hi, i am a beginner to access and need some help if someone would be kind enough...
i have a table (tblCustomer) which has all the standard customer details such as name, address, phone etc. The primary key of the table is a autonumber field (Customer ID). i have autoformed it so all the fields are now on a blank form. I want to add a text box to the form so that i can find a record by typing the customer's surname. Once this has been searched, i would like the records to be listed in a table in a subform further down the original form. I would then like the user to select the correct record they are looking for from the sub form, and the details to come up in the main form so they can be edited. The reason for the whole sub form part is because there may be more than one customer with the same surname.
please could someone suggest how to achieve this.
thanks
View 3 Replies
View Related
Mar 30, 2006
Hi, i am a beginner to access and need some help if someone would be kind enough...
i have a table (tblCustomer) which has all the standard customer details such as name, address, phone etc. The primary key of the table is a autonumber field (Customer ID). i have autoformed it so all the fields are now on a blank form. I want to add a text box to the form so that i can find a record by typing the customer's surname. Once this has been searched, i would like the records to be listed in a table in a subform further down the original form. I would then like the user to select the correct record they are looking for from the sub form, and the details to come up in the main form so they can be edited. The reason for the whole sub form part is because there may be more than one customer with the same surname.
please could someone suggest how to achieve this.
thanks
View 7 Replies
View Related
