I have a custom record counter on a form using the below code:
Private Sub Form_Current()
If Me.NewRecord Then
Me.lblRecordCounter.Caption = _
"Record " & Me.CurrentRecord & " of " & Me.Recordset.RecordCount + 1
Else
Me.lblRecordCounter.Caption = _
"Record " & Me.CurrentRecord & " of " & Me.Recordset.RecordCount
End If
End Sub
I think at some stage the form was saved with a filter on and this may be causing the issue. The problem I have is:
There are 1749 records. Everytime I open the form the custom record counter displays "Record 1 of 501". The built in record counter shows 1 of 1749. The moment I hit the next record arrow the custom record counter displays "2 of 1749" and if I go back again it displays "1 of 1749.".
I know it's a filter causing the problem because I have a macro that does a clear search. As soon as I hit the clear search the custom counter goes back to "1 of 501" again (even though the built in one stays at 1 of 1749).
I'd like to override the default behaviour for deleting records in a form.Specifically, I want to build my own custom delete procedure so that when the user presses the Del button, my code fires to complete the deletion of the selected record(s). In order to do that, I'd set Allow Deletions = No for that form. I'd also want to code the KeyDown event for the Del key so that if record(s) are selected, my custom delete code fires, else the default behavior for the Del key happens.I'm primarily interested in how I might code the KeyDown event.
I am building a db for reservations for my limo company. I want to have a cmd button that verifies the user to make sure she wants to delete a run. This is what I have so far:
Private Sub cmdDeleteRun_Click() Dim Response Response = msgbox("Are you sure you want to delete this run?", vbYesNoCancel + vbCritical, "Really delete run?") If Response = vbYes Then
End Sub
I don't know what I am missing for the cmd to actually delete it.
To briefly explain my database; it is a bespoke referral management system within a hospital. Each record on the database contains patient demographic information, as well as information on their referral (i.e. date of referral, date of assessment, date of commencing treatment, discharge date etc.) Therefore, the same patient will appear multiple times in the database, with each separate record corresponding to a unique referral pathway.
The database forms are split to show patient information at the top, with referral information shown in a subform. I am trying to add navigation buttons to the subform that will allow the user to scroll through the referrals corresponding to the patient currently displayed on the main form.
Each patient has a uniquely identifiable number associated with them, and so it seems straightforward enough in my mind to have a button that will search for the record in the database where the patient's number matches the patient number of the current record, and where the referral date is minimum (for "First Referral"), maximum but less than current (for "Previous Referral"), minimum but greater than current (for "Next Referral"), and maximum (for "Last Referral").
I have a questions for all you experts out there. I need to create a record counter. I have found documentation on how to create a simple record counter, but of course it needs to be a little more complicated than that.
I need to beable to count the records that contain a certain value in a certain field. For example, I need a count on all records which contain the value "Orland" and so on.
Hi - I'm fairly new to Access and have jumped in head first. I do not know much about it, but I am very familiar with the other MS programs. I also don't know anything about visual basic.
I found an article online that described how to put in a custom record counter by using the Oncurrent Event. I have a form with a subform and I'd like have a custom record counter on both, however, when I open a new record, I get an error telling me there is no current record. This is more a problem for the subform, as the message pops up repeatedly until I put something into it to make it a current record.
The error is run-time error '3021': No Current Record.
This is the code that I used (keep in mind that I only copied this and used it, I didn't write it):
Private Sub Form_Current()
' Provide a record counter for using with ' custom navigation buttons (when not using ' Access built in navigation)
Dim rst As DAO.Recordset Dim lngCount As Long
Set rst = Me.RecordsetClone
With rst .MoveFirst .MoveLast lngCount = .RecordCount End With
'Show the result of the record count in the text box (txtRecordNo)
The user wants all of the records in the database to have continuous numbering. I have used AutoNumber in the past but if you delete a record, it produces gaps in the numbering.
I was wondering if there is a way to number the records so they are always numbered continuously (1,2,3,etc.) even if some record is deleted.
I have tried using the following:
Set db = CurrentDb() Set recordlist = db.Openrecordset("SELECT Max(tblContactHistory.ItemID) AS MaxID FROM tblContactHistory")
ItemID = (recordlist!MaxID + 1)
recordlist.Close Set recordlist = Nothing
but I still cannot get the records to have continuous numbering after a record is deleted.
I would like to add a column to my query called "counter" and count starting with 1 each time the employee number is listed, when the employee number changes I would like the counter to start over again at 1 and continue this throughout the query.
I have created a custom message box form that suits my needs for this particular situation. It is called (opened) in the middle of a bunch of other VBA procedures. What I am having a problem with is how do I "pause" the rest of the code from running after the message box call?
For example, with a regular msgbox() function, once called, the code will wait for the user input or click of a button, then continue. My problem is that I am using the basic docmd.openform procedure, and it opens the message box form just fine, but then continues on with the rest of the code after the form is opened.
How would I "pause" the code after the call of opening my custom message box form, then continue it after the user clicks one of my closing buttons from that form?
Furthermore, although its not entirely needed in this situation, for future reference, how would I have that message box form return a value to the calling procedure?
Hello everyone, new member here. I am in charge of a student sign in sign out database at the college where I work. This database has two forms and one table. What I would like to do is include a text box on the first form, which is the sign in form that will display a running total of the students that are signed in. This text box should count 1, 2, 3 etc. each time a student clicks the sign in button. Thanks for the help
The problem that I have is that when I add a new field to my form at runtime I get the following error Runtime Error '2424' The expression you entered has a field, control, or property that Microsoft Access can't find.
This error appears when I try to update any field. I posted this before and was informed that access has a field counter limit up to 750 on a form, this includes fields which were deleted or hidden. I dont deliberately add and remove fields, but this is the main form in my database so it is not unbelieveable that this may be at the limit and this is why the error is occurring. Does anyone know how to reset this?, or what I should do to get around this.
I want to create a custom sequence when deleting a record but I'm having a problem.
What I want to do is prompt the user for the reason he/she is deleting the record and then archive this so it can be viewed in a log at a later time. This extra step is not as easy as I thought it would be.
What I did was put some coding into the On Delete event of the form. The code opens a form with a text box. The user can enter the reason for the delete in the text box and click OK or click cancel to abort the deletion.
My problem here is opening another form from the On Delete event. As soon as the form is open, the code just passes to the next step of the sequence and deletes the record.
There are 2 approaches that I've thought of but I'm not happy with either.
Approach 1: In the On Delete event, set Cancel to True. That way the deletion is not handled by the delete sequence but rather by the new form that opens. The problem is that deleting a record from another form is not that simple. What I've tried is to execute a SQL statement that grabs the ID of the record from the open form and delete it. The problem is that although the record is deleted, all the fields on the form say #Deleted in them which is not that pretty. (Maybe there's some other way to do the deletion without this problem that I'm not aware of...?)
Approach 2: Use an Inputbox. Unlike a custom form, the Inputbox delays execution of the code so the code can determine from the results (i.e. should it cancel the delete if the user clicked Cancel). The problem with Inputboxes is that they are not very flexible in the layout, they look pretty "packaged" (i.e. they look to me like the programmer didn't bother creating a means to enter a value). There's no way to place the buttons where you want or have it do better validation (e.g. I want the FORM to check that there's at least a few words describing why the user is deleting the invoice rather than have the form with the record get the input message passed back and then give an error and then pop open the Inputbox again)
I think that especially when you're creating an application with anything financial, you'll want to log a deleted invoice including the date, amount, possibly the client's name, invoice number, reason for deletion and the login name of the person doing the deletion. All of these are easy to record other than the reason.
i want to be able to create an On Click Event when pushing a command button that will run an Update query to update a record and after it has been updated that specific record will pop up on a Form and be displayed. i know a different way is to run the Update query and then have it displayed in a Select query but i want it to be displayed on a Form instead. is it possible?
I have created a command button through the wizard that is basically: DoCmd.GoToRecord , , acNewRec
Well, it puts all this code in the event:
Private Sub NewVerification_Click() On Error GoTo Err_NewVerification_Click DoCmd.GoToRecord , , acNewRec InboundQuestions!Page1.SetFocus VerificationNo.SetFocus Exit_NewVerification_Click: Exit Sub Err_NewVerification_Click: MsgBox Err.Description Resume Exit_NewVerification_Click
End Sub
Well, my PK field is one users enter in manually (please don't ask why). What I need is something that checks to see if the number in that field is a duplicate key, so I can tell users via msgbox "Duplicate Number, try again". Right now, it's not that descriptive, it says "You can't go to the specified record". I guess all i need is an If statment, but I don't know the code that checks to see if [field1]'s value has already been used in my table. Any ideas?
My database has been set with the 'All records' locking option set to prevent two or more people editing files at once. This is working fine but I wondered if there is a way to make a more personal message appear instead of the default Access one, just to maintain a uniform look.
I would like to create a custom pop-up Message when one attempts to add a duplicate record.
Currently, I have a default pop-up message when you try to add a duplicate record. I set the field as Indexed= yes(No Duplicates) which generates a default pop-up message but it is technical and wordy. I would like to be able to simplify the wording to just let the user know not to add a duplicate record.
I have created 2 reports one that needs to be exported as an excel file and the other report as a PDF file. I have the exports working correctly for each on a button except this saves the files with the name of the report. The client wants the name of the file to appear as one of the fields on the report (the info that is in the description text box for example).
I have thought of two ways to do this and neither seems to be great - one is create a copy of the report with the new name and then export that saved file then delete the renamed report OR similar to that but instead rename the file save and rename it back (this causes all kinds of problems if the user cancels out that would need to be handled in the error handling - this is not a good way to go.
Ihave implemented custom events to sync between open forms. I noticed that if the code is engaging in middle of the way referncing a form with form constant (Form_formName) then the listeners stops listening so the event isn't firing, without getting the reason of this i just avoid using it Now, i have a problem that i want to open multiple instances of the same form and i didn't find another way than set frm = new form_formName as this is the only way i seem to be able to reference a closed form, so my question as followes
1. whether the "breaking" issue can be solved without avoiding "Form_FormName" 2. or a way to open
I'm having a problem with argument in custom function..My table is like this:
myValues 123.5 32.7 65.8 11.1
What I want to achieve is to multiply each value with the average of all values, so that at the end I get (avg(myValues) = 233.1):
myValues2 7196.963 1905.593 3834.495 646.8525
For this I would like to have a function (this is simplified example of bigger problem, that is why I need function, otherwise simple SQL would suffice as mentioned below also), so that I could do something like this:
'SELECT TestFn([myValues]) as myValues2 From MyTable' or 'SELECT TestFn("[myValues]") as myValues2 From MyTable'
and I should get the table above.
My code is like this
Code:
Public Function TestFn (FieldName) as String Dim myAvg as Double myAvg = Davg(FieldName, "myTable") TestFn = FieldName * myAvg End Function
I understand the problem is that Davg needs string, but if I change to string then the multiplication does not work. The 'FieldName' parameter does not have a Type specified because that is where the problem is if I give string Avg works ,but calculation does not. If I give Number, avg does not work.
P.S. "as String" at the end of function is not a mistake, I need number as a string
P.P.S I did not inculde NZ either as in my example it can never be Null.
I'm working to put together a shortcut menu for a form that will be viewed as a datasheet. I'm trying to put together the vba to create the menu. I'd like a lot of the standard options i.e. sort a-z, filter toggle etc. I'd also like to add the menu option that is displayed in the default menu called "Text Filters". I've been unable to find the id code for that option, and since it, when chosen opens another menu, I'm not real sure how to code it. Here is what I have so far:
Sub CreateWIPShortcutMenu() Dim cmbRightClick As Office.CommandBar Dim cmbControl As Office.CommandBarControl ' Create the shortcut menu. Set cmbRightClick = CommandBars.Add("cmdWIP", msoBarPopup, False, True)
