Modules & VBA :: Search 1 Field Based On Multiple User Input
Jul 4, 2013
I have a search form that looks up a value in a field and lets the user know if it is there or not, they have now asked to put in multiple values to search... eg. searching containers number, I will put user input box where they can put these numbers
NYKU023561
TRLU102356
TCNU123023
This will search my container number field and show a pop up message box saying..
Please note below
NYKU023561 - has been found you cannot use this container
TRLU102356 - has not been found please check internal system
TCNU123023 - has beeen found you cannot use this container
Below is the basic search I had before which was all I needed.. how can I adapt this?
Code:
Private Sub Command256_Click()
If (CntSearch & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Dim CNT As String
[Code]....
View Replies
ADVERTISEMENT
Oct 13, 2014
I have a form in my front end database that is supposed to allow a user to search for a record based on account number and then make changes to the that record and for it to save in the back end database. I have the search function working where it populates different input boxes on the form with what is stored in the back end database but I cannot get the update function to work. I have tried to assign each input box with a variable and then run an Update SQL function to update each of the fields but the updates are not storing. Any example of a successful update statement that uses VBA variables in it or a way to update a specific record via a recordset type function?
View 8 Replies
View Related
Jun 29, 2015
I'm attempting to build an import module so that my users can take data from different walks of life and import it into my tables. I'm doing this by setting up a module and allowing the user to specify which column data will come from when importing data. So lets say I have a field that is CustomerID in my table and user one pulls data from 1 place and in his excel or csv file, customerid is in field 1. Another user does the same thing and its in field 2.
How can i make some form of a loop that when I'm attempting to update data, i pull the correct column?
strSQL = "SELECT * FROM tblImportTable"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
With rst
Do While .EOF = False
If IsNull(DLookup("DefaultValue", "dbo_tblImportTemplateDetails", "Template_ID=" & Forms!frmImport!TemplateName & " AND FieldName='CustomerID'")) Then
[code]....
View 6 Replies
View Related
Dec 30, 2014
I am new to VBA and I'm trying to write a query that will update a table with dates based on user input. For example a user will run data each Monday and that date will be day01. The date table has 28 days total and I need each day row to update with the next date i.e. day01 is 12/30, day02 is 12/31, day03 is 1/01 etc..I am having issues just running the update the query. I get a too few parameters error message on the strsql statement. There are only two columns in the table, order_day(date column) and date_value(text). I want to update order_day. I also need creating a loop so it knows to go back and add days to the other values.
Here is what I have:
Code:
Sub Update_Dates()
Dim rs As Recordset
Dim db As Database
Dim lmsg As String
Dim transactiondate As Date
Dim strsql As String
[Code] ....
View 6 Replies
View Related
Sep 10, 2013
How do I use a update sql query to update a field based on a string from an input box. Heres what i have been trying:
Code:
Dim NETWORKBOX As String
NETWORKBOX = InputBox("NETWORK TO IMPORT" & Chr(10) & "EXAMPLE: PRIMARY", "NETOWRK TYPE")
sql = "UPDATE " & TABLENAME & " SET NETWORK = " & NETWORKBOX & " ;"
DoCmd.RunSQL sql
If i run the code and input 'PRIMARY' in the NETWORKBOX the sql query will return an 'enter parameter value dialog box' with the word 'PRIMARY' above the input field.
View 1 Replies
View Related
Sep 9, 2004
I have a database that I created that uses a form to fill out information about server builds. I contains information about who built it, the IP address, server name, etc. I want to be able to create a dailog box that allows the user to input a server name to query the database and create a report based on that input. And if the user input isn't found in the database, a dialog box comes up with some sort of error message. I am still pretty new to Access, so the more detail you can give me the better. Thank a lot.
View 1 Replies
View Related
Jan 16, 2014
I import a CSV field which has not preserved the CR/LF when it was exported from BCM Remedy. There is no setting for that. The memo field prints on my report like this:
A custom solution would be developed, that once implemented, could become the standard product in similar situation. Normally would assign to Network Engineering, but will work with Ray and the IPT Team to cost out the solution and get approval to proceed. 2012/05/24 10:44:28 AM PCOLLINS Sent to Ray Massie for review to determine if a solution needs to be proposed, or if they can wait for the National IPT solution to be ready in 2013.
I want to add VBA code that inserts a CR/LF in the memo field before all but the first occurance of a string that looks like a date, the first occurance doesn't need it. I will do it right after I import the CSV file into the table, so it happens only once, and it always prints and displays the CR/LF.
The memo field is called "NBS Update" and the table is called "CCRR Remedy Data"Here is what I have, but don't actually know what to put in to find the date and add a CR/LF:
Code:
Dim db As DAO.Database
Code:
Dim db As DAO.Recordset
Dim srtSQL As String
[code]...
View 14 Replies
View Related
Sep 10, 2013
I have a database table in which I'm trying to pull sales data and generate sales reports from. The problem I face is that the sales data is recorded into a table with this structure:
Year | Customer | Sales_Month_1 | Sales_Month_2 | Sales_Month_3
Rather than having a single field "Month" in which I could set criteria or prompt the user to select a month to derive sales data from, I need to write a query that through user input (or through some code within the query) the right field will be selected. An example of this would be generating a report for the 1st month of the year, obviously.
View 1 Replies
View Related
May 31, 2015
I run a charity pre-school and have invoices to send out. Some of the parents cant afford to pay the whole amount in one go, so we give them a payment plan. e.g. If an invoice is for a 6 week term we let them pay weekly. So I have a check box on my form to say "are you on a payment plan".
Then - I have an free form input box..."How many payments..." and the answer may for instance be '6'.
What I want to do now is dynamically create/display 6 date fields, to record what the agreed payment dates are.
but maybe the answer is 4, or 7, etc. So I need to create/display the correct number of payment date fields.
View 4 Replies
View Related
Dec 12, 2011
I have query in MS access which does date filter based on value given by user.
SELECT *
FROM Table1
WHERE ((DateValue([Table1].[Date_col])
Between "#"&[Forms]![Frm1]![Date1]&"#"
and "#"&[Forms]![Frm1]![Date2]&"#"))
But this gives empty output
View 1 Replies
View Related
Oct 5, 2006
Last question.. honest.. maybe ;)
I have a query that prompts the user to display certain/all of the members in my member table. The problem is, using a parameter query in the memberID field only allows the user to send the message to one member, not multiple members. "1 or 2 or 3" or "1,3" obviously don't work.
I've tried creating a form with a list box to select the members (which I;ve done) but obviously you need code and stuff to get it to work which I think will be too complicated. Is there a simpiler way or is the form the only thing that will work?
Thanks
Kris
View 1 Replies
View Related
Nov 3, 2005
Ok this might be basic stuff i'm a bit dumb
I have a query showing lots of records of equipment that is in different rooms. I would like to have a form where the user can bash in a room number and all the records from the query that have that room number in it pop up.
Please help the dumb
View 3 Replies
View Related
Aug 23, 2006
hi everybody, im have a database with table called "project". there are many column in this table. my user want to export this table to Excel, but only some of column, with particular order ( depend on him) to analyze in Excel.
he asked me to build a form with a list box, drop box,somthing like this, so he can choose what column to export in what order.
i try to make a query like this: " Select Forms!UserInput.combobox1.value , Forms!UserInput.combobox2.value,etc, From Project" but it wont work.
Dou you have any idea.
thanks in advance
View 2 Replies
View Related
Jul 31, 2013
I have list of candidates shown in report. I need each candidate to have a unique number based on user input. And this number increases by 1 for the rest of the candidates in the report.
Example is below:
If user enters 1132 in the user input box then the numbers shown in report should be as below:
Name Assignment Number
Felicity Thomas 1132
Andrew Sen 1133
Andy Luker 1134
Similarly if number entered is 2345 then the numbers shown in the report will be:
Name Assignment Number
Felicity Thomas 2345
Andrew Sen 2346
Andy Luker 2347
View 1 Replies
View Related
Jul 1, 2013
I'm trying to make a report that a untrained user can use to review the relevant data from the database I'm creating. All of the work I've done so far has been directly in the tables . Here's a quick outline of my general data organization:
Department:
DepartmentID(PK)
DepartmentName
Employee:
EmployeeID(PK)
EmployeeName
DepartmentID(FK)
Needs_Work (check box)
[code]....
Basically, I want the user to select a department to view using a combo box or option group (which I'm not sure of how to do in a report). Then, I want to view the employees within that department sorted first by whether or not the Needs_Work check box is selected, and then by how many of their competencies are unchecked.
View 2 Replies
View Related
Dec 31, 2013
I have a TABLE with the following data:
- EMPID
- LASTNAME
I have a form with the folliwing data:
- text box for user to enter EMPID, txtEMPID
- LASTNAME
I would like to create a button to initiate a query to do the following:
- once the user enters a EMPID in the form, it will search in the TABLE under the EMPID field...
- if the user enters an EMPID that is in the TABLE...display "Y"
- if the user enters an EMPID that is NOT in the table...display "N"
- a error message box needs to pop up if "Y" to alert the user that "the EMPID already exists and that duplicate entries are not allowed"
One of my main questions is how do I run a query based on the users input and search a table? would the following work in the query?
View 7 Replies
View Related
Mar 28, 2015
I am building a form to create a user record and at the same time i have some yes/no options which are located in other tables but when i want add a user i cannot select any yes/no options they seem locked?
View 1 Replies
View Related
Nov 29, 2013
I have a field 'Payment Types' with values (Cash, Cheque, Debit/Credit Card) and a field 'Payment Received' which is Yes/No.
When putting the order through the user selects the payment type and ticks a box if payment has been received.
On a report for delivery drivers, the owner wants it simple for the driver... he wants all the payment types listed with a checkbox next to each one, then wants the appropriate box ticked if payment has been received.
So I need something on the report (or underlying query) which ticks the appropriate box, i.e.
If payment received = true then payment type checkbox = true.
Or should I put the payments into a separate table with both fields so multiple payment types can be marked as paid?
View 6 Replies
View Related
Nov 23, 2005
Hey all,
I have two fields 1 & 2
field 1 is a simple combo list of user defined values ie A, B, C or D
Field 2 relates to a attribute of the data in field 1 and is not always the same for A, B, C and D. i.e
A could have a,b,c,d or e
B could have c,e,f,g or h
C could have a,g,h,i or j
D could have v,w,x,y or z
I would field 2 to have a combo box which only displays a,b,c,d, or e when A is chosen in field 1; c,e,f,g or h when B is chosen; etc
How do I do this?
Cheers all,
Matt :confused:
View 2 Replies
View Related
Mar 30, 2006
Hello
I have created a database for my department to log all of our jobs to keep track of them and I want to create a simple search for them but I am having difficulties.
I am familiar with creating queries to search tables for matching records, but is it possible to get search criteria from the user (ideally from a search form they fill in) to form the query?
For example, I would like the user to be able to query the jobs by month and/or business area and/or supplier...is this possible?:confused:
Any help gratefully received!!!
:)
View 2 Replies
View Related
Aug 20, 2013
I am trying to get my VBA code to dump a query once the user pushes a button. I have the following code to call up the Excel app.
Code:
Option Compare Database
Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
[code]....
The qry_PP_Errors_Union is a Union query. In this query there is a date field. I would like to be able to to use that date field as a parameter. So I have written this VBA to prompt the user for a Begin Date and an End Date.
Code:
Beep
strBegindatemsg = "Enter the beginning date." & vbCrLf & vbLf
strBegindate = InputBox(Prompt:=strBegindatemsg, Title:="Begin Date")
Beep
strEnddatemsg = "Enter the beginning date." & vbCrLf & vbLf
strEnddate = InputBox(Prompt:=strEnddatemsg, Title:="End Date")
Now the part that I am missing is that I am not sure how to make the "strBegindate" and "strEnddate" the criteria for the union query.
The following is the SQL for my union query.
Code:
SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, UICError AS Error, "Update and Internal Correspondence" AS Category FROM qry_PP_UIC_Error
UNION
SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, BIDError, "Bids" FROM qry_PP_Bid_Error
[Code] ....
Without the criteria, my code works for dumping everything out into Excel. However, dumping all the data results in a 7 mb Excel file that requires manual deletion of the information that is not pertinent.
View 3 Replies
View Related
Jun 5, 2006
Is there an easy way to get user input like the parameter value box in an update query, where you want the user to specify the table name and field name to run the query on?
View 1 Replies
View Related
Jun 5, 2014
Input box Search problem. I have been using the following to find a record in my frmMain using an Input Box:
Private Sub cmdClientIdSearch_Click()
'Searches for Client by ID number
Dim rs As Object
Dim strCriteria As String
strCriteria = InputBox("Please enter Client ID" & vbCr & "Do not type leading zeros")
[Code] ....
It works great if I run it from a command button from frmMain. But, if frmMain is open and I run it from a command button on my switchboard, I get the following error message:
Run Time error 7951. You entered an expression that has an invalid reference to the Recordset Clone Property. I tried inserting a command to open frmMain in the first line, even before the two Dim statements, but I get the same result.
View 6 Replies
View Related
Dec 28, 2012
I have a table, at the table I'v got these fields:
ID | num1 | num2 | sum
I want that the user put numbers at 'num1' and 'num2' fields and then the 'sum' field will calculate automatically the operator (sum=num1+num2).
I've tried to put any combination at 'Default value' of the sum field (all the fields are numbers , also tried to change the sum field to text...nothing works).
I'm getting a message that 'num1' field is not recognize at the table
(I tried =[num1]+[num2], without the '=', num1.table+num2.table , ...nothing works)
I also tried to do it with SQL command but it dosn't work.
There is any way to do it, is it possible? Or other way to do it at least at Form or at Report ?
View 7 Replies
View Related
Nov 28, 2013
I have a form in Access2000 with 5 text fields which get transferred to the table for each new record. Is there a way i can "LOCK" one field so that once the user has input that data it never changes until closed. I can already lock the field but once i create a new record the field then goes blank.
View 4 Replies
View Related
May 30, 2006
Hi there i am building a search form and I want it to be able to display results from multiple criteria......Currently I am able to display results in a listbox, whenever the user types in a id number in a text box. So if a user types 63 in the ID text box the record with and ID of 63 will appear in the list box or it will wont if the record does not exist..... What i want to do is be able to search on multiple criteria. Sof if a user wants to search based on a name instead of a id number they woudl be able to. What I am struggling to grasp is how to invoke a OR in the criteria box. So that the list box will display results based on either the ID text box OR the name text box.
Any thoughts?
View 1 Replies
View Related
