Help In Insert/update Multiple Selection From A Listbox
Aug 7, 2005
thank you in advance for all your BIG help. :)
I have 3 tables - SMaster, SI_map, ILookup
I need to create a form to Add/Update into SMaster and SI_map tables, which has one-to-many relationship, that's why I use a list box to show the values from SI_map.
in SMaster [ Sid, other fields ]
in SI_map [ primary key, Sid, ILookupid ]
in ILookup [ ILookupid, IName ]
questions:
1. How to get the multi-selected values from the list box?
2. insert these multiple records into SI_map table at the same time with SMaster (if I use the same form to populate all these fields)
3. how to populate the records into form for user to see and update the values?
Any suggestion is appreciated, it is very flexible to change any format such as SMaster and SI_map can be separate forms, as long as it works.
This is probably in the wrong forum, but I'm not sure what area the answer would cover, either queries, macros, VBA etc.
I have a form, on which is a listbox with multiple selection enabled getting it's data from a query. What I want to do is for the user to be able to select multiple products from the listbox and have some VBA code or query concatenate each id and insert them into a table, separated by commas so I can separate them again for reports etc.
Eg.
Listbox: ID 1 2 3
User selects 1 and 2 and clicks submit. Selections are concatenated to 1,2 and inserted into the table.
Please bear in mind I'm not the best at this kind of advanced databasing, so a simple or at least easy to follow answer would be very appreciated.
In my application, I am allowing multiple selection in a listbox. The data is saved in table. While retrieving, the items that were selected for saving, show as selected. But on printing ListIndex, it prints -1. What could be wrong ? I need to resolve it. Any solution ?
I am designing a small database. I have problem in selecting Mutiple list values from list box.
I have a table of Personal.With fields SrNo ------------- autonumber Name -------------- Text Age ---------- Number Interset ------------ (Text) //In Interset Properties through lookup tag I have Display Control -------ListBox Row Source Type --------- Value List Row Source ---------------- "Swimming","Football","Cricket",hockey"
When I made form I got all the List Box Items. For single Selection it has worked. But If I want Multiple selection. I changed the property List Box property Multi Select from "None" to Simple. Now It worked with Multiple Selection.But If one first record I select Swimming, Football.On second record it would be blank. And select my self. But the previous one is saved on all the next records.If two selction on first next all same selction with 2. If three then three.
I want Individual selection. It must be saved.According to corresponding record.
I have a form called "Record Call" when a user can input the calls that they have made or are scheduled. This form is based on the table "Call Records". I have created a new table called "CallAttendees" and added a listbox to my form for users to select who attended/participated in the call.
My code loops through the selections and writes these selections to the "CallAttendees" table. In this table i have an auto #, CallRecord, and Attendee fields. My code is supposed to write the "ID" field from the "Call Record" table to the "CallAttendees" table so that i can create the relationship between the two tables. However, this field is coming up blank since my form isn't generating the auto # until the record is closed.The tables are stored in a SQL server. I also tried saving the record prior to running my code.
i am trying to insert multiple values that i have selected in my listbox to my database access table when i click the "add record button" but the values does not appear in my database table.
i have 2 listbox, when i select the first list box(businessNature) it will display the records in the 2nd list box(lstCuisine). However, the records in the the lstCuisine list box is not entered into the table in my database.
(ps: in my property sheet for my lstCuisine listbox its multi select is simple)
Here is my codes:
Private Sub Add_Record_Click() If IsNull(Name) = True Or IsNull(Mobile) = True Or IsNull(Email) = True Or IsNull(CompanyName) = True Or IsNull(BusinessNature) = True Then MsgBox "Please fill in Business Nature, Name, Contact, Email and Company Name" Else DoCmd.GoToRecord , , acNewRec End If Dim conceptValue As String
Okay then, after much trouble and confusion, I finally realized I need to use an Extended listbox in order to allow for multiple items to be selected from a list on my form (rather than the evil multiple selection combobox!).
However, now I am trying to figure out how to make one listbox (IndustryClassification) only be visible if the item "Industry" is selected in another listbox (TypeOfBusiness). Coding I can use for this in the AfterUpdate event of the listbox?
I have a form with a combo box this combo box looks up values in my STC table. The STC table has two fields STC & Resolver and each STC has a unique associated Resolver. What i want to happen is after the user has selected the STC from the combo box on the form the associated Resolver be displayed in a textbox on the form. I am ussing the following code but nothing seems to be happening:
Private Sub cboSTC_AfterUpdate() Dim ResolverSource As String
This is what I'm trying to acheive and I'm sure its possible but cant quite get my head around it to do it.
I have a form with a multi select list box from which our customers can select a number of items they would like added to their worklist for the day. They then click on a command button which then sends those items to a table from which our employees then generates a worklist.
However what I would like is that once the customer has entered their selections I would like them to be able to click another command button before submitting the work to the table which would then show them the selections they have made before the info is submitted to the table. They would then review what they've selected and make additions or subtractions as neccessary.
I'm not sure whether or not I have to create two tables the first of which would hold their initial selections and then the first button would then run a query based on that first table and then once they click the submit button it sends that info to another table from which the days worklist can be drawn.
Another question is what if once they've reviewed what they've selected and wish to remove something? If selections go to a temporary table and then they unslected an item will that remove it from the table or would it create a new record which is not what I want.
If you could help that would be most aprreciated. I know very little in the way of coding so any explicit instructions would be most welcomed
I have a Listbox [ListRandom], that populates a random selection of employees. The listbox is set to "Multiple Select = Simple" What I have now is: I can select several employees and then click a [print] button, this prints an individual form with those employees information, and then adds them to a table [tblselected]. What I am trying to accomplish is After those employees are selected, printed, and added, I then want to go back to the random list to select the other employees, but I would like the first ones that were selected/highlighted to refresh and be gone, so that I dont accidently add the same employee twice.
Is this possible? And can someone point me in the right direction. thanks....Enviva
Hi, below is the codes that I use to produce a report based on user entry on a form. On the form is a listbox for the user to select the report they wish to view. On the same form, the user must enter the date. Depending on certain selections, some fields on the form are not required. How can I force the fields that are not required to become disable based on the selection from the listbox. Thanks
Private Sub cmdGetReport_Click() Dim strRptName As String strRptName = Me.lstReports.Value
Select Case strRptName
Case "Originations by Branch"
DoCmd.OpenReport "rptOrigByBranch", acViewPreview, , "ClosingDate Between #" & Me.txtStartDate.Value & "# AND #" & Me.txtEndDate.Value & "#"
Case "Loans Sent to Branch"
DoCmd.OpenReport "rptLoanSentBr", acViewPreview, , "Br = '" & Me.txtBranchNo.Value & "' AND ClosingDate Between #" & Me.txtStartDate.Value & "# AND #" & Me.txtEndDate.Value & "#"
I'm having trouble with with my form (frmViewCust). What I am attempting to do is create a form which shows a list of customers (from a query) and has a section with the customers details (from tblCustomers). I need it so when a certain customer from the list is selected, their details will be shown in the customer details section.
The best i could do was have the list and details as sub forms (fsubListCust an frmCustDetails). I made frmViewCust to show records from the same query as fsubListCust and create the textbox CustomerID. I successfully made it for when the CustomerID is shown, fsubCustDetails shows correctly corrisponding with the same CustomerID made. How can i make it so when a record is selected from fsubListCust, the subform will change to the same record for frmViewCust.
Alternativly I made it so the list was a list box instead of the sub form, however I still encouter the same problems. I can't make it so the record (shown in the navigational button) is changed to be the same as the record selected in the listbox.
I have been working on this problem for 3 days, and can't figure out what to do. I'm using Access 2010.
I have two tables. Tier1 and Tier2.
Table: Tier1 has Tier1ID (autonumber) and Tier1Desc
Table: Tier2 has -Tier2id (autonumber) -Tier1ID (which is the link back to the Tier1 table), -Tier2Num -- this number shows which tier2 records are associated with each other - and about 6 fields with descriptive info, 2 of which are memo fields
I have a form that has 2 listboxes:
- Listbox 1 has the row source to Tier1 table, control source Tier1ID - Listbox 2 is an unbound listbox with the row source to a query.
The query is all the fields from Tier2 with the criteria where Tier1ID in Tier2 table = Tier1ID in Tier1 table
This all works. When I select something from the Tier1 ListBox, it displays the associated tier2 items for the tier1 selection.
What I need : When the selection is made in the 2nd listbox, I need the other 6 fields in tier2 table to be displayed based on the "Tier2Num". The listbox does return Tier2Num correctly.
I just need to display all the rest of the fields.
- I tried a popup form based on a query, but can't seem to pass the tier2Num to a form. (The query works if you enter the Tier2Num, which is the [listitem] selection) - I tried dlookup in a textbox - I tried to add all the fields into the listbox, but could only get 3 to display (then I was going to do textboxes with the control(#) in it.
I want to insert obtain marks of the subjects. Find the attached my db. when i select combo value my requirement is to insert the all records which associate with the combo. but when i select combo value it will happen nothing.
Hi all, greate site and i have been able to solve most problems by using the search box although this problem is doing my head in...!!
I have a db that records project numbers and their details. I am using a listbox to allow a user to multiselect Project Involvements Tasks(ie Documentation, Build etc) against a project number.
I am able to read the selections into a separtate table with two columns which is structured as:
as you can see I dont have a problem getting the Itemsselected into a table... the problem that i am having is getting them out again when the record is displayed - ie marking them as itemsselected.
I believe that the event would be onCurrent which would loop through this table pick up the project number and recorded invovements and mark them as selected in the listbox. if there is no invovement then the listbox would show no selections.
I am using this code to read the selections in
=========================== 'Records project involvements against project Public Function AddInvolvements(ctlRef As ListBox) As String On Error GoTo Err_AddInvolvements_Click
Dim i As Variant Dim dbs As DAO.Database Dim rs As DAO.Recordset Dim qd As DAO.QueryDef Dim strDelete As String
Set dbs = CurrentDb Set qd = dbs.QueryDefs!qInvolvement Set rs = qd.OpenRecordset
'Delete records where project number exists against an invovelment incase of involvement changes strDelete = "Delete Project_Involvement.ProjectNo " & _ "FROM Project_Involvement " & _ "WHERE (((Project_Involvement.ProjectNo)=[Forms]![Add_Project_Details]![ProjectNo]));"
For Each i In ctlRef.ItemsSelected rs.AddNew rs!InvolvementType = ctlRef.ItemData(i) rs!ProjectNo = Me.ProjectNo.Value rs.Update Next i Set rs = Nothing Set qd = Nothing
Exit_AddInvolvements_Click: Exit Function
Err_AddInvolvements_Click: Select Case Err.Number Case 3022 'ignore duplicate keys Resume Next Case Else MsgBox Err.Number & "-" & Err.Description Resume Exit_AddInvolvements_Click End Select
End Function ===================================
Any help would be much appreciated - also thanks to Pat Hartman for his excellent examples esp http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=54924
How do I get my combo box selection to display related results in a list box? I have a many to many table structure for a contact list that relates many contacts to many costcenters. What I want is when the user selects a costcenter from the combobox the related contacts show in a list box.
I have a form which has a listbox listing all the letters a student has had sent home. Basically i want to be able to click on a letter in the list box and for a popup form to open with the record details. However it just brings up a blank record.
The list box has a unique field called standardletterID.
the code behind the button to filter was just used through the wizard to open the popup form is:
Dim stDocName As String Dim stLinkCriteria As String
Need help on a list box issue. Solution is probably real simple, just can't see it with my bleary eyes.
I have a form (INSTLKP) with only a list box (List6) based on a table ((tblPersData) with fields PersdataID; SSN; Last; First; MI) on it. When I open the form up, the listbox does display all records associated withthe table. I would like to doubleclick on a desired SSN in the list box and have that record open up in a form (Perssub1) in edit mode.
And that is where the rub is... I have the following code in the On doubleclick event of List6
I am currently working on a form for my users to look at budget data. I have several listboxes for them to filter queries by. What I was wondering is, if it is possible to have data in one list box be filtered by the preceeding listbox?
For example: Three list boxes: Report_Period, Budget, Dept.
Each list contains all the values possible. This data is all in a filter table (and the listboxes created using SELECT DISTINCT queries to that table for the specific field). So, if I select Budget XYZ, could I filter the Dept listbox to only show the dept.'s associated with that budget? These are all multi-select listboxes by the way, so budget ABC and XYZ would only show the departments associated as well.
I've got this multiple select listbox which writes data into a textbox:
Private Sub List2_AfterUpdate()
Dim Cursisten As String Dim ctl As Control Dim Itm As Variant
Set ctl = Me.List2
For Each Itm In ctl.ItemsSelected If Len(Cursisten) = 0 Then Cursisten = ctl.ItemData(Itm) Else Cursisten = Cursisten & "," & ctl.ItemData(Itm) End If Next Itm Me.txtCursisten = Cursisten
End Sub
And I've got a SELECT ALL button to select all records in the listbox:
Private Sub cmdSelectAll_Click() On Error GoTo Err_cmdSelectAll_Click
Dim i As Integer
If cmdSelectAll.Caption = "Alles Selecteren" Then For i = 0 To Me.List2.ListCount Me.List2.Selected(i) = True Next i cmdSelectAll.Caption = "Alles De-Selecteren" Else For i = 0 To Me.List2.ListCount Me.List2.Selected(i) = False Next i cmdSelectAll.Caption = "Alles Selecteren"
The only thing is that when I use the SELECT ALL button, the function List2_Afterupdate doesn't work anymore. There must be a simple solution but I just can't figure it out. Can anyone please help me?
I have a list box that is correctly listing resources from a table (tblResource). I would like to select one of the list box entry and run a query against the tblResource to show the information for that resource in a form. I have tried to use the lstindex with the control (lstResource) in the where clause of a query to accomplish this with no success.
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 use the follwing code to filter a report based on the listbox selection on a form. Below is the code I use, the problem it will error if the results have an apostrophe in the string.
Private Sub FilterDesc_Click() Dim strWhere As String Dim ctl As Control Dim varItem As Variant 'make sure a selection has been made If Me.ListCarrier.ItemsSelected.Count = 0 Then MsgBox "Must select at least 1 Carrier"
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
