Allow only 1 Year selection for each main record

[andy_dyer]

Hi,

I have a database that has a table for Project data (tblProject) which is linked via a foreign key ProjectID to a table for financial years (tblFinanceYear) which is in turn linked to a table for Year (tblYear) through a foreign key YearNameID.

There must be a way of doing this but cannot think of the right combination or words to search for it correctly...

For each ProjectID I want there to be the option to only have one instance of YearNameID and to error saying this year already exists...

Is there a way of doing this?

 

[andy_dyer]

Thanks Pat - but with my structure...

tblProject - Primary Key ProjectID

tblFinanceYear - Primary Key FinanceYearID & Foreign Keys ProjectID & YearNameID

tblYear - Primary Key YearNameID

Where do I put these indexes?

 

[andy_dyer]

Sounds like you need a composite (compound) unique index.

1. Open the table in design view (in the BE if it's split).
2. Then open the indexes dialog.
3. On the first blank line, enter a name for the unique index.
4. Select the first field for the composite index.
5. In the bottom section that shows index properties, select unique.
6. On the next line, skip the index name field and enter the second field name.
7. Save

The indexes already exist on the middleman table (tblFinanceYear) which is the link table but if I simply go to the YearNameID and make it unique then it
only allows one Year in the whole database...

If i try and add another year with the same name to either the same project or a different one it allows me to go through the motions and looks like it has taken the data but doesn't present any error but simply clicks off an autonumber on FinanceYearID...

I've got a cut down version of my database in 2003 if it helps anyone to help me...

 

[andy_dyer]

Still stuck on this - anyone able to make a suggestion?

 

[andy_dyer]

Thanks Pat - I've now got that working and it will allow me to use the Year 2012 in different projects but not allow more than one 2012 in the same project... BUT...

It still doesn't error - how can I get it to check if the year 2012 (for example) already exists for that project and then instead of closing down the year detail form (frmFinanceYearDetail) and looking like it has accepted it it gives me an message box...

Ideally I want a box that states that the year already exists and gives the option to either change year or to cancel input which clears everything and closes the form down...

This level of coding is way above my head!

Thanks so much for your patience and help

 

[andy_dyer]

I don't see the purpose of tblYear. Why wouldn't you just use the year value instead of creating a separate table?

That it because I had no idea this existed or any idea how to use it - I'm a complete VBA newbie...

Hence setting up tables for everything from Yes/No to Years... those I at least kind of understand...

 

[andy_dyer]

Did you review ALL your indexes? You have several that should not be there and one of them may be marked as unique and is therefore causing the problem.

I'd reviewed my indexes on the tables involved in this process and they looked fine and tblFinanceYear looked like your picture...

I've also now gone through my entire database and removed any rogue indexes that don't add any value and I still have the same issue...

It still stops two instances of the same year being added to the same project but doesn't give any error... Just 'dirties' the tblFinanceYear and uses up an automnumber...

 

[andy_dyer]

Hi Pat and also anyone else who might be able to spot the error of my ways...

Any idea why it would allow two entries even though it only stores one for a 'unique' index?

Thanks

 

[boblarson]

It still stops two instances of the same year being added to the same project but doesn't give any error... Just 'dirties' the tblFinanceYear and uses up an automnumber...

The Unique Index isn't going to have any effect UNTIL the form's BEFORE UPDATE event when it goes to write the record, then it should error out. If you want to do the check you will need to check in the form's BEFORE INSERT event. Then you can cancel if it already exists and not use an autonumber (but be aware your year selection must be the first thing to be selected so that it can check as the Before Insert event will fire for the first thing that is added.

So, like this:

Private Sub Form_BeforeInsert(Cancel As Integer)
   If DCount("*", "TableNameHere", "[YearField]=" & Me.cboYear & " AND [ProjectID]=" & Me.Parent.ProjectID) > 0 Then
      Cancel = True
      Msgbox "This year already exists for this project."
      Me.cboYear.Undo
  End If
End Sub

Oh, and I was assuming you had a parent form for the projects and then had a subform for the yearly items. But I could be wrong so it would probably still be useful to upload a copy like Pat suggested.

 

[andy_dyer]

Thanks Pat & Bob

Attached is a sample of my database which replicates my problem..

I've tried to insert Bob's code and now I get an error on the form opening let alone when trying to close...

Any help gratefully received

 

[boblarson]

First off, why do you have

Private mrstClone As Recordset

instead of

Private mrstClone As DAO.Recordset
or
Private mrstClone As ADODB.Recordset

You need to disambiguate. From your code usage it looks like it should be

Private mrstClone As DAO.Recordset



As for your code. I have run into a snag because I'm getting an error which usually means some corruption. I will need to deal with that.

 

[andy_dyer]

Too many bugs. Forms are too confusing. Forms are too tall for a laptop screen and there are no scroll bars. I have no idea how to cause the problem.

PS, column names should not contain embedded spaces or special characters. Improperly formed names become a real pain with VBA.

Hi Pat - sorry the forms display fine on my laptop but I'm running 1600 x 900 pixels which may be smaller than yours... I'll repost with scroll bars added to all forms to make it easier to see

I'm not sure about the confusing comment - that may have been caused by me cutting down my main database to amke this sample one so it doesn't flow quite right.

To replicate the issue

Click "View All Projects" button - it opens on Bob1 project
Click on "Finance Years" button - it opens the yearly budget for Bob1 (it has 2011 and 2012 in there already)
Click "Add New Project Finance Year" button - it opens the annual detail form (previously with an error with me using the BeforeInsert code recommended - but I'll comment that out and repost below)
Then if you choose to add 2013 from the finance year combo box and save and return it updates the summary table
If you choose add another new year and now choose 2011,2012or the newly added 2013 you get no error but also when you look in tblFinanceYear there is no entry...

Does that help? Thanks again

 

[andy_dyer]

First off, why do you have

Private mrstClone As Recordset

instead of

Private mrstClone As DAO.Recordset
or
Private mrstClone As ADODB.Recordset

You need to disambiguate. From your code usage it looks like it should be

Private mrstClone As DAO.Recordset



As for your code. I have run into a snag because I'm getting an error which usually means some corruption. I will need to deal with that.

Thanks Bob - in the new upload i've made that change and also commented out the beforeinsert code that was erroring. Let me know what you think?

 

[andy_dyer]

Still unable to get this solved... so far none of my users have tried to do this but I'd like to get it solved as I know it's a potential problem...

Any ideas??