Can't seem to find a solution to my problem here or in a few reference books I have on my shelf here at work.
I inherited several access databases from my predecessor a while back and although I’ve fixed most of the pre-existing problems and re-programmed most of the queries and reports at this point I’m stumped on one issue. One of the access files forces me to save every query, form or report I create no matter what. If I create a query because I want to do a quick one time query I can’t just close it and say no to saving when I’m done, I have to save it and then go back and delete it if I don’t need it anymore. I can’t track down any setting or code anywhere in this one access file that would cause this behavior to be different than all of the other dozen or so databases I inherited. This becomes a big issue in the case of if I accidentally do a major change I didn’t mean to that the undo command doesn’t correct, if I go to close it saves the query if it’s been saved before and makes my error permanent and I have to go dig out the nightly backup to undo the mistake.
Any thoughts on where to find or change this setting would be great as I’ve run out of ideas on my own.
Following program i have written in access. what it does is from the query i have created picks up the sizes of doors. calculates hit1 by qty then it needs to put the value into another table alongside the correct sizes. the problem im facing is how do i update the value into another table. heres the listing.
Option Compare Database Option Explicit Dim Db As Database Dim Rs1 As Recordset Dim Rs2 As Recordset Dim lf, rh, drw1, drw2, drw3, drw4 As String Dim q1, q2, q3, q4, q5, q6, qty As Integer Dim hit1, hit2, hit3, hit4, hit5, hit6 As Integer
Private Sub Command70_Click() Dim rs1fieldcount As Integer Dim counter As Integer ''Assign a mdb ''+++++++++++++++ Set Db = CurrentDb counter = 0 Set Rs1 = Db.OpenRecordset("custdoorsize") Set Rs2 = Db.OpenRecordset("cusdoorsbase", dbOpenDynaset)
rs1fieldcount = Rs1.Fields.Count
If Rs2.RecordCount = 0 Then MsgBox "No record found in query"
Else this is where the value will come from Do Until Rs2.EOF If Rs2.Fields!left_door_size <> Empty Then lf = Rs2.Fields!left_door_size hit1 = 1 q1 = hit1 * Rs2.Fields!qty End If
If Rs2.Fields!Right_door_size <> Empty Then rh = Rs2.Fields!Right_door_size hit2 = 1 q2 = hit2 * Rs2.Fields!qty End If
If Rs2.Fields!draw1_size <> Empty Then drw1 = Rs2.Fields!draw1_size hit1 = 1 q3 = hit3 * Rs2.Fields!qty End If
If Rs2.Fields!draw2_size <> Empty Then drw2 = Rs2.Fields!draw2_size hit4 = 1 q4 = hit4 * Rs2.Fields!qty End If
If Rs2.Fields!draw3_size <> Empty Then drw3 = Rs2.Fields!draw3_size hit5 = 1 q5 = hit5 * Rs2.Fields!qty End If
If Rs2.Fields!draw4_size <> Empty Then drw4 = Rs2.Fields!draw4_size hit6 = 1 q6 = hit6 * Rs2.Fields!qty End If
Do While counter <> rs1fieldcount
This is where it finds the correct sizes where it will display the qty value
If Rs1.Fields(counter).Name = lf Then Rs1.Edit Rs1.Fields(counter).Value = q1
End If If Rs1.Fields(counter).Name = rh Then Rs1.Fields(counter).Value = q2 End If If Rs1.Fields(counter).Name = drw1 Then Rs1.Fields(counter).Value = q3 End If If Rs1.Fields(counter).Name = drw2 Then Rs1.Fields(counter).Value = q4 End If If Rs1.Fields(counter).Name = drw3 Then Rs1.Fields(counter).Value = q5 End If If Rs1.Fields(counter).Name = drw4 Then Rs1.Fields(counter).Value = q6 End If counter = counter + 1 Loop
If Rs2.EOF Then Set Rs1 = Nothing Set Rs2 = Nothing Set Db = Nothing MsgBox "Finished ....." Exit Sub Else Rs2.MoveNext
I am fairly new to access and am curious if this is possible. I have a x number of procedures, I will use 4 as an example, with those 4 procedures I have a reference table that has certain devices for those 4 procedures that need to be compared against the device charges. ie Procedure 1A 2B 3C 4D
Procedure Device Reference 1A has AAA,BBB,CCC,DDD 2B has AAA,EEE,FFF,GGG 3C has AAA,HHH,III,JJJ 4D has FFF,KKK,LLL,MMM
Device Charges has AAA,III.LLL.ZZZ
Is there maybe a case statement that I can use to have access try to solve for the best combination of the devices with the procedure to give me the most matches possible and not match say AAA with 3C and LLL with 4D giving 2 out of 4 matches, instead of the result AAA with 1A,III with 3C, and LLL with 4D, and flagging ZZZ as not a match or it matches 3 out of 4 Im not sure if this is the best place for this but any help is greatly appreciated and if more information is needed please let me know thank you for your time in advance
I have looked all over for an answer, and I think there may have been a few examples on this forum that may have helped, but I only have Access 97, so can't open the example databases to fully understand!
Bascially, in the database I am trying to set up, I have Museum items, for instance a photo, and the database users would like to have a list of the names associated with that photo. (obviously for some items there will be no people associated with it, and varying numbers of people for other items!).
I need some way of having the 'associated people' data displayed on the item information form (or somewhere handy). And I need it to have the flexibility to account for differing numbers of associated people. Would using a subform solve this? And if I use a subform, where will that data then be stored? In its own table??
One file is numbers I averaged over the past 3 months. The other file is actual numbers. Not all zip codes are in each file. I need to figure out the difference between the 2 and add the difference to this months numbers. So I created a query and found there were 94 records that matched based on zip cod and customer type. Then I found the difference in sales amounts. Now I'm stuck at figuring out which zip code and customer type don't match in the 2 files. So if zip code 10001 with customer type S isn't in the actual data for this month I need to add the zip code and customer type and have it be -303.23.
I’ve posted on here a few times lately, as I’m creating a database at work. It is going to be used to keep various information about new persons applying for jobs, and I want it be viewed/sorted by either 'Job Grade' or 'Job Title' I keep doing a lot, and then getting stuck on certain things. I’m stuck again, but I think it’s a little more difficult this time…
What I would like to happen is have the database open up, and a screen appears with two choices – ‘View by Job Title’ and ‘View by Job Grade’. When you click one, a new screen appears and a list of some sort appears with all of the job grades / job titles (depending on which button is pressed) on that are currently in the database.
From here, you can choose a job grade / title and it will bring up the form I have created – but only with the records that have the chosen job grade / title.
So for example, there could be 20 records in the table. 5 with job title ‘Manager’, 5 with job title ‘Assistant’. Then there could be 5 jobs with grade ’1’ and 5 jobs with grade ‘2’. When you clicked on ‘View by Job Title’ and then choose ‘Manager’, the 5 records with the job title ‘Manager’ would come up.
I hope I’m making sense so far.
The data would all be stored in one main table, and I already have the form created, so it would be good if I could use this.
Is there any way to do all of the above?
If anyone could help at all, it would be very much appreciated.
The recruitment database I have designed for work is okay in the main except for where I need to filter candidates depending on their skill sets. What I need to be able to do is filter candidates that possess ALL skills selected in a multiselect listbox...
Table structure runs as: Candidates (many) linked to Job Role (one) (because candidates can only have one job title (in theory anyway) Candidates linked to Skills table via junction table (candidate having many skills / one skill belonging to many candidates blah blah)...I'm sure this set up is as normalised as I can get (I aint no expert tho)...
My solution so far (suggested by another forum) was 3 listboxes on a form that runs like... ListBox 1 = Job Role (Manager, Team Leader, Clerk, etc - set to SINGLE SELECT because an employee can only have ONE job title (supposed to anyway LOL)... ListBox2= Skills (Payroll,Audit,Taxation, etc - SET TO MULTISELECT because employees can have more than one skill)... ListBox3 = Candidates (populated by making selections in ListBoxes 1 and 2)
It all works well but is VERY slow as I was told to use make table, append queries and quite a bit of VBA to make the WHERE clauses as SQL statements, etc
I've searched high and low all over the net and have found things that come close but I'm just not adept enough to work it out (I started Access late in life). I feel I need some kind of subquery that first of all finds all candidates that e.g. have ALL 3 skills selected in Listbox 2 (creating a recordset of one row per skill meaning each candidate is listed in the recordset for as many skills selected and then filtering again with a count function that only displays candidates with a count of 3 skills - this subquery would then be used to populate Listbox 3 -
Sorry if I've overcomplicated this but it seems such a simple thing to and I'm getting a lot of pressure at work having being trying to solve this for weeks...
Any help putting me in the right direction or if you know of any similar example databases that would help me learn more would be much appreciated
Ok here is what was going on. I have an option group, I would select an option, type in my search criteria into a text box, and click an event button. The program would then search my database for that criteria and grab the emails of the individuals meeting that criteria. A message box would then pop up, listed all the emails it had grabbed I would click ok (my sendobject command is set to edit the email) and at this point instead of opening the email to edit the program would crash.
After scratching my head for several days I moved on and have come back to this problem again two weeks later, and within 15min I realized what was wrong. I guess it is good to walk away and come back fresh sometimes.
What is happening is that the program does not account for entries in my database that do NOT have an email.
Example, let us say we want to search a state...lets use the state of GA for example. If I have 5 entries from GA with email addresses and one entry from GA without an email address, it will crash the program.
My only guess is that the way the program is setup is that it is grabing whatever data is in the email location. It that location is blank, its grabing blank and crashing the program.
I need a way to discard the entries it searches that don't have email addresses or something...
Please help, this is the last item for this project.
Here is the current code:
Code:'Code by M. Walts'Important information! this code requires a reference to the Microsoft DAO object libraryOption Compare DatabaseOption ExplicitPrivate Sub cmdEmail_Click()'will hold the dynamic SQL queryDim strSQL As String'will hold the WHERE clause portion of our SQL queryDim strWHERE As String'will hold all the recipients of this messageDim strRecipients As String'the recordset we will use to get the emails of the records that match our criteriaDim rst As DAO.Recordset'if there is input in the search criteria, then we will run the query and send the e-mailIf txtSearch <> "" Then'if you have more buttons, just add mosr cases (the value of the radio button'= the Case number, so Value of the State radio button is 1, etc.)Select Case opgSearch.ValueCase 1strWHERE = "WHERE State = '" & txtSearch & "'"Case 2strWHERE = "WHERE PrayerSupport = '" & txtSearch & "'"Case 3strWHERE = "WHERE Denom = '" & txtSearch & "'"Case 4strWHERE = "WHERE PACTTrainer = '" & txtSearch & "'"Case 5strWHERE = "WHERE PACTPartner = '" & txtSearch & "'"Case 6strWHERE = "WHERE City = '" & txtSearch & "'"Case 7strWHERE = "WHERE Donor = '" & txtSearch & "'"Case 8strWHERE = "WHERE MailingList = '" & txtSearch & "'"Case 9strWHERE = "WHERE Conference = '" & txtSearch & "'" Case 10strWHERE = "WHERE YouthPastor = '" & txtSearch & "'"Case 11strWHERE = "WHERE PreviousCustomer = '" & txtSearch & "'"End SelectstrSQL = "SELECT EMail FROM tblUser " & strWHERE'run the query and get the results into the recordsetSet rst = CurrentDb.OpenRecordset(strSQL)'Loop through the recordset and add all the EMailsDo While Not rst.EOFstrRecipients = strRecipients & ";" & rst!EMailrst.MoveNextLoop'remove the first ; from the strRecipientsstrRecipients = Right(strRecipients, Len(strRecipients) - 1)MsgBox strRecipientsDoCmd.SendObject , , , , , strRecipients, "Email Subject", "Email Body", Truerst.CloseSet rst = NothingEnd IfEnd Sub'stops a ' entered in the field from breaking the queryPrivate Function SQLSafe(safeMe As String) As StringSQLSafe = Replace(safeMe, "'", "''")End Function
(This means: Between the amount of 0 and 100, the eventual amount equals to 5. Between 100 and 500, the evebtual amount is 10. etc. etc.)
Over the amount of 50,000 for every 5,000 the amount goes up, the eventual amount (column c) goes up 10, with the condition the eventual amount (column c) should not be over 500.
My current If-statement: Eventual Amount: IIf([Amount]<100,5,IIf([Amount],500,10,IIf([Amount]<100,15 etc. etc.)))
Is there an easier way to do this... It's going to take mighty long to do it like I am currently doing it. Thanks a lot Regards kruger101
I have a database for my company which I am making.
The products are hydraulic cranes and come in numerous models (or sizes), in turn these models come in numerous versions, 5 different ones in all, lets call them for simpleness 1-5. However, some cranes come in versions 1-3 only, others 4-5 only, some all versions and no-doubt some a random mix!
At present I have a table with the fields "Model" (Text), and seperate fields for each "Version" (ie. 1, 2, 3 etc), these are Yes/No tick boxes. Ticking a given box for any given model, means that only these options are available on a form. Ie When you select a "400" crane, you can for example only select Version numbers 1, 2 and 3 - those that are checked in this table.
What I need to do is when selecting each individual model on a particular order is to display 2 figures hydraulic oil flow-rate and also hydraulic pressure required from a reference table I assume. These figures are different for every given model/version combination.
The difficult part as I see is relating the yes/no check box from the table above to version number on the reference table.
How difficult is it to make a sychronisationfunction? I have a meetings database. Sometimes the meetings are enterd localy and need to be sychronized with the main file. Is this hard to do? where do i start.
Hi Everybody. I've been nosing around here because I have this difficult problem with a database design. I modelled my program in UML so I have a class diagram. Now I want to create an access database out of it, but this is too hard for me.
It's about a school project for flowers. Every year, they make a cross plan. This cross plan contains crossings. Many crossings. And every crossing exist from 2 genotypes. A mother and a father. With this crossing several new genotypes are created. How on earth do I realize that in a database? A plant is male as well as female so you don't need to indicate which sex it is.
Further more I want a genotype to be judged on his characteristics by a user as much as he wants to.
Well...I hope someone can help and if you have questions about it don't hesitate to ask.
Where [WeekUren] contains multiple references to unique records in [Projects].
[WeekUren] has the following collums which are important: -ProjID -Week -Persoon
Where these three always form an unique combination. For instance: 1 | 1 | 1 1 | 2 | 1 1 | 3 | 1 1 | 1 | 2 1 | 2 | 2 2 | 1 | 2
Saying: Person1 will work week 1 till 3 on Project1, Person2 will work week 1 and 2 on Project1 and week1 on Project2.
Weeks are here relative to the project start date (a value of [Projects]) and always start with and increment with 1. So week1 of Project 1 and 2 don't neccesarily fall on the same dates, hence Person2 is still able to work both Projects.
Bear with me here.....
Now I need to select all duplicate records (where people are assigned to two (or more) projects in the same real week.
Real week is defined by: Code:DatePart("ww",((WeekUren.Week-1)*7+(SELECT Projects.Start FROM Projects WHERE Projects.Id=WeekUren.ProjId)))
And the Query to convert [WeekUren] in to the relative form is: Code:SELECT WeekUren.Id, DatePart("ww",((WeekUren.Week-1)*7+(SELECT Projects.Start FROM Projects WHERE Projects.Id=WeekUren.ProjId))), WeekUren.Uren, WeekUren.Persoon, WeekUren.ProjIdFROM WeekUren;
The query to print out duplicates is ofcourse: Code:SELECT *FROM WeekUrenWHERE (((WeekUren.Week) In (SELECT [Week] FROM [WeekUren] As Tmp GROUP BY [Week],[Persoon] HAVING Count(*)>1 And [Persoon] = [WeekUren].[Persoon])))ORDER BY WeekUren.Week, WeekUren.Persoon;
Sofar so good, combination of the two however give me a syntax error. Hence my question here how to construct such a diabolical Query. Thanks!
I have a financial database that gets downloaded transactions off the internet from our accounts. The problem is that the transaction payer/payee is not always unique and needs to be classified. This is easy done using If statements but I really want to enter parts of the string into a table and have a query return a category for this payer/payee. Example: I want this: AUTO ONE CLARKSON CLARKSON WA AU006495 to be recognised as: car parts
or with this tranaction: CLARKSON MINI MART CLARKSON553908 look for "mini mart" and return supermarket
This step will make classifying transactions a lot simpler and user friendly. thanks
I have Two fields with DateWorked and EmployeeNumber. All I want to do is count the number of employees that worked on a specific date. In the employee numbers I have 4 dummy numbers that I don't want to count in my query (00001-00004), but will have entries almost each day. The rest are 5 digit numbers.
Some employee will work some days, but not others.
So I set up my query with:
EmployeeNumber Table Total: Count Criteria: >4
I did that think that it would count all employee numbers great than four, but all it seems to do is give me the count of days that more than 4 employees worked.
What am I doing wrong? I feel like this is simple, but after some of the more complex things I've done over the past week, this seemingly "easy" thing has me stumped.
Alright, so my pride and joy and departmental savior database has been fully implemented and people are so pleased with it that they *all* want to use it at the same time. Initially, I made it custom for three peoples' UI preferences, but since it's such an intuitive design, other staff now feel comfortable using it instead of passing the work down the line to the one person who knows how to use that "newfangled thing".
A couple years ago, I used FMPro and allowing for multiple users at once was an annoyingly complicated process dealing with permissions and rights and accounts and passwords and yadda yadda. Is it any easier to set up in Access? Does anything special even need to be done to allow for multiple simultaneous users?
This is a toughie (i think so anyway!). I'll attempt to explain!!!
I start with a CONTINUOUS form in my DB that shows all readings for a single given customer's connections. E.g. Customer A has 5 connections and each connection has say 3 readings. Thus this query which uses joins between the Customers, Connections, and Readings tables, would return a list of all readings for each connection, so in the above example: 1*5*3 = 15 records.
Not too tricky so far, but then what I want is for each connection to have just one line showing the latest reading (easily achieved by use of SQL Aggregate Max function on the date field coming from the readings table) TOGETHER WITH the last-but-one (next most recent) reading. So back to the example taking Customer A's connection 1, the row would be as follows:
Cust Conn CurReading PrevReading A 1 750 500
Where the CurReading value (750) comes from a different record than PrevReading (500).
I've tried all sorts of ways (subqueries etc.) to achieve this without success. The main problem being that any sub query would require parameters from the current record's fields, which seems not to be possible. Can anyone help or is this simply not possible in MSAccess Forms. If it isn't possible anyone have any suggestions as to an alternative way?
