I have a database which connects to another application (by creating an object) for the purposes of reading/writing data. Part of the code involves creating an instance of this application, and then taking input from the user via an Access form.However, when I load the form, the focus stays with the other application, and the user has to 'click into' the form in order to reset the focus to the form and key the relevant details. Not the end of the world, granted, but frustrating over time and something I'd like to iron out.
I originally had this process in Excel, and used the following code to change the focus back to Excel (so that the user could key directly into the form)
Code:
Dim ExcCap As String
ExcCap = Application.Caption
' ...assorted code, including creating the object to 'house' the other application...
' Return focus to Excel
AppActivate ExcCap
But this doesn't apply to Access as it utilises the Application.Caption / AppActivate method to identify the appropriate window to switch back to. In Access, I don't have a caption to reference.I've tried setting the focus in the Form_Load event but it doesn't bring the form 'to the fore'
I have a simple UDF that takes a string and returns a variant, which is an array of strings Example Input "Brick Wall" Return value would be a variant array with first element "Brick" and and second element "Wall" Now I have a table with a field of strings, and I want to make a query that returns all the results from the function, one per line.
So if my input table looks like this
[strField] "kick the ball" "return the pass"
my query result should looks like this
[Orig] [new] "kick the ball" "kick" "kick the ball" "the" "kick the ball" "ball" "return the pass" "return" "return the pass" "the" "return the pass" "pass"
Last time I had to do something like this I used VBA exclusively, with ADO objects, but I thought a query based solution would be easier.
With my current data the largest return array size my function returns is 27 elements but I wouldn't want to rely on that number being fixed.
I have an Access 2010 application that talks to SQL Server. Part of the application creates tables on the SQl Server and inserts data to it (I know SQL Server has its on data inport/export wizard and bulk insert capabilities but for reasons outside of my control it needs to be done from Access). So I have a connection to the SQl Server and i have VBA that happily executs SQL statements on the SQl Server. The problem is I want to return a recordset from the result of a SQL query issued to the SQL Server. The idea being to check for the presence of tables already created, and if so, append to the already existing table, otherwise if it does not exist, create it and insert data to it.Ive tried the following code but it seems no recordset is being returned:
Set objConnection = New ADODB.Connection objConnection.Open "DRIVER={SQL Server};SERVER=10.200.3.14;trusted_connection=yes; DATABASE=" & Me.Combo54 strSQL = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = '" & Me.Combo54 & "'" strSQL = "USE " & Me.Combo54 & " " & strSQL Set Rst = cmd.Execute("USE " & Me.Combo54 & " " & strSQL)
I have a continuous form populated with a pivot view from SQL. When I enter data in the form I send the data to SQL (ctrl after_update) and then re-populate the form. It works.
When I have entered data in a ctrl - then I have to bookmark the record so I can select it again (find_first). I can "save" the location of the control where I have entered data, but I want to select the location where the user has set focus - which triggered the ctrl after_update..My ctrl after_update looks like this:
I'm trying to catalog these shelves for work. So someone can then search for the item and it will pull up the shelf its on. The shelf has alot on it so I want to set the focus on the item on the shelf. But I'm not too sure how to do that with my search button.
This is the code for my search button:
Private Sub cmdsearch_Click() 'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter. 'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _ we remove the trailing " AND " at the end. ' 2. The date range works like this: _ Both dates = only dates between (both inclusive. _ Start date only = all dates from this one onwards; _ End date only = all dates up to (and including this one).
I am new to Access and to the forum. I made a check printing system
table name = 'burgan' form name = 'burgan cheque' Fields = 'PV' and 'cheque' and 'Beneficiary'
I have inserted a button (Command31).The function I would like to add on clicking the button
1. it should chose the highest value in both fields (PV and cheque) 2. Create new record 3. Increment it by 1 (both fields 'PV' and 'cheque') 4. the focus should stand on 'Beneficiary' field.
I'm populating a report with a query which pulls criteria from a form. When the 'run' button is pressed it opens the report, running the query, to filter the data. What i'm attempting (and it works if there is data present).
The data is text, which is a filename, which populates an image control. Most of my records have an image present but for the ones that don't I think I need to turn the image control's picture property to 'blank'.
I'm just now encountering problems with the records with no pictures so when i came up with this it worked with my tests which at that time only had images present....
I have two problems.
1) When I run the code as below i get Run-Time Error 2185; you can't reference a property or method for a control unless the control has the focus.
2) when i try to set the focus on the picture control in the report to see if there is text/value present i get runtime error 2478; database doesn't allow you to use this method in the current view.
I assume this is talking about me opening the report in acViewPreview mode but i thought i needed to do this so the images are displayed in the image control.
Code: Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) Dim strDBPath As String Dim strRelativePath As String Dim strPath As String 'rptOriginalOwnerCategoryItem!Picture.Text.SetFocus [B]Me!Picture.SetFocus[B] 'Test to see if the record has a relative path stored
On a form (default view: Single), I have a bunch of bound controls that display one record at a time from tblMain. I have a bunch of unbound controls as well (buttons).
If the user tabs through all the controls, reaches the last one (btnLast), and presses tab again, the focus is set to the first control (btnFirst) and the bound controls change to display the next record. I just want the focus to be set to the first control without the displayed record being changed.So I set up an "If" statement in Private Sub btnFirst_GotFocus()
Code:
If Screen.PreviousControl.Name = "btnLast" Then [code to change record back to previous record] End If
But btnFirst gets the focus when the form opens, so Screen.PreviousControl spits out an error.I've accommodated this like so:
Code:
On Error GoTo ErrorHandler If Screen.PreviousControl.Name = "btnLast" Then [code to change record back to previous record] End If Exit Sub ErrorHandler: Exit Sub
How to set focus to a non Default instance of a form.
Environment A2007 ADP Project Document Window Option - Tabbed Documents MS SQL 2012 Express DB Windows 7 64 Bit
I using Allen Browne's method to open more that one version of form, storing each form object in a collection declared in a module. No problem there.
Now I'm trying to add a command button on an form to set focus to one of these non-default instances already open.
The form I am trying to set focus to has a the following related properties
Default View: Split Form Pop-up: No Modal: No
The form that has the command button on it is of the same type.
Here is the code I've tried:
'Code on Calling Form Private Sub cmdProjectList_Click() Dim FunctionResult As Boolean
If AppForms.GoToForm("ProjectList") = False Then AppForms.Load_ProjectList
[Code] .....
The code compiles and executes with seemingly no problems. It finds the form loaded, then cycles though and finds the form in Forms but the SetFocus call seems to do nothing. When I run the code against a defualt instance ( one not opened using Allen Browne's method) it works fine and sets focus to it as expected.
I'm using a label as a button so it looks nicer, but if I press it without officially exiting the last text field I was in, then that text field doesn't update, so the vba believes it's blank or whatever it was.
I could manually setfocus to a couple different fields or have a teeny field thats hard to see to set focus to, but these both seem roundabout.
I have a tab control on my form. In the change event of the tab control I am trying to tell it to move to the first field on each page of the tab control whenever the page no. changes:
Private Sub TabScreenAssess_Change() 'when a new page gets the focus, set the focus to the first control on that page Dim PageNo As Integer
PageNo = Me!TabScreenAssess.Value Select Case PageNo Case "0": gynFemale.SetFocus Case "1": tbPriorHx.SetFocus Case "2": suSubUseAssess.SetFocus Case "3": mhaCognitiveFunct.SetFocus End Select
End Sub
This works when I am tabbing normally through the controls. But when I click back to the first tab, it says "Access can't move the focus to the control gynFemale" (gynFemale is the first control on the first tab.) It works for the other tabs. That is when I click on another tab, the focus goes to the first field on that tab. When I enter a value in the gynFemale field (it is a combobox, with options Yes or No), sometimes it appears to save the value, then I discover that it has been saved as record #0.
I'm looking for a bit of code for a module that will return the field name from an expression (that uses an array) instead of returning the value in that field.
Example: (Minimum is a working module that finds the smallest number in the array) Field1 = 34 Field2 = 53 Field3 = 21 Field4 = 62
I have a code that finds the first, second, and third minimum value in a row across the fields. Now, I am trying to find a code to look at these values, find the field it is located, and return the field name. I tried several variations of my code to return the field name rather than the value, but have been unsuccessful to this point.
Function NthMinimum(intPosition As Integer, ParamArray FieldArray() As Variant) As Variant Dim varTempArray() As Variant, varTempValue As Variant, intArrayValues As Integer Dim I As Integer, J As Integer ReDim varTempArray(UBound(FieldArray)) intArrayValues = 0
[Code] ....
As you can see, this works to find these values while ignoring NULLS. How to return the field name?
How i can cancel the a carriage return and replace with a tab or stop it moving down my subform and make it move the the next field instead of going to a new line.
I am using the Keydown but don't know how to cancel it, it just bring an message but it stops it moving to a new record,
Code:
Private Sub Barcode_KeyDown(KeyCode As Integer, Shift As Integer) If KeyCode = vbKeyReturn Then MsgBox "rtn pressed" End If End Sub End If
I'm new to Access and VB but I managed to write parts of what I want access to do.By tweaking a code I found on the internet, I managed to write a small Subroutine that allows the user to click on one of the fields of a record in a report and then Access opens the form on that specific record.*My code goes something like this:*
Code:
Private Sub Edit_Click() * Dim strWhere As String * Dim DocName As String * DocName = "FormName" * strWhere = "[Field Name]='" & Field & "'" * DoCmd.OpenForm DocName, acNormal, , strWhere End Sub
Now I want to get the name of the Table where the record exists.So, let's say when I click on the "Field" it gets the name of the table where the record with that field exists and sets it in a variable.OR even better would be, get the name of the Form where that record exists but I guess that's a little more complicated since the record is directly linked to the table...*
what is the best/cleanest way to return the next primary key value which is in the format yyyy-###.These are case files for the current year, ### being 3 digit number incremented by one for each new case.
I could use a function that finds the last one, checks the date, increments by one and returns it, but maybe I could use a public property or a class? I'm keen to use classes more for code maintenance.With a class then: would i create a PrimaryKey variable of the class type/object, so when I need a new key I would just type PrimaryKey.NewKey, for example?
I have a query with a Start Time where the need to return a set integer in another field in my query. I am attempting to get this to work in my StripSecondsQry.
I am not getting any error messages and I am not getting any output, When I view this in the Locals window I can see that it should be returning 7, but instead I get nothing unless I change it to
Code: Function SortStart(StartTime As String) As Integer
then I get zero.
I had this working within the query, but I had to add one more time and then received a message that the expression was too complex.
Basically I want to get the ID (a number) from a specific record where the JOB NUMBER equals the string I have typed in to a field on the form, also called JOB NUMBER.
However, my problem is that it doesn't navigate to the record where the criteria matches, it just chooses the ID from the very first record of the table.
