Hello,
In an empty field how to detect that the RETURN key has been pressed, i don't know hot to differentiate the RETURN KEY and the field exit by clicking the mouse in an other field because in the 2 cases the value of field is set to "", is there a way to test VbKey in a field ?
This because i want to set my field to a default value only when i press RETURN and i don't want the LostFocus or Exit set my field to this default value if nothing has been typed in my field.
(I must precise that for some reasons in my form the KeyPreview is set to TRUE.)
I create the tables and groups in SQL Server, I linked all the tables from SQL Server to MS Access database, and I assign the groups to each user in SQL Server.
In MS Access VBA, how can I detect back the user roles (group) from SQL Server?
The code below opens a PDF selected from a form. Whatever document number appears in the textbox (FICNo) on the form is the document that is opened when the command button is clicked.It works but I would like to add a message box to say File not found if the document number in the textbox has no corresponding document in the folder (ScannedFiles).
Code:
Option Explicit Option Compare Database Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _ ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long Private Const SW_SHOWNORMAL As Long = 1 Private Const SW_SHOWMAXIMIZED As Long = 3 Private Sub CmdOpenDoc_Click() ShellExecute 0, "Open", "C:UsersDavid.BallDesktopScannedFiles" & Me.FICNo & ".pdf", vbNullString, "C:", SW_SHOWNORMAL End Sub
I was working along a code that seemed to work fine until all of a sudden, the next time I tried it it, it doesnt. It is a simple code really as below which is giong to be the foundation for a selection followed by SQL input into the record source of another form to generate results. I get the following error message
"Microsoft Access cant find the form "Querypurpose" referred to in a Macro expression or Visual Basic Code"
It doesnt make any sense to me since, if I were to take away the " Forms!Querypurpose.RecordSource = SQL" line, than everything works fine. Did I disable any function in the VB editor cos I was playing around with rst before it happened :confused:
Would anyone be able to explain why...thanks so much!
***** Private Sub Command59_Click() On Error GoTo Err_Command59_Click
Dim stDocName As String Dim SQL As String
stDocName = "Querypurpose" SQL = "SELECT ClientInfo.ClientName FROM ClientInfo;"
Before the record is written for the first time OR edited/updated to the table, I want to search tblClass and determine if the new/updated info will create a duplicate record.
In this specific case, a duplicate record will be defiened by a record where the only fields being considered would be StudentID, Trimester and SubcatID. The fields WorkGrade and SkillGrade should not be considered.
The code I came up with was the following and it was put in the BeforeUpdate:
Dim conn As ADODB.Connection 'Connection Object Dim rst As ADODB.Recordset 'Recordset Object Dim strSQL As String 'SQL statement for open statement
' Create object variables Set conn = CurrentProject.Connection Set rst = New ADODB.Recordset
' Create sql to search for records in tblClass that match 'studentID, Trimester, and Subcatagory in the form record being added strSQL = "SELECT * " & _ "FROM tblClass" & _ "WHERE fldStudentID = " & StudentID & " AND " & _ "fldTrimester = '" & Trimester & "' AND " & _ "fldSubcatID = " & SubCatID & ";"
' Open recordset rst.Open strSQL, conn, adOpenKeyset, adLockOptimistic
If rst.RecordCount >= 1 Then ' record already exists in tblClass msgbox "Record already exists!",, "Duplicate Record Error" Me.cboSubcatID.SetFocus Cancel = True End If
' Close and disassociate object variables rst.Close conn.Close Set conn = Nothing Set rst = Nothing
This code worked great except when I went to edit an existing record. When I went to change a grade (WorkGrade or SkillGrade) on an existing record, it told me that I could not enter the record because the record already existed (ie, the record I had open and was editing). I am not sure if it is my code that I need to edit or if it is the placement of the code I need to change.
I've looked all over this forum and others and google, and am amazed that I've found nothing that addresses this issue.
I want to execute some code after a record has been deleted. If I uncheck the "Tools / Options / Confirm Record changes" option (it's extremely disruptive) then the "Before/After Del Confirm" events don't occur. The "On Delete" event occurs before the record is deleted and therefore doesn't help me.
Are the "Before/After Del Confirm" events the only events that occur after record deletion? If so, is there any way to disable the confirmation dialog and still get the events so I can handle it with my code and not disrupt the user every single time a record is deleted?
I have a form with 55 values entered. On closing the form, I need it to decide if this is a new record. If it is a new record, it loads all 55 values into an array. If all values are above 20,000, it goes ahead and closes the form. If any are below 20,000, it writes the record to another table and brings up that form to add add a comment.
Every thing does as it should except determining if it is a new record. What property will trigger it properly? All the properties I have tried either triggers it on all the records (even if it is an old record) or it does not trigger it and closes without prompting the code.
I have a client where a couple of (senior management) users have now got touchscreens attached to their laptops. I already have a facility for users to personalise the look of their forms in terms of colours and fonts (blended with the security options of which controls/forms etc are visible). The system is distributed as accde 2010 runtime.
I added a font size option which enlarges visible controls to cater for it, but it is intended as an occasional change (i.e. user goes into setup and configures the properties). However one user regularly works away without their touchscreen so they have to go in to the properties and reduce the font size and then when back in the office, enlarge it again.
I suggested just configuring everything to the larger font, but the non touchscreen users don't like it since they see less on the screen.
It is not a major hassle for the user but I thought it would be nice if I could detect if a touchscreen was attached and it would automatically adjust the font size.
Some code/api to detect if a touchscreen is attached? What would be really cool is if the code could detect based on the monitor the form is on, but I suspect that is a step too far!
I have a main form (Client) from which I open another form (FilterCriteria). Since FilterCriteria code references the Client form, I want to ensure the user can't close Client while FilterCriteria is open.
I'm thinking I need to intercept Client's 'Unload' event and cancel it - but I can't figure out how to check if FilterCriteria is open.
Is there a way for visual basic to detect where the directory filepath of where the *.mdb file is running on and use that filepath to automatically register the *.dll and *.tbl files...
The reason for this is because the application can be installed on a D drive other than the C drive. Using the Visual Basic Editor's menu to add a reference is a moot point also because of this.
I have created several queries that in turn create a report. I am trying to write some VBA script to make them execute correctly, however am having trouble with the loop function.
DoCmd.OpenQuery "Qry_SubjectColleaguesByDivision" Do Until Tbl_ReportSubject.RowCount = 0 DoCmd.OpenQuery "Qry_DataToTrainingReport" DoCmd.OpenQuery "Qry_DeleteDataToTrainingReport" Loop DoCmd.OpenReport "Rpt_TrainingDue28Outstanding" DoCmd.OpenQuery "Qry_ClearTrainingReport" Qry_SubjectColleaguesByDivision populates Tbl_ReportSubject with the relevant colleagues. Qry_DataToTrainingReport calculates the report data for the first user in the list and copies to Tbl_TrainingReport, Qry_DeleteDataToTrainingReport deletes their record in Tbl_ReportSubject. Qry_ClearTrainingReport clears Tbl_TrainingReport.
My issue is getting the DO UNTIL statement to work. I am trying to get the loop to finish when there are no records in Tbl_ReportSubject.I know the data structure does not seem robust based on how the data is handled, however this is to do with a) Legacy issues, and b) data importing from other systems.)
Without putting a /CMD parameter value in the command line, is there any way to detect in VBA that the current state of the application is that it is executing from a scheduled task ?
What's happening is that my Outlook email is failing because a Namespace Logon method cannot be invoked when there is no user logged in. I must jump over that statement when running from a scheduler.
I have a search form that has 8 criteria fields, of which the user can fill (from combo boxes and txt boxes) any number of them to narrow or widen the search.This by the way works perfectly. What I want to have is a message box pop up if the user does not enter any criteria and click search.
I tried stringing multiple if isnull statements together but realised that because I have an onload function that sets all fields to "" the isnull function won't work, so have changed this to the Len function.But when I run it, it just skips straight past the len line and opens the search detail form regardless. I suspect I have the order of things wrong, as it does not throw up any errors when run.
I have developed an application using MS Access and MySql tables. The Access forms maintain/display orders that are stored in the MySQL linked tables. The MySql tables can also be updated independently by another process. The problem is that the Access forms do not reflect the backend changes when they are made by the other process.
For the moment I have implemented an on Timer process that causes a requerys for the forms every 5 seconds but this is not very elegant and causes the form screens to flicker.
Is there a better way I can automatically make the front end forms reflect changes to the back-end database when it is updated indepenently ?
I'm wondering if it's possible to create a Query / Macro / VBA or whatever works, to detect the duplicate records in a table and delete them with the push of a command button. Or, even to do this automatically when the database opens using an AutoExec macro or something ?. I'd like for one copy of each record to be left intact in the table.
I have a split Access 2010 database. If the backend file is not accessible (for instance, if our shared folder connection is broken), I get no warning message at all when opening the front end. Just the home screen you would get when launching access from the Start menu. I was thinking of adding some VBA code to my startup form to give users a warning that there is a connection problem, but Access won't even load the startup form.
I have a Table ASSET_COMBO that links to the above tables.
I have a form to create amend an ASSETS table. The form uses combo fields to filter the options available to the user. I.E the User Picks Manufacturer then selects from list of Asset Types provided by that Manufacturer, then selects from list of Models.
I want to create a form to allow create and amend records on the ASSET_COMBO Table.
The problem I have is that I want to ensure that there are no duplicates on ASSET_COMBO Table. I know that I can Select Multiple keys from the table with no duplicates allowed. But from what I have read this can cause problems. Is there a simple way that I can detect that a query on the ASSET_COMBO Table has returned a valid record or has not found a record.
I have a string where alphabetical and numerical characters are mixed up. I need a string function where I can find the index of the first non-numerical character. I tried with the mid function but could not get it to work.
I have a report that is fed by a parameter query. When a command button (Command23) is clicked a parameter box opens and the user enters a subsystem number. A report for that subsystem then opens.
I want to open the report with VBA and show a message if the user has failed to enter a subsystem number in the parameter box and just clicked OK.
The problem is I don't know how to refer to the parameter box and detect if it has been left empty before clicking OK. I first tried to use IsNull in an If statement with Command23.Value, but now realize it is the value in the parameter box I am after, not the "value" of Command23.
How can I change my code to detect if the parameter box has not been filled out?
Private Sub Command23_Click() If IsNull(Command23.Value) Then MsgBox "You did not enter a Subsystem", vbOKOnly, "No Criteria Entered" Else DoCmd.OpenReport "rptTESTInfoBySubsystem", acViewReport End If End Sub
I am importing a spreadsheet on a daily basis that has the same columns in every time but depending on the company I am importing it for different columns will be blank and not needed each time.
What is the best way of detecting and removing blank columns.
1) a delete button (to delete a record) 2) a Search Payee box (that lists all payee names) 3) an SSN text box
I have code in the "On Enter" event of the SSN text box that pops up a message box asking which way to format the SSN (regular or as tax id). This works fine when entering a new record.
I just discovered, however, that when I delete a record then this message box comes up as well and I don't want it to. Instead I want to use the code "srchPayee.SetFocus" after a record is deleted to move the cursor/focus to the Search Payee box so that the SSN message box does not invoke.
I've tried putting the "srchPayee.SetFocus" in events on the form and also on the delete button, but no matter what I do the SSN message box still pops up after the record is deleted. Which event can I put this code in so that after deleting the record the focus moves to the Search Payee box and the SSN message does not appear?
Form1 has a sub form, Form2, which have a tab control with a number of tabs. One of them, Tab4, includes a sub form, Form3.
I have an SQL statement that I want to run (using DoCmd.RunSQL) when Form3 is shown on screen the first time, e.g. when Tab4 activates. I've tried OnClick for Tab4, OnActivate for both Form3 and Tab4 along with a dozen other events with no result. Maybe the problem is that the tab control is also in a subform?
I have a combo box with 'yes' and 'no' as options. The default is 'no'. When a user changes it to 'yes', I need to make several text boxes visible on the form. Q. Would it be better to user the OnChange or AfterUpdate event and why?
Can someone help? I am working on an event database (based on Microsoft 2003 event database template). At present when I register attendees, I can preview an invoice which is generated based on the information I have entered.
I have set up another option to preview a Letter of Confirmation based on this same principal. As I am "Visually Basic" challenged, I simply 'copied & pasted' the event procedure and made the changes as needed to ensure the correct report was opened (ie not the Invoice report but Confirmation Letter report).
However, when I click the button to open preview the letter, the "Print Invoice" box also opens up (as it does when previewing the invoice). I don't want this box to open as I don't need to enter any details.
Can someone please have a look at my VBA event below to see what I would need to delete to stop the "Print Invoice" box from popping up.
Private Sub LOC_Click() On Error GoTo Err_ConfirmationLetter_Click If Me![Attendees Subform].Form.RecordsetClone.RecordCount = 0 Then MsgBox "Enter attendee and registration information before previewing the Confirmation Letter." Else DoCmd.OpenReport "ConfirmationLetter", acPreview, , "[RegistrationID]=" & Forms![Attendees]![Attendees Subform].Form![RegistrationID] End If
Exit_ConfirmationLetter_Click: Exit Sub
Err_ConfirmationLetter_Click: If Err <> 2501 Then MsgBox Err.Description End If Resume Exit_ConfirmationLetter_Click End Sub
Many thanks for any help. Kath Price Auckland, New Zealand
PS - Below is the original 'Preview Invoice' event that I copied: Private Sub PreviewInvoice_Click() On Error GoTo Err_PreviewInvoice_Click If Me![Attendees Subform].Form.RecordsetClone.RecordCount = 0 Then MsgBox "Enter attendee and registration information before previewing the invoice." Else DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 DoCmd.OpenReport "Invoice", acPreview, , "[RegistrationID]=" & Forms![Attendees]![Attendees Subform].Form![RegistrationID] End If
Exit_PreviewInvoice_Click: Exit Sub
Err_PreviewInvoice_Click: If Err <> 2501 Then MsgBox Err.Description End If Resume Exit_PreviewInvoice_Click End Sub
