How To Automatically Insert Criteria In A Query.

Dec 8, 2006

hey all, In my database (For a doctors practice) I have individual patient records with information about them on there. There are 5 boxes showing the current medication, if any, that they are on. The form is all linked up and so when I go through each record for each patient all the data changes as it should. I have a seperate table with all of the drug information on it and I want to be able to click on the box on the patient form (One of the boxes that has one of their medications in) and I want it to be able to automatically put the name of that drug in a search query and bring up the results in a report or form view. Is it possible to do this?

So far I have managed to create a control button on the pateint data form and when clicked, it asks for a a parameter value i.e. the name of the drug. I'd like to be able to miss out this step and for it to automatically enter the name of the drug from the text box into the serach criteria.

PLEASE HELP! I need this urgently!

Thank you for your time.
normski

View Replies


ADVERTISEMENT

Forms :: Automatically Insert Object To OLE

Apr 10, 2014

I have Ms Access table with OLE Object column. So I am using form to handle this table. I want to store word documents in this column. Now, to add new item I should right click on OLE field, then choose "Insert object", then choose Microsoft Word Document.

How can I to automate this process? How can I automatically insert empty word document on adding new item?

View 1 Replies View Related

Forms :: Automatically Insert Data To Another Table By Completing Checkboxes

Jun 1, 2015

I'm on my way on creating a simple Database for a company. This database contains several tables, one of the tables will record information about the training that had been completed by each employee. There are about ten sections of training that should be completed.

For instance, I have two tables called 'Development' and 'CSA_Lisence'. 'Development' is the table that record the information about training which containing ten checkboxes (which represent ten sections of training) and CSA_Lisence will be automatically requeried when all of the checkboxes on Development are fully checked.

Here's the step I've been worked on :

First, I made a function called 'CheckCompletion' to ensure whether all the checkboxes are checked :

Code:
Public Function CheckCompletion() As Boolean
Dim blnComplete As Boolean
Dim strCompletionSummary As String
strCompletionSummary = Basic_Inspection & Certifying_Staff & Safety_Management_System & Regulation_Part_145 & Part_M & EWIS & Fuel_Tank_Safety_Level_2 & Dangerous_Goods & Human_Factor & Basic_Supervisory_Training

[Code] ....

Second, I made a function called 'UpdateEmployee' to handles if all boxes are checked :

Code:
Public Function UpdateEmployee()
Dim emp_numb As Long
Dim emp_name As Long
Dim strsql As String
emp_numb = [Forms]![development].[employee_number].Value

[Code] ....

Then, I put this code on every checkbox's after update event (example only) :

Code:
Private Sub Basic_Inspection_AfterUpdate()
Call UpdateEmployee
End Sub

The problem is, nothing happened with the tables. However, when I managed to remove the 'If checkCompletion' condition, it worked and the 'CSA_Lisence' is requeried, but I will have ten multiple records with same contents (I just need one record per employee). I guess there's something wrong or missed in my code. Or i need to remove something?

View 7 Replies View Related

Automatically Change A Criteria

Nov 3, 2005

Hello to all the xperts out there,

I have a query with this criteria for Date field:


Where (([tblList].Date)>=#11/1/2005# And ([tblList].Date)<#12/1/2005#))

Is there any quick way, besides creating a form and run the query based on it, to have this criteria updated itself when it's a new month? I've gotten tired of changing it every month.

Thanks in advance

View 2 Replies View Related

Modules & VBA :: SQL Insert Into With Where Criteria

Nov 18, 2013

Been trying to get this piece of VBA code to work but not sure why it isn't.

This SELECT statement works fine:

Code:
strSQL = "SELECT [" & strTmp & "].* FROM [" & strTmp & "] WHERE (([" & strTmp & _
"].[Work center]) Like '*' & [Forms]![frmImport]![txtCode] & '*');"

However when I try to use this statement to create a new table like this:

Code:
strSQL = "INSERT INTO [" & strNewTable & "] SELECT [" & strTmp & "].* FROM [" & strTmp & "] WHERE (([" & strTmp & _
"].[Work center]) Like '*' & [Forms]![frmImport]![txtCode] & '*');"

db.Execute strSQL

I get the "Too few parameters. Expected 1." error.

Why would the syntax for the WHERE criteria not work when going from a SELECT to an INSERT INTO using the same SELECT statement that works independently as a row source?

View 5 Replies View Related

General :: Automatically Delete Data That Meets Criteria?

Mar 15, 2013

I'm an amateur (is there such a thing as having databases as a hobby?) with little training. I volunteered to organize a growing volunteer group I'm a part of by making a simple database for the volunteer office staff. One of the things I want it to do is make name tags for our weekly meetings. I don't want to print one for each name on our list because the list is over 400 names long with only around 35 regular attendees. I made two tables--one is the MakeNameTag table that lists regular attendees (it is a lookup field on the Names table).

The other is the Absences table. It has two fields; DateOfAbsence and MakeNameTag. If someone misses a meeting (does not pick up their name tag), their name is added to the Absences table. A query then filters the table for dates in the last 28 days. If their names shows up four times in the last 28 days I want for their name to be deleted from the MakeNameTag table. How to do that automatically? Our office volunteers have minimal PC skills, so the solution needs to be very user-friendly.

View 3 Replies View Related

Queries :: How To Insert Multiple Conditions / Criteria For A Field

Jul 24, 2013

I am trying to make a query that outputs the minimum "Need Year" AND ALSO if the need year was equal to 9999 it shows "NO DATA".

This is what I have so far for checking the minimum value:

field: Need Year: MinofList(PMS_output!pqi_ny,PMS_output!iri_ny,PMS_ output!sdi_ny,pms_output!sai_ny)

I am not sure if I should be putting it in the criteria to check whether this minimum value (need year) equals to 9999 or not and if it does, it says "NO DATA" instead of 9999.

View 3 Replies View Related

Queries :: Insert Text From Textbox On A Form When Combo Box Meets Criteria?

Jul 12, 2013

Is it possible to insert text from a textbox on a form (Data) when a combobox on (Data) meets a criteria?

Example: Test: IIf([Results]="Positive" text207)

So if the Results combobox is Positive then the text from Text207 is inserted.

View 9 Replies View Related

INSERT Query - Insert New Data Only

Jul 2, 2010

Table TBL_NEWDATA is used to append new data to table TBL_PERSON_ALLOCATIONS.

TBL_NEWDATA { Person_ID, Department_ID }
TBL_PERSON_ALLOCATIONS { Person_ID, Department_ID, ... }

I need to devise a query to append data for a particular Department_ID from TBL_NEWDATA to TBL_PERSON_ALLOCATIONS where that data does not already exist there. i.e. for Department_ID 'Research', I would want to append 'Person_ID', 'Department_ID' (in this case: 'Research') to TBL_PERSON_ALLOCATIONS for any tuples not already held.

INSERT INTO TBL_PERSON_ALLOCATIONS (Person_ID, Department_ID)
SELECT Person_ID, Department_ID
FROM TBL_NEWDATA
WHERE TBL_NEWDATA.Department_ID='Form...'

[code]...

This Query takes a single argument from a control (Forms!Main!IN_Department), and this is the Department_ID to be updated.Is there any way to do this using a single query or will I have to use sub queries? I'd hoped not to as to keep the database as concise as possible.

View 2 Replies View Related

Insert Records Into Table Based On The Select Criteria From The First Table.

Mar 22, 2005

Hi

I have a small database with 4 tables that I am using for the current problem.
The tables are call, parents, mailman, orders.
Call and parents are related by the call ID (a primary key in the Call table.).
Mailman and orders are related by a Unique Id (a primary key in the mailman table.).

Forms involved are frmmain and frmsub.

Frmmain contains the call table information in the main form and parents information in the subform.

When a user enters a call with call ID and enters the operator name and parents information in the sub form,
When a user clicks the OK button on the main form, necessary changes should take place
if they enter the case type in the sub form part of parent information as ‘missing information’ or ‘missing link’ then the parent information with fields first name, lastname, case type, operator information should be inserted into mailman table in appropriate fields.

Simultaneously a record should be inserted into orders( after the record is first inserted into mailman, since both tables are linked with unique id) with the following information.
Orderid being autonumber.
Uniqueid from the mailman table.
Orderdate system date.
Ordertype should be “Mailman”

View 4 Replies View Related

Setting Query Criteria To Be 'blank' Depending On The Criteria Of A Combo Box

Oct 21, 2006

I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).

However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.

Any help would gratefully be appreciated. Thanks

View 5 Replies View Related

How To Query Data For Specific Criteria - Criteria Help

Aug 20, 2007

I have data for hundreds of stores. The data was pulled for the top 15 items by store, so I cannot obtain only the top 5 items that I need. How can I query this data to extract only the top (or bottom) 5 Subjects, by store, based on the percentage column?

StoreSubjectSalesSales %
1516Fiction56431.5-24.15%
1516Audio Unabridged1650.8-231.04%
1516History / Military History10081.1-29.99%
1516Role Playing / Graphic Novels14773.9-20.27%
1516Mystery13152.6-19.84%
1516Audio Abridged1785.9-141.84%
1516SciFi / Fantasy27535.3-7.93%
1516Juv Audio/Video1580.6-100.13%
1516Biography8103.6-15.89%
1516Sports7910.8-15.64%
1516Current Affairs / Law8141.9-14.34%
1516Reference7183-16.22%
1516Juv Non-Bk4585.9-25.02%
1516Science / Tech2961.4-33.98%
1516Movies / TV / Music / Dance3395.3-29.46%
1872Fiction307344.3-7.49%
1872Business134307.5-13.48%
1872Psych / Self Improvement100650.4-10.05%
1872Audio Unabridged29165.9-27.32%
1872Cookbooks57463.3-13.56%
1872Computers59235.7-12.37%
1872Regional59883.4-11.22%
1872Health & Fitness64713.8-10.29%
1872Maps19358.4-27.66%
1872Current Affairs / Law47927.1-11.08%
1872Travel Foreign42583.7-12.27%
1872Religion / Bibles80255.6-6.07%
1872SciFi / Fantasy67641.4-6.49%
1872Study Aids / Notes38299-11.24%
1872Games41745.1-9.79%

View 2 Replies View Related

Tables :: Use Insert Into Command To Insert Filepath Into Table That Adds Blank Label

Aug 28, 2014

I am at work, and I have acquired a database that prints labels. They now want the database to be coded so that after certain labels are printed the database will print a blank label. I have the code figured out as a Do While statement in order to print the blank label. The problem I am having is that I am trying to use the Insert Into command to insert the filepath into the table that adds the blank label.

|DoCmd.RunSQL "INSERT INTO Rod_tmakLabels ( Print, [Order] ) SELECT Yes AS Expr1, 'Rods Labels' AS Expr2"|

If I run the above command, it just adds the text "Rods Labels" at the end of the table. Is there anyway with the INSERT INTO command that I can insert the new label between the 2nd and 3rd row and add another row? Or is the command designed only to add a new row to the end? I haven't had any luck searching for this yet.

View 1 Replies View Related

Queries :: How To Insert A Prompt For Number In A Query To Calculate Against Another Field In Query

Jul 15, 2014

I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?

View 9 Replies View Related

Queries :: Query To Insert Missing Rows From Another Query / Table

Oct 8, 2013

I am using an Access 2010 DB to keep track of a schedule. Essentially, at least one person needs to be signed up to work for every hour of every day in a week.

Tables:
Days with 7 records
Hours with 24 records
Workers with as many people that sign up to work the different hours
Schedule signifying the worker, day, and hour which are signed up.

As of now i have a query that relates these results and gives me a line detailing the worker/time information for the slots that are signed up for.What I'm TRYING to do is to create a query that gives me BLANK worker info when there is no one signed up for a particular hour.Currently my Schedule table has the following:

WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
16 | 5 | 15


What I'm looking to do is have this table matched up with another table (or query) that provides every combination of day/hour. When an day/hour combination is skipped, the query will be able to "fill in the blank" with a row. Like this:

WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
| | 14
16 | 5 | 15

View 2 Replies View Related

Run Query Automatically

Nov 23, 2005

hey guys,

i'm very new to access and its functions. i need to run/update query, say weekly, and generate a report to be dispatched via email. is there any way i can do that automatically, preferably triggered by system calendar?

thx

cheers!:D

View 1 Replies View Related

Queries :: Insert From A Query To A Table When Query Is Changed

Jul 10, 2015

I work with access web database. In access web aggregate functions are disabled in query design. So I made a query in client and i thought then i can insert this data to another web table. but i don't know how to update this table.

Because the source table of the query is also updated.

I upload the database with a table in it and a query. I want to add the data of this query to another web table....

View 1 Replies View Related

Automatically Running Query

Jan 23, 2007

Behind a command button, i have 3 queries being run. 2 of these queries are append queries, copying the specified record into another table (criteria set to ID of the main form) and the other query is to delete the record from the current table once it has been copied. This is then followed by another delete (not query)

When each of these are run, boxes pop up asking if you're sure you want to do it. I was wondering if it would be possibly to bring up my own box to ask if the user is sure they want to continue, and when they click yes, all the questions that the query asks about wanting to do it, are automatically chosen as yes.

I.E there is only one option box, yes and no, and no others appear.

Is this possible, if so, how?

Thanks
Aidy

View 4 Replies View Related

Query That Refreshes Automatically

Dec 11, 2007

I have a database where students use a Form to sign in for computer help. I would like to have a queue displayed on another screen that shows the students what position they are in for help. How would I go about doing that? I thought that having a query on a seperate screen (with no keyboard lol) would be a good way of displaying the student's order, provided I can get a query screen to update automatically or refresh say every 30 seconds. Is this possible, or am I way off base?

View 3 Replies View Related

INSERT INTO Query

Apr 24, 2006

Hi, with SQL code, I was wondering if/how it is possible to use more than one SELECT statement in an INSERT INTO statement.

So, basically, I want my code to look something like this but I am not sure of the correct syntax:

INSERT INTO tablename
VALUES
(SELECT........), ('value 1)
(SELECT........) ('value 2)

View 1 Replies View Related

INSERT INTO Query

Apr 24, 2006

Hi, with SQL code, I was wondering if/how it is possible to use more than one SELECT statement in an INSERT INTO statement.

So, basically, I want my code to look something like this but I am not sure of the correct syntax:

INSERT INTO tablename
VALUES
(SELECT........), -> value 1
(SELECT........) -> value 2

View 1 Replies View Related

Insert Query

May 8, 2006

hi,

is it possible to have an insert query to fill some of the parameters by select query from other table and rest of the parameters by passing the value.


ex:
insert into table1(a,b,c) values (10,select b fom table2 where.... , 35);

i tried with the above syntax but was unable to succeed.

if this is not possible can anyone help me in solving the problem




thanks,

aravind

View 1 Replies View Related

INSERT INTO Query

Feb 21, 2007

Hi,

I'm trying to write the VBA code to insert data into an audit table. My code is in a function which will have the values to be inserted to it passed to the function.

My function code looks like:

Function InsertAuditRecord(stAction, stTimestamp, stUser) As String

Dim SQL As String

SQL = "INSERT INTO tblAuditLog " & _
"(UpdateDescription, UpdateTimestamp, UpdatedBy) " & _
"VALUES (stAction, stTimestamp, stUser)"

DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True

stAction = ""
stTimestamp = ""
stUser = ""

End Function


The values "stAction", "stTimestamp" and "stUser" are the values passed by the calling procedure, but when this query is executed, the Enter Parameter Value window pops up, asking for the values for each of these 3 variable.

Can anyone tell me how to code the query so that it will use the values passed to the function?

Thanks,
Michael.

View 2 Replies View Related

Query To Automatically Update Report

Oct 26, 2011

I have created a parameter query that uses a combo box form. The people who will be using it when completed do not want to see the results in query form. Is there a way to have the query update a form or report without having to create and reformat each time?

View 13 Replies View Related

INSERT Query Which Generates An ID Per Rec.

May 17, 2005

Hi all,

I have an INSERT INTO query that inserts multiple records in an excisting table.

What i need is a functionlity that fills a column called, lets say 'myID' with a query-unique increment.

So, when the query inserts 50 records the ID should increment from 0 to 49.
The next time i might insert 23 rec's and then the ID column should go from 0 to 22.

I tried to call a simple VBA function in the SQL query that returns i+1 and increments i, but that code is only evaluated once every run, so that doesnt work.

any suggestions? maybe in SQL or VBA

Thanks alot!

remvs

View 5 Replies View Related

Insert Field In Query

Jan 17, 2006

I am executing a query and I want to force a value of ALL into a additional field called BUKT. How would I go about this. I need to do something similar to DATE: Now() but with a set value.

Help!!

View 3 Replies View Related







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