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 a to build a slot booking database in which users will be able to book slots (ranging from 1-30) on a particular day for a specific site (location).
When trying to build the relationship between slot in tbl_available and slot in tbl_appointment i get the following error "No unique index found for the referenced field of the primary table" the same error pops up when trying to build a relationship between site in tbl_available and site in tbl_appointment.
I am trying to create a one-to-many relationship between these two tables. I want to be able to access the 3 fields on the [Processors] table within reports based on [AllItems]. [AllItems] is a listing of account activity where the [AccountNumber] repeats. I have every field set as the "Primary Key" on [AllItems] as that is the only way to avoid importing duplicate data. I am getting the error: "no unique index found for the referenced field of the primary table"
Hi, I'm not sure where to post this, maybe VBA or Forms would be better, but I think it's fundamentally an index problem, so I'm posting here. I've searched both the net and here, but most of the questions are about preventing duplicate records.
I want to prevent duplicate records but save changes to existing records.
I have a form based on a table that has a 3-field-unique-index (but not the primary index, which is autonumber). I've written a function to check for existing records with the given combination of the three fields, as long as all have data (it bails if any are null). I'm calling the function from BeforeUpdate for each of the textbox controls. I was also calling it from the form's BeforeUpdate, but commented it out as it seemed redundant. I have other code there verifying that the user wants to keep the changes.
I'm trying to save the users from getting all the way to the end before discovering there's a problem. To test it, I added then deleted one character from one of the three relevant fields in an existing record, and bing, up pops my alert that I'm violating the index. Then I can't leave the field, I'm stuck there because I can't save the record.
I want it set up so that other fields in the record can be changed/updated AND so that a new record for the same person can't be entered (a dupe).
Oh, and I'm running into all kinds of problems with acCmdSaveRecord not being available. I feel like an idiot that I haven't been able to make sense of the posts about that. I can just comment it out and let Access save the record when it closes the form. But my bosses really want the users prompted about saving changes.
Any thoughts or suggestions or insights greatly appreciated. I really tried to search this out, so any tips for better searching are also appreciated.
Here's the code for the function:
Public Function CheckForClientDupes() Dim response As Variant Dim strFamID, strLName, strFName As String Dim strFilter As String
If IsNull(Me!FamilyIDNo) Or IsNull(Me!strLastName) Or IsNull(Me!strFirstName) Then Exit Function End If
response = MsgBox("There is already a client with this combination of FamilyID, Last and First names in this database. Would you like to Continue Anyway (Yes) or cancel data entry and Erase Your Changes (No)?", vbYesNo, "Duplicate Client Alert") If response = vbYes Then ' DoCmd.RunCommand acCmdSaveRecord Else response = MsgBox("You have chosen to cancel your changes. Your changes will be erased.", vbOKCancel + vbQuestion, "Data Entry Cancelled") If response = vbOK Then Me.Undo Else Exit Function End If End If End If
I am building a database to capture monthly statistics on a number of items. I want to ensure that users don't enter statistics for the same item for the same reporting period. I found the following instruction, but can't make it work:
It suggests that I create multiple primary keys in the table
When I do it, it comes back with an error: Index or primary key cannot contain a null value.
To prevent duplicate records, I use multiple fields indexes, which worked fine until now. I learned that each "empty" fields are consider unique by Access, so not the best in an index to prevent duplicate records. I managed to have it worked using the default value property to give each "empty" records the same value.
Now my problem is that I have a date field which is optional, but I need to use it as a unique identifier in an index. I could again use a default value, but since it has to be a date, I find it confusing. I would rather have a default value of "No date specified" or "-" or even better, "". Unfortunately none of those works with date fields.
I have been working on a simple data base for some time now (beginner level) and am still trying to improve it. I would like to do something but before that I would like to have your opinion to know if it is even possible?I have a query QryMainReport:
Start Date/Time End Date/Time Employee
At the moment this is what the format of my report looks like (I removed other unnecessary fields):
StartTime----------EndTime---------------Employee 12/06/2014 01:00--12/06/2014 03:00------John Smith 12/06/2014 04:00--12/06/2014 06:00------Jane Doe 13/06/2014 02:00--13/06/2014 05:00------John Smith 13/06/2014 08:00--13/06/2014 08:00------Jane Doe
I would like to do as a report. (Dates would always be from Sunday to Saturday). I am not sure it is possible to do that. I suppose first it would mean:I would have to do a query to separate the times from the dates?I would have to find a way for Access to find the unique dates and unique names?Does it mean I have to use cross tab queries?
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?
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
