Adding Items To List Boxes
Oct 4, 2006
This is a somewhat complicated question to explain, so please bare with me.
I have a form with a ‘list box.’ The box is populated using a Table/Query Row Source Type method. The table I am grabbing the list from is “Department,” where it contains a list of my company’s departments(ie. Accounting, HR, Payroll, etc.).
Now, the only thing this table does not have is the word “ALL.” I need this word in the “List Box” because I want users to have the option to select ALL.
Is there anything in VBScript code wise or in Row Source that I can do to include this word?
I know I can manually go into the Department table to simply add the word, but this is not the point, because there is more to that(I have different list boxes that are grabbing lists from different tables, AND I have to refresh these tables twice a week). Please help.
Thank you.
Joe
View Replies
ADVERTISEMENT
Sep 4, 2005
Hey Guy and Ladies,
In one database (Originally an Orders Database created by wizards)
This code to add items to a drop down list in the “orders details subform” works fine.
Private Sub ProductID_DblClick(Cancel As Integer)
On Error GoTo Err_ProductID_DblClick
Dim lngProductID As Long
If IsNull(Me![ProductID]) Then
Me![ProductID].Text = ""
Else
lngProductID = Me![ProductID]
Me![ProductID] = Null
End If
DoCmd.OpenForm "Products", , , , , acDialog, "GotoNew"
Me!ProductID.Requery
If lngProductID <> 0 Then Me![ProductID] = lngProductID
Exit_ProductID_DblClick:
Exit Sub
Err_ProductID_DblClick:
MsgBox Err.Description
Resume Exit_ProductID_DblClick
End Sub
When the same code runs in my new database
Private Sub OptionID_DblClick(Cancel As Integer)
On Error GoTo Err_OptionID_DblClick
Dim lngOptionID As Long
If IsNull(Me![OptionID]) Then
Me![OptionID].Text = ""
Else
lngOptionID = Me![OptionID]
Me![OptionID] = Null
End If
DoCmd.OpenForm "Products", , , , , acDialog, "GotoNew"
Me!OptionID.Requery
If lngOptionID <> 0 Then Me![OptionID] = lngOptionID
Exit_OptionID_DblClick:
Exit Sub
Err_OptionID_DblClick:
MsgBox Err.Description
Resume Exit_OptionID_DblClick
End Sub
I get the error message
“You tried to assign the Null value to a variable that is not a Variant date type”
Any suggestions?
Also if anyone knows a better way to add items to a list box “On the Fly” I would really appreciate it.
It would be nice if you could type in the value and it would automatically add it to the list for future use.
Thanks for all of your help,
Joe
View 7 Replies
View Related
Oct 16, 2014
I have an access project that I am working on and need to be able to select multiple items from a listbox and have the exact selections appear in a textbox on the same form. I have looked around and have not been able to find any code that works.
I have tried:
Me.user2 = Me.slct_auditor.Column(0, 1)
Me.user3 = Me.slct_auditor.Column(0, 2)
Me.user4 = Me.slct_auditor.Column(0, 3)
Me.user5 = Me.slct_auditor.Column(0, 4)
Me.user6 = Me.slct_auditor.Column(0, 5)
Me.user7 = Me.slct_auditor.Column(0, 6)
Me.user8 = Me.slct_auditor.Column(0, 7)
but when skipping the first item in the listbox it is still passed as into the textbox.
View 4 Replies
View Related
Apr 30, 2005
i have a combo box containing dates, however it will only display the date at the top of the list, no matter which date is selected.
any help would be gratefully excepted!
cheers
View 2 Replies
View Related
Aug 3, 2006
Need some help here....
I am designing a database to keep track of workers for a haunted house. I have a Roster table, a table of all the nights we are open, a table of Spots in the house, and a table to record who works what nights and what spot they are in. This table has a Room combo box and a Spot combo box. The Room combo has a Row Source of SELECT DISTINCT SpotsAll.Room FROM SpotsAll ORDER BY SpotsAll.Room; The Spot combo is then populated with VB code all the Spots that are in that room. That works fine. This is what I'm trying to accomplish: When a Spot is assigned to a Worker for that night, I want that spot to no longer be available in that list FOR THAT PARTICULAR NIGHT. So, lets say Joe Somebody works in Spot 1 (out of 4 lets say) of the Library Room, when we assign another person to the Library room, I don't want Spot 1 in the Spot list.
Can this be done with a query or VB code? If I'm not making sense, please let me know. I can also upload the Database I am creating so that you can play around with it and see what I mean.
(I have attached a word doc. that shows the table relationships.)
Thanks a lot ahead of time!!
ScrmingWhisprs
View 13 Replies
View Related
Apr 7, 2006
I've made a button on my form which adds a booking ID into the listbox, but it doesn't seem to work. This is my code:
Private Sub Command18_Click()
Dim BoI As String
MsgBox "Booking successfully added!", vbInformation, "Booking"
If Month(Me.Date_1) = 1 Then
ListJ.AddItem Me.BoI
ElseIf Month(Me.Date_1) = 2 Then
ListF.AddItem Me.BoI
End If
End Sub
If someone could tell me the problem, I'd appreciate it. Thank you. :)
View 5 Replies
View Related
Mar 10, 2006
MsgBox "Booking successfully added!", vbInformation
'Depending on which month is booked, will determine which listbox is goes in on frmMain
If Month(Me.Date_1) = 1 Then
Forms!frmCurrentBookings!ListJ.AddItem = Me.BoI 'Add to ListJ on
frmCurrentBookings
ElseIf Month(Me.Date_1) = 2 Then
Forms!frmCurrentBookings!ListF.AddItem = Me.BoI 'Add to ListF on frmCurrentBookings
End If
End Sub
So basically... I have a date field in the form 'frmAddBooking'... if the month of the date is 1 (January), then the conntents of the date field is added to a listbox in a different form (frmCurrentBookings)... but this doesn't work...
If somebody could help me I'd appreciate it greatly.
View 2 Replies
View Related
Nov 8, 2004
I have a form with a List Box (List BoxA) that returns a value from a query. I then have a Text Box (Text BoxB) that uses the value from List BoxB and multiplies it by let's say 2. It works giving me the correct value but only becomes visible after I click inside List BoxA. How can I make it visible as soon as the form is opened? I tried refreshing the form data, didn't work and I tried a requery macro which didn't work either.
Thanks,
Lester
View 3 Replies
View Related
Sep 21, 2004
i want the user to make a selection from one of two option boxes, with the choice that they make i want a list box to appear straight away, depending on there selection, can anyone help please.
Cheers
Andy
View 6 Replies
View Related
Nov 20, 2013
Allen Browne's "Use a multi-select list box to filter a report" solution, in particularly with two multi-select list boxes? The code works fine for me for either box so long as I code it for one box alone. Combining the two into one code results in a type mismatch error. I'm trying to use the code to pass the contents of both multi-select boxes as Where conditions to a report. Both boxes are based on number fields. To try to isolate the problem, I've removed Allen's setDescription and OpenArgs conditions. We're unfortunately still on Access 2003 as the company desires to squeeze every dime by using until end-of-life next year.
Code:
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
Dim varItem As Variant 'Selected items
[Code] .....
View 14 Replies
View Related
Jun 6, 2013
I've got a table with 23 columns. Column 1 is the ID row which has a unique client ID in it. Then we've got a column called 1st_Reason and one that goes with it called 1st_Transfer_Date. This pair repeats for 2 through 10. Each of the Reason columns can have a set value, for example "First Processor".
What I need to do is create a date with months on the Y axis and the 5 different reasons on the X axis. I need to count the number of "First Processor" across all 10 Reason columns for each month. I would need to repeat that for each other reason type, but if I can get one to work I can simply change the reason type.
Here's what I'm using to count May's total:
Code:
=Sum(IIf([Current_Reason]="First Processor" And [Current_Processor_Transfer] Between #4/30/2013#
And #6/1/2013#,1,IIf([1st_Reason]="First Processor" And [1st_Transfer_Date] Between #4/30/2013#
And #6/1/2013#,1,0)))+(IIf([2nd_Reason]="First Processor" And [2nd_Transfer_Date] Between #4/30/2013#
And #6/1/2013#,1,IIf([3rd_Reason]="First Processor" And [3rd_Transfer_Date] Between #4/30/2013#
[Code] .....
I get wildly inconsistent results. On one of the types, it's only counting where it's the 1st_Reason. Some of the others return the same value regardless of which month I have selected.
View 2 Replies
View Related
Dec 6, 2006
I have made a few lookup tables in the database I'm working on using the lookup wizard and entering the text I want in them. I made a form and they come over fine on them.
My question is that I thought that there was a way when you entered something other than was in that field it would automatically add it to that list and you could just select it next time instead of typing it in again. Is that possible or am I thinking of something else.
View 2 Replies
View Related
Mar 12, 2011
I have been trying to modify a sample database to suit what I want to do but I am getting stuck on the very first part cascading combo boxes i want to open a form and add multiple items to a table - first i want to select, from a combo box a department - then select a supplier from all the suppliers/manufacturers related to that department then select a stock item based on the description from all the items available from that supplier
I have attached the database I am using to modify and my database.
View 5 Replies
View Related
Nov 7, 2004
Hi just started working for a Company and I found they have tons of
databases but no structure or standard, so I would like to build a tool that will go thrue a list of databases and get all the table names, the items and type. Is there functions in vba that can get me a list of the tables in the database and the get all the items in the database ?
Thanks on any input
View 2 Replies
View Related
Jul 16, 2015
I have a form where there are 2 list boxes: Part_List and acbPartList. acbPartList has multiselect enabled and what I want to do is be able to select multipler records in that list box and then press a button to add those selected records into the other list box. Here is my code for my button:
Private Sub addItemButton_Click()
Dim varItem As Variant
With Me.acbPartList_Existing
For Each varItem In .ItemsSelected
Me.Part_List.AddItem (varItem)
Next
End With
Me.Part_List.Requery
Me.Refresh
End Sub
I'm not sure if passing varItem is correct, but regardless, it isn't working because Part_List is based off of a query, its not a value list.
View 3 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 5, 2015
I have managed to amend records based on the user selecting multiple items in a list box by using the following code.
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("Usage", dbOpenDynaset, dbAppendOnly)
Set ctl = [Forms]![frmsearch]![lstSelector]
[Code].....
what I need to do now, is to loop(I think) through and amend all the records in a populate listbox, with no selections, similar to above, but without selection.
View 3 Replies
View Related
Feb 28, 2007
Hi everyone,
I have been making progress with customizing a
MS Access program, but one major problem is that
I have been trying to make a List Box or Combo Box
that I can use to enter data in the TABLE, but I
find that I get a pull-down list that has the list of
values from only the parameter that is primary key.
More specifically, the program is set up as follows:
I have two tables in this program: One that is called
"invoices" and one that is called "items". In both of
these tables, there is one common parameter, which
is "Item Number". Item number is the primary key,
and I used the "relationships" function to tie this
parameter to itself between the two tables.
I was successful in setting up a list-box for the
"Item Number", but when I try to set up a list-box
for another parameter that is supposed to display
a person's initials, the pull-down list displays the list
of item numbers instead of the list of people's initials.
In fact, I don't know if there is an extra step I need to
take so that the database stores a list of people's initials.
Instead, I just fill in the initials in the field for each
record for which the "Item Number" is the primary key.
How can I get the list-box to pull down a selection of
different people's initials, or in other words how can I get
all the people's initials to be stored so that the list of
initials can be looked up. Just so you know, I have tried
different choices of entries in the "Lookup" tab in the
Design mode of Tables, including Display Control,
Row Source Type, Row Source, and Bound Column,
but the outcome is that the only parameter that I
get get in the pull-down list is the primary key, which
is Item Number.
I appreciate any help you can offer in explaining how
to correct this.
On a separate note, one of the parameters is "Date", and
on the reports, I'm trying to figure out how to filter a
specific date range so that I can limit each report to a
specific month. Please advise me on this procedure as well.
Thanks.
View 7 Replies
View Related
Mar 24, 2006
Hi,
I would like to be able to select multiple items from a dropdown list like we often see on web forms. The kind where you hold down the control key to select up to 5 items. Is this possible on an access form? If so, how would do we implement, and how is such data be stored?
Thanks!
~Bruce
View 12 Replies
View Related
Sep 29, 2014
I am using the following code in a textbox (CountryID) to show a country name (CoName) from a table (tblCountry). If the comboBox (cmbCoName) in current form (frmCountry) has no entry then CountryID remains blank, great. If I use the form to add a new entry then CountryID flashes because it does not recognize the entry. Can I use "or" to add another condition in the IIF statement so that it allows the "not in the combobox list" entry?
Code:
=IIf(IsNull([txtCoName]),"",DLookUp("CoName","tblCountry","Country_ID = " & [Forms]![frmCountry]![txtCoName]))
View 2 Replies
View Related
Jul 8, 2014
I have added a list box to a form with Single Selection mode on. Its purpose is to improve the interface. It contains items that represent all records. Clicking on the list box causes the form to jump to another record.
The problem is following: if I click on the List Box, it clears the selection and nothing is highlighted but it jumps to correct record. When I use standard record selection buttons, it highlights the correct items.
I read the index of selected item from .ListIndex property because Selected() does not work in a Single Selection mode. However, this is read-only property and I cannot use this to highlight the item back from VBA. But when I use Selected() it is not working. I mean when I click again on the same item it's selected. It's weird. I attached a simple database file with this problem.
The second problem is, when using standard record selection buttons, access iterates through all records and then jump to empty one. That is not like a new record. I don't know which event to use to control this situation. I would like to deselect all items, let user enter the data and re-query the List Box with a new record.
View 3 Replies
View Related
Dec 1, 2014
I have the following tables:
tblOrderDetailsKeyItemIdOrder QtyPO#001A204001002B204001003C304001
tblTransactionsKeyTransDateItemIDReceivedPO#0011/1/2014A1040010021/2/2014B2040010031/3/2014A54001
I also have a table that list the items and on that table the column ItemID is the key.I want to create a query that will list all items ordered on a PO# and how many have been received so far. With that I will calculate the outstanding qty. I want to see:
qryOutstandingPOPO#Item IDOrder QtyReceivedOutstanding4001A201554001B202004001C30030
But all I see is this:
qryOutstandingPOPO#Item IDOrder QtyReceivedOutstanding4001A201554001B20200
It is missing item C because I have not received any yet so there are no records in the Transactions table for it to select.
View 4 Replies
View Related
Jun 17, 2015
I have a multiselect listbox and two date fields (StartDate & EndDate) in an Access form.I am trying to add records to the Table through the form on a button click.I select multiple items from the list box and the date range between the start date and end date will be equal to the items selected from listbox.For each item selected from the list box I need to add a separate record with a date.So the first record will have List box item selected1 and the start date.Next record will have item 2 from list box and date as dateadd("d",startdate,1)And final record will have last item selected from the listbox and date as enddate.
View 5 Replies
View Related
Feb 1, 2013
I understand how to pass text boxes to bookmarked locations in Word, but when it comes to combo boxes, list boxes, or option buttons, I am lost. So, my problem this time is the following:
I have an access user form that is asking the user to input data and make selections. Once entered I am trying to get everything to export directly to respective bookmarked locations in a Word Report. I have my text boxes working and I have the combo box now working. The issue I am experiencing is with the user making multiple selections from a list box and I am not really sure how to get that to export to the word document.
Here is what I have:
Code:
Dim strNames As String
Dim ctl As Control
Dim varItem as Variant
'ensure the user has made a selection from the testers name text box
If Me.testersNamesText.ItemsSelected.Count = 0 Then
MsgBox "You must select at least 1 Capability Testers Name"
[Code] ....
I am very new to trying to code with the Visual Basic side of things, I know this is probably the best method to do this but the issue that I am experiencing is receiving a Null error for the line with
.Selection.Text = (CStr(Forms!WebBasedIFV!testersNamesText))
And when I attempt to pass the strNames in place of the testersNamesText I receive that the user form can't find the field "strNames" referred to in my expression.
View 7 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 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
