I have a form in which the user has the privilege to download some data from the table.the user will paste some of the numbers to the textbox from the excel in order to download the specific data.i need to write a query to parse the textbox using the in condition
View RepliesI am designing a book collection database where each book can contain multiple authors. I used three tables; tblBooks, tblAuthors, tblBookAuthorJOIN. The tblBookAuthorJOIN allows me to create a subform with drop down combo boxes in the Books form linking multiple authors to each books indexed field. The Author table contains the usual FirstName, LastName, MiddleName fields. I would to be able to display the names of each author in the forms header but I am having difficulty.
For example a book may have two authors such as:
Authors table would contain values such as
Last Name: Grisham
First Name: John
Last Name: Twain
First Name: Mark
Textbox in header would display: John Grisham; Mark Twain
The authors would be displayed in the following format in the same order as listed in the subform datasheet.
Attached is a graphic of the form.
Basically what I have is a form where a user has a drop down combo box that he can pick more than one value.
I then want to take what he has inputted e.g. Option1, Option2 and put that on a report in an unbound text box with another field value.
For example
=[Field1] & "/" & Option1/Option2
Those forward slashes are quite important as well, so any solution would need to include those.
I have a database in Microsoft access 2010. The database has a table that stores prospective customer records, and a form that is used to input a search criteria(s) via textboxes, which then queries prospective customers table and returns the records that contain the inputted search criteria(s).
An Example of Textboxes values on the search criteria form:
Textbox - name: bob
Textbox - address: Left blank
Textbox - category: car,boat,truck
I Have tried creating a query with the following
field: name
criteria: like “*” & name & “*”
field: address
criteria: like “*” & address & “*”
field: category
criteria: like “*” & category & “*”
SQL code:
SELECT customerName ,address,category
FROM prospectiveCustomers
WHERE customerName LIKE “*” & name & “*” AND address LIKE “*” & address& “*” AND category LIKE “*” & category& “*”;
That works, but only for one value in a textbox. Once there is more than one value in a textbox (e.g name: bob,smith), the query returns no records.
I have also tried splitting the values using the comma as a delimmter, then inserting the values into a new table. That is fine until one of the search criteria textboxes has been left blank. So the query I created will run, but returns no records.
SQL CODE:
SELECT prospectiveCustomers.name, prospectiveCustomers.address,prospectiveCustomers. category
FROM prospectiveCustomers, [SearchCriteria-name], [SearchCriteria-address],[SearchCriteria-category]
WHERE prospectiveCustomers.name Like [SearchCriteria-name].name AND prospectiveCustomers.address LIKE [SearchCriteria-address].address AND prospectiveCustomers.category LIKE [SearchCriteria-address].category;
Access 2010 - I would like to use DLookup to show results values from a table and display in a unbound textbox on a form. the results from each column in the table need to be on seperate lines, a break if you may. Here is the code I have so far.
Code: txtKeywords = DLookup("colKeyword", "tblKEYWORDS", "cboCategory = '" & txtcategories & "'")
In colKeyword Column In tblKEYWORDS table match what i select in cboCategory Combo drop down box and populate txtcategories textbox on form
What I would like to do is show All colKeyword results in textbox [txtcategories] and display each result on its own line!
example
entry1
entry2
entry3
entry4
Trying to figure out during the key down event -
a.) how to also stop a leading zero (e.g. don't allow 04, but allow 4)
b.) Don't allow any combination of numbers to be outside 1 to 36
Yes, I can do this on a validate event after the fact. Just wondering if there is anything that could put this all into the KeyDown event to prevent errors in the first place.
This code works great to prevent any key entry except 0..9, Tab, and backspace
Code:
Private Sub txtSectionNumber_KeyDown(KeyCode As Integer, Shift As Integer)
Select Case KeyCode ' only accept a number 0 to 9 Backspace or Tab
Case vbKey0, vbKey1, vbKey2, vbKey3, vbKey4, vbKey5, vbKey6, vbKey7, vbKey8, vbKey9, vbKeyBack, vbKeyTab
' do nothing and accept the value
Case Else
KeyCode = 0
End Select
End Sub
In the US Western States, a survey township is simply a geographic reference used to define property location for deeds and grants as surveyed and platted by the General Land Office (GLO). A survey township is nominally six by six miles square. They are assigned numbers of 1 to 36.
I've got a rapidly expanding database that I designed to do my quotes for work and now I'm trying to expand it to add up the invoice amounts when the jobs come to fruition. I've got 99% of it working well but as I'm not the only one entering data I'm trying to make it as foolproof as possible and here is my problem.
I have a multi-line textbox that receives 'vehicles' in an abbreviated format and I need to get them in order based on a sort column number in the appropriate table. This i can do but the code I have found removes the duplicates which I don't want as I can can two identical vehicles on the same job.
e.g
Vehicle Sort Order
UNT/FL 500
FL4/45 400
E7/F5 600
UNT/FL 500
I want as
FL4/45 400
UNT/FL 500
UNT/FL 500
E7/F5 600
I have tables that are ODBC linked to a SQL database. The source tables have record date and time stamps written into one field. This means that I can't use the Access date filter functions on these fields. Is there an easy way to parse this field into two fields, one each for the date and time?
View 3 Replies View RelatedI have a field in our database with five distinct strings, separated by a delimited (a slash or "/").
I've been able to successfully write the formula to parse the first field:
ARL: Left$([pipeline]![Short Description],InStr(1,[pipeline]![Short Description],"/")-1)
The second field:
BRANCHMGR: Mid(Left([pipeline]![Short Description],InStr(InStr(1,[pipeline]![Short Description],"/")+1,[pipeline]![Short Description],"/")-1),InStr(1,[pipeline]![Short Description],"/")+1)
and the last field:
DATE: Mid$([pipeline]![Short Description],InStrRev([pipeline]![Short Description],"/")+1)
how to get the third and fourth strings parsed. I know its going to include some nested InStr or InStrRev commands -- just having trouble figuring out the structure. Could potential need a LEN command as well.
Typical field might look like this:
"Name1/Name2/Description/Update/Date"
I have a record in a table (Table1) with the following:
Item Start End
A 2012 2030
I have another table (Table2) with the following:
Item Start
A 2018
A 2025
What I would like to do is update/append Table1 so that it looks like the following:
Item Start End
A 2012 2017
A 2018 2024
A 2025 2030
I am having a problem with parsing the array value to Forms!<arraryvaluehere>.dirty.
Please see the code below:
Public strListOfForms(0 To 2) As String
strListOfForms(0) = "frmForm1"
strListOfForms(1) = "frmForm2"
strListOfForms(2) = "frmForm3"
For i = 0 To 2
Debug.Print CurrentProject.AllForms(strListOfForms(i)).IsLoaded
Debug.Print Forms!strListOfForms(i).Dirty
Next i
Note: CurrentProject.AllForms(strListOfForms(i)).IsLoade d works and Forms!strListOfForms(i).Dirty does not.
Is this because it is taking "strListOfForms(i)" literally? Is there a way that I can get around this?
We publish a PDF file every week full of taskings that our subordinate organizations need to accomplish for the following week. I would like to have parse the PDF and store the data in a table. I think the only way to start this operation is to first save the PDF to Text (Plain) as this create some way to delimit and parse the data. So, once it's saved as a txt file, it basically looks something like this:
Code:
//
Column 1 Data/StuffIDontCareAbout/
StuffIDontCareAbout/StuffIDontCareAbout//Column 2 Data
StuffIDontCareAbout/StuffIDontCareAbout/
Column 3 Data
[Code] ....
...and so on and so forth anywhere between 50-100 more times
The entire text file will always start and end with "//" at the top and bottom. You also see that each tasking paragraph (starting with "Column 1 Data" and ending with "Column 3 Data") is preceded by a "//" on its own line, and also followed by a "//" on its own line. The text file will always look exactly like this with each tasking paragraph having the same number of forward slashes.
Column 1 Data always starts on a new line under "//" and runs until the first "/" (single forward slash).
Column 2 Data always starts after the "//" on the 2nd paragraph line and runs until the end of the paragraph line
Column 3 Data always starts on the 4th paragraph line and runs until the end of the paragraph line
Here's a quick example of what I'm talking about:
Code:
//
37 NOS001/DCO/
TaskPer/TBD//310001ZAUG2014-292359ZSEP2014
GenText/Remarks/
(U/FOUO) This will contain the actual task description and details. You can see that "(U/FOUO)" contains a forward slash.
[Code] ....
So after it's parsed, my table would have the following new rows:
Code:
Column 1 | Column 2 | Column 3
37 NOS001 |310001ZAUG2014-292|(U/FOUO) This will contain
582 NOS012 |280001ZAUG2014-022|(U/FOUO) This another task
I'm trying to parse the following into an Array by splitting the csv file using a "," comma separator. There should be 63 different data pieces in this File. When I do a count of them from the (ubound array) i only get 54. The last data piece on each row gets concatenated to the first data piece of the next line. Is there a way to stop this from happening? This is causing problems with working with the data.
Date,Open,High,Low,Close,Volume,Adj Close
2013-06-07,1625.27,1644.40,1625.27,1643.38,3371990000,1643 .38
2013-06-06,1609.29,1622.56,1598.23,1622.56,3547380000,1622 .56
2013-06-05,1629.05,1629.31,1607.09,1608.90,3632350000,1608 .90
2013-06-04,1640.73,1646.53,1623.62,1631.38,3653840000,1631 .38
2013-06-03,1631.71,1640.42,1622.72,1640.42,3952070000,1640 .42
2013-05-31,1652.13,1658.99,1630.74,1630.74,4099600000,1630 .74
2013-05-30,1649.14,1661.91,1648.61,1654.41,3498620000,1654 .41
2013-05-29,1656.57,1656.57,1640.05,1648.36,3587140000,1648 .36
I have a report in which a textbox generates numerical values and letter values. I want to...On report load - if textbox = numbers then hide otherwise show if it contains letter values.
View 9 Replies View RelatedI 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.
I am looking to check 3 sub forms for values and if they contain any then delete them. After a bit of googling I discovered that you should use recordset.recordcount - to which I tried but I get method or data not found - is this a valid method?
My code to which Im using is:
If Me.NoneChargeable_Admin_subform.Recordset.RecordCount >= 1 And Me.NoneChargeable_Manufact_subform.Recordset.RecordCount >= 1 And Me.NoneChargeable_Research_subform.Recordset.RecordCount >= 1 Then
DoCmd.RunSQL "DELETE NoneChargeable_Admin.*, NoneChargeable_Manufact.*, NoneChargeable_Research.* " & vbCrLf & _
"FROM NoneChargeable_Admin, NoneChargeable_Manufact, NoneChargeable_Research;"
End If
DoCmd.Close acForm, "NoneChargeableHrs_frm", acSaveNo
End Sub
I am trying to use a list-control on a form to let the user select multiple values. I have understood that this requires some VBA-code to step through the selections in the list, since the "multivalue-selection" is set to "Extended".
When I try to execute the code I have (found and have tried to adjust), then I get the error message "Object required". The "ListCount"-paramater always only results in a ZERO-value, when i step through the code:
Function 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
[code]....
Trying to run a query where each 4 fields calling a custom function will not just re-run the same custom function over and over again for each field in a single record.
A Function has a huge amount of multiple queries and logic to perform.The Function returns a Integer, Integer, Integer, and optional Integer. Each integer requires a DLookup to lookup a String description value for each individual integer (in each of 4 fields).
The problem is, the DLookup in each column that runs against each of the integers re-run the same function.The result is that a single record, each of the 4 columns returning a single of the 4 values, the complex function is re-run 4 times.
The function is huge, part of a Business Rules Engine. Depending on the Rule-Meta data - it might launch up to a dozen queries and perform logic steps for each record. This is not the ordinary SQL Query.
Imagine if one record (for 1 field) takes 0.1 second to run. By referencing the function in 4 columns, this same function is re-run 4 times (0.4 Seconds) Against 50,000 records - this duplication of re-running the function for each column can really add up.
Possible Solutions: Researched Class Modules - There is a comment that the property Get, Let actually reduce performance. There are huge advantage of code documentation, documentation and centralization.It doesn't claim class modules reduce execution as each propery is returned. It also describes that Class Modules can't be called directly in a Query - unless each property is wrapped in a function.
Function Returns one String with delimiters: e.g 34;54;55;1 This single column goes into a Make Table (runs function one time per record) Then the D-Lookup is run against static local data. This pevented the function from being run over and over across the network linked data.
Final Solution: Eventually, the many hundred lines of VBA code for the Rules Engine will be converted into SQL Server T-SQL Functions on the server.For a Rule Engine development, Access has been great for a rapid protoype development and testing. The TSQL will be a final big step requiring re-coding. It is not currenty my option for the delivery time frame.
There is a text box in a form that user can enter the value.
How can I check if the value entered by user is already existed in the record of the linked table or not?
If the value (or record) do not exist, that new value/record will be added to
the table.
If the value do exist, just show the msgbox to indicate.
How can I do that ? :confused:
Thanks
I 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.
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
[Code] ....
I am having problems getting Access to evaluate whether a textbox is empty using VBA behind my form. I'm currently using Access 2000 on a Windows98 machine, but I also tested this in Access 2003 on an XP machine and got identical results.
The way Id like the code to operate is if the Product1 field is empty, the value taken from another form would be pasted there, and if it isnt empty, go to the Product2 field and if that field is empty, paste it there, otherwise go to the Product3 field, etc.
I have tried using If IsNull(Product1) Then but Access seems to think that the unbound textbox with no default value isnt empty.
I tried using If ("Product1" <> "*") Then but Access again seems to think that there is something in that unbound/no-default value textbox.
My code looks like this:
If ("Product1" <> "*") Then
DoCmd.GoToControl "Product1"
DoCmd.RunCommand acCmdPaste
DoCmd.OpenForm "ActiveProduct", acNormal, "", "", , acNormal
DoCmd.GoToControl "Description"
DoCmd.RunCommand acCmdCopy
DoCmd.OpenForm "Order Form", acNormal, "", "", , acNormal
DoCmd.GoToControl "ProdDescr1"
DoCmd.RunCommand acCmdPaste
DoCmd.Close acForm, "ActiveProduct", acSaveNo
DoCmd.GoToControl "ProdQty1"
DoCmd.Close acForm, "Product Lookup form", acSaveNo
ElseIf ("Product2" <> "*") Then
DoCmd.GoToControl "Product2"
DoCmd.RunCommand acCmdPaste
End If
I admit that my VBA skills are limited, but after having scoured these forums as well as Access help and examining the logic of my code, I am completely stumped. Any suggestions you can offer would be greatly appreciated!
Thanks!
I have a textbox on my main form that calculates the total of a field on the sub-form. It works well and display the correct ammount on the textbox when I load up the form, but if I do changes the values of the sub-form it doesn't update at all ! I need to close and open the form again to see the changes.
How can I update the textbox so it always reflect the values on the sub-form?
Code:
Private Sub Check253_AfterUpdate()
Me.Text255 = Environ("UserName")
If Me.Check253 = -1 Then
Me.Text254 = DLookup("[Lot]", "[tblAutoGen]", "[Inuse] = -1")
Me.Text256 = DLookup("[Exp]", "[tblAutoGen]", "[Inuse] = -1")
Me.Text258 = DLookup("[Lot]", "[tblEthanol]", "[Inuse] = -1")
Me.Text260 = DLookup("[Exp]", "[tblEthanol]", "[Inuse] = -1")
Me.Text262 = DLookup("[Lot]", "[tblDPBS]", "[Inuse] = -1")
Me.Text264 = DLookup("[Exp]", "[tblDPBS]", "[Inuse] = -1")
Me.Text266 = DLookup("[Lot]", "[tblTE]", "[Inuse] = -1")
Me.Text268 = DLookup("[Exp]", "[tblTE]", "[Inuse] = -1")
Me.Text259.Value = Date
End If
End Sub
How do I clear the values in each textbox if check253 = 0 rather then going to each and clearing them?
I have created a report and i am having trouble with the =Sum() function. I would like to sum all the values that are held in a text box which has the expression of '=[BookingSF_Qry subreport].[Report]![TotalSF]*[HowManyDays]'. No matter what i try i cant get a total value of all of these.
View 14 Replies View RelatedHello the problem I am having is that when I imported data from excel, it somehow placed carraige return at the end of the text box ( this is what I believe), this is a problem because in that field is a drop down box and it complains about my value not being valid. I press the del key behind the value and it doesnt complain. Can anyone help me?
View 1 Replies View Related