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
inserting values from a listbox in to a table. I have a table with a field named: PrefReports. This field will hold data from values in a list box that the user selects.
Table: Profiles Field Name: PrefReports Type: Memo
The user utilizes a form to input the values necessary for the query. I then have the query tied to a command button. When the command button is clicked, the query is executed.
How can I take what the user selects in the list box, and insert into my TblProfiles.PrefReports field? When I run the query, all other information makes it to the table except the list box values.
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 :)
Hi, I am wondering If I can Sum the value of rows in one field of my Query Table and Inserting that Calculated value into the bottom of the summed Row in the VBA or through Query Design View.
Please let me know, Thank YOU!
Below I have attached the picture of the data that Im trying to sum and insert into a new row that Hopefully can be created through MS access Query.
Hi, Am currently working on a project for AS IT. one problem that i need to come across is trying to see data in a field that comes from a query?!?! Confused?! so am i!
When i first open the form, a paremeter query appears asking me to insert a surname (which i have created) when i insert the surname....and click on the drop down button, it gives me three values...ForeName, Surname and class
When clicking on the pupils name, i want the Class of the student to be inserted into the Class Field!
I have a load of data in an excel spread sheet that I'm trying to put into an access using Excel VBA (Jet/ACE SQL I think!?). I do this by constructing INSERT INTO strings and looping through my spread sheet. Let's say the primary key is associated with the field [Company] which holds the company names. A simplified version of what I was using (it all works great ) is below:
Code: Set rs = New ADODB.Recordset Set cn = New ADODB.Connection cn.ConnectionString = GetConnectionString() cn.Open cn.Execute strSQL
where strSQL =
Code: INSERT INTO tblTestMDB ([Company], [Date]) VALUES ('BP', '30/09/2012')
Unfortunately, I am having issues with users adding the same company twice. I'd like it to:
- update the existing company info if the company already exists - add the company if it is not already there.
I'm trying to do it in two stages. First an update, then an add. The first part works great :
Code: UPDATE tblTestMDB SET [Date]='21/03/2015' WHERE [Company]='BP';
The second part: (I have tried numerous variations on the syntax):
Code: INSERT INTO tblTestMDB ([Company], [Date]) VALUES ('BP', '30/09/2012') WHERE NOT EXISTS (SELECT * FROM tblTestMDB WHERE [Company] = 'BP');
I find the error messages rather cryptic but they include things like "Query input must contain at least one table or query." or "Missing semicolon ( ; ) at end of SQL statement." (<== No I'm not!?
I have a form with many fields and one field has an OnChange event to run an insert query or log an entry in a table about the change. I get insert entry written with everything except one field.The OnChange event code is:
Code: If AddressStatusFld = 2 Then 'If it has changed from Current to Non-Current create a Contact Log entry for this matte If (Val([MatterShortNoFld]) Mod 2 = 1) Then Me.OperatorFld = 16
[code]....
I just can't work out why the record in MatterContactsMade table has a null value for the field Operator.
I want to insert data into a table where a symbol does not already exist for that date. I am not sure what i am doing anymore :( So please help me verify that this won't insert duplicates? Am i using the correct logic? :confused:
"INSERT INTO StocksData (Symbol, [Security Name], [Market Category], " & _ "[Reg SHO Threshold Flag], FileDate) " & _ "SELECT DISTINCT Symbol, [Security Name], [Market Category], " & _ "[Reg SHO Threshold Flag], '" & fileDate & "'" & _ "FROM " & sTableName & _ " WHERE ((Symbol Not In (select Symbol from StocksData)) AND (" & _ fileDate & " Not In (select FileDate from StocksData)));"
I'm trying to insert 10% of a dataset from dbo_billing into another table Random_Temp. Another form is open when this query is to be ran that passess in the billyear and billmonth... I'm sure it's a syntax issue as I can isolate the random number part and it displays the appropriate data, I just can't re-write it to insert into the other table:
INSERT INTO Random_Temp ( indx, peopleId, audited ) SELECT TOP 10 PERCENT b.indx, b.peopleId, b.audited FROM dbo_Billing AS b WHERE (((b.billYear)=[Forms]![billing]![billyear]) AND ((b.billMonth)=[Forms]![billing]![billmonth]) AND ((b.recertifying)=-1)) ORDER BY Rnd(-(1000*b.indx)*Time());
I am just beginning to manipulate Access from Excel.
I am trying to insert a row into a table. I am adding values for every field (8) except an Autonumber.
I have 2 Insert strings which are identical except for the fact that the one that doesn't work, doesn't specify which fields I am entering data into, which I presume shouldn't be necessary in this case.
When I try to use the second version I receive the error message...
Number of Query Values and Destination Fields are not the same.
Here is what I am trying to do. I have a query with 2 fields. "Time In" & "Time Out". What I would like to happen is this. Whenever a character, let's say a "t", is entered into that field I would like the current time to populate that field. Right now we are actually typing in the time. I have the fields set up as DateTime fields currently.
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?
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?
I have a form for member details that comes from the member table and I want to include a button on the form that will remove the member details from the member table to a member history table and then delete the details from the member table. When moving the record I also want to include details from another table called 'promotion', this table is linked to the member table via the member ID (Foriegn Key).
Firstly I want to know if this is possible and if so how I would achieve this. I am having trouble getting the SQL query to identify the current record that the form is displaying so I can select additional information and move it into the Member History table.
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 :(
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!
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 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]));
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?
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...
Hi there. I have a problem with queries that i am creating on the fly with access. My setup is this:
A table called Order which has various fields including a field called Date Taken. The format of this is Date/Time with the format d/m/yyyy.
A form called frmMultiSelect which has a listbox called lstdates and a command button called cmdOpenQuery.
in lstdates the row source is: SELECT DISTINCT Order.[Date Taken] FROM [Order] UNION select "......ALL......" from [Order];
And in the button onClick event i have the following code (adapted from a web source):
Private Sub cmdOpenQuery_Click()
On Error GoTo Err_cmdOpenQuery_Click Dim MyDB As DAO.Database Dim qdef As DAO.QueryDef Dim i As Integer Dim strSQL As String Dim strWhere As String Dim strIN As String Dim flgSelectAll As Boolean Dim varItem As Variant
Set MyDB = CurrentDb()
strSQL = "SELECT * FROM [Order]"
'Build the IN string by looping through the listbox For i = 0 To lstDates.ListCount - 1 If lstDates.Selected(i) Then If lstDates.Column(0, i) = "......ALL......" Then flgSelectAll = True End If strIN = strIN & "#" & lstDates.Column(0, i) & "#," End If Next i
'Create the WHERE string, and strip off the last comma of the IN string strWhere = " WHERE [Date Taken]=" & Left(strIN, Len(strIN) - 1)
'If "All" was selected in the listbox, don't add the WHERE condition If Not flgSelectAll Then strSQL = strSQL & strWhere End If
MyDB.QueryDefs.Delete "qryCompanyCounties" Set qdef = MyDB.CreateQueryDef("qryCompanyCounties", strSQL)
'Open the query, built using the IN clause to set the criteria DoCmd.OpenQuery "qryCompanyCounties", acViewNormal
'Clear listbox selection after running query For Each varItem In Me.lstDates.ItemsSelected Me.lstDates.Selected(varItem) = False Next varItem
Exit_cmdOpenQuery_Click: Exit Sub
Err_cmdOpenQuery_Click:
If Err.Number = 5 Then MsgBox "You must make a selection(s) from the list", , "Selection Required !" Resume Exit_cmdOpenQuery_Click Else 'Write out the error and exit the sub MsgBox Err.Description Resume Exit_cmdOpenQuery_Click End If
End Sub
When i open the form and look at the list box the dates are there along with the ......ALL...... however the dates whose day is less than 10 are displayed with the leading 0 e.g 04/12/2005. When i click the run query command button on one of these dates the query is opened blank. the sql code output is: SELECT * FROM [Order] WHERE [Date Taken]=#04/12/2005#;
If it was changed to 4/12/2005 it works but i dont know why access is putting in this leading 0.
Please help me!! I can send the database if needed
I want to have users be able to choose query "x" "y" or "z" from a listbox. Upon selection of query "x" "y" or "z" from the listbox, I want the query to run? I want to run this query from form "RunQueries" Does anyone know how I can do this? Thank you in advance for any suggestions, I appreciate all the help I can get
