I have an Access 2002 database with a relationship between 2 tables I want to delete using VB code. I setup the relationship from within Access. Is there a way to find out the name of the relationship, if there is such a thing. The tables are: TableA.Id linking to TableB.BankAcctId with both cascade functions. What would the VB code look like? Something like ALTER TableB DROP CONSTRAINT ...
Thanks
In Oracle, you can set up table constraints which constrain data accross mutliple attributes/fields. Can you do this in Access?
For example. I have:
OrderLine(orderNum,orderLineNum,[productNum],date)
I dont want to allow for the same product to be put on one order twice. Therefore, (orderNum,productNum) must be unique, but productNum is not a part of the primary key so it can be repeated in every row for all the database cares. :P
How can I set this up.
In Oracle I would do something like:
CONSTRAINT chk_prod CHECK UNIQUE (orderNum, productNum)
Thanks for any help.
I have a table with the following fields:
format
region
rarity
they are all set to lookup value lists:
format: tape; dvd
region: 0;1;2;3;4;5;6;7;8;pal;ntsc
rarity: 1;2;3;4;5
I have a check that makes sure (format, rarity, region) is unique.
It works fine until i set region to either "pal" or "ntsc", then I get the following error.
"Error 'Error evaluating CHECK constraint. ' in the validation rule."
I set up the constraint by going to table design view -> indexes -> created new index with each of the three fields listed one after another -> set to primary=no, unique=yes, ignore nulls=no.
Can anyone help me work out why im getting this problem?
Hi,
I have developed a small hotel booking system which inserts records into a reservations table. The table looks like this:
[ReservationNr][StartDate][EndDate][RoomNr]
I am now trying to add a constraint which prevents a room from beeing booked twice on the same day, hence the time period defined by StartDate and EndDate in a record must not overlap with another record.
Is Access capable of adding such a constraint?
It was easy to add a constraint that prevents a booking where the StartDate is before the EndDate, but this one needs to reference already existing records.
Any help would be much appreciated.
Thanks
Hi everybody .I created a bounded form that allows user to enter the following fields information
to the sql server db that is linked to access .The fields are: Projectno, taskcode, taskdiscription
and total hours worked.
I want for example when a user tries to enter total hours work for a user (Hours in hours worked table) and
if he did not already enter wages of the employee for that particular project (amount in hourly wages table)
that user get a massage prompt and ask user to enter amount in to hourly wages table first. Possibly opening that form.
I be happy if some one help me add this feature to my form. Thanks
http://i5.photobucket.com/albums/y180/method007/constraint.jpg (table reletionships pic)
Hi guys i got a form and sub form. When i leave the field hours empty in the sub form and try to click on close or some where in the form i get this error:
MsgBox "Primary key violation", , "Explanation"
instead of
MsgBox "Violation of Not Null constraint", , "Explanation"
i want to trap not null error and prompt the user that he has to enter value for hours field but aparently it does not. i be happy if some one help me fix this error .Thanks
picture of form in action:
http://i5.photobucket.com/albums/y1...tnullerror1.jpg
http://i5.photobucket.com/albums/y1...tnullerror2.jpg
code for on error :
Code:Private Sub Form_Error(DataErr As Integer, Response As Integer)Select Case DataErr Case 3162 MsgBox "Violation of Not Null constraint", , "Explanation" Case 3146 MsgBox "Primary key violation", , "Explanation" Case 3155 MsgBox "Trigger violation: 'Wages of the employee in this project is not existed. You must enter data of hourly wage first'", , "Explanation" Case Else MsgBox "Other errors" End SelectEnd Sub
Hi all. I want to check for not null value constraint for all tables in database . I tried the following but when i run it i get the following error:
Compile error:
Invalid Next control variable reference
I be happy if some one one help me fix this error.if i remove the bold lines the program workes well but it does not put not null for table fileds that requries value.
picture of the output without the bold part
http://i5.photobucket.com/albums/y1...007/notnull.jpg
Code:Option Compare Database''This module displays field name and type in a massage boxFunction ShowFields(pTable As String) As StringDim db As DatabaseDim rs As RecordsetDim I As Integer''Dim j As IntegerDim n As Integer''Dim NL As StringDim strHold As String, ST As String''Dim x As Variant''NL = Chr(13) & Chr(10) ' Define newline.Set db = CurrentDbSet rs = db.OpenRecordset(pTable)n = rs.Fields.CountReDim x(n - 1, 1) As String'''ST = "Create Table " & pTable & vbCrLf''adding Create table and table name to statementST = "Create Table " & pTable & vbCrLf & "("For I = 0 To (n - 1)For Each fld In tbl.Fields ST = ST & rs.Fields(I).Name & " " & FieldType(rs.Fields(I).Type) & "," & vbCrLf If fld.Required = True Then ST = ST & " NOT NULL" & " " Else ST = ST End IfNext Irs.Closedb.CloseSet db = NothingShowFields = ST '' returns the fields name to main functionEnd Function
on click even code
Code: For Each T In db.TableDefs '''Skip the system tables If Left(T.Name, 4) <> "MSys" Then ''' this line determines the primary key of the table ''' by calling GetPk function from module pk = Left(GetPK(T, db), InStr(1, GetPK(T, db), "<-") - 1) cont = cont & ShowFields(T.Name) & vbCrLf & " primary key " & "(" & pk & ")" & vbCrLf & ")" & vbCrLf End If Next T ''' This line of code post the generated table statment to a massage box MsgBox cont
Can anybody help me understand why I get the error
"Syntax error in CONSTRAINT clause"?
I get it in Access 2003 and Access 2007.
Both are clean installs with no add-ins
Running this code in VBA, or pasting the SQL directly in a query results in the same error.
Sub test()
sTableName = "Test"
sSQL = "CREATE TABLE " & sTableName & "_Config (" _
& "[idConfig] Int Primary Key," _
& "[Config] Memo," _
& "[Instrument] int," _
& "[Serial_No] Text(25)," _
& "[Firmware] int," _
& "[Orientation] int," _
& "[Sensors] int," _
& "[Sensor_Size] float," _
& "[Sensor_1_Distance] float)"
DoCmd.RunSQL sSQL
sSQL = "CREATE TABLE " & sTableName & "_Leader (" _
& "[idLeader] Int Primary Key," _
& "[idGroup] int," _
& "[idFile] int," _
& "[idConfig] int," _
& "[DateTime] DateTime," _
& "[Heading] float, [Pitch] float, [Roll] float," _
& "[Pressure] float, [Depth_BSL] float, [Height_ASB] float,"
_
& "[Min_Valid_Sensor] int, [Max_Valid_Sensor] int," _
& "[ASM_Bed_Level] float," _
& "CONSTRAINT [FK_Test_Leader_idConfig] FOREIGN KEY ([idConfig]) REFERENCES [Test_Config] ([idConfig]) ON DELETE CASCADE ON UPDATE CASCADE" _
& ")"
DoCmd.RunSQL sSQL
End Sub
The first table is created just fine.
And if I leave the constraint clause out, the second table is created also.
If I try to run it as an SQL query right in Access, after the error is delivered, it highlights the word DELETE, and if I reverse the update and delete portions, it highlights the word UPDATE.
I have looked up the clause in Access help and even using their example, i get the same error.
Any help will surely be appreciated!
I am developing a simple database for students to register courses using Microsoft Access 2010. I have 4 tables: Student, Register, Course, and Section (1 Course can have many Sections).
Then, there is a criteria that a student can only take up to 6 courses. Hence, may I know that where and how can I include this constraint? I believe that it should be in the Macro (Before Change) of the table Register...
My relationship is: Student -> Register <- Section <- Course
Error: Error evaluating check constraint in the validation rule
What does this mean?
I have used a combo box in the table to input into 2 fields - one restricted to numbers 1 to 5 and the other restricted to letters A to E
Message comes up when I try to change the view or press submit. Does it have anything to do with the field type?
Is there anyway that access could create a drop down box within a drop down box? For instance, when a selection is made in a drop down box, it opens another drop down box with choices.And is it possible to link an inserted image from a form to open in MSPaint that when it opens, the image is already there?
View 1 Replies View RelatedI have a drop box that has four selections from a table (NORTH, EAST,SOUTH or WEST) I also have a another table that has two fields which is a NAME of a person and either NORTH, EAST,SOUTH,WEST depending on the persons name. What I need to happen (within a form) is that when I select NORTH from a drop down box, I can then select names in another drop down box that correspond to NORTH only. Then only names from the South list if I select SOUTH etc
View 2 Replies View RelatedOk, This is something I have long wanted to do, but cannot figure out.
I am working on a database for a company that orders a large number of Items. I have these all in one table (itemCode, itemDescription, price, categoryCode, etc). I have another table with categorys (categoryCode, categoryDescription) . Each Item is asigned a categoryCode and the appropiate relationship exsists. Right now I have a comboBox in a form that when selected opens a drops down menu with all the Items. Then another one auto loads up (continuous form) and I get the same drop down list of all the Items.
What I would like to have is a combo box then when it is selected shows the categorys, then based on that selection, I see all the Items that are in that category.
I guess another way of saying this simply is, I would like to create a drop down menu inside of a dropdown menu.
Is this possible? Does anyone have another alternative suggestion that might work well? I'm relectant to use a list box, becoase ideally it would be a drop down menu inside of a drop down menu as seen on many websites.
Thanks in Advance!
:)
A group of us are working on this database for a girl scout troop and we were asked to create drop-down boxes for the parent information and doing one for the bus information. We are a bit clueless right now so I was wondering if anyone could help us out. I have added database Its called project.mdb
This is the link for the database
https://s51.yousendit.com/d.aspx?id=0D6D0LHFP3X551Y4BT5GC5DK3T
This is my e-mail
absolut_producer@hotmail.com
I have a form with some drop down boxes
The form is based on a table
One field is lastname and one is firstname
The row source is based on table/query
The lastname field works fine but the firstname brings in the lastnames
What am I doing wrong
Hi Guys,
I have a combo box on one of my forms that on clicking the arrow the user has the option to either input yes/no. However i have just noticed that the user doesn't have to input yes or no and can actually put whatever they want in it (which sort of defeats the object of the combo box!!)
Any ideas guys/gals??
regards,
Lee
My database is set up with a sub form showing:
Mfg, Model, Serial#, and Type. When I drop down and choose the Mfg, I then go to the next drop down (Model)and the only choices I have are those associated with the Mfg I just selected from the previous drop down. On my Enter Repair Data form, I want to be able to drop down a box for Part Number and then have a drop down field for the Part Description to match the part number that i just selected. Then I would like to have the next drop down to show a unit price for the Part Number and Description fields that I just entered. Is it possible to accomplish this with an existing Data Base? My data base is 1.61M so it's too large to upload here, but if someone can help me my ICQ # is 65-489-369, e-mail is huntiir@bellsouth.net. I can upload a copy of my database if needed.
I also need to have a field added to another report in my data base sowing the total labor for units shipped on a particular date to a particular customer. The report already totals parts used in the repair of the units for that date. I just can't seem to get the total labor to show up.
I was also trying to set up an inventory Report on parts used for my repairs where it will calculate the number of parts in stock in the form of:
RR#, Part#, Description, Unit Cost, and Total Units in Stock. You can see that I am somewhat complicated and ignorant to the use of Access.
Can you help me out please?
Hello,
How to do drag & drop in a form ?
I would like user click a field on a listbox and place it with the mouse in a field of the form as you can see in windows explorer to move a file from a place to an other.
I tried with mouse events but i don't obtain exactly what i want.
I this possible to do that ?
Thanks in advance.
Currently the database has a parameter query in which the query is entered via a pop up box, it would be preferred if the query could be selected from a list of criteria in a combo box and run via a command button.
Any ideas would be much appreciated.
Regards
Hello Everyone. I'm not all that new to access however my problem does exceed my experience and I'm hopeing someone out there maybe able to help me.
A while back I began to build a database to keep track of post production activites such as what items were being produced on a perticular production line. Its my job to book all these items in according to date, line, item, order and qty.
This was all good and simple until it was decided that I had to assign a category to each item. Their are multiple models to one perticular category and to make things even more difficult the same item could be produced on more than one production line.
I work within a Pool company where their are 7 production lines, each with their own purpose and facilites of making certain products until you come to other products that could be produced on any of 3 or more lines.
So, I made a table 'Category' which lists the different types/acronyms for products and the lines in which it could be produced on.
I than created a new column within my Post Production table called 'category' and chose to use a drop box so I may select which category to assign the item. The drop box shows both columns 'Catefory','Line'.
The problem I have is that theres 40+ rows within the Category drop box and because the same name of the category may appear more than once simply beginning to type in the category name isn't good enough.
Is there a way to filter out which categories is listed within the drop box according to which line it maybe coming off once I've already selected which line within the same row?
I've tried selecting a relationship and createing a query both failed.
WarrenG.
I am creating a table in Access 2003, and I need to add a drop down list to one of my columns. See the attached picture - I need to add a drop down list to the "Division" column that will allow the user to select from one of 5 choices. How do I do this?
I'm an Access beginner, BTW. :)
I am building a query for a university and the table i am working from has a column with every course run by the department.
How do i create a query that allows me to add a drop-down menu to the course instead of having to write out the course name everytime i want to query the number of pupils on a particular course.
Hi there.
I work for an Internet Marketing company and my boss wants me to design a huge huge form in Access - requiring alot of Drop Down Menus. Alot of them are simply Yes/No questions, some, however, are not.
This is what I have done so far:
I have a field called Sector which I have selected as a Yes/No Field.
In the Form Design View I have selected:
Row Source Type = Field List
Row Source = Sector (this is a separate table in which I have put all the options)
In Format, instead of Yes/No, I have changed it to "-" - this is simply because when I view it Normal View the first thing in the Drop Down Menu is a Yes or a No.
This all seems to work fine until I try to select one of the options and I get this error:
"The Value you entered isn't valid for this field - For example, you may have entered text in a numeric field or a number that is larger than the FieldSize settings permit"
I have gone back and forth into the Design View on the Form and the Table trying different things, but nothing I do seems to work.
I don't even know if I have gone about doing this Drop Down menu in the correct way to begin with.
I'm not an Access Expert so please if anyone has any ideas please let me know. It'll be much appreciated :)
Thankyou.
I have developed a small database (attached) but want to jump to the recorde selected. Anyone got any idea how I can achieve this - it seems to be 1/2 working, but I cannot figure out the rest.
Cheers
When I get focus of a combo box I do an automatic dropdown but when I select an item from the list the dropdown doesn't close so it covers my other fields until I click randomly anywhere on the form. How can I get the dropdown to close as soon as I make my selection from the list?
Thanks
Apologies if this has been answered before.
I am an inexperienced Access Database writer who wants to learn more - so first question, are there any Access Gurus' near to Buckingham/Milton Keynes UK that would be willing to spend a couple of hours showing me advanced access programming with a specific databsae in mind (i have a more complicated one than the one attached to attempt) (paid of course).
Secondly, I have a form which I would like to finish this week and am struggling with. The main form is frm_PatientDetails. I am trying to add a subform frm_Practitioner which shows a drop down box of the practitioners names, but allows you to choose more than one. This is a many to many relationship as a patient can have more than one practitioner and a practitioner has more than one patient. I have had a go at creating a junction table, and a query, but got a feeling i'm doing it all wrong.
I've attached the database, so any other feedback on what i've done right and wrong would be much appreciated.
Thanks in advance for the help.