Modules & VBA :: Delete From Table Based On List Box Selection
Nov 21, 2013
Upon the results which populate my list box what I am trying to do is allow the user to select various values from the list box and then hit a button which deletes the values from the table. My code is currently:
Code:
Private Sub Del_TsCorrection_tbox_Click()
Dim lngID As Long
Dim strSQL As String
[code]...
But it doesn't delete, infact it doesn't do anything. The list box values are still in the list box and if I look in the table they are still there also.
I have a form with a Listbox which lists Customers and a variety of customer information
I also have a table on the form that lists the donations that customers have made.
How can I populate the Table based on the customer that is selected in the Listbox?
There is an ID field that links Customers with Donations
I'm guessing I need some code in the ListBox_CLick() event that triggers the table to query or to Load with a query based on the ListBox.Column(0) which contains the ID?
I have a table (ComponentT) of different device components a user can select from ( generators, gearboxes, etc.)
The table has 3 fields - ComponentName, ComponentType, ComponenentDetails
I have a query returning all the ComponentDetails values.
I then have a combobox that uses this query as a row source.
The user can select different components they want from the combobox. Once something is selected, it is added to a new table - UserSelectedComponentsT
As a device can have only one gearbox or one generator etc. I want a warning messagebox to be displayed if the user tries to select a generator from the combobox when one has been previously selected.
I'm making a database that so I can log calibration information about equipment every year.I have 2 tables:
The Equipment details table The Calibration record table
ID Number is shared between the 2 tables so a calibration record can be linked to its' details.
For Example (simplified sample data):
Equipment Table ID Item Unit Type 104 Thermometer DegC PT100
Calibration Table ID Cal Point 1 Test Equipment Unit Under Test Date 104 20 21 22 06/01/15
What I want is a button on a form that creates a new blank record in my Calibration record table with the ID number already entered based on what record I selected in a combo box linked to my equipment table. I really don't know where to start.
I have a form that contains a list box with 11 separate options. Within this list is the option named "None". My desire is to have a message box displayed when any option is selected other than "None". I have searched various threads, however I have been unsucessful nailing down my issue. Thanks in advance for the assistance.
Dear all, is it possible to run a query (or ideally a report) based on a selection from a drop down list in a form? (in accsess 97). The reson being is i have a big list of of codes for different absence reasons & at present the user has to type in the correct code to display the data in a report based on the code they enter, however its not easy for users to remember which code to type in to run a report.
Idealy what i would like is a drop down list with the absence codes & the absence desciption next to each code, then when the user selects a code from the list it displays the corresponding data in the report?
Is this possible?, any help would be much appreciated, thanks.
I have a query that allows the user to put in a date range - this works.He also wants to limit what he prints.
For example at the moment he gets
OrderA rest of line 1 for orderA OrderA rest of line 2 for orderA OrderA rest of line 3 for orderA OrderB rest of line 1 for orderB OrderC rest of line 1 for orderC OrderC rest of line 2 for orderC OrderD rest of line 1 for orderD . . .OrderZ etc
He only wants to say print lines for OrderA and OrderD. It's he's choice and not based on anything else already in the table.
I see that in the criteria on the design for the query it allows me to bbuild an expression and I can select a list box on the form such as
[Forms]![name of form where list box is]![name of list box].[EXPRESSION VALUES]
I don't know which expression value to choose. I have tried afterupdate and beforeupdate but none of the rest look like they are the ones I should be using.
I'm building a customer form using a list box of summary customer info on the left side of the form (CustId and Names) and customer details on the right hand side. I want the details shown on the right to be those for the customer selected in the list box on the left. I'm having problems getting the details to update when a different record in the list box is selected or clicked.
I originally set the data source for the detail records to the base customer table but couldn't work out how to update them when the list box was clicked!
I then tried creating a query with the select clause conditional on the list box (CustId = Me.qryContactDetails) and set the data control source for the detail record to the query (=[qryContactDetails]![FirstName]). The query works as expected but the field in the form just shows '#Name?'.
I tried adding a field requery on the List31_Click() event using Me.[FieldName].ReQuery but that didn't seem to change anything
I have a form whereby a user for my database can be created. The fields required are listed below:
Payroll ID Forename Surname Location Manager
The last two fields are related to two other tables; tblLocation and tblManager, which are related to one another.Each location (there are three) can have more than one manager, however, a manager can be associated with only one location.I would like whomever is creating the user to only be able to select a Manager that works at a particular location, currently, I can select LocationID 1 and ManagerID 3, but ManagerID 3 works at LocationID 2.
How can I base the 'Manager' field in my form on the previous field's selection, e.g. 'Location'.Also would need to restrict the order in which the form's fields are enter, i.e., 'Location' before 'Manager'.
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.
When creating a query I have set up a parameter query as instructed (in this case searching for a name) but when I run the query it returns the error 'this expression is typed incorrectly etc.'
I have worked out that this must be due to the fact that the names are not typed in by a front-end user but need to be chosen from a dropdown list (which i have stored in a separate table) - So, the query seems to fail based on that.
I have a form where I add a new event, and it gets added to a list box in another form, I was intending to select the most up-to-date record within the list box but it seems to keep selecting the old latest record and not the new record that ive added. I had tried to select the first record but then move to the last record hoping it would refresh but that still doesn't work.
This is the code I used below:-
Quote:
'Select last event record added to event list box Me.lstPatientEvents = Me.lstPatientEvents.ItemData(Me.lstPatientEvents.L istCount - 1) lstPatientEvents.Selected(lstPatientEvents.ListCou nt - 1) = True
Dim dbs As DAO.Database, sql As String, rCount As Integer Set dbs = CurrentDb sql = "DELETE * dbo_InvPrice Inner Join (dbo_InvPrice Inner Join UpdatedPricing on dbo_InvPrice.StockCode = UpdatedPricing.StockCode ) ON on dbo_INvPrice.PriceCode = UpdatedPricing.PriceCode " dbs.Execute sql, dbFailOnError
I have a form that allows me to change the information for a list of personnel. On this form I have a list box that displays all the personnel in the list. There should never be more than 12-15 people in this list so it's an effective way of selecting records, however I can't figure out how to make the form select the record that is tied to that person from the list.
In VBA I've tried creating an "On Click" command for the list that, when a name is selected, is brings up the corresponding record. I thought I could use a DoCmd.FindRecord but I guess I don't quite know what I'm doing. I didn't want to use SQL to limit the records to just the one that matched I wanted to keep it simple if possible.
Code: Private Sub Personnel_List_Click() DoCmd.FindRecord Personnel_List, , True, , True End Sub
The Personnel_List box is tied to the Personnel_Table, and the table only displays the names available.
I have 1 combo box contains 2 columns look-up directly from the properties(Not VBA)
Now i want to select record from the list
Example: i want to select PM-1234-1111 so i dont want to type starting letter PM to select but i want to type 234 or 123 or somewhere in the middle or end to filter that contained text in all the items is it possible?
I am trying to get my list box to filter based on the selection of a combo box.
My Combo box cboOrgRole is on my main form and is bound to OrgRoleID - on form - frmOrgEntry
The unbound list box lstRoleList is in a tabbed subform - frmPersonnel
This is my data pull for lstRoleList
Code: SELECT tbl00PersonRole.PersonRole, tbl01Orgs.OrgRoleID FROM tbl00PersonRole INNER JOIN tbl01Orgs ON tbl00PersonRole.OrgRoleID = tbl01Orgs.OrgRoleID WHERE (((tbl01Orgs.OrgRoleID)=[Forms]![frmOrgEntry]![OrgRoleID]));
This is the code I have on cboOrgRole AfterUpdate
Private Sub cboOrgRole_AfterUpdate() Me.frmPersonnel.lstRoleList.Requery End Sub
I have also tried Recalc - both throw a Compile error: Method or data member not found
My goal is to be able to select the role of the organization, (General Contractor, Architect, Engineer, or Client) and have it list the available titles for the specific organization type.
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...)
I am trying to generate an email that will send a copy of the last record entered from my table "Main" to a departement email, based on the department field entry on my form.
I know that I can send emails both thru outlook as well as direct. Would prefer to sent the direct (without opening outlook). Here is what I'm working with:Table with record informaiton called "Main"
Form called "Action Entry" which contains the information that I would like to send- part of which is a feild called "Assigned To" which is a list of departemnts linked to a secondary table called "departments"
Table called " departments" which contains the following Fields: ID, Departments, Email.What I would like to happen is when I hit the exit button on my form, an email goes out to the "Assigned Department" email address associated to the select departmet, that shows the information in the form (which i expect will be a report saved as PDF). I understand some of the VB code to create an email, but don't knwo how to tell it to select the correct email based on the Department selected on the form.
My table has a yes/no field and about 10 records. I have created a form for the user to select which of the records he wishes to use - the records are displayed in a continuous form with a check box for the yes/no field. The user is to select one record by checking the check box in the record.
I know this should be obvious, but I can't see it. How can I make it so the user can only select one of the records (when he clicks on one, that one is yes and the others revert to no?
I have a form with a drop down list, when the user selects an option and press a button. I would like to add the value of the drop down list to a table as a new record.
So far I have a Multi Select enabled list on which the user selects the serial numbers they want. They then click the "Report" button which will trigger a query based on the selected serial numbers to create a report on those serial numbers.
I have the code for the multi-select list working already. It creates a string of comma separated values that are the serial numbers which are selected in the list. Somehow I need to pass this string to my query so it can use it as a filter.
Here is some of my code:
Code: Option Compare Database Option Explicit Private Sub Form_Current() Dim oItem As Variant Dim bFound As Boolean
[Code] ....
Here's my current query in SQL:
Then finally how to I get the query to execute and create a report based on all of this?
Is there a way in which someone can select a table from a drop down list and then search that table for a record then move that record to another table.
For example.
User selects 'Mikey's_table' searchs for a record then move this selected record to 'Mandy's_table'
(all the tables have the same structure etc. identical apart from the name of the table and records within)
I have the list of tables that all the records will be on and the users will know which table the record is in, i basically need to know if there is an ability to search for a record over multiple tables then edit that record and move it to another Table.
I have tried to use a Union Query which works when searching but i cannot edit or move the record ...
I am designing a nomination form (web database so no vba macros can be used).
The form has 3 combo boxes: cboStaffName, cboLevel, cboNominee.
The form is bound to the tblSubmit table where the submissions are populated.
I used a select statement:SELECT Staff_List.Staff_Name FROM Staff_List; to populate the combo box for the Staff Name selection.
This is the select statement to populate the job level combobox:
SELECT Staff_List.Level, Staff_List.Staff_Name FROM Staff_List WHERE (((Staff_List.Staff_Name)=[forms]![frmtest]![cboStaffName]));
The select statement to populate the combobox for the nominee combobox:
SELECT Staff_List.Staff_Name FROM Staff_List WHERE (((Staff_List.Level)=[forms]![frmtest]![cboLevel]));
The problem is that the staff_name in the first combobox is still found in the nominee combobox which should not be because a staff cannot nominate self. There is a field in the Staff_List called YesNo that should be activated for each staff that is selected so that the select statement on the nominee combobox can be updated accordingly to remove items with the field "Active"
How to get the checkbox selected for each corresponding staff.
I have an unbound combobox on a form that is used to filter the records of a subform based on who they have been assigned to. The combo box is based off of a query to the personnel table and utilizes a UNION querry to add the option "**ALL**" with a id number "111111" as the first option in the combo box.
In other words: the combo box has a list of names to choose from and the word "ALL" at the top as the first selection. The idea is that when "**ALL**" is selected, the subform should display all records regardless of who they have been assigned to. I'm trying to accomplish this with VBA. I've started developing the code to try to impliment this, however currently when "**ALL**" is selected, I'm getting a datatype missmatch that seems to be caused by my use of recordset.