Did Anyone Ever Solve This One?

Aug 31, 2007

I am having the same issue.

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.

View Replies


ADVERTISEMENT

Can Someone Solve This Vba Program

Oct 10, 2006

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

End If
Loop
End If
End Sub

please help

View 1 Replies View Related

Solve The Queries

May 8, 2006

Dear My Friends

I want to solve the follwing matter.

** Enter-Invoive Number
** Enter-Amount


If the amount 1000> - Show stamp duty as $1

If the amount 1001-2000 -Show stamp duty as $.2

If the amount 2001-3000 - Show stamp duty as $3

If the amount 3001-4000 -Show stamp duty as $4

Like wise upto 50,000

If the amount 50,000 < Show stamp duty as $50

I want to above results in access report with name of invoce number, amount and stamp duty .Please help as soon as possible

View 1 Replies View Related

Having Access Solve For The Best Answer, Is It Possible

Aug 23, 2007

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

View 3 Replies View Related

Will A Subform Solve My Problem?

Aug 31, 2006

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??

View 2 Replies View Related

A Difficult To Solve Problem!!

Nov 14, 2006

Hello,

My problem is this:
I want to retrieve some data from the database, the data is as follows:

pourcentage de processus évalués dans l’année

my problem is that when I am writing a querry for this...it gives me an error because of the << ' >> mark in the word << l'année >>

my querry is this...
strSQL = "insert into temp_indicateur (id,description) values(" & rs!id & ",'" & rs!description & "')

I am getting these values from another recordset.

Can anyone please help??

View 2 Replies View Related

Query Needed To Solve Candidate Skills Database

Jul 30, 2007

Hi there,

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

Regards

dazza61

View 4 Replies View Related

I Figured Out The Problem, Now Please Help Me Solve It - Search/Email Results

Nov 30, 2004

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

View 2 Replies View Related







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