I have a Table that lists the Members of a local football League. The Table contains Surname, FirstName, Address, DOB, Club and Age.
The table contains approx. 2,000 entries currently.
In order to stop Players re-registering with another Club, I wanted to have a message come up when the same Name & DOB were entered into the table.
I used a multiple-field index on the table covering, Surname, FirstName & DOB.
To see if this worked I typed in a Player already on the db but the system let me continue after all details were entered.
I have followed the Help in MS Access. I tried a quick sample with just 3 names in it and this worked perfectly, but now all of a sudden I have hit a snag.
I opened the table in design mode, pressed the Indexes button and proceeded to select the 3 fields required with the name of the Index in the Index Name only on the first line. Am I supposed to insert a new line in the Table or highlight something prior to doing this?
Could somebody please advise as to what I may be doing wrong?
Add and Index named Location to the tblGuest table that consists of the StateProv and City fields and uses the default property settings, Delete the PostalCode Index, save then close the table.
I have looked up all info in the text reffering to Indexing and cant seem to come to a conclusion of the way to complete the underlined area area. For the class they provide you with the files and in this particular file the primekey is Guest ID with a field value of GuestID. There are two other Indexes PostalCode with field value PostalCode and GuestID with field value of GuestID.
I created a help file for a using a db I created, though I am thinking it would be nice to have it "look more professional" something similar to the Contexts and Index help option found in IE.
I am think a form would be the easiest to do with a subform for each element in the contents, so when the user clicks on an element on left form (which is the contents) the detail of that page appears in the right form the subform (perhaps just using the visible property)
There are tab options for a form so I can use that to make the contents, Index, search, favorites
some questions come to mind though... 0) how would I create collapsible menu (like a tree menu) see the contexts and Index help option found in IE. 1) how to create a search field to look for anything in the db except the code (i.e. part of a record, a description of the record, etc.) ? 2) how would I create a favorites section (a place to save help topics viewed) ? 3) how do i create a navigation system (back and forward through the subforms) - it seems I would need to track which forms are displayed and when then use that to navigate back and forward ? 4) how create various options such as stop or refresh search queries, and perhaps turning on/off select highlight
Finally is there something similar to this already out there that I can just customize various options off of, perhaps even a tutorial.
thanks for any direction you might be able to provide.
When I load a spreadsheet I can't add an index because it is a linked table, so I want to save it in MDB format so that I can add an index and then export it to SQL. However, when I save the file as an MDB file the data is all still linked as a spreadsheet and I still can't add an index. (I would add the index at the last step in SQL, but Pervasive SQL is very temperamental.)
Basically I just want to export an Excel spreadsheet into SQL and add an index.
Can anyone tell me why access puts the primary key field in the index list twice for the same table? For example i have a primary key called CustID, when i go into design view and click on the index button on the toolbar, i see it listed twice, once as primary key and then again as its own field?! I dont see the reason for this but often times with many of my databases i see this happen to many of my tables. :confused:
Have the situation where a database backend is on a number of sites and if changes have to be made to the design structure of the backend database I would like to run a VB generated report to check that all indexes are correct. While this is not an issue with single field indexes, I have issues with multi field indexes.
Don’t Understand why , tab index change’s All 55 fields are in the same order as the tables. The field is custname the frist fiels. Go to my Customer form Thery are 55 fields, The frist field is ( custname ) I go to the properties Then I go to tab index And I type in 1 Then I exit. Save form Then I open the form and the cursor is not on the frist field ( custname ) I go back to the properties And back to tab index Now it is 44 I have don e this procedure several times. And the tab index changes sometimes it is 44 , sometimes it is 49 And other times it is 36. How can I keep the fields in the same order that I set up the tables..??? What can i do about this. John527
I don't know why, but my Microsoft Access 2000 Help's Answer Wizard and Index Tabs are completely empty! These pages are completely blank, with no dialog boxes or anything. How can I fix this?
Can't figure out why I can't create a one to many relationship between two tables:
TableA uses a composite key as its primery key (field1, field2). The table has a unique index comprised of these keys. The index even has a name. The table also doesn't contain any duplicate information, so the fields comprising my Primary Key are unique. The table I'm joining tableA is unpopulated at this time.
I am trying to set a multiple field index on a table. I would like to use 3 fields. One is a text field, the other two are date fields. The text field and one date field will always have a value. The other date field can either have a value or no value. The index will work if I only use the text field and date field that always have values. If I try to add the third field, it will not find a duplicate record. Can I not include a field that may have a null value?
Hi, To make a long and tedious story short: Does anyone know why Access (2003) creates an index called PrimaryKey and an index called User_ID when I'm in the table design view and ask it to make the User_ID field the primary key?
After years of database development I've only just noticed this. Not normally not a problem but for this: Use ADOX to find the field name of the primay key for a table. I took the code right out of a Access 2000 developers handbook. It creates a collection of the table indexes and steps through them one at a time checking if the PrimaryKey property is true. It hits the index called PrimaryKey and says 'yes it's true, this is the PK', I then go on to use the index.name property in an SQL Select to create an ADO recordset. The only thing is that the index called PrimaryKey is not a field name and the field name User_ID is not a PK :eek: I go into the indexes list in table design and delete and re-create the indexes and PKs as I want and the code works fine.
So, again, why does Access do this? I'm so confused! :confused:
I have a problem with indexes in tables. I made an inventory DB where we can store items on several locations. Their are several difference items, each kind of item has his own table because they all have different fields that the user has to fill in. The only thing they have in common is the place, they're all stored in the same room, which has numbered racks and each rack has numbered spaces (for example: Sample X is in Room 1, Rack A, location B) The combination of Room-Rack-Location has to be unique, this I can do in one table by using indexes, but how can I combine the indexes of several tables? Do you think it is better to keep the track of place inside another table but how can I do this?
i have 2 tables, P.O and Invoice. In PO tbl i have an Invoice field with Index set to Yes (No duplicate) On a form based on PO table, I couldnt enter a new record due to this so i set the Index back to NO Now..I have a combo bound to PO table with a query taking a list serial number from another table. I want to filter away whatever serial numbers from this list that have been added to PO so i write this SQL "SELECT DISTINCTROW tblComputer_Cabinet.[CC_Serial #] FROM tblComputer_Cabinet, tblPO WHERE (((tblComputer_Cabinet.[CC_Serial #])<>[tblPO].[CC_Serial #])) ORDER BY tblComputer_Cabinet.[CC_Serial #];" which worked fine before i removed the index
I have a form that has tabs on it. I have a list box on one of the tabs linked to a query, trying to show only those records that go with the person's name on the header of the form.
Example in query:
=[forms]![frmContacts]![Page]![4].[id] -- [id] is the person number. This is were I get confused on how to identify the correct tab and make the data only show up for that person.
i have this form in microsoft Access which contains the fields refno name cpr and o combo box to choose the payment and add button , refno is the primary key....
when i open the form the ref no automatically appears and when i fill in the fields and choose one of the chosses from the combo box i get this message error Index or primary key cannot contain a null value where is the eroor exactly?
Every time I change me Tab Index, for my work order, when I save and restart the Tab Index is in a different order. It seem it changes back to A DEFAULT ORDER.I set the labels to "0" and then set the fields in the Tab order I want.
I am currently trying to insert a record into my database. The fields in my table are RecordID, BillNumber, CongresspersonID, and Vote. RecordID is the primary key and its data type is Autonumber. It says the primary key can't be null, but it's not! It wouldn't let me upload the database because it says it's too big. This is my code:
PrivateSub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.ClickcongressCommand.CommandText = "Insert into votingRecord(BillNumber, CongresspersonID, Vote) Values('" & ComboBox1.Text & "', '" & TextBox1.Text & "', '" & ComboBox2.Text & "')" Dim numOfRecordsChanged AsInteger = congressCommand.ExecuteNonQuery()
