VBA Query Used In A Form

Aug 7, 2005

I have these queries (QRY1 and QRY2) made in vba which are used to fill 2 multiboxes (ListIncluded and ListNotSelected) in a form. The left multibox (ListIncluded) shows all the people that attented to a certain class, the right box shows the ones that werent present. The table "tblPersoneel" is used to find the names of the people. Basicly I use these tables: tblPersoneel (all the people) tblTrgRegAlgemeen (general data about the lesson like date etc) and tbltrgRegDetails (details, uses TrID to relate to a record in tblTrgRegAlgemeen and PersID to link it to the ID from tblPersoneel). I use buttons to move around the people.

The following code is used:

Dim SQL1 As String

SQL1 = "SELECT [tblPersoneel].[ID], [tblPersoneel].[Achternaam] & ', ' & [tblPersoneel].[Voorletters] & ' ' & [tblPersoneel].[Tussenvoegsel] As MyName, [tblPersoneel].[Rang], [tblTrgRegDetails].[TrID], [tblPersoneel].[Regnr], [tblPersoneel].[Toegang] " & _
"FROM tblPersoneel LEFT JOIN tblTrgRegDetails " & _
"ON ([tblTrgRegDetails].[PersID]=[tblPersoneel].[ID]) " & _
"WHERE ([tblPersoneel].[Toegang] = " & Me.ListToegang & " " & _
"AND tblTrgRegDetails.[TrID] = " & Me.TrainingID & ") " & _
"ORDER BY [tblPersoneel].[Achternaam];"

Forms!frmTrgInvoer!ListCurrent.RowSource = SQL1

Dim SQL2 As String

SQL2 = "SELECT [tblPersoneel].[ID], [tblPersoneel].[Achternaam] & ', ' & [tblPersoneel].[Voorletters] & ' ' & [tblPersoneel].[Tussenvoegsel] As MyName, [tblPersoneel].[Rang], [tblPersoneel].[Regnr], [tblPersoneel].[Toegang]" & _
"FROM tblTrgRegDetails RIGHT JOIN tblPersoneel " & _
"ON [tblTrgRegDetails].[PersID]=[tblPersoneel].[ID] " & _
"WHERE [tblPersoneel].[ID] NOT IN (" & SQL1 & ") " & _
"ORDER BY [tblPersoneel].[Achternaam];"

Imo this would result in the following: at the record i am viewing in the form i have people in the box which uses SQL1 and the other people are in the right box using SQL2. Any person cant be in both (one cannot be in a lesson and not be there).

I am pretty sure this is a SQL problem and not VB and I really hope someone can help me with this.

Thanks in advance!!!

View Replies


ADVERTISEMENT

Query In Forms Question (printing A Single Selected Query Record From A Form?)

Oct 19, 2004

What is the best way to impliment a query in a form so that the user can view the query records, and have the option to print or save the selected record using command buttons?

I tried subforms but I could not get the command buttons to work in the subform after it went into the form, it wanted to print the entire form instead of the selected record from the subform.

So in a nutshell I have 3-4 queries that are built, and I want to have them show up on my form in a format that the user can scroll through the results and select a single record of the results and then print or save that individual record from the form, if such a thing is possible.

Thanks in advance

Todd

View 1 Replies View Related

Forms :: Update Form / Query Without Updating Underlying Tables To Query

Jul 23, 2015

I have a form which will be used as the basis to print a label.

It is bound to a query and when I open the form I pass over a 'where' condition to return 1 record. I then use the query to produce a report/label.

What I want to do is to update the form/query without updating the underlying tables to the query.

View 14 Replies View Related

General :: Enter Value In A Form / Run Query And Display Result On Form

Jun 15, 2013

Is there an easy way of entering a value in a text box, passing to a query to do a count function and then return the value of count function in to anther text box?

View 5 Replies View Related

Forms :: Using ID Value Passed To Form From Previous Form In Query

Aug 17, 2014

I have a big table, EquipmentDetails and separately I have job plans.Job plans can have many pieces of equipment. I store the relationship in EquipmentDetails_JobPlanDetails, but am feeling frustrated as I can't seem to get Access to build the SQL query from this in the way I want.

I am generating a List Box in a form which is populated from a query.The query calls on a table which lists ItemID from EquipmentDetails and JobID and in the current form (where the list box is) I want to pick up and display the ItemIDs associated with that JobID. The current JobID reaches my form correctly (I've proven this by displaying it in a text box) from the previous form.

The problem I have is that I can't seem to get the SQL query to only get ItemIDs that are linked to the current JobID. No matter what I try, it either gets all of the ones in that table, or I can't compose one.What kind of SQL do I need to only grab the ones relating to the current JobID please?I've tried building it in the design view but it says that it can't do it because the outer joins are ambiguous.

View 2 Replies View Related

Query In One Form With Results Displayed In Another Form

May 13, 2005

First, I am a beginner with Access. I am a graphics designer that has been assigned to cover for a db programmer that quit!

Here is my problem:

I have a database that we use to hold customer information. There are 22 fields in each record, and we are now well over 3000 records.

Once upon a time, to find a specific customer, we would just go to the bottom of the page, and use the arrow buttons to scroll through them all. This is no longer possible as the size is too big to manually search.

What I would like to do:

Upon Access startup, display a form that has a single input field and a button titled "Search". The input field is titled [UserName], as this field is the unique key identifier for the record. When the user types in the UserName and clicks the SEARCH command button, another form appears to display JUST THAT RECORD in the easy to read form!

If this is really basic, I appologize. I have 20 years experience with commercial illustration and only 7 days experience with Access. I have purchased books, and hit the forums, but I am not a VB programmer, and my skills this area are REAL weak!

My resourse books include the following:
Wiley - Access 2003 Bible [Prague, Irwin & Reardon]
Osbourne - How to do Everything with Access 2002 [Anderson]
O'Reilly - Access Cookbook [Getz, Litwin & Baron]

Thanks in advance for any assistance.

View 1 Replies View Related

Form With A Query To Display Result In Another Form?

May 13, 2005

First, I am a beginner with Access. I am a graphics designer that has been assigned to cover for a db programmer that quit!

Here is my problem:

I have a database that we use to hold customer information. There are 22 fields in each record, and we are now well over 3000 records.

Once upon a time, to find a specific customer, we would just go to the bottom of the page, and use the arrow buttons to scroll through them all. This is no longer possible as the size is too big to manually search.

What I would like to do:

Upon Access startup, display a form that has a single input field and a button titled "Search". The input field is titled [UserName], as this field is the unique key identifier for the record. When the user types in the UserName and clicks the SEARCH command button, another form appears to display JUST THAT RECORD in the easy to read form!

If this is really basic, I appologize. I have 20 years experience with commercial illustration and only 7 days experience with Access. I have purchased books, and hit the forums, but I am not a VB programmer, and my skills this area are REAL weak!

My resourse books include the following:
Wiley - Access 2003 Bible [Prague, Irwin & Reardon]
Osbourne - How to do Everything with Access 2002 [Anderson]
O'Reilly - Access Cookbook [Getz, Litwin & Baron]

Thanks in advance for any assistance.

View 3 Replies View Related

Inserting Data From One Form To Another Using A Query In A Form

Sep 6, 2004

HI

I have a form for member details that comes from the member table and I want to
include a button on the form that will remove the member details from the member
table to a member history table and then delete the details from the member table.
When moving the record I also want to include details from another table called 'promotion',
this table is linked to the member table via the member ID (Foriegn Key).

Firstly I want to know if this is possible and if so how I would achieve this. I am having
trouble getting the SQL query to identify the current record that the form is displaying so I can
select additional information and move it into the Member History table.

Any information would be greatly appreciated.

Thanks.

View 2 Replies View Related

Query Results In Form, But What If Query Is Null?

Aug 30, 2005

I have a query that displays results in a form, but if the query is null, I want to display a different form, or just an error message that says something like "your query returned no results" (right now it will display the form with no fields)

I am a beginning Access/VBA user and have searched and browsed the forum for combinations of null/query/form, but haven't found what I need. Can anyone point me in the right direction?

Thanks for any help.

View 5 Replies View Related

Form/Sub Form Update Query?

Sep 22, 2006

:confused:
I have a form displaying individual contract information. This form is a continuous form and on each record I have a button that activates a dialog box with two text boxes to facilitate NOTES entry for the current record on the main form. When I close the dialog form - I need the NOTES text entered to be updated in the corresponding text fields on the continuous form.

I have tried the following :

1. After the dialog has closed - set the main forms Bookmark property/ Requery the form and then reset the forms Bookmark.
Problem with this solution is that occasionally I get ' Not a valid bookmark' error message.

2. In the Unload event of the dialog - I have tried Requery calls for each of the text field controls on the constinuous form prior to the dialog closing. This works fine for records that already exist in the table and the data does reflect the changes correctly. For brand new records, the updates are not reflected on the main form and the data only appears when the main (continuous) form is closed and re-opened.

Does anyone have any ideas on how to do this properly? Has someone done something similar?

Thanks
Regards
GUIDO

View 1 Replies View Related

Query A Query, Where It Is Based On A Form?

Jan 10, 2007

Hi All

I have a query that runs based on the contents of a date entered in a form (I will call that Query1).

I need to take the data from Query1, and then have Query 2 analyse the data (I want it to tell me where the Count of First Day is more than 5).

When I use the query design grid, I include the fields want from Query 1, and put the criteria in to say CountofFirstDay >=5; but when I run it, it asks for the data for the form (relating to Query 1)

What can I do? thanks!

Maria

View 1 Replies View Related

Forms :: Parameter Based Query - Filter A Form Based On Another Form

Mar 20, 2013

I have a form which uses a parameter based query to present an individual senior doctor with a list of names for of individual juniors to provide an assessment report on. When the first form opens the user enters their RespondentID.

Once senior has decided which names to comment on I have another form which has the questions to be answered which is opened by clicking a button on the first form.

How can I pass a parameter from the first form to the second so that only the records relevant to that senior doctor are displayed? The underlying table has 60 senior doctors and 20 junior doctors. The senior doctor is identified by the field RespondentID in the first form. I've tried putting a WhereClause in the FormOpen command but I still get a dialogue box asking for the parameter RespondentID when the second form is opened.

View 2 Replies View Related

Query Form

Jul 25, 2007

I have a query form which user use to search for records according to their conditions they selected from the dropdowns.

Now when there is result for the conditions specified it works fine but when there is no record it pops up message saying no record and as one hit okk it gives below error:

"Run Time error 7874 eta database cant find the object 'xxx-0725'"

this is some table gets created everytime we search something.
and if there is no result this is not created somehow and it gives error.

View 3 Replies View Related

Need Help With Query/Form

Nov 25, 2005

I have a form with 4 pulldown boxes. What I want to do is basically query a query? But I am having trouble. I am basically looking for an AND/OR type clause.

If I choose any of the 4 combo's or some, or all. I want all the selections made in the pulldowns to be considered in the query. I have to consider some pulldowns will have not been selected. For that matter I must consider none were selected.

Basically having trouble passing this info from the form to the query and getting the query to do anything with it. I am not talking opening the query, that can be done with a button with an event handler. I am talking actually getting the query to give me the results I asked for. Basically I can get the query to take the data in the form of "AND" or "OR" but not to consider both.

Maybe I am talking in circles but I want clear my desire. If I chose 2 pulldown boxes, and select data. The query should consider both that I chose, and since the other 2 remaining boxes have not been selected ignor those NULL values as if nothing was in the criteria.

Any help would be appreciated.

View 3 Replies View Related

Query By Form

Jan 9, 2006

I have a form that has a bunch of unbound fields on it. I have a query that is tied to the fields of the form. The purpose is the form is a filter and when you type something in it goes through the query and then creates a report. The form is not bound to anything. Is there a way to have a list box in the form that has a list of information that people can choose multiple records from the list box and have it go through the query?

View 5 Replies View Related

Query By Form - SQL? Help

Feb 13, 2006

I created a form with several combo boxes on it. When data is chosen within the combo boxes, a query/report can be run and it retrieves all of the information in accordance with the combo boxes.

The functionality that I need to build in is the option to leave any number of those combo boxes blank in order to retrieve ALL information from the field linked to that combo.

Example:

I have three combo boxes. One is named team and it lists all team names. The second combo box is priority (1-2-3-4 etc). The Last combo box is day of the week. As it stands now, I can input data into each of these boxes and my report functions fine. However, I want to be able to leave one or more of them blank and it pull back info. Such as....if I dont choose a specific team, but I choose a specific priority and a specific day of the week, it should pull back ALL teams that have that priority and day of the week.

Does this make sense?

I was given a link to a site by the name of Brinkster to include an ALL function on my combo box, but that will not work for some reason. I know what I want to do is possible, because I have incorporated it into another database of mine. However, I cant make heads or tails of my notes or how that database is setup.

Help?

need more info?

View 6 Replies View Related

Using A Form To Run A Query

Mar 8, 2006

I tried a search but wasn't able to find what I was looking for. I would like to be able to use a form to run a query in my db. I need to use name, begin date and end date and then have a button to run my query. My problem is that I can't get the unbound text boxes (for the dates) to use a date format. Have I taken the wrong approach?

View 1 Replies View Related

Using Query In Form?

Sep 12, 2006

I have a table with a lot of columns. I have two columns i want to prepair to be used in a tablesheet form. One with sizenumber and another with productnumber. I have a datasheet form where I want to use the data from sizenumber and productnumber writen as one ex. "3-345767" (first number is size and the second is productnumber). Well I succsesfully done this in a query table but I can't get it to work or sort in the form. Its greyed out. Any suggestion? Can I do this on another way?
I gonna get greyhaired of this.. :confused:
Any suggestions would be help full!

View 1 Replies View Related

Query By Form

Dec 15, 2006

I am trying to set up a database to set up a referral system. I have set up a form where I can enter the details of the booking. I have set a form so that I can enter the date of booking, for example booking for the future. The results are nicely produced in a datasheet/spreadsheet style. But I want the results to be displayed as a form, in a table style. So that I can edit and then enter the outcome of that booking.

I am a novice on Access, can anyone help.

View 4 Replies View Related

How To Run A Query From A Form

Feb 12, 2007

I have a query that I wrote that searches several tables for an employees work ID number. The query works GREAT! But now I want to build a form with a search button that when clicked will run the query and if it finds the employee in any of the tables, it will return the requested fields on the form. If it doesn't find anything, it will send a MsgBox that says "Not Found!"

Any ideas on how to go about this?

Is this even the right place for me to ask this question?

Thanks,
David

View 5 Replies View Related

Query From Another Form

Mar 13, 2007

Hi,

I created a form (frmPatient) which contains patient ID information and demographics. A pateint can have multiple mammographies on different dates. I have a button on frmPatient to open the mammographic findings form (frmMammo) and enter mammographic findings for a certain examination date (examDate). The primary key for frmPatient is PatientID (autonumber), the primary key for frmMammo is MamID (autonumber). PatientID is foreign key for MamID. They have a one-to-many relation.

Here is what I want to achieve: On frmPatient I want to add a button and a listbox. The button will trigger a query to show the dates of prior mammographic examinations on the listbox (if they exist) for the actual patient. To create the query, I think I'll have to use PatientID, MamID and examDate. For "On_click" property of the button I'll use a "Me.listbox.rowsource=" statement followed by the query statement. The problem is that I can't formulate the query. It is important for the query to show only the examination dates of the selected patient and not all of the patients. I tried the query wizard but it shows me all of the patients with a mammography and I don't know how to restrict it for a certain patient ID. I also don't know how to activate a query by pressing a button.

Any help would be very useful. Thanks....

View 2 Replies View Related

Query By Form - Please Help

Oct 8, 2007

I don’t know much of VBA/SQL. There are many commodities in my DB for which I need data as under
year-wise
upto a year
from a year to the present year
between certain years
I export the query result to Excel to make a table/chart which will be printed.
I used the following for all my text boxes. This returns result for the year I specify for the commodity.
Field: MyFieldName
Show: Unchecked
Criteria:
[MyFieldName] Like "*" & [Forms]![SearchForm]![MyTextBoxName] & "*" Or [Forms]![SearchForm]![MyTextBoxName] Is Null

Then I tried the following code for the text box(Year)
Field:
[MyYearField] Between [Forms]![SearchForm]![txtFrom] And [Forms]![SearchForm]![txtTo]
Show:Unchecked
Critreria:True

This didn’t work. Then I put this code under the field in my query

Field:MyYearField
Show:Checked
Criteria:Between [Forms]![SearchForm]![txtFrom] And [Forms]![SearchForm]![txtTo]

I typed the desired key words in the respective text boxes and also the years (From-To) in the input form. Upon hitting the button the query again asks for input for all text boxes. But the good thing is that the query returns the category of records for the years I specify. Any solution to avoid this duplicate input?

View 1 Replies View Related

Query With A Form

Oct 10, 2007

I am having trouble using a form to define parameters in a query. I would like to have the query look at the form and query by that. First I want to make a selection in two check boxes, I have the fields set up as yes/no and I need to query based on both. Right now I can get the query to run if the boxes are check in the form but if they are not it will not give me any data. Here is the expression I am using.
[Forms]![form name]![field name].

View 8 Replies View Related

Query By Form

Nov 12, 2007

Hello guys

I have a question that's probably a beginers one but i looked all over the forum and the examples and couldnt find any solution. If you could help it would be great. Here we go:

Lets say we have 1 form, a table and a query. On the form there is a combo box named combofield with values 1, 2 and 3. Table has this fields:

name
f1
f2
f3

query looks like this:

field from some other table 1
field from some other table 2
name
f1

What i need to do is when i chose 2 from combo box and press some button is to get this query:

field from some other table 1
field from some other table 2
name
f2

and same thing for value 3 from combo box

field from some other table 1
field from some other table 2
name
f3

I know i could just make 3 queries and just pick one based on the values in the combo but in reality i have some 15 fields that i need to chose from.

Tnx in advance

View 4 Replies View Related

Query By Form

Feb 26, 2008

Hi!

I spend a couple of hours searching for this, but I can not find it or even I don't know if it is possible.

I have a query based on inputs from a form (textbox).

I want that:
1) If i put any value in the txtbox, the query show the results for that
2) If I don't put any value in the textbox the query show me only the NULL value and empty ones. (not all values).

What should I write in the criteria to do this?

View 3 Replies View Related

Form - Query

Feb 17, 2005

Folks, I am a newbie in Access programming and I tried searching in this forum before posting this question. I finally gave up and am posting this here. Please help me find a solution to my requirement below:

I have a form called "frmplan" that has about 15 fields. I have an auto id field in this form whose record source is coming from "tblmaster". Based on this autoid field I need to fill out the value for the other 14 fields (I am using dlookup statement on each of the field Control Source now which is not working well) from 4 other tables in this database.

I thought using a query would be a good method, but I dont know how. What is the easiest way to get this done?

Thanks for all your help!

I have 2 other requirements on this same form - But I will bring it up as soon as I implement a solution for this requirement above.

View 7 Replies View Related







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