Modules & VBA :: Writing Code To Open Query After User Selected Fields To Include From ListBox
Jan 14, 2014
Access 2007-10
Listbox created: List62 (I know I need to rename it, but for now)
Multi-select: Extended
Row Source Type: Field List
Row Source: qryFieldList
Open Query: qrySelectedFields (I added primary key to first column, just to have at least one destination field).
Goal: to select multiple fields within listbox, click on command button to open query "qrySelectedFields" with selected fields from list box.This is the code I have on the command_click:
Dim varItem As Variant
Dim strSQL As String
If Me.List62.ItemsSelected.Count = 0 Then
MsgBox "Please select one or more fields.", vbExclamation, "Healthcare REIT"
Me.List62.SetFocus
End If
[code]....
It does absolutely nothing - doesn't add the fields to "qrySelectedFields", doesn't open the query, notta.
I have a form that has four textboxes, date dropdown box and a date box. I am trying to get the form to do the following: Block user from writing in the textboxes if they have not selected a date in the date box.
Get the datebox to look at the behide screens records to see if there has already been a documented date (duplicate date) and inform the user that this date has already been used. Then auto them to the date dropdown.
Is it possible to have the user select which field is added to the query through a parameter? In my query I have a table where each record has a part number and has sales number for each month. When the user runs the query I want them to be able to select which month is returned.
My table basically looks like this:
PartNumber,Jan,Feb,Mar,Apr,May etc. 01PartNumber,15,20,30,25,28 etc. 02PartNumber,12,14,12,16,20 etc
I want the user to enter "Mar" when prompted and return the parts numbers with values from "Mar".
I have a listbox on a form that show all Files for a particular volunteer. I have an "Edit File" button below the listbox to be able to edit that particular file. Is there a way about opening that form to that specific record without using a query?
I have three fields on the form. First one is Combo box - "Contact person", Second - "Code" is Text field, Third - "Department" is the text field. Corresponding to this I have a table "Department "with fields Contact person, Code, Department.
I want that when I select the Name from the combo box, corresponding records like Code and Department should appear in the respective text box.
I have three fields on the form. First one is Combo box - "Contact person", Second - "Code" is Text field, Third - "Department" is the text field. Corresponding to this I have a table "Department "with fields Contact person, Code, Department.
I want that when I select the Name from the combo box, corresponding records like Code and Department should appear in the respective text box.
I have three fields on the form. First one is Combo box - "Contact person", Second - "Code" is Text field, Third - "Department" is the text field. Corresponding to this I have a table "Department "with fields Contact person, Code, Department.
I want that when I select the Name from the combo box, corresponding records like Code and Department should appear in the respective text box.
I have three fields on the form. First one is Combo box - "Contact person", Second - "Code" is Text field, Third - "Department" is the text field. Corresponding to this I have a table "Department "with fields Contact person, Code, Department.
I want that when I select the Name from the combo box, corresponding records like Code and Department should appear in the respective text box.
I am trying to write some fields to an access table definition. It is working for the most part except that I want to write some of the fields as dbText and others as dbDate. For some reason it is writing all of them as dbDate and I don't know why.
Code: additionalColumns = Array("a","b","c","d","e","f") 'add additonal columns to report For i = LBound(additionalColumns) To UBound(additionalColumns) MsgBox additionalColumns(i) If additionalColumns(i).Value = "a" OR "b" Then Set columnNames = xlsht.Cells(1, additionalColumns(i)) Set FieldName = tb1.CreateField(additionalColumns(i), dbDate, 10) tb1.Fields.Append FieldName Else Set columnNames = xlsht.Cells(1, additionalColumns(i)) Set FieldName = tb1.CreateField(additionalColumns(i), dbText, 150) tb1.Fields.Append FieldName End If Next i
I have button on my form that uses a selected record in a listbox (non multi-select) to run code that updates a table. The code itself works great so long as there is a record selected in the listbox. If no record is selected an error is thrown. I'd prefer the end user not to ever see that error. If the end user clicks the button and no records are selected, I'd like for nothing to happen at all. I've tried several variations of checking the listbox with an if / then statement to see if records are selected and if so to exit sub, but they all throw errors when no record is selected in the listbox.
Code:
If IsNull(Me!lstHeatTreatments) Then Exit Sub End If
I'm using the following code to clear selected records from a listbox after a button is pressed:
Code:
For i = 0 To lstProducts.ListCount - 1 lstProducts.Selected(i) = False Next
The code works great except that it leaves the listbox scrolled all the way to the bottom. What is the best way to scroll back up to the top of the listbox without selecting another record?
I am having trouble with a If statement. I am trying to save a record into a table when an item is Selected (highlighted blue) in a list box. Keep in mind that my listbox is set to multiple selections. I have the following code but sometimes it works and sometimes it doest.
The reason why I am trying to get this to work is because when a item is selected I will add it to the table and when an item is deselected I will delete it from a table. See my code and pictures I have attached :
Private Sub list_audits_Click() Dim strsql As String Dim list As String Dim id As String id = Me.User_ID.Value
To start, I am going to generalize to a database of Cows. the fields are:
Name (enforced unique/"Primary Key" in access language?),
size, color, age.
The goal is to have a form with check boxes for the fields that the user selects. Scenario is one cow buyer cares about size, while another may care about color and be curious on age. So rather than guessing at what reports the users will want, have a form that allows them to select the fields they want then print a report. An intermediate step would be a query to filter the data.
See the picture below ....
Also, from my perspective, this is the basis of the best code i would write; if someone wanted to add a column for cow gender you wouldnt have to change all your forms/queries/reports:
Global import table.Cows
Form 2 in picture (form with check boxes): for ( i=0, i<width(Cows), i++) form.create.checkbox cb.append(i) cb.append(i).label(Cows[i])
Then create the report based on cb0, cb1, cb2, etc. queries
Show me the selected item in a Listbox without looping in the whole list, because my Listbox is multiselect and I want only the item that selected recently.
I have an on click event to mail a report which works. I want to change the text to include data from a table.
I changed the code to include the field 'office' from the table 'Checks' but get an error saying 'Object required'.
Code is : Private Sub cmd_mailreport_Click() Dim office As Object Set office = Checks.office DoCmd.SendObject acReport, "checks", "PDFFormat(*.pdf)", _ "info@company.com", "", "", office & " Daily Check - " & Date, "Attached is the report for the office", _ True, "" End Sub
An if statement which will disable out a field depending on which type of field is selected from a separate lookup.I know that is confusing, so let me explain with an example. If I have a lookup field connected to "Vegetables", "Fruits" and "Nuts". If the user selects a "Vegetable" from the lookup field, I need another field disabled, for example sake, "What fruit did you buy?". If the user selected a "Fruit" from the lookup, "What fruit did you buy?" would be enabled.
I know how to enable and disable fields, but it is the If Statement. How would you specify an If statement to specifically look at which category of a lookup is selected?The lookup is not static. So going with our example, the user can add more fruits, vegetables, and nuts as desired, so simply specifying the IDs for the available options will not work. The If statement needs to encompass the category.
With the OutputFile set to "" so the user can select the directory on where they want to store the exported template. I'm trying to figure out if I could get the file path and the file name and store it on a string so I could use it for something else.
What I want to do is... open a file dialog from VBA "This of cause is no problem" the file dialog is opened from a double click function of a textbox holding a link to a picture, what i want to happen is... when the explorer opens I want it to show only the picture that was linked in the textbox.
Example "C:markPhotosPicture 085.jpg"
the code i have so far is as follows...
Private Sub FilePathLbl_DblClick(Cancel As Integer)
Dim fFile As String Dim fd As Office.FileDialog Dim retVal As Variant Set fd = Application.FileDialog(MsoFileDialogType.msoFileDialogFilePicker)
[Code] ....
It opens in the correct folder but it shows all the pictures in that folder.
I have a table linked to SQL Server 2014. As SQL Server 2014 does not support calculated fields I created a query to use formulas. Now I want to write formulas on the existing fields ( TotalMarks ) of table Not to create new fields.
I have a form which contains a listbox. It is a multi-select listbox. And I have a button on the form which runs a report. But I want the report to only show the selected records from the listbox. The report gets its records from an underlying query. But how do I make the selected records on the listbox become the query criteria?
I have a Access table that stores BatchNo,Scandate,NewBatchNo . As I can't attach Access database here so I have exported data from Access to Excel in Sheet1 to show how the data is stored in Access table.
Now I have to write code in VBA that will check the last NewBatchNo in the table . In the attached workbook its 194389. Now vba code should check the NewBatchNo which are blank before 194389 . Now in the records where NewBatchNo is blank , it should add the corresponding BatchNo,ScanDate in the listbox1 in form1.
e.g Sheet2 in the attached workbook stores Bathcno and Scandate of those records where NewBatchNo is null and before the Last used NewBatchNo which is 194389 in our example.
For the life of me I can't get the Multiselect Listbox to correctly pass along all of the item selections to a Query which a form is based on.
I've been up and down the forum, and I can't figure out what piece of code to use and how to use it successfully.
I've been able to get a string created using the example posted here (http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=47909) and it's in the format of "54,67,89,100" etc.
Public Function Public Function fMultiSelect(ctlRef As ListBox) As Variant Dim Criteria As String Dim i As Variant
' Build criteria string from selected items in list box. Criteria = "" For Each i In ctlRef.ItemsSelected If Criteria <> "" Then Criteria = Criteria & "," End If Criteria = Criteria & Format(ctlRef.ItemData(i), "0000000") Next i
fMultiSelect = Criteria gMultiSelect = Criteria End Function
I now need to pass that string to a Query. Once it's been passed to the query, I can open the report based on it.
Essentially I have a button that will perform the string creation, and I would then like to open a report. I want to base the report off of a query and then have the query use Criteria to dwindle down the report.
Am I missing something here?
The long explanation:
I have a single form that allows for the selection of the report. Once the report is selected, certain fields appear that allow for certain criteria to be selected (ie. Class Name, Multi-Select Class Name, Student Names, Multi select Student Names, Dates, etc.)
Once the report has been selected and the criteria set, a user hits a single button that runs the specific report.
Any better ideas of how to set this up. The reports will ultimately be basing their criteria on what the form has in all of it's unbound fields.
I also have a table that specifies the Display Name, actual Report Name for the button to figure out what report to run.
Bottom Line. I want to use a Listbox to filter a report. If I can use a query to base the report off of even better. I don't want to create the SQL in VBA.
I have a query that opens a listbox in a form. In the listbox I want to add a doubleclick event that opens a pdf document with the selected record in it. The record is in column 1 of the listbox.
I can't use the FollowHyperlink method of the colecction application due the record in the column is a Key. I am not sure if FollowHyperlink method can be used in a listbox
I have been checking codes in the web and I found this:
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 ShellExecute Me.hwnd, "open", "D:SebDesktopGA-BDpdf est.pdf, "", "", 4"
However, I don't know how to organize the code in a logic way in Sub End Sub. Neither I don't know if the code is for the doubleclic event in the list box or a Module. If is for a module I don't know how to write the code in it.
This was to dumb to write in the title. But when I am trying to write a code, the drop down, only stays down for a few seconds. Does that mean I am doing something wrong. I am trying to learn to write, but I am about sharp as marble, when it comes to writing.
Further to an earlier post asking how to check if forms were open ...
I have a form EditCompanyForm which pops up to let people edit the details of a company. The form can be opened from one of three forms.
When the EditCompanyForm is closed I want to update the form that opened it.
So I have this rather ungainly bit of code. __________________________________________________ ___________ Private Sub Form_Unload(Cancel As Integer) Dim oAccessObject As AccessObject Dim FormName As String FormName = "CompaniesForm" Set oAccessObject = CurrentProject.AllForms(FormName) If oAccessObject.IsLoaded Then Form_CompaniesForm.Requery End If
Dim oAccessObject1 As AccessObject Dim FormName1 As String FormName1 = "CompaniesCallForm" Set oAccessObject1 = CurrentProject.AllForms(FormName1) If oAccessObject1.IsLoaded Then Form_CompaniesCallForm.Requery End If
Dim oAccessObject2 As AccessObject Dim FormName2 As String FormName2 = "CallListForm" Set oAccessObject2 = CurrentProject.AllForms(FormName2) If oAccessObject2.IsLoaded Then Form_CallListForm.Requery End If End Sub __________________________________________________ _
Do I need 3 separate AccessObjects?
Should I be using ... Set oAccessOjbect = nothing ... at the end?
I have a query table in Access 2007 with requests pending an individuals review and approval. I would like to create an automatic email each day to that individual which includes the actual query table impeded as part of the body of the email (not as an attachment).
