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?
View RepliesI have run into an issue with a basic DLookup. The database has grown in size and now we could have multiple entries, but I want it to return a certain one. So the information could be in it three times. Of course DLookup stops after the first one. How do I get it to loop to check the entire table? Someone mentioned to me to use a recordset, but how to write that as I have never used it before. Below is what I was using until this new request came up.
<code>
Private Sub txtloan1_AfterUpdate()
If IsNull(DLookup("[loan1]", _
"settlement", _
"[loan1]=""" & Me.txtloan1.Text & """ AND [status] = 'Open'")) = False Then
Cancel = True
MsgBox "Test", vbOKOnly, "Warning"
End If
End Sub
</code>
This was also executing after the user entered the information within a text field. I did not want them to enter all the data and then have it come back as a duplicate.
Any Single line of code available?
View 2 Replies View RelatedI want to use an array to store data from a list box into a variable. I want it to be able to store one value, or multiple values, depending on what is selected.
Main problem: this list box feeds off a table which has employee names and their e-mails. The list box itself only shows the names, and when I select what I want the array to store is their e-mails, not their names.
Code:
Dim strNames As String
Dim varItem As Variant
Dim intCount As Integer
For Each varItem In Me.lstNames.ItemsSelected
intCount = intCount + 1
Select Case Len(strNames)
[Code] ....
That code successfully displays the item I selected, but only displays the name. I need to make it look in the table and get me column #2. I also want it to be able to select more than one item at a time.
Now the getrows is working fine at my end. Now I have a different problem at hand:
Sub Test2()
Dim myrset As Recordset
Set myrset = CurrentDb.OpenRecordset("SELECT * FROM Holidays;")
myrset.MoveLast
[Code]....
Recordcount is coming fine
Manual Array is also giving the right result
But the Index method is giving the wrong value. Why?
(Holidays table is just having the values in the array only ie. #08/15/2014# and #08/29/2014#)
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
Wend 'end of while loop
x = Arr
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)
[Code] .....
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()
Is it possible to create a field in a table that stores only two possible data choices: a check or null value ?
View 6 Replies View RelatedI am trying to perform a calculation within an IF then Statement. The difference is I need Access to remember a values to complete the calculations prior to setting the final answer. I think this is basic however I am a novice and can't seem to get it to work.
Code:
If [Forms]![Jobentryfrm]![StyleJobCurrentSub]![Assembly] = 39 Then
BL = ([Forms]![Jobentryfrm]![Height] * 2) + ([Forms]![Jobentryfrm]![Length] * 2) + 8
bw = [Forms]![Jobentryfrm]![Length] + [Forms]![Jobentryfrm]![Width] + 12
[code]....
I'm building a database to organize the editing of a massive report my office is working on. I've got a big table of all the sections. Each section has a unique ID and a version number. I wrote the queries so that they return only the most recent version (i.e. the Max version # of each section.) The results of the query appear in a subform.
I was able to use VBA to allow the user to double click on the record in the subform, and look at the record in another form. (I was really proud of this.)
Anyway, it turns out what I need is to double click on the record in the subform, and create a new record based on this record, but increment the version # by one.
I've created an unbound form for this, and googled around. I need to use VB to store the variable, and then put it in the unbound text box.
I am trying to create a form with a button attached to each record that would allow the user to click the button and it would automatically open outlook and fill in the TO:, SUBJECT: and BODY: fields. Here is the code that I currently have:
Code:
Private Sub Command33_Click()
Dim strEmail As String
Dim strMsg As String
Dim oLook As Object
Dim oMail As Object
Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.CreateItem(0)
[code]...
There are two issues I keep running into:
1. This code opens outlook and populates all of the fields but pastes the email incorrectly. Instead of pasting just the email (email@email.com) it pastes the html tags as well (email@email.com#mailto:email@email.com#) which means that the user would have to delete everything between the #'s in order to send the email every time.
2. I currently have the email BODY pulling from a table but this obviously limits what I can do. I would like to simply encode the BODY within the VBA code. The setup I am looking for is:
one paragraph
a blank line
a hyperlink to a website
a blank line
another paragraph
I have a subform in datasheet view. I would like to put a button on the mainfor that checks all the subform records for spelling mistakes. Is this possible?
View 3 Replies View RelatedI have a table like this :
Fields 1 --- field 2
A --- tuesday
A --- wednesday
A --- thursday
B --- tuesday
B --- wednesday
I want to end like this :
Fields 1 --- field 2
A --- tuesday,wednesday,thursday
B --- tuesday, wednesday
I was thinking of doing a loop in vba with recordset and a filter on field1 and concatenate the days of field2 in the first reccord of each letter.But my table is big so my code needs to be fast.
how to do this ?
I have a table in which there are approximately 15k records. The name field has approximately 2500 unique values. I need to search the table for multiple names and return the results from the table. This would then be used as a report. Due to the users being very novice, a form is needed for the search.
Example:
Search for Smith, John; Wilson, Bill; Jones, Jeff
Return
Name Year Brand Make
Smith, John 1999 Oldsmobile 88
Smith, John 2001 Chrysler 300s
Wilson, Bill 1994 Porsche Carrera
Wilson, Bill 2007 Kia Sporty
Jones, Jeff 2004 Chevrolet Camaro
Jones, Jeff 2011 Chevrolet Impala
My knowledge of forms is basic, however, I can be a quick learner if on the right track.
Hi all,
I had table with following data
Table
f1 f2 f3
1 10 aa......
1 11 aaa...
2 10 bb...
2 11 bb.......
f3 is memo field
I had to retrieve data by grouping records based on f1 value
so i gave groupby in totals section to f3 field also.
I am getting the values correctly, but memo field is truncating.
Its only displaying first half arround 236 charecters only.
If i query directly without performing any group by
then i am getting entire data for the memo field.
please any one give the solution.
waiting for your help.
Thanks
Hi,
In the query field i am putting: -
Consecutive Months: Query_Month_Consecutive([Report_2_group_data].[Availability], array([Monthly_availability_CT].[12],[Monthly_availability_CT].[11],[Monthly_availability_CT].[10],[Monthly_availability_CT].[9],[Monthly_availability_CT].[8],[Monthly_availability_CT].[7],[Monthly_availability_CT].[6],[Monthly_availability_CT].[5],[Monthly_availability_CT].[4],[Monthly_availability_CT].[3],[Monthly_availability_CT].[2],[Monthly_availability_CT].[1]))
Basicly the values of them fields are entered into the query, all fields exist and everything is ok.
Anyhow the function is not loaded, i have tested with a simple msgbox and it does not do it.
However when i simply do
Consecutive Months: Query_Month_Consecutive([Report_2_group_data].[Availability], Monthly_availability_CT].[12)
It works and the msg box appears?
I really need it to be an array for what im doing any help would be great
Thanks
k0r54
I've got the following code on a form. The entirety of the sub is listed just in case. The relevant portion is below.
Code:
Private Sub Form_Timer() 'timer set to fire once per minute
'AutoPurge old records (>7yrs) and take action if Hotword and/or NANU expire
Dim Minute As String
Minute = Right(Format(TimeValue(Now()), "hh:mm"), 1)
'Only run these every 10 minutes
[Code] .....
The part I'm having trouble with follows:
Code:
If DCount("*", "qryNotifyExpiredNANU") = 1 Then
MsgBox "The NANU " & DLookup("NANUnumber", "qryNotifyExpiredNANU") & " has expired. It has been removed from the Active NANU list.", vbOKOnly, "NANU Expired"
ElseIf DCount("*", "qryNotifyExpiredNANU") > 1 Then
MsgBox "The NANUs " & !!!!RETRIEVED VALUE HERE!!!! & " and " & !!!!RETRIEVED VALUE HERE!!!! & " have expired. They Have been removed from the Active NANU list.", vbOKOnly, "NANU Expired"
End If
The intent of that (incomplete) ElseIf is to provide a list of all NANUnumbers that have expired. When a single NANU expires, the DLookup works perfectly since the query only has one record. But I'm stumped on dealing with anything more than one value.
The desired result is that the value from the NANUnumber field of each resulting record in qryNotifyExpiredNANU would be used to build a string for use in the MsgBox.
Using DLookup is out (I think) because I can't identify which records the query returned to apply a WHERE clause.
I also looked into using Loop with a RecordSet, but can't figure out how to approach it.
So to sum it up, I don't know how to grab each value present in the query and I also don't know how to concatenate a string with a dynamic number of values.
I'm trying to set the control source for a control on my report that describes the number of portions that goes into a box. Sometimes there is a specific number of portions per box and sometimes it's a range. When the number of units is specific, then it is entered into the MasterCaseMinUnits Field. If there is a range of units possible than the min is entered into MasterCaseMinUnits and the max is entered into the MasterCaseMaxUnits.
I want my report to look at the mastercasemaxunits, if it is blank it will only display what is in the MasterCaseMinUnits field. If there is a value in the masterCasemaxUnits field, then it will display the masterCaseMinUnits & " - " & MasterCaseMaxUnits. this is the code I'm Using:
Code:
=IIf(Nz([MasterCasemaxUnits],[MasterCaseMinUnits])=[MasterCaseMinUnits],[MasterCaseMinUnits],[MasterCaseMinUnits] & " - " & [MasterCaseMaxUnits])
When I run my report, the control displays: #Type!
I have a bit of code that uses controls on a form to batch add records to the tables. Before the code executes, it needs to do a check to make sure that all of the needed data has been supplied.
Code:
If IsNull(txtDateAdded) Then
MsgBox "Please supply the date that the Label record was created"
Exit Sub
Else
If IsNull(cboRecordCreator) Then
[Code] ....
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 am making an asset table which amongst other fields have fields for "serialNo" and "Manufacturer"
I am trying to write some code that after update of manufacturer in the form, will check to see if that serialNo and Manufacturer exist.
ive managed to do it for one value, using
If Not IsNull(DLookup("[serialno]", "Assets", "[serialno] = '" & Me!serialno & "'")) Then
msgbox "blah blah"
which works great, but am struggling when i'm asking it to lookup two values.
How can I check if a table contains blank fields/values.
If there are blank fields I want to replace them with 0.
I'm certain this question has probably been asked before, but I can't seem to find it!
I have a form field called fldTitle, and want to ensure users write something that is meaningful by evaluating words within the title to a table of keywords (tblKeywords). I know how to write the IF/ENDIF and the other stuff required, but am struggling to find the right commands to do the comparision. Could someone help me out?
Many thanks.
I have the following VBA code that auto populates a word template:
Private Sub Command24_Click()
On Error GoTo ErrorHandler:
ErrorHandler:
[Code].....
The code executes flawlessy but there are three values that are based off check boxes set as YES/NO (EMPLOYEDATREGISTRATION, EMPLOYED and FRINGEBENEFITS). These values show up as -1 for YES and 0 for NO.
Is there anyway to add code to mine that would allow me to change these values before they are sent to the Word template?