Modules & VBA :: Using Records In Listbox To Add New Record To Table?
Mar 27, 2014
I'm using access 2010. I want to put some code behind a button that will allow the user to add selected products in a listbox to which ever operation is selected in a combobox by creating records for each product / operation combination in a third table:
I have a form with a combobox on top that allows the user to select the operation for which they want to add products. There is also a listbox that displays all records in tblProducts. The user can select one or more products and then the idea is that they can then press a button that will use the selected record IDs from the list box and the record ID from the combo box to create new records in the many to many table.
I have a table with a multi-select listbox as one of the fields. I want to loop through the recordset (table) and changes the listbox selections for each record.
To go into a little more detail, the table (tblEmail) has a field (Label) that is a multi-select listbox. The listbox pulls from another table (tblLabel). I want to loop through records in tblEmail and edit/change the Label(s) for each email though VBA.
I've tried doing something like this:
Code: With rst .MoveFirst Do Until rst.EOF .Edit !Label.Selected(0) = True .Update Loop End With
However I get an error that says "Run-Time Error '438' Object doesn't support this property or method" ...
I have a training matrix that lists employee names and certifications on various operations. The objective is to choose an operation and run a query to display everyone who is certified on that op. There are additional variables.
Code: Name EMP ID OP1 OP2 OP3 OP4 OP5 ----------------------------------------------------------------------------- John Doe 526261 C C C Bob Doe 555622 C C C Sheila Doe 066600 C C C
Okay that looks about right for the data itself. The listbox has all the ops, you choose an op and hit a button and it goes and finds everyone who has a 'C' in that op column and pulls their record.
In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.
the data has a fixed part lets say
Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this
Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8 Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...
I am building a simplified re-order point system - if inventory position drops below a certain level (the yellow level is this case) one or more purchase order lines has to be created in another table.
I have one table with the following field and data:
ItemId Red Yellow Green Multiple Inventory position 0001 10 30 50 5 45 0002 5 40 47 5 23 0003 11 20 30 10 5
I would like to generate new records (in another table) based on the above fields and three records.Basically the end result should look as the following:
The logic is quite simple - if inventory position is less than the yellow value new order lines should be created in multiple qty (based on the multiple field) until the aggregated value (in table 2) is above the green value.The priotization value should be based on the start inv (in tbl 2) compared to the values in red, yellow and green in tbl 1.
I need the following code to take data from a form and input it into two tables linked by the item ProjectID. My Code is as follows:
Code: Dim pDate As Date Dim fid, jan As Integer Dim leng As Double Dim strSQL As String Dim varItem As Variant pDate = Date
[Code] ....
I take the items that I'm putting into DailyT to track the date, foreman, Job Number, and how long they spent on the job. I got the DailyT part figured out.
What I don't know how to do is use the items in the list box lstSel and put them into the table ProjectT so that each record in ProjectT has the ProjectID from DailyT and the ActivityID that comes from the items in the listbox.
I know I'll need a loop but I don't know how to get the autonumber ProjectID (or define it) from the entry that I made in DailyT to put into ProjectT.
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 have a form where I do a search and find the correct auditor in the listbox from the AuditorTable. This form is opened from a master form where I have the same fields in the table (ClientTable). What I need is that when I find the one, I double click it and the records from that auditor is passed to the client table.how to use Dlookup.
I would like to use a listbox set to multiselect to add records to a junction table. I've been using code to accomplish this with checkboxes (love how it looks and works) but after moving my tables to Office 365 as the backend, linkedto a local frontend, sql does not like this particular set up, and I do not have the time or knowledge to sort out why. So what I need is a step by step to look at the many, in this case possible roles a contact can have, and choose one or more, which then creates a record in the junction table with the contact id and role id.
I would prefer to not use a combobox on a continuous form because every time a user goes to select roles he would have to scroll through all the choices for each separate role.
My form has a listbox (lstHeatTreatments - Multi-Select disabled) that displays Heat Treatment descriptions and an unbound textbox (txtHTDetails) that I would like to have display the corresponding memo field when a description is selected from the listbox.
This is my code so far:
Code: Private Sub lstHeatTreatments_AfterUpdate() Dim myConnection As ADODB.Connection Dim myRecordSet As New ADODB.Recordset Dim mySQL As String Dim selectedRequirementKey As Long Set myConnection = CurrentProject.AccessConnection Set myRecordSet.ActiveConnection = myConnection
I have an access db with a linked table (to Sharepoint) which contains about 15000 records. I have to copy those records to the local access db. To do this I have a bit of VBAcode. It works, but for each next record that is copied it takes a bit longer. When starting the program the records are copied quite fast, but soon you see it slowing down until after a couple of thousand records it just goes too slow to be usefull, meaning it would take a lot of days to copy all records.
What I now do is let it copy 500 records and exit ms-access completely and then start it again and let it copy the next 500 records. This works but is tedious as I have to do it about 35 times. Why this gradual slowdown occurs?
The VBA code is pretty straightforward.(I use VBA because I do a bit of data wrangling while reading in the values of the records)
Code:
Dim importdb1 As DAO.Database Dim rsimportdb1 As DAO.Recordset Dim i As Double Set importdb1 = CurrentDb Set rsimportdb1 = importdb1.OpenRecordset("2015") Set rsMain = CurrentDb.OpenRecordset("tbl_import")
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?
When we browse through records in a subform we store the records in the database.When we want to delete a records for example the third record from the five records always the first records will be deleted. How can we delete the records where the cursor is at? When we are at the third record and press the delete button the third record from the list in the subform should be deleted.
Code:
Option Compare Database Dim FocusBln As Boolean Private Sub Identificeer() Me.[Datum Aangemaakt].Visible = True Me.[Datum Aangemaakt].SetFocus If Me.[Datum Aangemaakt].Text = "" Then
I'm trying to populate a listbox from a recordset. I will explain what I'm trying to achieve.
I have a (continuous) form with some data from a table. On the form header I have a listbox, showing all (distinct) customers from that form.
The user is able to do some filtering. That all works great. But I want to populate the listbox with ONLY the customers on the form AFTER filtering.
One way I thought might me the solution was using RecordClone. But I'm stuck there. It must be something simple for an expert. I'm not entirely a newbie, at least not to VBA. Is there an easy way to refer the listbox' recordset to the actual recordset on the form?
I'm trying to hash two scripts I've found into 1 functioning filter, however I'm still relatively new to vba and can't figure out how to get this working.
I'm trying to use Allen Browne's Search Criteria:
with another snippete of code I found here:
Code: 'Purpose: This module illustrates how to create a search form, _ where the user can enter as many or few criteria as they wish, _ and results are shown one per line.
[Code]....
It's the date part I'm having trouble with, the rest of the search criteria work fine without the date, but I can't get it working when I try to modify and merge the date sections of each code.
Also I'm using a listbox for the "Yesterday";"Last 4 days";"Last 9 days" and not a combo box.
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 parent table (tblLabels) and a child table (tblRevision) where the revision history for the parent table is kept.
The parent table is populated via an excel import and may have several records imported at once. Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.
This is what I have so far:
Code:
Private Sub cmdAddNotes_Click() Dim strSQL As String Dim RevisionDate As String Dim RevisionRevisedBy As String Dim RevisionDesc As String
[Code] ....
When I run the code nothing happens. No error, no new records create, etc. My suspicion is that I may need to use an INSERT INTO query instead of an UPDATE query, but I'm not sure how to go about matching up the foreign keys if that's the case.
I have been looking for days on the net for my listbox problem. It is there and found a few, even on this forum. but when i try the solutions mentioned i am in a total loss and do not know what to change to make it work for me.
I have a form named A/B Retriever with a record source qry input AB Bins Than i have a unbound combobox with row source qry input AB Bins. This populates 8 textboxes with B through I carton boxes, stored in a bin. The user selects a Bin location from the combobox and can put a "x" in a textbox to illustrate that the box is empty. This works perfect.
Underneath the input bin and box part i have 8 listboxes that shows a query that has counted the empty boxes with the corresponding bin location. this also works. but the question from users where, If i click on a, lets say empty B-box at Bin location 12A20, they want the combobox automatically focus on the combobox with the corresponding Bin location. This is a quick way for them to delete a empty box (remove the X).
MS Access 2013: I have two database tables as below:
tbl1_MainDB --- It has a field named as "City" where I get huge data for some city names. Sometimes This field may have some unknown/new names which are not listed in our 2nd table ("tbl2_RefrDB")
tbl2_RefrDB --- It's a reference table which has raw names for cities, and then standard names of their city and state in another fields.
Target --- I want to create a VBA prorgram (Sql query) which can look from tbl1_MainDB.[City] to tbl2_RefrDB.[Raw_City] field, and if found then pick the "Standard_State" and "Standard_City" record values from there, and update into the 1st table "tbl1_MainDB".
...if not found in "tbl2_RefrDB" table, then user can be informed & ask for updating the new/unmatched city record as a new record in this table.
I want subtrack a value on the current record from the previous record and display the result in text box in subform datasheet. This is done during data entry. The calculation works most of the time and randomly it returns the wrong value. I have the code in an after update event after I enter a number. Here is what my code looks like:
I have a form with a sub form. when a record is choosen in a combo box the sub form is filled out with a record.
what I am trying to do is have a button that will copy that record to a history table then delete it off the the main table.
I cheated by using the wizard to get the code to delete the record but I am having troubles modifying the code to copy that record to the history table. Here is the code below. I have tried to insert code in several places but it just errors out.
'------------------------------------------------------------ ' Master_tbl_sub_fm ' '------------------------------------------------------------ Function Master_tbl_sub_fm() On Error GoTo Master_tbl_sub_fm_Err With CodeContextObject On Error Resume Next
I have a form with a listbox that displays the name of a table. Once the listbox item is selected, the table name is set to a variable called myFile. I want append the records from the table (myFile) into another table.
I am using Access 2010 and I currently use a command button on a form to add new records to a table using data that the user has entered into the form using the code below:
Although this is pretty self-explanatory, here is a key for reference:
Code: Private Sub cmdAddMet_Click() Dim DB As DAO.Database Dim RS As DAO.Recordset Dim strSQL As String Dim i As Integer Set DB = CurrentDb
[Code] .....
This works great but I would like to be able to pull in data from another database based off of Me.lstFacilities.Column(1, i) which is the FACILITY_ID field and is located in the other database's table. I thought about adding in another string variable(strSQL1) and opening up a separate recordset and database:
Code: Set DB1 = OpenDatabase("serverotherdb.accdb") strSQL1 = "SELECT [FieldName] FROM [tblOtherDatabase] IN 'serverotherdb.accdb'" Set RS1 = DB1.OpenRecordset(strSQL1)
However, I'm not sure where to start pulling in the data from the [fieldname] in the [tblOtherDatabase] when I start the loop below:
Code: For i = 0 To lstFacilities.ListCount - 1 If lstFacilities.Selected(i) = True Then RS.AddNew RS!RELATIONSHIP_ID = Me.lstFacilities.Column(0, i) RS!MEASUREMENT_PERIOD = Me.cboMeasure
