I'm populating a combo box from a query. I'm running a DCount on 2 criteria. The user selects the criteria from 2 combo boxes. If the user types * into the combo box, I want to be able to loop through each combination from the combo box values. I need to get the list of values from the combo box and put it into a string array, however it throws a type mismatch if I did something like
BRANCHES(x)=CStr(cboBranches(x)) 'cboBranches(x)=cboBranches.Value(x)
So I tried to run the sql from vb but found out that I can't return a string value from that (vb sucks). So I'm back to trying to get the values from the combo box. Any help would be appreciated.
Normally I would gather these up into an array using ASP and email the split up comma separated values out to the client via an email.
Code:drinks=Request("drinks")drinks_split=split(drinks,",")'email drinks to client
However, this time I need to store those values in an access database. I am assuming that the client may want to run queries on the different array values, for example, how may people chose coca cola and sprite.
Is it better to store the value separately - one Ms access field for coca cola, one for sprite (which is what I thought I would have to do to create queries on this) - or can they be stored under one field heading "drinks" and queries be performed on different values within that field?
Could someone explain a quick way of doing the latter i possible in MS access and the SQL for querying?
How do I delete values in an array? I want to reuse that array but first I need to delete its values so I would start with Arr(0) rather than where I stopped off with the last loop at Arr(10) [assuming the last calculation inserted until Arr(9)]
Here is my code:
Set rst = CurrentDb.OpenRecordset( _ "Select * from dbo_ProductStructure where ChildProductNbr Like '*" & txtPartNumber & "*'") 'search associated fields with user input While rst.EOF = False ReDim Preserve Arr(i) Arr(i) = rst.Fields("ParentProductNbr") i = i + 1 rst.MoveNext
I'm trying to store all the OrderNumber + Item combinations in 2 arrays and then because the OrderNumber column really contains 2 values I'm interested in, I split it up and store that column's values in 2 arrays. So in total, I have 3 arrays. An array for Item, an array for Order, and an array for RepId (which is the one that I split up from the OrderNumber column).
Anyways, when I print the RepId array with the ' MsgBox PostValCol1(x) ' It prints 4 values like it's supposed to. But when I tested it again by looping through the values and just doing a MsgBox, It goes for a long time and that's because it has a lot of empty values in that array. How to get rid of those empty values/not store them in the first place?
Code:
Set rop = CurrentDb.OpenRecordset("Select OrderNumber, ItemNumber From dbo_EntryStructure Where (ProductNumber = '" & txtPartNumber & "') AND (ActionCode = 'I')")
While rop.EOF = False ReDim Preserve ArrRepOrder(j) ReDim Preserve ArrItem(j)
arrParams = Split(arrLocations, ";") For i = 0 To UBound(arrParams) MsgBox (arrParams(i))
strSql1 = "SELECT branchName FROM ustax_customerBranchLocationsTBL WHERE branchName = '" & arrParams(i) & "' AND branchCustomerParentID = " & Me!customerID & "" 'MsgBox (strSql1) DoCmd.RunSQL strSql1 Set rs = db.OpenRecordset(strSql1) Dim count count = rs.RecordCount
Next
Above is my code, what i am trying to do is loop thru the array and check if the values are in a table. I am getting an error after the first loop of 'A RunSQL action requires an argument consisting of an SQL statement', why would I get this error if I am looping thru the array? Wouldn't the sql statement just be read with the new value each time?
I have to deal with string arrays that store text. I need info on copying, comparing, appending arrays. Also on passing arrays as parameters to subs or functions. Where I can get to this info quickly without having to browse through many screens.
In addition to this I have some questions:
I have to find the duplicates of values in an array. Here is the code that I use.
Code: Sub FindDuplicates() Dim I As Integer, J As Integer, IEND As Integer, text() As String ReDim text(IEND) For I = 1 To IEND - 1 For J = I + 1 To IEND If text(I) = text(J) Then text(J) = "" Next J Next I End Sub
It works but is not performing well. Are there more efficient ways of doing this?
I've created an array that I created and declared as a Public array in my module. I created a function that populates the array so that I can use the values in another function. I've gotten the array to populate but when I go to use the values in the array in another function, the array appears at Empty. I seem to be stuck on declaring it properly or something so that it can be used by other functions.
Public arrWebIDs As String Public Function FillArray()
I am working on setting up a Document Control System and have a table, called List_of_SOP where I have, amongst others, the following fields:
- Document Title - Document Number (unique identifier) - Responsible - DL (where this is a multiline lookup column where one ticks the names of the people to whom this document needs to be distributed)
I want to be able to fill an array with the checked values of the DL field, given a particular Document Number. So far, the code is somewhat on these lines:
Dim rs1 As DAO.Recordset Dim rs2 As DAO.Recordset Dim rs3 As DAO.Recordset Dim db As DAO.Database Dim strSQL1 As String Dim strSQL2 As String
I have a table with application records. One of the fields captures schools the applicant will work at. This field stores data in comma delimited format. There could be 1 school name; there could be 5 school names.
My ultimate goal is to build a report which shows me records of all applications, grouped by school choice. I want to see: School A was selected by 5 people, School B was selected by 7, etc.
Is it possible to write a query from this table that will enable this?
Basically, what's the best practice or how do we store a query's value into an array then checking what the max or min value is and how to check if let's say "4" is in the array?
Lets say I have table with one of the column header as notes. The notes is of type 'text'
In the notes column information like the following is typed out
MAX O.D 3.456" Min ID 1.2" and OAL 3.4"
or
MAX O/D 5.456" Min I.D 1.2" and Min Length 4.4 inches
I want to pull out the first numerical value (3.456 - first example, 5.456 -second example) and the last numerical value (3.4 -first example and 4.4 in second example). How would I be able to do this.
So the end result should be 2 more columns with max od as one and min length as the other. Regards George
I have a form which displays contact details for customers. I want to add a button to the form which concatenates name and address fields, adding spaces and line breaks, and then copies the full name and address so that it can be pasted into other applications (e.g. into a letter).
To do this requires that I implement some code to perform the following actions, but I can't figure out how to do it. Can anyone help?
1. Check Title field. If it is not null, take it's value and add a space after it. 2. Check First Name field. If it is not null, take it's value add it to the output of step 1 and add a space after it. 3. Check Surname field. If it is not null, take it's value and add it to the output of step 2 and add a space and a line break after it. 4. Check the Company Name field. If it is not null, add it's value after the line break and add another line break. 5. Repeat this for every field until the end of the address is reached, then copy the result.
I have got like subform which I think is the tech term I use it to filter dates on field "date raised" and open's a report depend on date's entered in txtstartdate and txtenddate it works perfectly but I need to add some check boxes to check other field's are true/false ...
Code: Private Sub cmdPreview_Click() Dim strReport As String Dim strDateField As String Dim strWhere As String Dim lngView As Long
[Code] ....
I need to add code to the text in red well I'm guessing
here goes with what I would like to add job cancelled1 check box name field name= job cancelled job on hold1 check box name field name = job on hold void property1 check box name field name = void property
E.g. if I put date range in my date boxes and tick job cancelled show all record in that date range cancelled same with job on hold and void property
here some code I did but wont work as it not in the same strwhere above
Code: if me[job cancelled1] = true then strwhere = [job cancelled] = 1 'field name else strwhere [job cancelled] is null end if
Some how need to add the strwhere to the code above ...
I get an error for this code: this is the error, i can firgure out how to have the string not be the textbox, but sub in the texbox name.
Code: myFormstr = Array("F_Value1", "F_Value2", "F_Value3", "F_Value4", "F_Value", "F_Value6", "F_Value7") For Each mystr In myFormstr me.cboProduct = Me.mystr Next mystr
I have a table field which long ago was merged from several other fields. When the data was merged into the field it was delimited by "1." then "2." up to "5." Example: MergedField = "1.Animal 2.Large 3.African 4.Grey 5.Long Nose"
I now want to split it appart in a query where "1.Animal" goes into expression1, "2.Large" goes into expression2, etc.
I need to base the text on where the one number begins and grab everything until the next number in the mergefield is detected.
I have one field where string contains several words separated by semicolon and my goal is to be able to remove duplicates within the string and keep only unique values. Here is an example:
My db contains an unlinked form that is used to append records to 1-3 separate tables (tests to see if master records exists, appends if needed, moves on to child records). One of the fields is a text field meant for notes. The VBA errors out whenever this text contains apostrophes or quotes.
Is there a simple way to recall the string field with quotes and apostrophes intact and append it, or will I have to validate and remove them from the string?
I have several comboboxes (6) on my form.How to populate these comboboxes with values depending on selected value in previous combobox.
Example.Lets say that you select value "Audi" in combobox 1, then available values in combobox 2 should be "A4","A6","TT" etc. and if you selected "BMW" in combobox 1, then available values in combobox 2 should be "3-series", "5-series" etc...
I have to join multiple values into a string for summarizing data on reports and exports. This process in vba is taking up to 10 minutes to process and will get worse as the size of these reports grow.
My method so far is to query the individual items into a recordset, loop through the values, adding them to the string then return the string in the query.
Here is an example:
Public Function SO_Description(intSO As Integer) As String Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQLSelect As String SO_Description = "Profiles: " Set db = CurrentDb
I would like to set up rules/constraints such that the value selected in combo box A determines the available values in combo box B. For example, "Combo box A" is bound to the [PartnerType] field and "Combo box B" is bound to the [PartnerRole] field. Let's say that the two choices in Combo Box A are "LLC", "LP", and "Corporation". There are 5 possible choices in Combo Box B: "X", "Y", "Z", "Q", and "U". If a user selects "LP" in Combo Box A, I would like Combo Box B to only show choices "X" and "Y". And if a user selects "LLC", only choices "Y", "Z", "Q", and "U".
Similarly, I would like to set this up so that Combo Box B is not initially visible--it becomes visible when a user selects "LLC" or "LP". If a user selected "Corporation", Combo Box B would remain hidden.
I have a main form that filters data in a subform based on selections via combo box users make on the main form.
So I have the 'department' and 'manager's name'. If someone selects 'Human Resources' from 'department' combo box, then I only want to see the managers that are in the Human Resources departement when I drop down the combo box for 'manager's name'. Currently I'm seeing all the managers and a user can select a manager that is not in human resources and get no data returned. I prefer for him to get a list of those that are in that department only. The source of the combo box is a query.
Once a supplier name is selected from the combo box i only want it displayed that suppliers products in the subform, subform. (products combo box) I can get a basic query to show these results but can get the combo box to do the same.
I have some combo boxes on a form which are set to display all the distinct values from a field in my database. If any record has a blank in this field, then a blank entry appears at the top of the list box. Is it possible to set blanks to be excluded?
