Modules & VBA :: InStr - Use Clause In Opening A Datasheet Form
Jun 16, 2015
I want to that the WHERE clause for a SQL statement that I am using options on a form to build. I intend to use the clause in opening a datasheet form.
This is the code I have for getting the substring
Code:
Dim intPos As Integer
Dim tempString As String
Dim BaseQueryFormStr As String
'BaseQueryFormStr is used to reopen the BaseMasterQueryFrm with the specified parameters
tempString = "WHERE"
I have read various answers on how to open a form in Datasheet view from VBA code, but I can't get my syntax correct.
Here is one of my 50 attempts to guess at the format. Removing different components, separators, variables only gets me a syntax error of missing separator or "expected =".
DoCmd.OpenForm ("frm_OrdersHOTHeadersOnlyWORKSHEET", [View as acFormView=acFormDS], , , , ,[acFormEdit])
In Filemaker, when you open a table in datasheet view and then select a record you can then open the form relating to this record. Can you do this in Access - I can't get it to work.
I have a check for lots of different data in a string and wondered if i can use CASE or similar.Sample code reads...
If InStr(1, Me.txt_sp, "Give & Take", 1) Then TM = "Standard" If InStr(1, Me.txt_sp, "Give Take", 1) Then TM = "Standard" If InStr(1, Me.txt_sp, "give and take", 1) Then TM = "Standard" If InStr(1, Me.txt_sp, "Give and", 1) Then TM = "Standard" If InStr(1, Me.txt_sp, "priority working", 1) Then TM = "Standard" If InStr(1, Me.txt_sp, "priority boards", 1) Then TM = "Standard" If InStr(1, Me.txt_sp", 1) Then TM = "2 Way"
I've been trying to work up a where clause that is generated by a button click event on a report. The workflow that i'm trying to obtain is as follows:
1) A report is run to determine the remaining work orders that need to be processed. 2) A button that is placed on that report is to be clicked, taking the user to the form associated with that work order, so it can be processed.
What i've been able to do so far is capture the unique ID for the work order and then print that in a message box. I can then open the form.
What i haven't been able to accomplish thus far is to open the form to the correct work order.
Things I've tried : I started trying to use the macro with the search for record option and using the where clause. Not successful. I am a little more comfortable in using vba so i switched to that pretty quickly.
Code: Private Sub btnJobEntry_Click() 'GOAL: open the work order form to the correct entry 'METHOD: store the uniqueID to a variable, then use that in the open command's where clause Dim strJobID As String 'store the unique ID in the variable
[Code] ....
I've put the strJobID variable in both the filter and where clause sections of the DoCmd but it just opens the form to the first entry. I'm fairly confident i'm not applying the filter/where clause correctly by using the incorrect syntax.
In my developing application I am making use of searchboxes to narrow down the amount of records. On a form I have a textbox and a subform with a table connected. In the textbox I can type a character that will be used in an 'Instr' SQL query. I am using the code to query one field. (see code below) In what direction do I have to look to make this code usefull to search through two fields. In my case that will be Tag and Function.
Code:
Private Sub mnu3_txt_UnitbookSearch_Change() Dim SQLstring As String SQLstring = "Instr(Tag, " & "'" & Me.mnu3_txt_UnitbookSearch.Text & "'" & ")" ReReadDescriptions SQLstring
I have a form in datasheet view that has a user directory with a list of each person. On each row you can click to open another form that contains the users details using the below VBA:
But once you open to that specific record, no other records are available to navigate to.
I want to be able to stay on that detail form that was opened and go to another record.
How do I continue to pull up that specific user in my detail form, but also load the others so I can navigate to them from the detail form without having to go back out to the datasheet form?
I'm trying to find some code to allow a datasheet form to link to a csv file.I'd like the link to the data to be dynamic so it connects when the form loads as the access database will be run from a network share by multiple people at the same time.
The CSV file will be different for each user and reside on their local PCs in their profile folders.
I've been trying variations of this snippet of code I found which is called On Form Load:
Code: Public Sub getData(path As String, fileName As String) Dim cN As ADODB.Connection Dim RS As ADODB.Recordset Set c N = New ADODB.Connection Set RS = New ADODB.Recordset
[code]...
setting up and connecting ADO data sources to a form.
I created a form to enter the lookup data and build the SQL string to create a recordset in VBA that is a subset of the original data. Using the Form wizard I created a form that displays ALL of the records in that original table (actually a QUERY). What I want to do is open this second form but have it reference the recordset I built in VBA.
Is there way a to get this audit trail module to work with a form in Datasheet view? It works fine with a form in Single Form view; I can open the table where the audits are saved and everything looks good. But when implemented on a form in datasheet view, nothing happens. I don't get any errors and nothing is saved to the audit table.
Link : [URL] ....
Here's the vba code:
Sub AuditChanges(IDField As String, UserAction As String) On Error GoTo AuditChanges_Err Dim db As DAO.Database Dim rsT As DAO.Recordset Dim ctl As Control Dim datTimeCheck As Date Dim strUserID As String
[Code] ...
You call it in the BeforeUpdate event like so:
Code: Private Sub Form_BeforeUpdate(Cancel As Integer) 'Track all changes made to the record If Me.NewRecord Then Call AuditChanges("Asset_ID", "NEW") Else Call AuditChanges("Asset_ID", "EDIT") End If End Sub
I'm trying to tie a from into the results from and AD Query. I'm not that experienced with doing this kind of thing so I may be going about this the wrong way. Anyway here's what I've got so far:
Code: Private Sub Form_Open(Cancel As Integer) Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Set cn = New ADODB.Connection With cn .ConnectionString = "Provider=ADSDSOObject;Trusted_Connection=yes;"
[Code] .....
When stepping through the code, it all goes well until it hits the line that actually connects it to the form "Set Me.Recordset = rs".
When this line is executed, Access crashes and attempts to restart.
I'm trying to have a form sorted in a particular order when it's opened.
When using the "order by" option in the properties sheet, it is sorted the way I want the first time I open the form.
However, if I change the order afterwards while using the form (for instance, by right clicking and selecting another sort order), well the next time the form is opened, it is sorted with this new sort order, and not the default one.
So I tried to add the following code :
Code: Private Sub Form_Open() Me.OrderBy "employee_name ASC" Me.OrderByOn = True End Sub
I am using Access 2010 and want to open a new form based on an ID from a previous form. Access makes this very easy to do in the macro builder but I am unable to replicate this in VBA afterwards. The reason behind this is I want it to also run some code to export some info to Outlook. I am only filtering the ID from one form to the next. The ID is from a single table.
I am using the following code to check for duplicate tickets when importing multiple records into a datasheet view form by using the paste append function.
Code: Private Sub Ticket_Number_BeforeUpdate(Cancel As Integer) DoCmd.SetWarnings False If DLookup("Ticket_Number", "Record_Store", "Ticket_Number= '" & Me.Ticket_Number.Value & "'") > 0 Then Cancel = True MsgBox "There were import errors, please open View Import Errors above." End If End Sub
The form is used to insert multiple records into the database at a single time.
That codes works to check for duplicates. And if there are none there are no popup messages.
If there are duplicates though it gives a popup for every single Ticket_Number that is a duplicate.
I am wondering if there is a way for it to give only a single popup once it completes checking all the records to be imported for duplicates.
I have a table that has two forms linked to it - one is the Main Form which is used to input the data for the bulk of entries, however on some entries there will be some additional data required which the other form (lets call it Time Form) takes care of.
What I would like to do is have a button on the Main Form that opens the Time Form, and then go to the specific record that is open on the Main Form;I have an ID field (Autonumber + Primary Key) which displays in a text box on the main form, and it would provide the number of the record for the second form to open.
IThe db has a form called ClientFormNew which logs client details into a table called Clients.Each client has a unique client ID in the table called ClientID. Sometimes Clients call back for further information and we want to log that information into a related table called ClientHistory.Each record in the ClientHistory table has a unique ID called HistoryID which is the same number as the ClientID so these fields are related.
When a client calls back and the telephone adviser opens up the ClientFormNew form and discovers they have called before they need to click on a button called Client History which then opens up the ClientHistoryForm..I am struggling with the code to put on the Client History button as I want the new form to display the details of the clients first contact then allow details of the second contact to be added in a new row. Also the new form needs to show all that client past history which would be in the form of the following fields from the ClientHistory table.HistoryID, ContactDate, Name, ContactMethod, ContactReason, AdviceGiven, Notes.
I am trying to program a button on my ms access form to open up an excel file.. So far the simplest code ive found online was from URL....
<code>
Private Sub Command57_Click() Dim xlTmp As Excel.Application Set xlTmp = New Excel.Application xlTmp.Workbooks.Open "C:Excel1.xls" xlTmp.Visible = True
[code]...
However the code doesn't work, any way to open a file from within access?
i have a calender of which show's records on date box's i m having a issue when i double click on a datebox it opens a form call update which is filtered on open to show only records for this date but on date boxs on 1st to the 11th i have add the code to do this but when the form open's the form is blank and shows no records but the code works fine for 12th to 31st which i can't understand why? as im using the same code that does dateboxs 12th to 31st on datebox 1st to 11th but doesnt work i'm pulling my hair out on this one !!
Code: TRANSFORM nz(count(T_qa.qaQAPK),0) AS SumOfQAs SELECT month(qaDate) AS QAmonth, Count(T_qa.qaQAPK) AS QAs FROM Q_ALL_qa GROUP BY Month(qaDate) PIVOT month([qaDate]) IN (1,2,3,4,5,6,7,8,9,10,11,12);
This query is record source for a report, then this report show all calculations in a form.
I have 7 of each (query + report) all showing on the same form.
All those queries calculate data for all departments.
In the form, I have placed a combobox.
What I want is to create a vba code which will add clause WHERE to all queries at the same time and then run it.
Code: WHERE qaDeptFK=Forms!F_CompLvl.cboDeptStats
However, if nothing has been selected in the combo, I want the queries to calculate data as normal, for all departments.
Where do I place the vba statement? Is it under combobox AfterUpdate event?
I am planning to use this: (As I never done it before) [URL] .... Modifying SQL On-The-Fly section
