Im running an update query but in the criterial field i would like to include something that instructs the query to be executed only on the value selected in my listbox.
i need some help with the syntax cause i dont know how to bound the query's execution to the selected/highlighted value in the listbox.
I have a query which returns names to a listbox. The query works by a Command Button and 'Requerys' by using the Command Button. But, I cant get the ListBox to refresh on a Requery. All it does is add results of the Requery to the bottom of the list. I'm obviously missing something basic....any ideas?
View 1 Replies View RelatedHello,
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"
End If
Exit_cmdSelectAll_Click:
Exit Sub
Err_cmdSelectAll_Click:
MsgBox Err.Description
Resume Exit_cmdSelectAll_Click
End Sub
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?
Tnx a lot!
Hi, I'm new here, so I hope I'm posting this in the correct place. I've searched the forum to see if there are any existing threads that might help me, but I've not found anything that does...
(I think this thread ( http://www.access-programmers.co.uk/forums/showthread.php?t=93444&highlight=Copying+data )may be trying to achieve something similar to me, but I'm a beginner and don't really understand it)
I shall stop waffling! I'm not entirely sure that what I'm trying to achieve is possible, I expect it probably is!
Right, I have a form (frmGroupRegister, which contains exactly the same fields as the table it comes from, tblGroupRegister), which consists of three things:
-GroupDate - The date a group took place on. It is my primary key, as no more than one group occurs on a specific date.
-ParentList (A listbox which contains a query showing the ID number, forename and surname of everyone in a table, tblParentDetails)
-ParentsAttending (A blank listbox)
I would like to place buttons in between the ParentList and ParentsAttending, which would allow users to conduct a 'register' of attendance by copying individual/multiple details from ParentList into ParentsAttending (much like you get when choosing which fields to include in a form when using a wizard for example). I would also like them to be able to remove people from ParentsAttending by using a button in case of accidentally adding the wrong person into the ParentsAttending box.
I'm aware that another, probably simpler way of achieving this would be to use a tick-box system, but I feel that visually, the first method would both look better and demonstrate who is present more clearly.
Any help would be much appreciated, but my Access skills are quite basic and things will probably need to be spelled out for me.
I'm using Access 2000 and Windows XP.
Thanks for your help,
Alice :)
Hallo Everyone,
I have a problem in linking the forms. I have a form called FORM1 and i have 2 comboboxes there. Now I have also a listbox in the same form and in this case I need to update the values in connection with the values that are given in the combo boxes. Can anyone say me how the updates are done automatically in the listbox.
thanks,
aravind.s
Hi there!
Been away from access db's for a couple of years and have suddenly job i have to do, and of course the client wants it finished yesterday :rolleyes:
Anyway, my problem is that i have a simple form with suppier details in it - and a combo box which shows the products that that supplier has. The combo box runs on a custom query object i created that matches the supplierID's. Simple!
But for some reason when i change the supplier record, the combo box doesn't with the new list of products. The dynamic QUERY updates just fine, if i check it, however these results aren't reflected in the listbox.
If i use a subform, then that updates just fine. Alas, not the list box, though.
I must be forgetting something simple! Is there some kind of update event i have to create?
Thanks!
I am trying to figure how to do the following with a listbox
On my Form I have a list box that shows my users a distinct list of items from a "master" table that they can select and add to their own user defined table that is used as a filter for a number of reports and queries
As long as I have only 1 column in both - I can get the following code to work
DoCmd.SetWarnings False
Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant
' Build a list of the selections.
Set ctl = Me.lstProjectLists
For Each Itm In ctl.ItemsSelected
DoCmd.RunSQL "Insert into my_project values ('" & ctl.ItemData(Itm) & "')"
Next Itm
DoCmd.Requery
Me.Refresh
DoCmd.SetWarnings True
What I would like to do is expand my list box to contain more than 1 column and when the user selects from my master list to populate theirs - I want to copy the entire row over.
Example of what I am looking for
On a form I want to have a list box that contains Project No (column1) and Description (Column2) - when a user selects a row and clicks on a command button (code sample from above) to "populate" the selection into their list - I want both columns to be copied into thier table "my_project" .
Is this possible?
Thank you,
Kenny
I have a form that student grades on. I use a listbox to pick the names. I also have a pop up form for me to enter new students that are not in the pop up "query" form. I can go to my form with the grades and hit a refresh button and the listbox is updated. I would like for my data to be requeried and up to date when I close the pop form. Any suggestion.
Thanks
I have a form with several fields bound to a table, and also a listbox of records that are not bound to any table. What I want to do is to be able to double-click a record in my listbox and have the record show up on my form in a particular bound field, and also become a part of the underlying table's record.
Is it possible to do this? If it is, how would I do it?
Thanks in advance to all who are willing to help!
I've a form with comboboxes and listboxes on, which populates a table with data. The cbo or lbo are bound to the relevant table field.
Unfortunatley, there is one lbo that just wont update the relevant table field.
I've tried lots of different settings and etc, following advice reseacrhed from these forums.I've even tried copying an lbo that does work, all with no luck.
Has anyone ever had similar experience? If so, how do I get out of it?
Cheers
Pete
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.
Thank you.
I have a form with a listbox in it. I want the listbox to update as the record changes.Let me get a little more in-depth...The form will be used to identify merchandise that exists in the company to fulfill current POs.
What I want is for the listbox (which displays Store Locations) to update when the PO# updates on the form (so as the record changes).Currently, the listbox only pulls all the store locations no matter if you hit next record to get a new PO.The two main fields I am working with are PO# (text box) and Store Locations (the listbox).How can I get the listbox to update as the record changes on the form?
I have 2 forms: frmExcEntry and frmItemSearch. frmExcEntry is a data entry form where the associate enters PO#, Item#, Qty, etc. frmItemSearch is a popup form that is used to narrow the search of the Item# from frmExcEntry. Right now, when the associate types the Item# (either PartNo or FactNo)in the txtItemNo control on frmExcEntry, frmItemSearch opens (frmExcEntry remains open) with all potential item #'s associated with the text entered in [txtItemNo]...(there could be multiple FactNo in our inventory system but PartNo is unique and the bound field for the control). frmItemSearch uses an unbound textbox (txtSearch) that populates with the text from frmExcEntry on txtSearch_GotFocus and queries a listbox with the list of possible PartNo to select. I want the associate to be able to double-click the accurate PartNo from the list box which will then update the value initially typed in txtItemNo and copy the PartNo over txtItemNo in the currently open frmExcEntry.
View 3 Replies View RelatedI 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 am using the selections made of the form to generate a query for the user.
I have a CITIES listbox that is populated with values from a stored query.
I would like to make it multi-select and populate a LOCATIONS list box and a NAMES list box based upon the CITIES that are selected.
I have the locations currently populated from a stored query that reads the City selection from the Form. It looks like this
Code:
SELECT DISTINCT (t_location.LOCATION) AS Expr1
FROM t_location INNER JOIN t_asset_master ON t_location.LOCATION_PHY_ID = t_asset_master.LOCATION
WHERE (((t_location.CITY)=[Forms]![MasterQueryGenerator]![CityList]));
I also want multi-select so that is you can un-select all and get the results for all cities.
Here is my half thought approach.
Code:
Private Sub CityList_AfterUpdate()
'Dim LocQryStr As String
'Dim r As Integer
'Dim ctl9 As Control
'LocQryStr = "SELECT DISTINCT (t_location.LOCATION) " & _
[Code] ...
I intended to have the variable LocQryStr as the row source but I abandoned the idea of having multi-select when I saw that .Selected(I) never returned true. Its like the values aren't read in this subroutine.
I have a list box called "product list box" based on a query called "searchqry", i also have another listbox called "type list box" , how do i get the type list box to only show "types" based on the section in products list box?
View 1 Replies View RelatedUsing a popup form
1. On my main form, I have a listbox, I would like to edit the values of the listbox.
To do this, I have a popup form with 2 listboxes, one to have the values of the listbox on the main form, and the other listbox with option values for the 1st
1) how to i pass the rowsource sql of the listbox on the main form to the listbox on the popup form
2) how on closing the popup form, do i update the rowsource sql listbox on the main form from the changed value of the popup form listbox rowsource sql
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?
Hi Guys,
I have got a query that updates details from one table2 to table1, "Reference" is the primary key and this is what the query uses to determine which need updating.
It all works great but if table2 contains a record in "Reference" that is not in table1 i just want it to ignore it, currently it just seeems to add them.
Any suggestion guys & gals?
Many thanks
Tim
Ok, i have a question about update queries.I have two tables (I'll call table 1 and table two for simplicity) and an update query. I want to get some data from table one to table two (via an update query). But in table two there is a field that isn't in table one but i want to add a value to that field via the query.My question is, can i manually put into the query what data to add to a field instead of/aswell as using data from other tables.I hope you understood my questions.Cheers
View 3 Replies View RelatedHi everyone,
can a listbox get its info from a query? i have been trying to create a calendar type form which is going ok and every day has a listbox to show the current events. each event will be put into a table via a form (events table ) and i was wanting to have every listbox look at the query based on the table based on the date as a criteria so every listbox will only show events related to that date. text boxs are not really an option as i need to show multiple events.
the fields in the table would be-
EventID
PersonID
Event Date
Event Time
Event Description
Event Notes
Event Priority
i was planning on only showing the time and description, then OnClick would open up the complete information.
i can relate the query to the main Calendar form too.
any advise would be great. i can work out the links if it can be done but i dont have access at work so cannot try anything until i get home :(
Many Thanks,
Nigel
Hi all,
I've two listboxes.
The first listbox contains Roses, lilies, Cactus, etc. When the user select Roses, the second listbox will display Pink roses, red roses, white roses, etc, and not all the other flowers that's in the table.
What should I do? I'm using Access 2003. Thanks in advance!
I want to pass a search parameter from a form to a query then use the query results to populate a multi-column list box. So far, I can pass the parameter to the query and generate results, however, I want the query results sent to a listbox rather than create a new window.
I set up the listbox to receive results from the query, but when the form opens the listbox is already populated with arbitrary query results. I want the listbox to be empty when the form is opened, I submit a parameter to a query, and the listbox fills with the results
Additionally, once the listbox is populated, I want to clear the listbox but am having the error: The RowSourceType property must be set to 'Value List' to use this method.
I'm still a little new to this, so please bear with me. I've searched to no avail for what I am looking for, so if there is an existing thread and you would rather point me in it's direction as opposed to answering my question, that would be fine.
I want one of the parameters of my delete query to be only the first column of my "ProdList" listbox, but don't know how to specify this. This is what my query looks like so far... Can someone tell me how do i need to change it?
DELETE ProductionLog.Username, ProductionLog.Date, ProductionLog.OrderNumber
FROM ProductionLog
WHERE (((ProductionLog.Username)=fOSUserName()) AND ((ProductionLog.Date)=[Forms]![Production]![TheDate]) AND ((ProductionLog.OrderNumber)=[Forms]![Production]![ProdList]));
Hi guys,
I'm working on a company recruitment database. I've stripped out where my problem is and attached the relevant file/tables/queries, etc...
I have a form with 2 listboxes (left and right) that are used to select various combinations of employees depending on job role and skill types. The results are displayed in listbox 3 (bottom) at the bottom of the page. You can then use this list to view more detailed records which open up on the right of the main form...
The left listbox which selects job roles works fine, however I want the right listbox to ONLY show employees that match ONLY instances of skills selected.
What currently happens is that employees are shown that match ANY of the skills chosen instead of ONLY the skills chosen...
I've tried IN, = , and different operators but cannot seem to find a resolution...
Note: (There's an annoying msgbox that displays the rowsource variable every time you click the listboxes that I used for testing LOL)
The query's are addressed in VBA behind the main form...
Any help would be appreciated
dazza61
I have a listbox inside a subform. I want to use the 'click' Event on a "search" command button (found on the main form) to trigger
a query from inside that listbox and fill it with values from a table. The following is the argument I have written for that event procedure:
Private Sub CommandSearch_Click()
Me.GS_SearchSF.Form.listSF1.RowSource = _
"SELECT MaxSize1 " & _
"FROM GS_Ctg_Float " & _
"WHERE Supplier = '" & Me.GS_SearchSF.Form.SupplierQuery.Value & "';" 'THIS ONE ISN'T WORKING
End Sub
Except as you might imagine, it is not working. It works when I just type a name from that table into the argument in place of
the text seen above in red. For instance:
Private Sub CommandSearch_Click()
Me.GS_SearchSF.Form.listSF1.RowSource = _
"SELECT MaxSize1 " & _
"FROM GS_Ctg_Float " & _
"WHERE Supplier = 'Viracon';" 'THIS ONE WORKS
End Sub
What am i doing wrong?
Here's some background:
GS_SearchSF is the name of my subform
listSF1 is the name of the list box in the SubForm that the
query is supposed to fill
SupplierQuery is the name of the list box in the subform
I want to extract the value from and put into the query