How To Avoid Auto-generated Messages Pop Up
Oct 14, 2011
I have the following code (below) that I use to allow the user to select a particular value to determine which records to delete. However, I was wondering if there was a way to avoid having the Microsoft Access auto-generated messages pop up.
For this particular case, after the user selects the value and hits the command button, then a msg box comes up verifying if the user wants to delete the records with selected value. Clicking 'Yes' will then generate the Access message that asks the user again if they want to delete the records and that these actions can not be undone. Clicking Yes here will delete the records.
However, clicking 'No' or x'ing off this Access generated message will then cause a Run-time error 2501. I want to handle this error if it all possible.
Code:
Private Sub CmdRemove_Click()
Dim strQuery As String
DoCmd.Beep
If vbYes = MsgBox("Are you sure you want to delete? Clicking 'Yes' will completely remove all prospects with the selected Class from the database", vbYesNo + vbQuestion, "Delete prospects?") Then
strQuery = "DELETE * FROM [Prospects] " & "WHERE Prospects.Class = '" & Forms("Classpopup").cmbRemove & "';"
DoCmd.RunSQL strQuery
End If
End Sub
View Replies
ADVERTISEMENT
Mar 6, 2006
How can I avoid the auto save record in Accesses? I want save the record under definite conditions.
View 1 Replies
View Related
Jul 13, 2006
I am developing a DB that is composed of approximately 50 tables. The primary key in every table is automatically generated byAccess. I understand that the primary key for each record in the table has to be unique. My question is: Is the primary key uniqueness applicable across the entire DB?
For example, table A has a auto gen key #25 in it. Therefore, no key in table A can be #25. Now I make an entry in table B (A:B, is 1:many). Can the primary key in table B be #25? OR, once #25 is used and continues to exist, it cannot be used in *any* table?
I have made several small examples and the keys across all the tables are unique. I don't know if this is guranteed across all the db tables, or just in my particular case?
Thanks
John
View 1 Replies
View Related
Sep 20, 2006
Can controls in a form be made flexibly or auto-generated based on parameters set in another form?
Supposed I have a continuous form with 10 textboxes, while at some other time I need to have 12 textboxes or 5 textboxes only. Total textboxes needed would be set from another form. The maximum number of textboxes should be unlimited. Is it possible?
The backend table actually can be created flexibly using SQL based commands. And to link the fields to the textboxes is another problem.
Any clue?
View 4 Replies
View Related
Jul 16, 2013
I am trying to construct a system in access where I can have a user type in a part number, revision of a part, job number, serial number quantity, and generate a serial number based on the quantity that they need.
I am getting hung up on even how to construct the tables effectively to accomplish this, let alone the forms, queries, etc.
The numbers have to be in the following form. XXXX-XXXX
For an example:
I have part ABC and I need 5 serial numbers.
I need to be able to create a batch of serial numbers automagically that reads like:
9001-0001
9001-0002
9001-0003
9001-0004
9001-0005
Then for the next part XYZ I need 3 serial numbers. It would be constructed like this:
9002-0001
9002-0002
9002-0003
With the goal of eventually being able to search a part number and revision, or a job number and return all serial numbers assigned to that.
Is this possible in MS Access?
View 4 Replies
View Related
Jul 22, 2013
Have a table with data in it and I want to add a new field to uniquely identify each record (simple numerical sequence like 1, 2, 3, 4.....). I don't know VBA and don't know SQL.
Tried opening the table in design view, adding a new field with "autonumber" as the data type but get the following error msg: "File sharing lock count exceeded. Increase MaxLocksPerFile registry entry". The table has about 750,000 records in it.
View 1 Replies
View Related
Apr 29, 2015
how to change Access default error messages with custom messages? Like, if I enter some data in a sub form where the parent form is empty new record, default error message says that 'You must enter a value in the 'column name' field'.
I want to disable this default message and display my own error message. Now I know MessageBox and MsgBox fuctions to display custom messages, but how to disable the default message.
View 6 Replies
View Related
May 10, 2005
Quote by Pat Hartman
>>>BTW, one somewhat undesirable thing happens when you go with replication. Access changes ALL your autonumbers to random instead of sequential. That means that from that time on, all new autonumbers will be assigned randomly to minimize the problem of conflicts between the design master and replica sets. A random autonumber can range from a very small negative number to a very large positive number. These autonumbers can no longer be used as your customer numbers or order numbers if these numbers are ever printed or given to the customer to be used for reference. A customer probably will be confused by an order number = -2. If this is going to cause you a problem, you'll need to switch to custom generated numbers. Post back for details if you need them.<<<<
I'd be interested in getting some details. Data will be entering that table through a data entry form and through appending from a second copy of the DB. I want to make the PK a random autonumber but then when the second DB is added to the Master DB have the table in the Master DB assign each record a "prettier" more usable id number. I know this is stupid but I'm not sure how to relate code to a table (there's no before update event for a table as far as I'm aware).
Thanks for any help you can give.
Stephanie
View 1 Replies
View Related
Mar 11, 2006
I have an orders database using Ac2K. Main tables are:
tblOrders: Has OrderID which is autonumber, key index (no duplicates)
tblOrderDetail: Has OrderDetailID --long integer
Tables are related in one to many relationships on OrderID-OrderDetailID with both cascades set.
Database is split with tables and front end on server. I can't put front end on desktops because users can access from any of over 600 computers in the school district even tho there are not all that many simultaneous users.
Data is input on main form bound to tblOrders. When user puts in required information (Date,Vendor other etc.), I save record and autonumber generates a new OrderID. The subform window then opens and user then inputs the details of the order to the tblOrderDetail. There can be multiple entries here.
Form and subform are linked by their ID fields as master-child.
PROBLEM: System which runs on network with 2-3 simultaneous users is unstable. It works for a while (maybe 10-20 orders) and then crashes. The subform window is blank (never opens).
When I examine the tables directly I found these problems:
1) Two of the OrderID's (autonumbers) are the SAME! They are usually right above and below each other and usually tied to the same user altho other order info may be different. I don't see any data "collision" if it's the same user. I thought this was IMPOSSIBLE.
2) There is an entry or two in the tblOrderDetail with ######### in one or more fields. The OrderDetailID field is blank. I once found the ######### in the tblOrders as well.
3) The key index is removed from the OrderID.
4) When I go to the relationships window either the link is missing or the cascade and integrity are no longer active. If I try to re-establish, I get a message about missing key index or unmatched data. If I then go and DELETE the records with the ##########, I can re-establish the relationship. And it works for awhile again.
5. Compact and repair doesn't fix anything.
6. One user "thinks" she saw a quick message about "time-out". Is there some setting in Access that I need to change (Tools-Options-Advanced?)
ANY ideas would be much appreciated. There is nothing worse than trying to work with a system that works most, but not all, of the time.
View 4 Replies
View Related
Jul 18, 2005
I've read on this forum that memo fields can be a spot of bother. I only saw this after I put several memo data types in my tables. I wasn't going to do that much with them; it was intended as a electronic scratch pad of sorts for the user. Where does the trouble arise from with them? Should I just leave them or should I change them to text fields with 255 chars and hope that's good enough?
scratch
View 1 Replies
View Related
Apr 28, 2006
I have inherited a 5 table database which is associated with membership details and all tables are linked via the membership number which appears in every table..eg
Main Register
membernumber, surname, forename, age etc etc etc
Home address
membernumber, address1, address2, address3, postcode
Business address
membernumber, company name, address1, address2,etc etc etc
Subscription
membernumber, grade, subscription, bank account, etc etc
Training
membernumber, college, results, etc etc
I am informed that it is bad practice to have duplicate data in multiple tables (ie. membernumber field) as updating the data item would be difficult (eg. if the members number needed to be changed for any reason). If this is the case how would I restructure the tables but maintain the link through the membernumber?
I am fairly new to Access....as you can no doubt tell.
View 4 Replies
View Related
Dec 14, 2006
is there a way that I can compare the ID number from a combo box selection and a table... in order to avoid duplicating that same entry?
ie
If me.cbo1.column(0) = [table name].[field] then
me.cbo1.column(0) and me.cbo1.column(1), etc... = vbWhite
End if
What I am ultimatly trying to accomplish is a way to get rid of one selection in a combo box after it has been selected once and used for another record. is the .requery a better method... if it is, can someone please elaborate on how to use that a little more.
View 2 Replies
View Related
Feb 24, 2005
Hello,
I'm making a table with a Yes/No choice many fields which represent subscriptions.
The problem is that the subscriptions available will need to be changed fairly often - will this mean the table and the form associated with it need to be redesigned?
If so, I don't know how to make fields that change depending on a different table.
If this can't be done, I'd be interested to find out how else I could go about this.
Thanks for any help you guys can offer.
View 2 Replies
View Related
Oct 18, 2007
Hey guys.This is a real basic question but I am a newbie at this.:confused:I have created a form in Access that is actually an INVOICE for my company and therefore I have fields like [qty1],[rate1] & [amount1] In the form in [amount1] I have used the following expression to get automatically generated values.=[qty1]*[rate1]This gives me the Amount. But problem is that the result that is automatically generated by this mathematical operation is not stored in my database under the field amount1 and the field is therefore blank. I wish to use this resulted value in a report and also want to use it in other forms e.g. my sales register.kindly help me out with this.:)
View 1 Replies
View Related
May 9, 2006
Hi I have a field on my form called cost of order
I also have a field called deposit on the form that is generated by dividing the cost by 2.
is there a simple way (most probably) to the send this data to my payment table, I already have a deposit filed set up in the payment table I just want to post the data in from the form?
View 5 Replies
View Related
Dec 19, 2006
Is there a way to have access remember a certain time like 2:00 pm and generate a report and send it to a specific person. I know how to make an attachment for sending someone a report but how do I get access to do it on a certain time lets say 2:00 PM?
View 1 Replies
View Related
Apr 30, 2007
This is in regards to my same database file posted in my last thread. Basicly it's a work order database. Each task has an employee and the quality of work they did. 1=Bad, 5=Great...so it's from 1-5. I need to create a report of all the tasks which had a quality score of 3 or below for the previous month. I'm good with SQL, but I can't figure out how these Access reports work.
I want my client to be able to double click on the report and have it generate. It would be nice to have it save to a PDF or Word doc, but that would be a plus. Right now I just need to know how to generate reports based on a criteria. I searched the web for 2 hours and could not find anything about this.
Thanks
View 2 Replies
View Related
Jun 2, 2015
I have a log in screen that my users must use before they can use my db. if they enter the wrong username/password they get the usual message boxes. I also thought it would be a nice personal touch if once they've logged in they get a little greeting, so i made a message box that says "Hello " followed by their first name
This did the trick for a while, however theyre sick of seeing "Hello" and want other greetings, i figured this wouldnt be too complicated and took me only about 10 minutes or so to implement.
Basically i created a table (tblGreetings), has two fields, an autonumber field (ID) and a short text field
I've added the following code into the login sequence:
Dim greetings As Integer
Dim greet As String
Dim randomNum
greetings = DCount("ID", "tblGreetings")
randomNum= Int((greetings - 1 + 1) * Rnd + 1)
greet = DLookup("Greeting", "tblGreetings", "[ID]=" & randomNum)
which in theory should generate a random number between 1 and "greetings" (which is an integer representing the number of greetings ive stored in my table), currently greetings=9
then it should pick a greeting from tblGreetings using the random number as an ID
This more or less works, except whenever the DB is opened and a user logs in, the "random" number is the same every time, it always seems to be 7 (which corresponds to the greeting "G'day").
Why this random number is always the same? it changes if i run the code a few times in the same session, but once the database is closed and reopened it still gives off the number 7, every time
View 3 Replies
View Related
Jun 16, 2007
Hi
I need to know how to avoid from overlapping between times per date when the user is isernt a new data.
I thought to use dllookup function but it finds one result but if there is more than one result.
Date Ent Ext
1/1/2007 8:00 17:00
1/1/2007 9:00 16:00
1/1/2007 10:00 15:00
In this case there is overlapping and I need a code that avoiding this situation.
Thanks
View 1 Replies
View Related
Nov 21, 2006
I have a table similar to this
table
-----
ID
field1
field2
field3
'ID' is the primary key but I want MS-Access to have an eye on field1-3 to avoid entering the same information into field1-3.
How can I do this?
(Using field1-field3 as primary keys is not desirable)
Please help.
Thanks in advance.
xxyy
View 4 Replies
View Related
Jun 13, 2005
Hi all,
I have an append query that I run daily and want to append only records with
different OrderID.
------------------------------------------------------------------------
INSERT INTO tblConfirmSchedule ( OrderID, JobCode, JobDate, Status, [Home#],
[Work #], Ext, [Other#], Ext2 )
SELECT Orders.OrderID, [Schedule Info].JobCode, JobSchedule.JobDate,
JobSchedule.Status, CustNew.[Home#], CustNew.[Work #], CustNew.Ext,
CustNew.[Other#], CustNew.Ext2
FROM (CustNew INNER JOIN Orders ON CustNew.CustomerID=Orders.CustomerID)
INNER JOIN ([Schedule Info] INNER JOIN JobSchedule ON [Schedule
Info].JobCode=JobSchedule.Scheduled) ON Orders.OrderID=[Schedule Info].OrderID
WHERE (((JobSchedule.JobDate)>Now()+3) And
((JobSchedule.Status)="Scheduled"));
--------------------------------------------------------------------------
Any ideas?
Thanks in advance,
Emilio
View 1 Replies
View Related
Nov 28, 2005
2 tables in an Access database
tblPurchases: columns: Index (PK-autonumber)| Store| Product| Cost
data: 1| Staples | Tape | $0.89
2| Staples | Paper | $4.50
3| OfficeMax | Markers | $3.50
4| OfficeMax | Paper | $4.75
5| OfficeMax | Pens | $1.98
tblInfo: columns: Index (PK-autonumber) | Store | Product
The data in the access database tblInfo.Store is queried from tblPurchases.
Here is the query for the rowin my database design view:
SELECT DISTINCT tblPurchases.Index, tblPurchases.Store FROM tblPurchases;
When I am in the database and access the column's pull down, I get ALL records, including duplicates (so OfficeMax is listed 3 times.) How do I get it so I only see
Staples
OfficeMax
in the pull down when in the database?
Thanks!
View 4 Replies
View Related
Aug 15, 2005
I have a form based on a lengthy SQL query.
I have several controls in this form whose control Source has the following formula:
=Nz(DSum("Capital","Capital_Act","DatePart('q', CVDate([Capital_Month] & ' 1')) = 1 And [projectid]=" & "'" & [projectid] & "'"),0)
When I navigate from one record to another record in this form, the values in the controls take a looooong time to populate (most of the time they come up as blank and when I move my mouse over that control - the value gets populated....this is slow !!!).
I believe this is happenning because I am using DSUM function all over the form.
What is the fastest way to display this control WITHOUT using DSUM?
Many thanks!
View 4 Replies
View Related
Dec 13, 2005
Ok, here's the deal. I'm tired of fixing our gigantic table because of corrupt records in my firm's database. I find that the problem ordinarily comes up when someone opens the same record in two different forms (whether on the same computer or on a different machine) and both attempt to make changes. Is there a way (preferably simple) to just LOCK a record in a particular table to keep this from happening? I assume there has to be, but I've just never taken the time to find out. I'm not sure if this locking is done on the form level or somewhere in a table option or something.
View 1 Replies
View Related
Mar 25, 2006
Hi guys
i really hope this is possbile...
i want to have one form, that relates to about 70% of the data in one table. however, the remaining controls will be coming from 3 other tables. i cannot for the life of me figure out how to create a form that will display these controls for all 4 tables on a single page.
every attempt i make with the wizard makes me the form with 3 subforms that the user has to scroll across the page to use. i need to have all my controls pretty much lined up on the page without moving horizontally across the screen to enter data
ive tried creating a form by dragging the fields into it in design view from the primary table, but i cant see how to include (or drag) fields from my other tables into this form
does anyone know of a solution to this problem?
thanks
View 1 Replies
View Related
Mar 21, 2014
I have the follwing three quantities: category, month and value.so I would like a report to place each value in the corresponding row (category) and column (month).using a cross tab query this is quite easy, and I also managed with a simple query, by placing 12 textboxes in the report and using the iif instruction (iif month-jan,value,null and so on for each month).
now the problem arises when i have two values for the same category but in different months. In this case, the report will place the last value in the line below the previous, leaving a blank space.I'll upload an example asap, in the meantime i try to explain it here below:
.......jan..feb..mar..
cat1 10
cat2 30
View 1 Replies
View Related
