Modules & VBA :: Deleting Values From A Table Selected From A List Box
Mar 19, 2014
Im trying to delete values from a table based on the selected values of a listbox. The listbox values have a hidden column which related to the ID on the table to which I am trying to delete from (if that makes sense).
If I use the following code:
Code:
Set ctl = Me.Results_listbox
For Each varItem In ctl.ItemSelected
CurrentDb.Execute "Delete * FROM Table1 WHERE Table1.ID = " & ctl.ItemData(varItem)
Next varItem
I get an error stating Object doesn't support this property or method.
View Replies
ADVERTISEMENT
Nov 24, 2014
I have database with an userform called AssignWP, combobox called WPDevBy, listbox called List352 (Multi select) and table called Justified.I am trying to update one field WPDevelopedBy of the table as combobox value based on list box multi selected records.
View 2 Replies
View Related
Aug 29, 2014
I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.
Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).
View 5 Replies
View Related
Jul 1, 2013
How do I delete values in an array? I want to reuse that array but first I need to delete its values so I would start with Arr(0) rather than where I stopped off with the last loop at Arr(10) [assuming the last calculation inserted until Arr(9)]
Here is my code:
Set rst = CurrentDb.OpenRecordset( _
"Select * from dbo_ProductStructure where ChildProductNbr Like '*" & txtPartNumber & "*'") 'search associated fields with user input
While rst.EOF = False
ReDim Preserve Arr(i)
Arr(i) = rst.Fields("ParentProductNbr")
i = i + 1
rst.MoveNext
Wend 'end of while loop
x = Arr
View 9 Replies
View Related
May 28, 2014
In appendix is .mdb file with this thema.
Inside you can find one form with listbox (with multi selecting ability).
I use this code :
Dim strSQL As String
Dim i As Variant
With Me.se1
For Each i In .ItemsSelected
SQL = "DELETE '*' FROM [t1] WHERE [id] = " & .ItemData(i) & " ;"
CurrentDb.Execute (SQL)
Next
End With
Me.se1.Requery
Result is this one :
1) If I delete only one item in list, it is ok. I can do it again and again and it is working fine.
BUT
2) If I delete more items in list at once, it is ok - but if I will try to do it again then there is an error because .ItemData(i) value is Null.
View 10 Replies
View Related
Sep 10, 2013
how to get the sum of column 2 of a list box total bags is in the second column, i only want the total of bags of the ones selected
I can get the sum of all the boxes but only want highlighted ones
Public Function SumListBox(sForm As String, _
sCtrl As String, iColumn As Integer) As Variant
Dim frm As Form
[Code]....
View 1 Replies
View Related
Apr 10, 2015
I want to use an array to store data from a list box into a variable. I want it to be able to store one value, or multiple values, depending on what is selected.
Main problem: this list box feeds off a table which has employee names and their e-mails. The list box itself only shows the names, and when I select what I want the array to store is their e-mails, not their names.
Code:
Dim strNames As String
Dim varItem As Variant
Dim intCount As Integer
For Each varItem In Me.lstNames.ItemsSelected
intCount = intCount + 1
Select Case Len(strNames)
[Code] ....
That code successfully displays the item I selected, but only displays the name. I need to make it look in the table and get me column #2. I also want it to be able to select more than one item at a time.
View 5 Replies
View Related
May 9, 2014
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.
View 8 Replies
View Related
Oct 14, 2014
I have a simple listbox (single column, no multi-selection).I want to enable a command button when the user selects an item in the listbox / disable it if no items are selected.I'm using the AfterUpdate event of the listbox, as follows :
Code:
Private Sub lstOptions_AfterUpdate()
Select Case Me.lstOptions.ItemsSelected.Count
Case 0
Me.comConfirm.Enabled = False
Case Else
Me.comConfirm.Enabled = True
End Select
End Sub
But when I select an item from the listbox, and debug the code, the Count is always zero? Even though I can see the item selected??
View 2 Replies
View Related
Oct 13, 2014
I have the code below and am trying to have a form allow Text Box to become visible based on selected items from a List box. Why I am getting the error listed?
Compile Error: Invalid Qualifier
Code:
Private Sub specific_opt_Click()
Dim users As Control
Dim ctrler As String
Dim xx As Long
If Me.specific_opt = True Then GoTo 169
[Code] ....
View 8 Replies
View Related
Nov 23, 2006
Can some one tell me why, on the attached database example, I can select multiple items from list boxes
"TipoCliente" and "FaixaEt" through "frmClientes" form but they are not passed forward to table "tblClientes" ?
Try to onpen attached database example and use form "fmrClientes" and select multiple items at
"TipoCliente" and "FaixaEt" list boxes by entering them with mouse click and Ctrl key pressed .
Select also single item from
"NivEns" Combobox and write anything on "Nome" and "Apelido" fields .
Go to the table "tblClientes" and you can see text fields "Nome" and "Apelido" and also the item "NivEns" from Combobox "NivEns" are all there but not the items you selected from "TipoCliente" and "FaixaEt" list boxes .
What is happenig ?
Lots of thanlks
Miguel
View 2 Replies
View Related
May 1, 2014
I am building a tracking database where we would be able to track information which field are null data and report them to our Administration to fill the null data. For this I have created a form name "Search" and I have a combo box control on that form which is bound to "Table = employee" and its ROW SOURCE TYPE="Field List", I would like to able to query records where the selected value in this combo box is null through out the table.
For example if I select "Telephone" from this combo box dropdown, I would like the query to show all the records where the "Telephone" is null, how to set the criteria in query to take the combo box value as "Field Name" and then compare it with the Field/Column in the table and show the null values.
View 4 Replies
View Related
Jan 28, 2015
I have a form full of cascading unbound combo boxes which allows me to assign companies and people to a project.I have a save button which writes the selected values to a table.To view the assignments when the record is later viewed I have placed a bound field behind the combo box.
View 7 Replies
View Related
Sep 23, 2014
My Membership Database includes 3 fields in the main Table which I need to clear prior to the new subscription year, but on a selective basis.The fields are R/N for (Renewal or New), a subscription date and an amount field (Currency).
At the due date I need to clear all 3 fields except where N/R=N AND date >01/01/2014, when data in all 3 fields is to be retained.I have tried several SQL codings to achieve this but have been unsuccessful so far.
View 6 Replies
View Related
Feb 20, 2014
I am trying to delete a record from a table and when I pass the variable as a text value it works but when I pass as a number I am getting a mismatch error.
I have to use it as a number as I am doing other update code in my database and it is a number.
Code:
DoCmd.RunSQL "DELETE * FROM TblIssueData Where tblIssueData.SerNum = ' & Me.txtserNum & ';"
When I am using TblIssueData SerNum as a text variable in table it works but when I specify SerNum as a number in the table it gives me data mismatch error. I have to leave it as a number for other VBA code in my database. I believe it is just a syntax error but not sure where to go with it.
View 2 Replies
View Related
Jun 30, 2014
I was able to create a very user-friendly pop-up search form : {URL] .....
I would like to take it a step further... Now, I would like to grab the selected record ID from within one of the list box columns and use it in my SQL statement (to copy the record into a new one). Is there any way to reference a highlighted record value within a list box recordset directly and/or any workarounds?
I know one would be to grab the ID and insert it into the text box, just don't know what the coding references would be for this.
View 4 Replies
View Related
Sep 25, 2014
I have a combo box which gets its values from sql server using a query which is called "get_query_reason", which works fine. Now I want to update combo box values based on a user selection, st string. Have written the code, but does not work:
Dim qDef As QueryDef
Dim Query As String
Dim st As String
Dim rs As Recordset
st = "SOV"
Set qDef = CurrentDb.QueryDefs("get_query_reason")
[Code] ....
View 2 Replies
View Related
Aug 12, 2013
I would like passing values from first form until third form.
In the first form I have a list box after selecting items (For each selected item in first form I have 4 values) and pressing button (or right click of mouse) the second form will be open, then in the second form I have 2 option (inserting, deleting), when I select inserting or deleting in the second form, third form will be open, in the third form there is a "OK" button, when I press that, passed values from first form will be used for inserting or deleting records to the table.
View 4 Replies
View Related
Jun 25, 2014
I am trying to format a spreadsheet to import into a Table. To do this I need to delete the top 8 rows and then the 4 rows below the data I need, both areas contain header data. The 4 rows below the data I need are blank but formatted oddly and it is causing issues during import.
Code:
Sub ExcelFormat()
Dim excelApp As Object
Set excelApp = CreateObject("Excel.Application")
excelApp.worbooks.Open ("Z:DataTest.xlsx")
End Sub
View 11 Replies
View Related
Jul 23, 2015
Situation: 3 tables. Manufacturers, Countries & Provinces/States. 1 Form for data entry in Manufacturers.
Countries table contains ID, Country and CountryCode fields. i.e. 47, Great Britain & GB
Provinces/States table contains ID, Province/State and CountryCode. as in 1, Alaska and US.
In the form the country is easily selected from a list refering directly to the Countries table.
Problem: How to make a list in the form from which the user can simply select the province for the country that has previously been selected. And not a list with all teh provinces and states from every country in the world. (This would be a really really long list...)
View 3 Replies
View Related
Dec 29, 2013
I am trying to use a list-control on a form to let the user select multiple values. I have understood that this requires some VBA-code to step through the selections in the list, since the "multivalue-selection" is set to "Extended".
When I try to execute the code I have (found and have tried to adjust), then I get the error message "Object required". The "ListCount"-paramater always only results in a ZERO-value, when i step through the code:
Function cmdOpenQuery_Click()
On Error GoTo Err_cmdOpenQuery_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
[code]....
View 9 Replies
View Related
Nov 22, 2013
I have several result fields which are all drop down lists. I want each result field's drop down list values to be different depending on the selected value of the Test1 drop down list.I came up with using the .rowsource keyword. My syntax seems to be fine but I'm not getting any values under the result fields when I run the form.Here is my code so far:
Private Sub Test1_AfterUpdate()
If Me.Test1 = "Stress Echo" Or Me.Test1 = "Stress SPECT" Or Me.Test1 = "Stress PET" Or Me.Test1 = "Stress MRI" Then
Me.Test1Result2.RowSourceType = "Value List"
Me.Test1Result3.RowSourceType = "Value List"
[code]...
View 14 Replies
View Related
Jun 22, 2013
I am interested, is it possible to read currently selected (highlighted) row in table?
I know that this is possible to move cursor on concrete field in table via VBA but is it possible to read record number when we move cursor manually (via arrows)?
View 13 Replies
View Related
Jun 29, 2013
I am attempting to insert a record with selected data into a temp table and I am getting "Run-time error '3075': Syntax error in (comma)...". Here is the code:
Code:
Private Sub XferDataToTempTable()
Dim db As Database
Dim strSQL As String
Set db = CurrentDb
[code]...
View 7 Replies
View Related
Aug 6, 2014
In my form's table (tblMain), I've got a lookup field (drop-down list) that lists the primary key field from a different table (tblDiff). tblDiff includes 3 more fields. In my form for tblMain, I want to include 3 more textboxes that get filled up with these 3 fields from tblDiff when the corresponding primary key is selected in the drop-down box.
View 9 Replies
View Related
Aug 9, 2005
I have a form that has a list; the list is retrieving some names from a table.
e.g
Table name is “ITname”
and I have a filed named “name” with the following: john, peter, mike, rock.
On my form under my list I have a delete button, I want to the button to delete the selected record from the list, how can I do that?
Adding, I also have a textbox and a button which will add a new IT which is inserted in the textbox, how can I do that?
Any help will be appreciated.
Thanks.
View 8 Replies
View Related
