how to prevent user from creating new record if... 2

[kcn]

Hi All,

I'm not sure how to word this, so bear with me!

I have a data entry form, Form A, which has the recordsource, Table A. The user enters data in Form A and then clicks on a button to open Form B. When this happens two values are coppied over to Table B (recordsource of Form B): an ID, and a Year. There's a one to many relationship between Table A and Table B.

I need to prevent the user from entering more than one record in Table B that has the same Year value for the relating ID.

ID YEAR
1 100 2000 OK
2 101 2000 OK
3 102 2001 OK
4 102 2001 Not OK!

I'm assuming I need some kind of code that loops through Table B and checks both fields, and then gives an error message to the user, but I don't know how to write this. Any help would be very appreciated!

thanks!
K

 

[scriverb]

If you setup a multiple field Primary Index for Table B(ID and Year together) then ACCESS will not allow the creation of this situation.

Open your Table B design and open the Indexes window. Here you can add additional fields to the primary index so that you have a multiple field index.

Let me know if this solves your problem.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[jksmi]

Hi, you can create a unique compound key on table B

you can either highlight ID & YEAR in table design mode and click the key icon to make it a primary key or open the indexes editor (in table design mode its an icon that looks like a ligtning flash) type in a name, type in ID & YEAR under fieldname and down in the bottom left change Unique to yes

Hope this helps, Jamie

 

[kcn]

Hi,

Thanks to you both, I did learn something but it doesn't solve my problem, because it doesn't stop the user from opening the new form and entering lots of data before getting that error message.

As I have it currently designed, the user clicks a button that opens Form B and copies ID and Year into new record in Table B. The warning doesn't show up until the user wants to close Form B.

thanks!

 

[jksmi]

Hi, in that case you could check the values from form A before opening form B - using a dlookup would be simplest

If IsNull(DLookup _
    ("Table B", "ID", "ID = " & me.[i]YourIdControlName[/i] & " AND YEAR = " & me.[i]YourYearControlName[/i])) Then
    DoCmd.OpenForm "FormB"
Else
    MsgBox "Year for that id exists or something..."
End If

 

[kcn]

Hi,

Well, I'm not sure if it's the use of Null or what, but the if statement didn't work. However, after playing more with data entry your original suggestions are working (though not as smoothly as I'd like) so I'm not too stressed!

Thanks again for your help.

k.

 

[scriverb]

kcn: I am glad that you got a workable solution to your problem but the DLookup function should work. Sometimes the syntax of the criteria portion of the statement is a little difficult. It usually has to do with type mismatch and the use of nested quotes in the criteria string. Why don't you post the code that you were trying to use here and let us help you get it to work. jksmi's suggestion to execute the checking before you open the form really is the correct way to do this.

Post back with a copy of the code behind your button and we can surely get it working for you.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[jksmi]

Hi, one thing that springs to mind is that I assumed that both ID and YEAR are numbers - if they're not then it will need to look a little different, that is you need to add quotes or apostrophe so that Access can match strings and hash marks (#) to denote a date, i.e.

IsNull[purple]([/purple]DLookup [purple]_
    ("Table B", "ID", "ID = [highlight #FF99FF]'[/highlight]" &[/purple] me[purple].[/purple]YourIdControlName [purple]& "[highlight #FF99FF]'[/highlight] AND YEAR = [highlight #FF99FF]#[/highlight]" &[/purple] me[purple].[/purple]YourYearControlName [purple][highlight #FF99FF]& "#"[/highlight]))[/purple]

Hope this helps, Jamie

 

[kcn]

Thanks for sticking with me on this. Here's the code. I did add a "'" to refer to YEAR as text field. I now get a syntax error.

*************************************************************
If IsNull(DLookup("ProjectID", "tblProjectYear", "ProjectID = " & Me.ProjectID & " AND YEAR = '" & Me.TxtSurveyYear)) Then

DoCmd.OpenForm "frmProjectSurveyYear", , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec
Forms!frmProjectSurveyYear.ProjectID = Me!ProjectID
Forms!frmProjectSurveyYear.Year = Me!TxtSurveyYear
Forms!frmProjectSurveyYear.Community = Me!Community

Else: MsgBox "You already entered a survey for this year!"

End If

 

[scriverb]

You need a single quote before and after the control reference for the Survey Year. ( See [red]Red[/red] Code )

If IsNull(DLookup("ProjectID", "tblProjectYear", "ProjectID = " & Me.ProjectID & " AND YEAR = '" & Me.TxtSurveyYear [red][b]& "'"[/b][/red])) Then

Post back with any problems.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[kcn]

This worked!
Thanks again for all your help.