Hi,
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?
Can anybody how to detect/check it?
Thanks.
hi everyone, i sort of run into a dead end trying to figure how to structure this. I have three tables for the 3 roles, tblClients, tblDoctors, tblBusinessContacts, each with a Primary key and some unique information about each role. I'm not sure how i can track appointments to each of these roles, i know how to do it if it was one role. I want to be able to later view all appointments for that day with all 3 of these roles.
Also, for clients i have a multiselect listbox describing their reason for the appointment and somewhow would like to associate that to the appointments. Some info i would like about these appointments:
1. date and time
2. details of contact
3. date for next appointment
What would be my best bet?
Thanks!
Joso
I use Environ function to detect the user name.
But, do we have function to detect the full Employee Name?
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.)
Thanks in advance for help.
VINCENT.
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
How can I modify the code to do this?
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;"
Forms!Querypurpose.RecordSource = SQL
DoCmd.OpenForm stDocName, acFormDS
Exit_Command59_Click:
Exit Sub
....
***
I have a subform that collects the following data to put in a record in tblClass:
StudentID, Trimester, SubcatID, WorkGrade, SkillGrade
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.
Any suggestions would be great.
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?
Thanks...
Hey,
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.
Code:
Private Sub Form_Load()
Me.PCNumberIn.Value = ""
Me.SerialNumberIn.Value = ""
Me.DeviceTypeIn.Value = ""
Me.DeviceModelIn.Value = ""
[code]...
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.
View 8 Replies View RelatedI 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.
View 8 Replies View RelatedI have ASSET_TYPE, MANUFACTURER and MODEL tables.
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.
View 13 Replies View RelatedI 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.
Using A2002 or A2003, trying to convert an app from Jet to SqlServer
i am using DAO rather than ADO
this statement causes an error
Set rs = dbs.OpenRecordset("tblconstants", , dbSeeChanges)
the error is
error 3622 - must use dbseechanges on a sql server table with identitiy bit set
alternatively
Set rs = dbs.OpenRecordset("tblconstants", dbOpenDynamic)
produces error 3001 - invalid argument
(Note: i've got another app, in A97 that works with sql server tables without any additional parameters )
Set rs = dbs.OpenRecordset("tblconstants") works in A97
------------------
any idea what do i need to do please
Hi. hope you can understand my english.
I have 2 SQL Servers.
Is there some way to set to which sql server connect the .adp (or .ade) via code??
Now I have 2 sql servers (development and production).. but soon I will have more server at production in different cities. All SQL Servers have different names. Same databases names but the servername are all different.
So I have to configure the connection of the .ade in the the development server. Then when I want to send and upgrade to one of my clients, I have to go to his city and manually reconfigure the .ade to connect to his SQL Server.
This problem would be solved if all SQL Servers had the same name, but this is not my case.
It would be nice to have a config.ini file or something like that where the .ade could read the name of the server to connect to.
Is it possible to implement something like this.
Thanks in advance.
Hi, i am looking at making use of SQL Server which we have at work to run our access Database from, is this a good idea ?
what are the benifits, problems etc...
Andy
I am trying to insert a picture in a field OLE object, but after I have selected the picture I would like to insert, I get the following error message :
A problem occured while MS Access was communicating with the OLE server or ActiveX Control.
Can someone help me to resolve this problem ?