I am glad to join you and i have this problem. I am creating a database for motor vehicles. I got this code from KBAlertz web site under article number (210242).
However, i am using ms access 2003. I intend to have three text boxes (VIN, Begin Date, End date) on the Query form, such that if i only have the VIN the query will return it if it is in the record, or alternatively by dates.
Please inspect for me the code below i do not know where i am going wrong and it's my first time to encounter VB, i didn't know how to attached my database to this post. i left comments as originally provided:
Private Sub cmdRunQuery_Click()
Dim db As DAO.Database
Dim QD As QueryDef
Dim where As Variant
Set db = CurrentDb() .....
I have a problem with making a (simple!) search form work in Access 2000. I wrote the code in (what i believe to be) Access 2000 version, and it runs on my machine that is Access 2002-3. The search form uses VBA code; an if-then logic for 3 fields that are chosen by the user, and discounts fields that are not selected. The code then filters results from a query based on 2 tables and presents these in another form.
This all works fine on my pc, and on others using Access 2002-3, with macro security set at medium (and macros approved to run). However, the search form does not work on pc's running Access 2000. I do not know the security levels for these pc's but please if anyone knows of what may be causing this... please help!!
I'm trying to calculate a numeric value of a field based on the value of another field as a row is created using an "After Insert" macro. To test this I've created a table which has two integer columns "NUMBER1" and "NUMBER2". The macro was defined using the wizard as:
Code: EditRecord SetField Alias Name NUMBER2 Value = 5+[NUMBER1] End EditRecord NUMBER2 is not populated when I enter a value in NUMBER1
I get the following in USysApplicationLog:
Error Description -20335 EditRecord failed because the default alias represents a record which is read only.
I have built a rather extensive database using Access 2000. When I install it on an Access 2003-enabled machine, the first time it is opened, the user is presented with the following errorbox:
"An error occurred and this feature is no longer functioning properly. Would you like to repair this feature now?"
At this point, clicking "Yes" results in an update the installation of Access 2003. Then the database must be opened manually again. Then, the following is displayed:
"Module not found."
Closing the database at this point and then opening it again results in a normally functioning database.
I have several questions:
1) What is the feature that is no longer functioning properly? 2) What is updated when the installation exec runs? 3) What module is not found? I assume this is one of the VBA modules that I've created, but I've check all 62 of them and they all seem to be there available. 4) My first guess was a reference problem, but I've checked the references used and all are available, as far as I can tell. These are the ones that I have referenced: VBE6.DLL, MSACC9.OLB, DAO360.DLL, STDOLE2.TLB, MSCAL.OCX, MSADO25.TLB, MSO9.DLL. 5) After getting the first error - the one about nonfunctioning feature, I clicked "no" to the repair now question. This does nothing except the database remains open. At this point, I opened the code and tried to compile, but I get the "module not found error". I likewise tried to set a breakpoint in one of the first modules and I get the same result. 6) I even tried to copy the forms, queries, tables, and reports into a blank database with no references and no other extraneous code and received the same result.
This site has been extremely helpful in the past and I am really relying on you guys again for the same stupendous help now.
I have an Access 2000/2003 database which opens up and displays correctly in Access 2007, but when I go to use the File>Print command, or for that matter any of the other pull down menu's nothing happens?
Has anyone else experienced thisproblem, or does anyone know what I am doing wrong?
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.
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?
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.
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]
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]
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.
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?
: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?
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)
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.
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.
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.
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.
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.
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?
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.
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?
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!
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 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?
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.
