Forms :: How To Run A Query Based On Users Input And Search A Table

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 Replies


ADVERTISEMENT

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 4 Replies View Related

Forms :: Limit Amount Of Data That Users Need To Input

May 23, 2014

We have a database where we are trying to limit the amount of data that users need to input.

We have a form, which contains order information. Where a customer has specific requirements, we add those requirements to a separate document review table MasterSpecification and DetailedSpecification.

When a customer has specific requirements, we can open the frmMasterSpecification and search for the document.

Assuming a review has been done the requirements will be shown on the subform Frmsubcoverage. I would like to be able to select the record source from this form and have the details entered into our order entry form.

Under the specification form there could be many sub coverage records depending on the type of item ordered to that specification.

View 6 Replies View Related

Switchboard Front Page - Users Able To Access Forms For Data Input

Dec 8, 2011

I have created a switchboard 'front page' through which I hope for users to access the forms for inputting data.

Within this front page I have an 'enter' button - successfully created - everything.

However, I am unclear as to how you activate this, or any other button. I have linked it to the appropriate form and stated the action I want it to undertake, but nothing is happening.

What further instructions do I need to provide ...and where?!

View 5 Replies View Related

Forms :: Optional Search Criteria For Users

Feb 27, 2014

On the access form I have designed , I need to give optional search option to the users. I have the following search options on the form (screenshot attached):

From Date
To Date
Port
Vessel
Voyage
---------------------

The query is as follows:

SELECT dbo_VESSEL.VESSEL_NAME, dbo_VESSEL.VESSEL_CD, dbo_VESSEL.VOYAGE_NUM, dbo_VESSEL.PORT_CD, dbo_VESSEL.DEPART_ACTUAL_DT, dbo_VESSEL.DIVISION_CD
FROM dbo_VESSEL
WHERE (
(dbo_VESSEL.VESSEL_CD Like [Forms]![VESSDAT].[Form]![txtvessel]) OR
(dbo_VESSEL.VOYAGE_NUM Like [Forms]![VESSDAT].[Form]![txtvoyage]) OR
(dbo_VESSEL.PORT_CD Like [Forms]![VESSDAT].[Form]![txtport]) OR
(dbo_VESSEL.DEPART_ACTUAL_DT BETWEEN [Forms]![VESSDAT].[Form]![txtfromdept] And [Forms]![VESSDAT].[Form]![txttodept]));
----------------------

However, the form still prompts me to enter values for all the boxes and still doesnt show the correct data.

View 14 Replies View Related

Forms :: Search Box Within Same Form As Input?

Jul 27, 2014

Im trying to have a search function in my Access database but as of yet nothing has worked!

The search box will go in the header of the main form .

So basically the form is for recording faults with systems.However i want a search funtion on the same form so i can seach by a reference number and close the ticket.

View 10 Replies View Related

General :: Custom Table Based On Users Choice Of Fields

May 15, 2013

I want to create a form where by the user can select the fields he requires and the table is created.

Example: I have a table of subjects like maths, english, french, chemistry etc,

The user can choose what subjects are available in that particular school and therefore a table is created based on the chosen subjects.

Is there any way it can be done even with vba?

View 4 Replies View Related

Forms :: Button Click Event To Choose Between Forms To Open Based On TextBox Input

Jun 7, 2013

Making a small database, Got 1 Table.

1. ContactDetailTable

Got 3 forms.

1. ContactIDForm
2. ContactInfoForm
3. NewContactFrom

In ContactIDForm it contain 1 textbox name 'TextBox' with Button Name 'Btn'

In ContactIDForm there is only 1 Text Box ContactIDTextBox and 1 Button. User Enters ID in TextBox and On Button Click Event it should check data from TextBox in Table name (ContactDetailTable) in field ContactID and if there is record matching, ContactInfoForm should Open else NewContactForm should open with ContactIDTextBox value in it.

View 10 Replies View Related

Forms :: Query Using Table - Indicate Previously Input Data In Case Of Duplicate Entry

May 30, 2015

table name : Schedule
Field 1 = Vessel code
Field 2 = Voyage
Field 3 = ETA
Field 4 = berthed
Field 5 = Sailed

there is a query by using above table and data entry form based on that query.

need to add following facilitate

While data entering, if given voyage number is already exist for the particular vessel code, msg should be pop up immediately at that time saying " This voyage number is already exist"

How could this be manage ?

View 4 Replies View Related

Forms :: Moving Subforms Based On Input

Jul 9, 2013

I'm creating a database for work, and one of my forms currently has 8 subforms on it. For each individual account there can be one, none, or many of any combination of these subforms that will have information on them. I need the form to be able to show all of them, as someone else will be inputting all the information when the database is complete, but to make it easier I have set it up with checkboxes so that when a checkbox is checked, a subform shows, if not checked it doesn't show. I have very limited skills with VBA, so that in itself was an accomplishment for me.

My questions is: is there anyway in access to do it so that if a subform doesn't show, all the ones beneath it that do show will get bumped up so that it is easier to read/input data?

I have attached my DB and the appropriate form is "ICinfoT."

View 2 Replies View Related

Forms :: Populating Controls Based On Input In Others From Reverse

Aug 3, 2015

I'm using I have a secondary control being populated by an entry from another control. I was able to find the code I needed to do this using a combo box here in this forum.

me.txtDependent=me.cboPrimary.column(#)
(specifics changed to protect my company's paranoid view of privacy)

the code itself was showing up in the secondary control instead of any value. I found though that moving the code from the On Change event for the primary control to the On Current event for the form itself everything is working beautifully.

However, I have a different task now that I need to be able to accomplish with the same two tables but in the opposite direction. I suspect that I'm going to need to create a second set of controls and maybe even a second relationship or table to do this. To use a set of specifics that I think will get this point across, if I had a master table which includes a field for "City" and a secondary table, CityState, which contains both "City" and "State", and two controls, a combo box cboCityState to look up the specific City and a text box, txtState to display the State, putting the following code in the City combo box's On Change event populates a text box with the State when the specific City is selected, or putting it in the form's On Current event will be sure that State shows up in all the copies of a form when the form is used in a search and the City is not changed :

me.txtState=me.cboCityState.column(2)

What I need to do now is to set up a control which allows me to type in the State and have the results show all records that include that State.

I'm using this in a Filter by Form environment. I know the objections to this method and the benefits to creating a dedicated search form, but this method appears to be working well in all other ways for us for now. I have several situations within this form where I have one control showing up at the initial load of the form and at the beginning of the filtering process, allowing selection of multiple values from a long list of values in a list box, for instance, (using the On Filter event) and other controls showing up as a result of the filtering process, to display the selected criteria only, for instance (using the On Apply Filter event), so I'm aware of how to make this work if that's required. I'm thinking I may need to create a second control, something like txtStateInput, to accept the State search criteria and another, something like txtCityResults, to show the cities that are part of the resultant records.

View 5 Replies View Related

Export Part Of The Table Based On User Input Via Form

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

Modules & VBA :: Update A Table With Dates Based On User Input

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

Tables :: Limiting Table Input Based On Variable Values

Feb 7, 2014

I'm quite new to Access and am just working on a little practice database where I keep track of sales of a product.
Basically I have a table keeping track of the different types of products, a table keeping track of customers, a table for orders and one for replenishments of stock. I have a query that calculates the stock of each type of product based on replenishments and sales.

Now I want to make sure that a customer can't order any more than there is in stock. I know you can restrict what's entered into a table with a validation rule but that's only for static restrictions. After searching the internet for an answer I've pretty much learned that I can't really make this restriction directly in the table, correct? But then how can I achieve this? Do I have to restrict the amount through a form somehow?

View 3 Replies View Related

Forms :: How To Add A Search Box To Form Bound To Query Not A Table

Apr 17, 2015

I have a form based on a query. I'd like to add a search box to the top to look up values and then populate the rest of the form. I can't do it with the combo box wizard because the relevant (third) option doesn't show up, I assume that's because the form is based on a query not a table.

Any alternative method of adding a search?

View 4 Replies View Related

Forms :: Define A Form Based On Relationships To Input The Data

May 3, 2013

I am a novice to access. I am building a database in an effort to learn in the process. I wished to enquire about the possible issues that could be with defining the relationships that i have created in the project. (attached img).

I cannot seem to define a form based on these relationships to input the data.

View 14 Replies View Related

Forms :: Update Text Box Based On Input Of Date Control?

Feb 10, 2014

Can you update a text box on a form based on input of a date control? Here is what I have. I have several date controls on my form, and I want to update a status textbox based on which of these date boxes is filled out, but I need the status to be filled into the table as well. Do I update the field on the table, and then requery the text box? Is there some other method for accomplishing this?

View 9 Replies View Related

Query Decisioning Based On Form Input

Jun 2, 2006

I have a database that has several tables, each table has account numbers in
them. I also have queries set for each table to bring in desired
information. Is it possible to set up a form so the user can input an
account number and depending on which table the account number is in, that
query will run?

View 11 Replies View Related

Query Criteria Based On An Input Date

Oct 2, 2006

I have a database with the following information:

FTA # Date Attended Last name First name assigned person......

Each week I need to generate a report that shows the people that attended for that week, grouped by the assigned person. I only want to show the data for a specific "date attended" (i.e. that days date). I figured I first need to generate a query that only returns the data for that specific date in the "date attended" column. I did that but it is manuel by using the criteria field.

Is there an easy way to type in the date that I want the query to use as the criteria?

Thanks
:o

View 14 Replies View Related

Pre-Define Query Based On Input Data?

Apr 21, 2014

I am looking to find out if I can build a "on the fly" pass-through query based on user predefined data.

Basically I am linking to a massive database. Access link to DB2 table. In order to make the system useable for the users they need to edit the query before it's ran. If not the query over the network takes around 5 minutes every time it's accessed and options such as filters and sorting are chosen. So if a user sorts one column and filters on another the query has now ran 3 times. Once to open, once to filter, and once to sort. That just took 15 minutes.

So If there is a way to build macro or form that asks them for specific information first and then modify's the query so that it only gets ran once that would be fantastic.

Example:Table has 7 fields/columns. Usually they will do 3 things. Pull back data from either a month or quarter. Then filter a column by it's content. Then sort by date of another date/timestamp column.

View 1 Replies View Related

Queries :: Query That Uses Different Columns Based Upon User Input

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

Modules & VBA :: SQL Query Field Based On A String From Input Box

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

Forms :: Search Query With Subform - Edit Records In Related Table

Nov 8, 2014

I have a query which looks for like * surname*

in tblemployee fname lname dept active

this works fine and i can search using a requery button

however as deptartments are stored in tbldepts

when i change the query to retrieve the dept name instead of number directly from the table and i try to change this on the datasheet subform it changes it in tbldepts instead?

how can i change what dept the employee is in (as in change the number in tblemployee - but display the actual name?)

View 1 Replies View Related

Show Records In Query Based Off User Input In Form

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

How To Pull Records Based On Input And Input-1

Sep 22, 2015

I need to be able to query the service dates for the bill. So I can query 1509 for the .billYYMM and .readdate and it will give me the TO Date. How do I in the same query tell the query to subtract 1 from the BillYYMM that I just entered and give me the read date for that field in that query? So Query 1 MBRHISTDETL.BILLMOYR and in the criteria box use [Enter the Bill Month Year YYMM you want to search for] will allow me to run the query at any time for any bill month year that I enter. So another field in that query is MBRHISTDETL.READDATE. So to get the FROM date and the TO date i need the following MRBHISTDETL.BILLMOYR (whatever I enter as input) and MBRHISTDETL.READDATE that corresponds and then I need [MRBHISTDETL.BILLMOYR]-1 and MBRHISTDETL.READDATE. The first gives me the TO date and the 2nd gives me the FROM date?

View 5 Replies View Related

Forms :: Combo Box Based Search

Sep 29, 2014

The idea is populate the 2 Comboboxes and year and after that query something like:

Code:
SELECT FileEntryNo, GroupCode, SubGrpName, SubGrpNo, FileNo, GroupName, Subject, NameTo, NameFrom, ConnectedFile, DocumentDate, Month,
Spelmonth, Year, Reference
FROM dbo.Files
WHERE (GroupCode LIKE 'XXX') AND (SubGrpName LIKE 'YYY') AND (Year = ZZZ)

So after the query is executed the results will be available on a third Combobox that after selecting a record will show the detail on the fields.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved