Using Form Data In User-Entered Query Search Criteria
Dec 20, 2006
I am attempting my first access database which tracks blood components in our medical facility. When a component is issued to a patient there is a button to push which links to a report that runs a query. The user must enter the unique key for the component at a promt, which ensures the report generated will be for that component only. My question is if there is a way to cpture and use that key automatically when pressing the button. If I need to clarify something, please let me know--I may not have the Access developer lingo down yet...
I have a report that is fed by a parameter query. When a command button (Command23) is clicked a parameter box opens and the user enters a subsystem number. A report for that subsystem then opens.
I want to open the report with VBA and show a message if the user has failed to enter a subsystem number in the parameter box and just clicked OK.
The problem is I don't know how to refer to the parameter box and detect if it has been left empty before clicking OK. I first tried to use IsNull in an If statement with Command23.Value, but now realize it is the value in the parameter box I am after, not the "value" of Command23.
How can I change my code to detect if the parameter box has not been filled out?
Private Sub Command23_Click() If IsNull(Command23.Value) Then MsgBox "You did not enter a Subsystem", vbOKOnly, "No Criteria Entered" Else DoCmd.OpenReport "rptTESTInfoBySubsystem", acViewReport End If End Sub
I am currently building a stock control system in access. I have a form with which the user selects a stock item from 3 combo boxes (stock name, stock weight and stock grade). The combo box links to a table of stock items. A stock item is made up of a name, weight and grade (all separate fields in the stock items table).
I wish to setup the form so as the user selects the stock name from the first combo box. Based on the value selected by the user, the appropriate stock weight(s) assoicated with that stock name will be displayed in another combo box.
Basically, I wish to take the result from one field in a form and apply its result to a query(?) in another field so as to filter(?) my result accordingly.
I am making a simple database for the computer laboratory in school (my place of work). Actually it is a Automated Logbook System. Here's the Problem. I want to make a form that will prompt me whenever I enter the data(ID number) of the user(student) 3 times already for the current week.
How I would go about appending data to a table that has been entered by a user on a form.
My initial idea to tackle this was to create a number of text boxes as a method of user input which would all then transfer to a table but this doesn't have much longevity to it(if the database requires additional columns to be added, etc.)
The setup at the moment is two tables, a main table, and a temporary table(which is where I intend to first store the user input, this is so that the user can view what they have entered and make any necessary changes(undo))
I'm wondering if there are any easier ways to go about this such as, a msgbox appears and asks the user to enter each individual column data for a row. This doesn't sound like the most efficient way but efficiency isn't a priority right now and is something I'll look at later.
I'd like it so that a user can input data into a number of text boxes, a button has an onclick event that will append all entered values to the temporary table, this is then relayed(I'm assuming through requery?) back to the user as a way of checking before really adding it to the main database, and then if they're happy there is another button which is then enabled so they can add it to the main database.
I'm setting up a customer database. I'm used to using ASP to query and manipulate data within databases, but this time the database is to run on friends work laptop and he wants it accessed directly using access.
Basically, in the sql statement to get it to search for a whole string i simply put
WHERE tblCustomers.CompanyName=[Enter Company Name]
However, he wants to be able to just enter the first few letters of the company name, and get results based on that. Is this possible from a query or within a form?
having trouble using a text box on a form to set the search criteria for a particular field within a query.
Ideally i would like to enter the criteria in a textr box then click a button which sets the criteria and opens the query results in a report,
I have designed the query but cant which works if you go into the design and enter the criteria. the problem lies with getting the text box on teh form to set teh criteria.
If i use the expression builder to set the criteria to the same value of the "text" within the "text box" on said form the following happens,
If i open the query itself it opens a small window and displays the "expression" that i entered in the criteia box, above the data entry. the query does work this way but dont understand why the expression is displayed??? The text box on the form also becomes locked, not allowing data to be entered.
I have tried to link the query direct to the text box. also tried creating a table which has data entered via form then linking the query criteria to a field in the table.
Could some one point me in the right direction please
In my search form I have 5 creterias and in the query behind it I joined them by AND. the problem is if the user entered only 2 or 3 of those creterias, the query will not return anything.
I have an append query that currently looks like this URL...This query automatically adds the machine parameters for a product code and lot number into the running condition log. This is so the user does not have to manually go in and tediously select each machine parameter.
The running condition log also has a date field to specify what day and record number the machine parameter's value was recorded on. When I run the query the appended rows look like this. The product and lot are defined by user parameter and there are actually about 36 machine parameters
Code: Productstockcode LotNo Day Record Parameter ActualCond PE-500 130816m71 StockTemp PE-500 130816m71 Zone 1 PE-500 130816m71 Zone 2
My question is: how do I modify the query to automatically add the date and record number in one shot? It needs to be user defined at the time of the query because this data is not stored anywhere else in the database. For each 36 machine parameters the day and record would be the same.
I have a form which lets the user enter a contract ID and then a combo box that has a list of reports which run against whichever ID is entered.
What I want to know if I put the ID's in a table if theres a way to allow the user to select multiple ID's in one go and run the report against them the range rather than 1 at time?
I'm thinking about utilising the check/tick boxes would this be possible? or another way?
Is it possible to run a basic select query to pull records based on multiple strings entered by a user?
I have a query with field criteria - Like '*' & [Type In MMDBID:-] & '*'
This allows the user to input one MMDBID and the records are retrieved from the db.
I can also use the OR statement in the same field criteria "AB123" OR "BC123", and all records based on those values are pulled back.
But I cannot get the user to input multiple values and I tried amending the SQL string based on the OR criteria above
SQL statement (Like) is below :
SELECT tblFund.MMDBID, tblFund.[Investment Name], tblCodesLive.[IOE Code], tblCodesLive.[Uptix Code], tblFund.[Red Payment Deadline] FROM (tblFund INNER JOIN tblCodesLive ON tblFund.MMDBID = tblCodesLive.MMDBID) INNER JOIN tblContact ON (tblFund.MMDBID = tblContact.MMDBID) AND (tblCodesLive.MMDBID = tblContact.MMDBID) WHERE (((tblFund.MMDBID) Like '*' & [Type In MMDBID:-] & '*') AND ((tblFund.Editing)=False) AND ((tblFund.Closed_Fund)=False));
I created a form that allows the user to choose the criteria that they want to see on a report using =Forms!formname!controlname in my query. It works great but I want to also allow the user to choose nothing and return all records instead of limiting them to choose just one type of record. Is this possible? Before I created the form my query had the [Enter parameter] on one criteria line and [Enter parameter] Is Null on the next criteria line and that was working great for my use but I need to create a simple form for other users.
I am fairly new to access but so far I have been able to get what i need from it, until now. I am trying to find a way of comparing two sets of data to find out an employee's average productivty.
Table 1 -Hours Worked (by day) - contains 'name' 'date' and '# of hours' worked - an employee would enter the hours here on a daily basis
Table 2 - Contracts Keyed (by month) - contains the number of contracts worked that is derived from seperate system - this is entered on a monthly basis (so for example: John keyed 30 contracts for the month of January) - the system i am pulling this info from does not have the ability to pull a daily count of contract per employee, only a range of dates and it then provides the sum for that range (unless I ran a query for each day, for each employee which would take me hours) - employees dont have access to this system to enter their own # of contracts keyed on a daily basis. - for entry, so far i have just been putting the first of the month and then the # of contracts.
In a nutshell, this is the calculation I am trying to create:
(Sum of "# of hours" for the month) / (total "# of contracts keyed" for the month) = employees average hourly productivity.
I have tried to do this with various types of queries and reports but with no luck, I get a prompt saying that access can't compare the 2 fields.
Is there a way to compare the data that is entered daily with the data i would enter monthly?
I have a form with several tabs on it, where the user enters various info.. I want to have the user hit the save button and have all info saved to a single table. Is there an easy way to do this. Please excuse my ignorance but I am fairly new to Access. Thanks in advance!
This is my first question posted on this site. I have created a survey with questions to be answered. The database is already populated with records of the people's names who were getting the survey. What I need is when the data entry clerk brings up the customer's name on the form, is that they are forced to enter the surveys answers for certain questions before they click the "Save" button. The answers are set up as radio buttons on the form (ie Less than 6 Months, 6 Months, 8 months and 1 Year).
Not sure if a Before or AfterUpdate macro is needed?
Any suggestions would be appreciated. Please email me directly if you have code.
I have a form where users enter data in various ways - combo boxes with drop down selections and text boxes where users manually enter some data. I also have several text boxes that return "answers" based on calculations from user entries mentioned above. The form is bound to a table where all the entered data is stored. I'm having trouble saving the calculated information to the bound table. It's easy in all the non-calced boxes, I just put the column name of the bound table in the control source, but in the calculated fields, I already have the formula in the control source. How can I identify the proper column to save the data in this case.
1. I'm in need of some way of preventing a user from opening form B unless he has entered information in form A.
Right now it is possible to open form B without entering data in form A (form A being the date/time info and form B being the case technical info) due to which a record is created for clientnr 0 (which should be a non existing client). Querying for clientnr 0 from time to time and deleting those records is easy enough, but who knows what kind of relevant data could be stored accidentally in a record that doesn't belong there.
This happens by the way, even though referential integrity is enforced throughout the dbase. Maybe I should say, because of that. I want to keep it that way of course to prevent orphans roaming around. But having orphans for clientnr. 0 is also not a good idea.
2. What is the wiser thing to do: keep all information concerning one case for one client (date/time info per case, techinfo per case, maybe even products sold info per case, payment info per case) in one table and have the data needed for each thing entered in seperate forms, or have all data split up into seperate tables like I have now. One for clients, one for dates and times, one for the technical info for that case etc.... ?
It is starting to seem a bit ehrm... useless to have all that data for one case floating around seperate tables. I don't know... it seems so much more complex (having to create multiple relations, multiple PK's per table etc.).
Where do I draw the line? For example the image attached (relationship2.jpg): now I have one PK in the table holding the clients (clientnr), two PK's in the table holding the case date/time info (clientnr and casenumberdatetimeinfo), three PK's in the table holding the techinfo (clientnr, casenumberdatetimeinfo and casetechinfonr). And what's next? Four PK's in the table holding the sold productsinfo? Five PK's in the table holding the invoice info? And what about the relations between those tables? Right now I can still comprehend.... but when I start thinking ahead, I'm starting to get dizzy. So some advice on this would really be highly appreciated.
3. Which build up of the relations between the tables in the dbase is better? The one in relationship.jpg or the one in relationship2.jpg?
I am not sure where this thread should be in forms or queries. I have database which has 3 linked tables by ID in each table. I have built a query on the 3 tables, then created the form.
Now when I enter data on the form and save it, when I open up the said form again , the data is not there. Yet the data is in both the table and query
If I only use 1 table, then there is no problem. The record source for the form is the query, and the query uses the 3 tables.
I have a form that the user enters employees daily duties. I have code to run sql after the BoxNum field has data in it. I want it to look at the BoxNum field and the dropdown box for the task to see if the entered box number has already been completed for the task selected on the form. Here is what my code looks like now. The code runs but gives me the msgbox no matter if it is a made up box number and I also get a syntax error 3075.
Code:
Private Sub Text13_BeforeUpdate(Cancel As Integer) Dim rs As DAO.Recordset Dim strSQL As String
I have a form where varying layers of information can be entered. In some cases, a user may know all information or only part. If i tie the query to the form, each field has to be populated for it to work. how do i set the query up to effectively use the information available and not require all information?
I have tried setting VBA code as follows:
If Me.Combo1 = Null Then Me.Combo1 = "*"
However, when i do this, it updates the form field with an asterick/wild card but does not include in query. yet, when i update the form fields with data (not asterisk/wild card) the query runs.
Can someone go through my forms and help me figure out why I cannot submit the entered data. Whenever I try to test my system and enter in inspection information I get an error that says....
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data and try again."
Steps:
1.) Type the given sample account number in the blue box 2.) Click "Search Acct #" button 3.) Click "Test" button 4.) Enter random info on the test form 5.) Read error message 6.) Help Ken_C :D
I am developing a form where students have to enter their ID number but I want my form to check through a table of students and their IDs and flag up if the ID number does not exist in the table.
I have a parameter form I used to enter data for a query. I want it to clear any previous data entered when it opens. I have tried the below without success. I also have the fields defaults set as "" and the form set at yes on data entry.
Private Sub Form_Open(Cancel As Integer) Me.BldgCmb.Value = "" Me.StateCmb.Value = "" Me.CityCmb.Value = "" Me.AddCmb.Value = "" End Sub
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:
I have a main form titled "projects" with a subform titled "inventory". They are linked by two fields "project" and "fiscal year" with a one to many relationship. These fields are also the primary keys in the main form. I also have the referential integrity option activated.
Whenever data is entered in the subform inventory, it duplicates the main form and all the other subforms linked to the main form. (I have about 11 other subforms linked to the main form). So if I enter 2 records in the inventory tab, there will be 2 records for the same project and fiscal year in the subform and all the other linked subforms with the same data, so it is almost like it is seeing the inventory subform as the main form. I have tried deleting the duplicate record from the main form, then it deletes the 2nd entry of the subform. I have attached a pdf with a print screen of the 2 duplicate records with the same fiscal year and project name.
I have another form called Notes that has a similar relationship, but doesn't have this problem. I think it might have something to do with the conversion. I originally created the database with Access 97 and we are now using Access 2007. Am I missing something that needs to be done in Access 2007 to link the 2 forms, so I can enter records in the subform without duplications?
Also, the database is split so there is a back end and front end. I have all the tables and relationships set in the back end.
